Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Executing a Command and Accessing Output Parameters

Executing a Command and Accessing Output Parameters

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.

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.

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.

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"));

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.

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.

Show:
© 2015 Microsoft