Using Parameters | Sams Teach Yourself ADO.NET in 24 Hours

for RuBoard

The Command object contains a collection of stored procedure parameters named, aptly enough, Parameters. If your stored procedure requires input parameters or returns values using one or more output parameters, you will need to add each parameter to the Parameters collection.

For example, consider the Customer_GetByID stored procedure you created at the beginning of this hour . To set up the parameter object, the first step is to declare the parameter, as follows :

 Dim parameterCustomerID As SqlParameter = New SqlParameter("@CustomerID", _                                                    SqlDbType.NChar, 4)

Note that the name of the parameter must match the name of the parameter declared inside the stored procedure. Additionally, the type must match, as well. After the parameter is declared, you must specify its value.

Specifying Parameter Value

Specifying a parameter's value is simple. You just set the Value property of the parameter object to whatever value you choose. Typically, the value is not hard-coded, as it is in the following example, but provided by user input or other means. The following code specifies the value for the @CustomerID parameter:

 parameterCustomerID.Value = "ALFKI"

Setting Parameter Direction

Specifying the parameter direction is also straightforward. There are only two types of parameters in ADO.NET for stored procedures: input and output. By default, when a parameter is created, it is an input parameter by default. If you're creating an output parameter, you'll need to change the Direction property to the output value, as in the following code:

 parameterCustomerID.Direction = ParameterDirection.Output

After you've specified all the parameter's properties and values, you can add it to the Parameters collection of the Command object:

 cmd.Parameters.Add(parameterCustomerID)

Putting It All Together

The example in this section uses the concepts from this hour to create a Web form that adds a new employee to the Employees table of the Northwind database. Before creating the Web form, you need to create a stored procedure. By looking at the schema of the Employees table shown in Figure 15.5, you can see that most of the fields in the table allow null values. Therefore, we can ignore most of the fields and only add the ones we need, plus a few extra to make it interesting.

Listing 15.5 contains the stored procedure that will be called to perform the insert into the database. As you can see, the stored procedure has nine parameters, the last of which is an output parameter used to return the EmployeeID of the recently added employee record.

Listing 15.5 Another Way to Call a Stored Procedure

 /*   This stored procedure adds a new record   to the employees table */ CREATE PROCEDURE Employee_Add (     @LastName nvarchar(20),     @FirstName nvarchar(10),     @Title nvarchar(30),     @BirthDate datetime,     @HireDate datetime,     @Address nvarchar(60),     @City nvarchar(15),     @PostalCode nvarchar(10),     @retval int OUTPUT ) AS INSERT INTO Employees (     LastName,     FirstName,     Title,     BirthDate,     HireDate,     Address,     City,     PostalCode ) VALUES (     @LastName,     @FirstName,     @Title,     @BirthDate,     @HireDate,     @Address,     @City,     @PostalCode ) SELECT @retval = @@IDENTITY

The @@IDENTITY variable always stores the ID of the most recently added record.

Listing 15.6 contains a Web form that accepts information about a new employee record and then saves it to the database using the stored procedure in Listing 15.5. Notice that after the stored procedure has been run, you can access the return value simply by calling the Value property of the output parameter.

Listing 15.6 Another Way to Call a Stored Procedure

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="VB" runat="server" >         Sub Page_Load(Source as Object, E as EventArgs)           If IsPostBack then             'Create and Open Connection             Dim conn as SqlConnection = new SqlConnection("Data Source=" + _                         "localhost;Initial Catalog=northwind;UID=sa;PWD=;")             'Create Command             Dim cmd as SqlCommand = new SqlCommand("Employee_Add", conn)             cmd.CommandType = CommandType.StoredProcedure             Dim pLastName As SqlParameter = New SqlParameter("@LastName", _                                                        SqlDbType.NVarChar, 20)             pLastName.Value = Request("txtLastName")             cmd.Parameters.Add(pLastName)             Dim pFirstName As SqlParameter = New SqlParameter("@FirstName", _                                                        SqlDbType.NVarChar, 10)             pFirstName.Value = Request("txtFirstName")             cmd.Parameters.Add(pFirstName)             Dim pTitle As SqlParameter = New SqlParameter("@Title", _                                                        SqlDbType.NVarChar, 30)             pTitle.Value = Request("txtTitle")             cmd.Parameters.Add(pTitle)             Dim pBirthDate As SqlParameter = New SqlParameter("@BirthDate",                                                         SqlDbType.DateTime, 8)             pBirthDate.Value = Request("txtBirthDate")             cmd.Parameters.Add(pBirthDate)             Dim pHireDate As SqlParameter = New SqlParameter("@HireDate", _                                                         SqlDbType.DateTime, 8)             pHireDate.Value = Request("txtHireDate")             cmd.Parameters.Add(pHireDate)             Dim pAddress As SqlParameter = New SqlParameter("@Address", _                                                        SqlDbType.NVarChar, 60)             pAddress.Value = Request("txtAddress")             cmd.Parameters.Add(pAddress)             Dim pCity As SqlParameter = New SqlParameter("@City", _                                                        SqlDbType.NVarChar, 15)             pCity.Value = Request("txtCity")             cmd.Parameters.Add(pCity)             Dim pPostalCode As SqlParameter = New SqlParameter("@PostalCode",                                                        SqlDbType.NVarChar, 10)             pPostalCode.Value = Request("txtPostalCode")             cmd.Parameters.Add(pPostalCode)             Dim pRetval As SqlParameter = New SqlParameter("@retval", _                                                              SqlDbType.Int, 4)             pRetval.Direction = ParameterDirection.Output             cmd.Parameters.Add(pRetval)             conn.Open()             cmd.ExecuteNonQuery()             conn.Close()             lblStatus.Text = "Employee added with EmployeeID # " + _                               pRetval.Value.ToString()           End If         End Sub     </script> </HEAD> <BODY> <h1>Add a New Employee to the Northwind Database</h1> <hr> <form runat="server" id=form1 name=form1>   <asp:label id=lblStatus runat="server" />   <table>     <tr>         <td>First Name:</td>         <td><asp:textbox id="txtFirstName" runat="server" /></td>     </tr>     <tr>         <td>Last Name:</td>         <td><asp:textbox id="txtLastName" runat="server" /></td>     </tr>     <tr>         <td>Title:</td>         <td><asp:textbox id="txtTitle" runat="server" /></td>     </tr>     <tr>         <td>Birth Date:</td>         <td><asp:textbox id="txtBirthDate" runat="server" /></td>     </tr>     <tr>         <td>Hire Date:</td>         <td><asp:textbox id="txtHireDate" runat="server" /></td>     </tr>     <tr>         <td>Address:</td>         <td><asp:textbox id="txtAddress" runat="server" /></td>     </tr>     <tr>         <td>City:</td>         <td><asp:textbox id="txtCity" runat="server" /></td>     </tr>     <tr>         <td>Postal Code:</td>         <td><asp:textbox id="txtPostalCode" runat="server" /></td>     </tr>  </table>  <input type="submit" value="add new employee"> </form> <hr> </BODY> </HTML>

The code in Listing 15.6 might appear a bit intimidating at first. However, keep in mind that much of the code is repetitive. Lines 10 “19 create and configure the Connection and Command objects. Then, lines 20 “67 set up a series of parameters that are required by the Employee_Add stored procedure. After all the parameters are created, the ExecuteNonQuery() method of the Command object executes the stored procedure. The remainder of the example in Listing 15.6 is display logic, necessary to present a form to the user so they can submit a new record.

for RuBoard