Parameters Collection

for RuBoard

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.

 command = new SqlCommand("get_customers", conn);  command.CommandType = CommandType.StoredProcedure;  SqlParameter p = null;  p = new SqlParameter("@companyname", SqlDbType.NVarChar,                         40);  p.Direction = ParameterDirection.Input;  p.Value = "Ernst Handel";  command.Parameters.Add(p);   p = new SqlParameter("@customerid", SqlDbType.NChar, 5);  p.Direction = ParameterDirection.Output;  command.Parameters.Add(p);  command.ExecuteNonQuery();  Console.WriteLine("{0} CustomerId = {1}",                   command.Parameters["@companyname"].Value,                   command.Parameters["@customerid"].Value); 

Each individual SqlParameter member of the Parameters collection represents one parameter of an 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 specify ”either through the constructor or by setting properties ”the 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 was an OLEDB .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.

for RuBoard