4.3 Parameter Object Overview

In the examples shown so far, the SQL command text and the data values have been embedded in a single string. This approach is easy, and convenient for writing data access code. However, it also has significant drawbacks that make it unsuitable for a production-level application. These include inflexibility, poor performance, and potential security problems when using user-supplied values.

To overcome these problems, you need to use another feature of the Command object: parameters. Command parameters are conceptually the same as method parameters in an ordinary piece of .NET code. The most common type of parameter is an input parameter, which carries information from your application to the data source. You can use an input parameter when calling a stored procedure or when coding a parameterized query. In addition, you can use output parameters, which return information from the data source to your code, or bidirectional parameters, which transmit values in both directions. Output and bidirectional parameters are used only when you are making stored procedure calls.

Every Command object has an associated collection of Parameter objects (referenced by its Parameters property). The Parameter object is a provider-specific object, which means a SqlCommand uses a SqlParameter, an OleDbCommand uses an OleDbParameter, and so on.

4.3.1 Creating Parameters

In order to create a Parameter object, you must specify a parameter name, and the exact data type for the information it will contain. For the managed OLE DB provider, you specify data types using the System.Data.OleDb.OleDbType enumeration. For the SQL Server data provider, you use the System.Data.SqlDbType enumeration. If the data type is a variable-length field such as a string or binary field, you also need to indicate the field length.

For example, the following code snippet shows how to create a SqlParameter object named @MyParam with a SQL Server integer type. Note that the name is preceded with an @ symbol; this is a convention of stored procedure programming with SQL Server, but it is by no means a necessity in your code.

SqlParameter param = new SqlParameter("@MyParam", SqlDbType.Int);

To use a variable-length data type, you need to use a different constructor that accepts a field length, as shown here:

SqlParameter param = new SqlParameter("@MyParam", SqlDbType.NVarChar,
                                      15);

Once you've created a Parameter, you will probably want to assign a value and add it to an existing Command:

SqlCommand cmd = new SqlCommand(commandText, con);

SqlParameter param = new SqlParameter("@Description", SqlDbType.VarChar,
                                       88, "Description");
param.Value = "This is the description";

cmd.Add(param);

Alternatively, you can create the Parameter and add it to the Command in one step using an overloaded version of the Add( ) method. This method returns a reference to the newly created Parameter object, allowing you to quickly set a value.

SqlCommand cmd = new SqlCommand(commandText, con);

SqlParameter param = cmd.Add("@Description", SqlDbType.VarChar,
                             88, "Description");
param.Value = "This is the description";

By default, when you create a parameter, it is configured as an input parameter, meaning that the Parameter.Direction property is set to ParameterDirection.Input.

You can retrieve parameters from the Parameters collection by index number or by the assigned parameter name:

// Select the first parameter.
param = cmd.Parameters[0];

// Select the parameter with the name "@Description".
param = cmd.Parameters["@Description"];

Now that you can create and configure Parameter objects, it's time to consider how to use them to build a parameterized command.