Handling Parameters

  • Article

Most stored procedures accept parameters whose values are either used as input to the stored procedure or are set during output. As with ADO.NET, the Data Access Application Block allows developers to explicitly specify all of the attributes of a parameter. These attributes can include direction, data type, and length. This approach is named explicit parameter handling. However, as a convenience, you can specify only the values when using input parameters. In this case, the application block will look up and supply the parameter attributes. This approach is named parameter discovery.

Explicit Parameter Handling

The Database class includes a variety of methods for passing parameters to stored procedures and parameterized SQL statements. This class also includes methods for setting and examining the values of those parameters. These methods are the following:

  • AddParameter. This method passes a parameter (input or output) to a stored procedure.
  • AddInParameter. This method passes an input parameter to a stored procedure.
  • AddOutParameter. This method adds an output parameter to a stored procedure.
  • GetParameterValue. This method finds the value of the specified parameter.
  • SetParameterValue. This method sets the value of the specified parameter when you want to execute multiple inserts using the same connection and command but with different parameter values.

The following code demonstrates how to use AddInParameter and AddOutParameter to specify parameters. It assumes that you have resolved the Database class you require and stored a reference in the variable named db. For more information on instantiating objects, see Creating and Referencing Enterprise Library Objects.

string sql = "GetProductDetails";
DbCommand cmd = db.GetStoredProcCommand(sql);

db.AddInParameter(cmd, "ProductID", DbType.Int32, 5);
db.AddOutParameter(cmd, "ProductName", DbType.String, 50);
db.AddOutParameter(cmd, "UnitPrice", DbType.Currency, 8);

'Usage
Dim sql As String = "GetProductDetails"
Dim cmd As DbCommand = db.GetStoredProcCommand(sql)

db.AddInParameter(cmd, "ProductID", DbType.Int32, 5)
db.AddOutParameter(cmd, "ProductName", DbType.String, 50)
db.AddOutParameter(cmd, "UnitPrice", DbType.Currency, 8)

Note

The preceding code example does not include a parameter name token specific to a database type. Because of this, the code can remain generic across different database providers. When this code executes against the SqlClient data provider (and therefore uses the SqlDatabase class), the following code would result in identical behavior as the preceding code. However, this code would not be portable to other data providers.

string sql = "GetProductDetails";
DbCommand cmd = db.GetStoredProcCommand(sql);

// NOTE: specifying parameter prefixes makes the code database-specific
db.AddInParameter(cmd, "@ProductID", DbType.Int32, 5);
db.AddOutParameter(cmd, "@ProductName", DbType.String, 50);
db.AddOutParameter(cmd, "@UnitPrice", DbType.Currency, 8);

'Usage
Dim sql As String = "GetProductDetails"
Dim cmd As DbCommand = db.GetStoredProcCommand(sql)

' NOTE: specifying parameter prefixes makes the code database-specific
db.AddInParameter(cmd, "@ProductID", DbType.Int32, 5)
db.AddOutParameter(cmd, "@ProductName", DbType.String, 50)
db.AddOutParameter(cmd, "@UnitPrice", DbType.Currency, 8)

Using Column Values as Parameter Inputs

The UpdateDataSet method requires three different commands: one to insert values, one to update values, and one to delete values. Typically, these commands use stored procedures and not SQL strings. Instead of specifying values to be used for the stored procedure parameters, the stored procedures use values that come from the DataSet being used as input. In this case, the appropriate overload of AddInParameter is the one that accepts a source column as a parameter.

The following code shows how to use column values as parameter inputs. It assumes that you have resolved the Database class you require and stored a reference in the variable named db. For more information on instantiating objects, see Creating and Referencing Enterprise Library Objects.

DbCommand insertCommand = db.GetStoredProcCommand("AddProduct");
db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);
db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);

'Usage
Dim insertCommand As DbCommand = db.GetStoredProcCommand("AddProduct")
db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current)
db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current)
db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current)

Parameter Discovery

By using the Data Access Application Block, developers can specify the values to be used for parameters, without requiring any other information about those parameters. When you use parameter discovery, you should specify all the parameters and set all output parameters to null.

The following code demonstrates how to use GetStoredProcCommand with only the parameter values specified and none of the attributes. It assumes that you have resolved the Database class you require and stored a reference in the variable named db. For more information on instantiating objects, see Creating and Referencing Enterprise Library Objects.

string sql = "UpdateProduct";
DbCommand cmd = db.GetStoredProcCommand(sql, 11, "Queso Cabrales", 4, 25);

'Usage
Dim sql As string = "UpdateProduct"
Dim cmd As DbCommand = db.GetStoredProcCommand(sql, 11, "Queso Cabrales", 4, 25)

The information about each specific parameter (for example, its data type) is still required for the underlying ADO.NET method call. To supply this information, the Data Access Application Block uses the ADO.NET DeriveParameters method to look up the parameter information.

Because the DeriveParameters call requires a round trip to the database, the application block also provides parameter information caching. After the first call to a particular stored procedure that requires parameter discovery, the information about each parameter is saved in the parameter cache. This means that subsequent calls to the same stored procedure will not require a round trip.

When you use parameter discovery, it is a best practice to specify all output parameters as null. You do not need to provide cursor parameters for Oracle stored procedures. The OracleDatabase object provides them. This object assumes that the cursor parameter is the first parameter in the stored procedure's parameters list.

Optional Parameters

It is possible to define a stored procedure with optional parameters. In SQL Server, the usual approach is to define the parameters with a default value in the stored procedure. However, some operations supported by the Data Access Application Block, particularly those that rely on parameter discovery, may not work correctly if you use a stored procedure that has optional parameters.

The majority of the methods of the block explicitly compare the number of parameters you pass to a method with the number of parameters the command should have. However, you can circumvent this by creating a DbCommand object first, then adding the required parameters to it before calling the target method. For example, to call the ExecuteNonQuery method using a stored procedure that has optional parameters, you can use the following code.

DbCommand cmd = db.GetStoredProcCommand("stored-proc-name");
db.AddInParameter(cmd, DbType.String, "somevalue");
db.AddInParameter(cmd, DbType.Boolean, true);
int rowsAffected = db.ExecuteNonQuery(cmd);

'Usage
Dim cmd As DbCommand = db.GetStoredProcCommand("stored-proc-name ")
db.AddInParameter(cmd, DbType.String, "somevalue")
db.AddInParameter(cmd, DbType.Boolean, True)
Dim rowsAffected As Integer = db.ExecuteNonQuery(cmd)