Share via

Executing a Command and Accessing Output Parameters

  • Article

A common database task is to retrieve specific, multiple-column values. For example, in a Web-based online retail application, you may want to retrieve full product details for a certain product in response to a user request.

Typical Goals

A typical goal in this scenario is to retrieve specific items of data either from one row in a particular table or from multiple related rows in different tables.

Solution

One of the most efficient ways to accomplish this goal is to use a stored procedure's output parameters. For example, in an online catalog, the stored procedure accepts a product ID as an input parameter and returns product details through a set of output parameters.

To call the stored procedure, use the ExecuteNonQuery method, passing both the input and the output parameters. When the method returns, the output parameters will be populated with the retrieved column values.

Using ExecuteNonQuery

The following code shows how to use the ExecuteNonQuery method by passing a DbCommand object. 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, 3);
db.AddOutParameter(cmd, "ProductName", DbType.String, 50);
db.AddOutParameter(cmd, "UnitPrice", DbType.Currency, 8);

db.ExecuteNonQuery(cmd);

string results = string.Format("{0}, {1}, {2:C} ",
                               db.GetParameterValue(cmd, "ProductID"),
                               db.GetParameterValue(cmd, "ProductName"),
                               db.GetParameterValue(cmd, "UnitPrice"));

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

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

db.ExecuteNonQuery(cmd)

Dim results As String = String.Format("{0}, {1}, {2:C} ", _
                                      db.GetParameterValue(cmd, "ProductID"), _
                                      db.GetParameterValue(cmd, "ProductName"), _
                                      db.GetParameterValue(cmd, "UnitPrice"))

There are other overloads available that allow developers to call the ExecuteNonQuery method in different ways. For a description of the types of overloads that are available and the factors that influence which overload you should use, see the section "Writing Code to Use the Database Classes" in Creating a Database Object.

Usage Notes

Consider the following when using the ExecuteNonQuery method overloads:

  • The ExecuteNonQuery method returns the number of rows affected by the query (typically for Insert, Update, or Delete operations).
  • You can use the ExecuteNonQuery method to change data in a database without using a DataSet by executing Insert, Update, or Delete operations.