Parameters and Stored Procedures

Parameters and Stored Procedures

We have covered the basics of using ADO.NET in a connected scenario. We have seen that by using the appropriate interfaces, we can write database code that is relatively independent of the data source. We are now going to begin to examine some additional features, where there may be differences among data sources. For example, we will look at the use of stored procedures. For the rest of this chapter we will focus on the SQL Server database.

Sometimes you have to parameterize a SQL statement. You also might have to associate the input and output arguments of a stored procedure with variables in your program.

To do this you build the SqlCommand class's Parameters property, which is a collection of SqlParameter instances. The installation procedure added the get_customers stored procedure to the Northwind database to illustrate the use of a simple stored procedure, which takes one input argument that is the company name and returns the customer ID for that customer.

CREATE PROCEDURE get_customers
(@companyname nvarchar(40), @customerid nchar(5) OUTPUT)
AS
select @customerid = CustomerID from Customers where
  CompanyName = @companyname
RETURN

GO

The StoredProcedure example shows how to do this.

cmd = New SqlCommand("get_customers", conn)
cmd.CommandType = CommandType.StoredProcedure

Dim p As New SqlParameter("@companyname",
SqlDbType.NVarChar, 40)
p.Direction = ParameterDirection.Input
p.Value = "Ernst Handel"
cmd.Parameters.Add(p)

p = New SqlParameter("@customerid", SqlDbType.NChar, 5)
p.Direction = ParameterDirection.Output
cmd.Parameters.Add(p)

cmd.ExecuteNonQuery()
Console.WriteLine("{0} CustomerId = {1}", _
   cmd.Parameters("@companyname").Value, _
   cmd.Parameters("@customerid").Value)

Each individual SqlParameter member of the Parameters collection represents one parameter of a SQL statement or stored procedure. As this example illustrates, the parameters need not have any relationship to any particular table or column in the database.

At a minimum, you have to specifyeither through the constructor or by setting propertiesthe name and database type of the parameter. If the parameter is of variable length, you have to specify the size .

In this example two parameters are added to the parameters collection. The first represents the input argument to the stored procedure, the second the return value from the stored procedure.

The name of the parameter corresponds to the name of the argument in the stored procedure get_customers . The other values to the SqlParameter constructor define the data type of the parameter. The first is a variable Unicode string up to 40 characters in length. The second variable is a 5-character fixed-length Unicode string. If this were an OLE DB .NET data provider, you would bind to the parameters by position, since only the SQL Server .NET data provider binds parameters by name.

The Value property is used to set or get the value of the parameter. It is used to initialize the @companyname parameter for input to the stored procedure. It is also used to obtain the value that the stored procedure set for the @customerid parameter.

Output parameters must be specified as such with the Direction property. In this example the @companyname parameter is set as an input parameter with the value ParameterDirection.Input . The @customerid parameter is set as an output parameter with the value ParameterDirection.Output . Output parameters must be specified, since input parameters are the default. To bind to the return value of a stored procedure, use ParameterDirection.ReturnValue . For bidirectional parameters, use ParameterDirection.InputOutput .

You can use the parameter names to access individual parameters in the SqlCommand parameters collection.

Parameterized commands work with both SqlDataReader and DataAdapter classes. When the DataSet class is discussed, you will see how to specify the Source property of the parameter, which indicates which column in the DataSet the parameter represents.