Walkthrough: Executing a Command and Accessing Output Parameters

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.

This walkthrough demonstrates how to retrieve multiple values from a single row by executing a SQL command and retrieving the output parameters. It assumes that the following stored procedure has been created in the database to which the connection string in the code refers.

CREATE PROCEDURE GetProductDetails
@ProductID int,
@ProductName nvarchar(40) OUTPUT,
@UnitPrice money OUTPUT,
@QtyPerUnit nvarchar(20) OUTPUT
AS
SELECT @ProductName = ProductName, 
       @UnitPrice = UnitPrice,
       @QtyPerUnit = QuantityPerUnit
FROM Products 
WHERE ProductID = @ProductID

To reproduce the demonstration

1. Configure the database. For the necessary steps, see "QuickStart Configuration" in Data Access QuickStart.

2. Create the database (when you are not using the Unity Integration approach). The following code uses the factory to create a Database object that has the default configuration.

   Database db = DatabaseFactory.CreateDatabase();
   

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

3. Create the command by adding the following code. It creates a DbCommand used with a stored procedure. In this case, it is GetProductDetails, which takes one input parameter, ProductID, and returns results in two output parameters, ProductName and UnitPrice.

   string sqlCommand = "GetProductsDetails";
   DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand); 
   db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productID);
   // Output parameters specify the size of the return data.
   db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50);
   db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8);
   

   'Usage
   Dim sqlCommand As String = "GetProductDetails"
   Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)
   db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productID)
   ' Output parameters specify the size of the return data.
   db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50)
   db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8)
   

4. Call ExecuteNonQuery by adding the following code. It passes the DbCommand, indicating that the GetProductsByCategoryDetails stored procedure will populate the output parameters.

   db.ExecuteNonQuery(dbCommand);
   

   'Usage
   db.ExecuteNonQuery(dbCommand)
   

5. Process the results by adding the following code, which places the values stored in the output parameters into a string.

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

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

Usage Notes

The ExecuteNonQuery method returns the number of rows affected by the query (typically for Insert, Update, or Delete operations). In the preceding code example, the return value is ignored.