10.4 Use Parameters in a SQL Command or Stored Procedure

Problem

You need to set the arguments of a stored procedure or use parameters in a SQL command to improve flexibility.

Solution

Create parameter objects appropriate to the type of command object you intend to execute; all parameter objects implement the System.Data.IDataParameter interface. Configure the parameter objects' data types, values, and directions and add them to the command object's parameter collection using the IDbCommand.Parameters.Add method.

Discussion

All command objects support the use of parameters, so you can do the following:

• Set the arguments of stored procedures

• Receive stored procedure return values

• Substitute values into text commands at run time

The IDataParameter interface represents a parameter and each data provider includes a unique implementation. Here is the list of IDataParameter implementations for the five standard data providers.

• System.Data.Odbc.OdbcParameter

• System.Data.OleDb.OleDbParameter

• System.Data.OracleClient.OracleParameter

• System.Data.SqlServerCe.SqlCeParameter

• System.Data.SqlClient.SqlParameter

A parameter object's properties describe everything about a parameter that the command object needs to use the parameter object when executing a command against a data source. Table 10.4 describes the properties that you will use most frequently when configuring parameters.

Table 10.4: Parameter Properties

Property	Description
DbType	A value of the System.Data.DbType enumeration that specifies the type of data contained in the parameter. Commonly used values include String , Int32 , DateTime , and Currency .
Direction	A value from the System.Data.ParameterDirection enumeration that indicates the direction in which the parameter is used to pass data; valid values are Input , InputOutput , Output , and ReturnValue .
IsNullable	A bool that indicates whether the parameter accepts null values.
ParameterName	A string containing the name of the parameter.
Value	An object containing the value of the parameter.

To use parameters with a text command, you must identify where to substitute the parameter's value within the command. The ODBC, OLE DB, and SQL Server CE data providers support positional parameters; the location of each argument is identified by a question mark (?). For example, the following command identifies two locations to be substituted with parameter values.

 UPDATE Employees SET Title = ? WHERE EmployeeId = ? 

The SQL Server and Oracle data providers support named parameters, which allow you to identify each parameter location using a name preceded by the at symbol (@). Here is the equivalent command using named parameters.

 UPDATE Employees SET Title = @title WHERE EmployeeId = @id 

To specify the parameter values to substitute into a command, you must create parameter objects of the correct type and add them to the command object's parameter collection accessible through the Parameters property. You can add named parameters in any order, but you must add positional parameters in the same order they appear in the text command. When you execute your command, the value of each parameter is substituted into the command string before the command is executed against the data source.

The ParameterizedCommandExample method shown here demonstrates the use of parameters in a SQL Server UPDATE statement. The ParameterizedCommandExample method's arguments include an open SqlConnection and two strings. The values of the two strings are substituted into the UPDATE command using parameters. The example demonstrates two ways of creating parameter objects: the IDbCommand.CreateParameter method, and the IDbCommand.Parameters.Add method. You can also create parameter objects using constructors and configure them using constructor arguments or through setting their properties.

 public static void ParameterizedCommandExample(SqlConnection con,      string employeeID, string title) {     // Create and configure a new command containing 2 named parameters.     SqlCommand com = con.CreateCommand();     com.CommandType = CommandType.Text;     com.CommandText = "UPDATE Employees SET Title = @title" +         " WHERE EmployeeId = @id";     // Create a SqlParameter object for the title parameter.     SqlParameter p1 = com.CreateParameter();     p1.ParameterName = "@title";     p1.SqlDbType = SqlDbType.VarChar;     p1.Value = title;     com.Parameters.Add(p1);     // Use a shorthand syntax to add the id parameter.     com.Parameters.Add("@id",SqlDbType.Int).Value = employeeID;     // Execute the command and process the result.     int result = com.ExecuteNonQuery();      } 

When using parameters to execute stored procedures, you must provide parameter objects to satisfy each argument required by the stored procedure ” including both input and output arguments. You must set the Direction property of each parameter as described in Table 10.4; parameters are Input by default. If a stored procedure has a return value, the parameter to hold the return value (with a Direction property equal to ReturnValue ) must be the first parameter added to the parameter collection. Here is an example that uses parameters to execute a stored procedure.

 public static void StoredProcedureExample(SqlConnection con,      string category, string year) {     // Create and configure a new command.     SqlCommand com = con.CreateCommand();     com.CommandType = CommandType.StoredProcedure;     com.CommandText = "SalesByCategory";     // Create a SqlParameter object for the category parameter.     com.Parameters.Add("@CategoryName",SqlDbType.NVarChar).Value=category;     // Create a SqlParameter object for the year parameter.     com.Parameters.Add("@OrdYear",SqlDbType.NVarChar).Value = year;     // Execute the command and process the results     using (IDataReader reader = com.ExecuteReader()) {              } }