Lab Working with Parameters

In this lab you practice using parameters in Command objects. You will pass parameters to stored procedures as well as SQL statements.

► Exercise 1: Creating and Executing a Parameterized SQL Statement

For this exercise, create a form that executes a parameterized query by allowing the user to enter a value into a TextBox that will be passed to the database as the parameter in a query.

1. Create a new Windows application and name it ParameterizedQueries.

2. Add a TextBox to the form and set the following properties:

3. Add a second TextBox and set the following properties:

□ Name = ResultsTextBox

Add a button and set the following properties. Now, the form should resemble Figure 6-3:

□ Name = ExecuteSqlButton

Figure 6-3 Form with controls in preparation for executing the parameterized SQL statement

5. Double-click the Execute SQL button to create the button-click event handler and switch the form to code view.

6. Add references to the System.Data and System.Data.SqlClient namespaces.

7. Add code to create a connection on the form.

At this point, your form code should look like the following (substitute a valid connection string for the NorthwindConnection):

Imports System.Data Imports System.Data.SqlClient

Public Class Form1

Private NorthwindConnection As New SqlConnection _

("Data Source=<ValidServerName>;Initial Catalog=Northwind;Integrated Security=True")

Private Sub ExecuteSqlButton_Click _

(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles ExecuteSqlButton.Click End Sub End Class

using System;

using System.Collections.Generic;

using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient;

namespace ParameterizedQueries {

public partial class Form1 : Form {

private SqlConnection NorthwindConnection = new SqlConnection

("Data Source=.\\sqlexpress;Initial Catal og=Northwind;Integrated Security=True");

InitializeComponent();

private void ExecuteSqlButton_Click(object sender, EventArgs e) {

8. Add the following code to the ExecuteSqlButton_Click method to create a new command object and set it to the parameterized query:

' Create a new Command object

Dim CustomersByCityCommand As New SqlCommand

' Set the command properties

CustomersByCityCommand.Connection = NorthwindConnection CustomersByCityCommand.CommandType = CommandType.Text CustomersByCityCommand.CommandText = & _ "SELECT CustomerID, CompanyName, City " & _ "FROM Customers " & _ "WHERE City = @City"

// Create a new Command object

SqlCommand CustomersByCityCommand = new SqlCommand(); // Set the command properties

CustomersByCityCommand.Connection = NorthwindConnection; CustomersByCityCommand.CommandType = CommandType.Text;

CustomersByCityCommand.CommandText = "SELECT CustomerID, CompanyName, City " +

"FROM Customers " + "WHERE City = @City";

9. Add the following code below the previous code (but still within the event handler) to create the parameter and assign it to the command:

' Create the @City parameter

Dim CityParameter As New SqlParameter

' Set its name and data type CityParameter.ParameterName = "@City" CityParameter.SqlDbType = SqlDbType.NVarChar

' Since the city column in the database allows ' null values we can set the IsNullable property ' to allow null values. CityParameter.IsNullable = True

' Add the parameter to the Commmand object CustomersByCityCommand.Parameters.Add(CityParameter)

// Create the @City parameter

SqlParameter CityParameter = new SqlParameter ();

// Set its name and data type CityParameter.ParameterName = "@City"; CityParameter.SqlDbType = SqlDbType.NVarChar;

// Since the city column in the database allows // null values we can set the IsNullable property // to allow null values. CityParameter.IsNullable = true;

// Add the parameter to the Commmand object CustomersByCityCommand.Parameters.Add(CityParameter);

10. Now add the following code that will set the value of the parameter to whatever is typed into the text box, set the code to run the query, and display the results in the ResultsTextBox. (Add this code below the previously added code but continue to keep it within the event handler.)

' Set the parameters value to the ' the text in the CityTextBox CityParameter.Value = CityTextBox.Text

' Create a StringBuilder to store the results of the query Dim results As New System.Text.StringBuilder

' You must open the connection before executing the command CustomersByCityCommand.Connection.Open()

' Assign the results of the SQL statement to a data reader

Dim reader As SqlDataReader = CustomersByCityCommand.ExecuteReader

While reader.Read

For i As Integer = 0 To reader.FieldCount - 1 results.Append(reader(i).ToString & vbTab)

Next results.Append(Environment.NewLine) End While

' Close the data reader and the connection reader.Close()

CustomersByCityCommand.Connection.Close() ResultsTextBox.Text = results.ToString // C#

// Set the parameters value to the // text in the CityTextBox CityParameter.Value = CityTextBox.Text;

// Create a StringBuilder to store the results of the query System.Text.StringBuilder results =new System.Text.StringBuilder();

// You must open the connection before executing the command CustomersByCityCommand.Connection.Open();

// Assign the results of the SQL statement to a data reader SqlDataReader reader = CustomersByCityCommand.ExecuteReader();

results.Append(reader[i].ToString() + "\t");

results.Append(Environment.NewLine);

// Close the data reader and the connection reader.Close();

CustomersByCityCommand.Connection.Close(); ResultsTextBox.Text = results.ToString();

11. Run the application and click the Execute SQL button. As shown in Figure 6-4, the application displays the command results.

-Inl x|

London

AROUT

Around the Horn London

BSBEV

B's Beverages London

CONSH

Consolidated Holdings

London

EASTC

Eastern Connection London

NDRTS

North/South London

SEVES

Seven Seas Imports London

| Execute Sgl |

Figure 6-4 Form displaying data after executing the parameterized SQL statement

12. Type Madrid and rerun the query (click the Execute SQL button).

13. Verify that the results show only customers from the City value passed in to the parameter.

► Exercise 2: Creating and Executing a Parameterized Stored Procedure

1. Create a new Windows application and name it ParameterizedStoredProcedure.

2. Add a TextBox to the form and set the following properties:

□ Name = CategoryNameTextBox

3. Add a second TextBox and set the following properties:

□ Name = OrdYearTextBox

4. Add a third TextBox and set the following properties:

□ Name = ResultsTextBox

5. Add a button and set the following properties. The form should now resemble Figure 6-5:

□ Name = ExecuteStoredProcedureButton

□ Text = Execute Stored Procedure

Category name: Order Year:

| Beverages |l997

i J

m

Execute Stored Procedure |

Figure 6-5 Form with controls in preparation for executing the parameterized stored procedure

6. Double-click the Execute Stored Procedure button to create the button-click event handler and switch the form to code view.

7. Add references to the System.Data and System.Data.SqlClient namespaces.

8. Add code to create a connection on the form.

At this point, your form code should look like the following (substitute a valid connection string for the NorthwindConnection):

Imports System.Data Imports System.Data.SqlClient

Public Class Form1

Private NorthwindConnection As New SqlConnection _

("Data Source=<ValidServerName>;Initial Catalog=Northwind;Integrated Security=True")

Private Sub ExecuteStoredProcedureButton_Click _

(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles ExecuteStoredProcedureButton.Click

End Sub End Class

using System;

usi ng System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace ParameterizedStoredProcedureCS {

public partial class Forml : Form {

Initiali zeComponent() ;

private SqlConnection NorthwindConnection = new SqlConnection

("Data Source=<Va1idServerName>;Initia1 Cata1og=Northwind;" + "Integrated Security=True");

private void ExecuteStoredProcedureButton_C1ick(object sender, EventArgs e) {

9. Add the following code to the ExecuteStoredProcedureButton_Click method to create a new Command object and set it to the SalesByCategory stored procedure:

' Create a new Command object

Dim SalesByCategoryCommand As New SqlCommand

' Set the command properties

SalesByCategoryCommand.Connection = NorthwindConnection

SalesByCategoryCommand.CommandType = CommandType.StoredProcedure

SalesByCategoryCommand.CommandText = "SalesByCategory"

// Create a new Command object

SqlCommand SalesByCategoryCommand = new SqlCommand(); // Set the command properties

SalesByCategoryCommand.Connection = NorthwindConnection;

Sal esByCategoryCommand.CommandType = CommandType.StoredProcedure;

SalesByCategoryCommand.CommandText = "SalesByCategory";

10. This stored procedure takes two parameters, so add the following code below the previous code to create the parameters and assign them to the command:

' Create the @CategoryName parameter

Dim CategoryNameParameter As New SqlParameter

' Set its name and data type

CategoryNameParameter.ParameterName = "@CategoryName" CategoryNameParameter.SqlDbType = SqlDbType.NVarChar

' Create the OrdYear parameter

Dim OrdYearParameter As New SqlParameter("@OrdYear", SqlDbType.NVarChar)

' Add the parameters to the Commmand object

SalesByCategoryCommand.Parameters.Add(CategoryNameParameter)

Sal esByCategoryCommand.Parameters.Add(OrdYearParameter)

// Create the @CategoryName parameter

SqlParameter CategoryNameParameter = new SqlParameter(); // Set its name and data type

CategoryNameParameter.ParameterName = "@CategoryName"; CategoryNameParameter.SqlDbType = SqlDbType.NVarChar;

// Create the OrdYear parameter

SqlParameter OrdYearParameter =new SqlParameter("@OrdYear", SqlDbType.NVarChar);

// Add the parameters to the Commmand object

SalesByCategoryCommand.Parameters.Add(CategoryNameParameter);

SalesByCategoryCommand.Parameters.Add(OrdYearParameter);

11. Now add the code that will set the value of the parameters to whatever is typed into the two text boxes, set the code to run the query, and display the results in the ResultsTextBox.

' Set the parameter values to the ' text in the CategoryNameTextBox ' and the OrdYearTextBox

CategoryNameParameter.Value = CategoryNameTextBox.Text OrdYearParameter.Value = OrdYearTextBox.Text

' Create a StringBuilder to store the results of the query Dim results As New System.Text.StringBuilder

' Open the connection before executing the command SalesByCategoryCommand.Connection.Open()

' Assign the results of the SQL statement to a data reader

Dim reader As SqlDataReader = SalesByCategoryCommand.ExecuteReader

While reader.Read

For i As Integer = 0 To reader.FieldCount - 1 results.Append(reader(i).ToString & vbTab)

Next results.Append(Environment.NewLine) End While

' Close the data reader and the connection reader.Close()

SalesByCategoryCommand.Connection.Close()

ResultsTextBox.Text = results.ToString // C#

// Set the parameter values to the // text in the CategoryNameTextBox // and the OrdYearTextBox

CategoryNameParameter.Value = CategoryNameTextBox.Text; OrdYearParameter.Value = OrdYearTextBox.Text;

// Create a StringBuilder to store the results of the query System.Text.StringBuilder results = new System.Text.StringBuilder();

// Open the connection before executing the command SalesByCategoryCommand.Connection.Open();

// Assign the results of the SQL statement to a data reader SqlDataReader reader = SalesByCategoryCommand.ExecuteReader();

results.Append(reader[i].ToString() + "\t"); }

results.Append(Environment.NewLine);

// Close the data reader and the connection reader.Close();

Sal esByCategoryCommand.Connection .Close(); ResultsTextBox.Text = results.ToString();

12. Run the application and click the Execute Stored Procedure button (see Figure 6-6).

I i" Parameterized Stored Procedure

Category name: Order Year:

| Beverages |1997

Chai 4997.00

Jti

Chang 7099.00

Chartreuse verte 4476.00

Cote de Blaye 49199.00

Guaraná Fantástica 1690.00

Ipoh Coffee 11070.00

Lakkalikoori 7979.00

Laughing Lumberjack Lager 910.00

Outback Lager 5469.00

Rhonbrau Klosterbier 4496.00

9asquatch Ale 2107.00

Bteeleye 9tout 5275.00

d

J Execute Stored Procedure |

Figure 6-6 Form displaying data after executing the parameterized stored procedure

13. Now try typing another category name and executing the stored procedure, verifying that the results are now displaying a list of products from the selected category. (For example, type Condiments, Seafood, or Produce.)

► Exercise 3: Using InputOutput and Output Parameters

1. Create a new Windows application and name it InputOutputParameters. Add a TextBox to the form and set the following properties:

□ Name = OrderIDTextBox

Add a second TextBox and set its Name property to FreightCostTextBox. Add a button and set the following properties:

□ Name = GetFreightCostButton

Below the button, add a second set of controls. Add a TextBox and set the following properties:

□ Name = CompanyNameTextBox

□ Text = Alfreds Futterkiste

Add another TextBox and set its Name property to ContactNameTextBox.

Add a button and set the following properties. The form should now resemble Figure 6-7:

□ Name = GetContactNameButton

Figure 6-7 Form with controls in preparation for demonstrating InputOutput parameters

8. Double-click the Get Freight Cost button to create an event handler.

9. Add references to the System.Data and System.Data.SqlClient namespaces.

10. Add code to create a connection on the form.

At this point, your form code should look like the following (substitute a valid connection string for the NorthwindConnection):

Imports System.Data Imports System.Data.SqlClient

Public Class Forml

Private NorthwindConnection As New SqlConnection _

("Data Source=<Va1idServerName>;Initia1 Cata1og=Northwind;Integrated Security=True")

Private Sub GetFreightCostButton_C1ick _

(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles GetFreightCostButton.Click

End Sub End Class

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace InputOutputParametersCS {

public partial class Forml : Form {

Initiali zeComponent() ;

private SqlConnection NorthwindConnection = new SqlConnection

("Data Source=<Va1idServerName>;Initia1 Cata1og=Northwind;" + "Integrated Security=True");

private void GetFreightCostButton_C1ick(object sender, EventArgs e) {

11. Add the following code to the GetFreightCostButton event handler:

' Create a new Command object

Dim GetFreightCost As New SqlCommand

' Set the command properties

GetFreightCost.Connection = NorthwindConnection GetFreightCost.CommandType = CommandType.StoredProcedure GetFreightCost.CommandText = "GetFreightCost"

' Create the Output parameter to receive the freight cost Dim FreightCostParameter As New SqlParameter FreightCostParameter.Direction = ParameterDirection.Output

' Set its name and data type

FreightCostParameter.ParameterName = "@Freight" FreightCostParameter.SqlDbType = SqlDbType.Money

' Create the OrderID parameter and set its value

Dim OrderIDParameter As New SqlParameter("@OrderID", SqlDbType.Int) OrderIDParameter.Value = OrderIDTextBox.Text

' Add both parameters to the Commmand object

GetFreightCost.Parameters.Add(FreightCostParameter)

GetFreightCost.Parameters.Add(OrderIDParameter)

' open the connection before executing the command GetFreightCost.Connection.Open()

' Execute the sproc; because we are using parameters ' to access the data we call ExecuteNonQuery instead of ' ExecuteReader.

GetFreightCost.ExecuteNonQuery() GetFreightCost.Connection.Close()

FreightCostTextBox.Text = Format(FreightCostParameter.Value, "c") // C#

// Create a new Command object

SqlCommand GetFreightCost = new SqlCommandO;

// Set the command properties

GetFreightCost.Connection = NorthwindConnection; GetFreightCost.CommandType = CommandType.StoredProcedure; GetFreightCost.CommandText = "GetFreightCost";

// Create the Output parameter to receive the freight cost SqlParameter FreightCostParameter = new SqlParameter(); FreightCostParameter.Direction = ParameterDirection.Output;

// Set its name and data type

FreightCostParameter.ParameterName = "@Freight"; FreightCostParameter.SqlDbType = SqlDbType.Money;

// Create the OrderID parameter and set its value

SqlParameter OrderIDParameter =new SqlParameter("@OrderID", SqlDbType.Int); OrderIDParameter.Value = OrderIDTextBox.Text;

// Add both parameters to the Commmand object

GetFreightCost.Parameters.Add(FreightCostParameter);

GetFreightCost.Parameters.Add(OrderIDParameter);

// open the connection before executing the command GetFreightCost.Connection.Open();

// Execute the sproc; because we are using parameters // to access the data we call ExecuteNonQuery instead of // ExecuteReader. GetFreightCost.ExecuteNonQuery(); GetFreightCost.Connection.Close();

FreightCostTextBox.Text = FreightCostParameter.Value.ToString();

12. Run the application and click the Get Freight Cost button.

The Freight Cost TextBox displays 65.83, the cost of freight for order number 10250. Type other valid OrderID numbers into the Order Id TextBox and run the stored procedure to verify that the output parameter contains the correct freight cost for those orders.

Now that you've seen how to use output parameters that return data from the database, let's implement the Get Contact name functionality and see how to use InputOutput parameters to both send data into the database as well as return data from the database.

13. Double-click the GetContactName button to create an event handler.

14. Add the following code to the GetContactName handler:

' Create a new Command object

Dim GetContactNameCommand As New SqlCommand

' Set the command properties

GetContactNameCommand.Connection = NorthwindConnection GetContactNameCommand.CommandType = CommandType.StoredProcedure GetContactNameCommand.CommandText = "GetContactName"

' Create the InputOutput parameter to send and receive data Dim NameParameter As New SqlParameter

NameParameter.Direction = ParameterDirection.InputOutput

' Set its name, data type, and value NameParameter.ParameterName = "@Name" NameParameter.SqlDbType = SqlDbType.NVarChar NameParameter.Value = CompanyNameTextBox.Text

' Add the parameters to the Commmand object

GetContactNameCommand.Parameters.Add(NameParameter)

' Open the connection before executing the command GetContactNameCommand.Connection.Open()

' Execute the sproc

GetContactNameCommand.ExecuteNonQuery() GetContactNameCommand.Connection.Close() ContactNameTextBox.Text = NameParameter.Value.ToString

// Create a new Command object

SqlCommand GetContactNameCommand = new SqlCommand(); // Set the command properties

GetContactNameCommand.Connection = NorthwindConnection; GetContactNameCommand.CommandType = CommandType.StoredProcedure; GetContactNameCommand.CommandText = "GetContactName";

// Create the InputOutput parameter to send and receive data SqlParameter NameParameter = new SqlParameter(); NameParameter.Direction = ParameterDirection.InputOutput;

// Set its name, data type, and value NameParameter.ParameterName = "@Name"; NameParameter.SqlDbType = SqlDbType.NVarChar; NameParameter.Value = CompanyNameTextBox.Text;

// Add the parameters to the Commmand object GetContactNameCommand.Parameters.Add(NameParameter);

// Open the connection before executing the command GetContactNameCommand.Connection.Open();

// Execute the sproc GetContactNameCommand.ExecuteNonQueryO; GetContactNameCommand.Connection.Close(); ContactNameTextBox.Text = NameParameter.Value.ToStringO;

15. Run the application and click the Get Contact Name button.

The Contact name TextBox displays the contact name record for Alfreds Futterkiste, Maria Anders. Type other valid company names from the Customers table and the Contact name TextBox will display their contacts.

Was this article helpful?

0 0

Post a comment