Executing a Command and Accessing Output Parameters

  • Article
Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
The latest Enterprise Library information can be found at the Enterprise Library site.

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.

QuickStart

For an extended example of how to use the ExecuteNonQuery method to retrieve multiple items of data, see the QuickStart walkthrough, Walkthrough: Executing a Command and Accessing Output Parameters.

Using ExecuteNonQuery

The following code shows how to use the ExecuteNonQuery method by passing a DbCommand object.

Database db = DatabaseFactory.CreateDatabase();

string sqlCommand = "GetProductDetails";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

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

db.ExecuteNonQuery(dbCommand);

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

'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "GetProductDetails"
Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)

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

db.ExecuteNonQuery(dbCommand)

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

There are other overloads available that allow developers to call the ExecuteReader 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 Adding Application Code.

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.
Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
The latest Enterprise Library information can be found at the Enterprise Library site.