The code sample above retrieves an entire record from a database table. Sometimes however we need only specific fields from a row in a database table. In this case we need to supply the stored procedure with not only an input parameter as in the previous section, but also one or more output parameters to store the result of the stored procedure.

The following is an example of a stored procedure which will take as input the CustomerID value and return only two fields: CompanyName and ContactName. For this limited result set, we have to use the AddOutputParameter() method. This method is used to return the output of a stored procedure.

It is recommended when executing a stored procedure with output parameters to use the ExecuteNonQuery() method instead of the ExecuteDataReader() method.

The SQL stored procedure is as follows:

Listing 6

CREATE PROCEDURE [dbo].[GetCustomerMultipleFields]
(
   @CustomerID NCHAR(5),
   @CompanyName NVARCHAR(40) OUTPUT,
   @ContactName NVARCHAR(30) OUTPUT
) 
AS
SET NOCOUNT ON
   SELECT
      @CompanyName = [CompanyName],
      @ContactName = [ContactName]
   FROM
      [Customers]
   WHERE
      [CustomerID] LIKE @CustomerID
GO

The following C# code executes the above stored procedure and uses the AddOutputParameter() method to obtain the results:

Listing 7

try
{
   // Create DataBase Instance
   Database db = DatabaseFactory.CreateDatabase();
   // Initialize the Stored Procedure
   DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper("GetCustomerMultipleFields");
   dbCommandWrapper.AddInParameter("@CustomerID", DbType.String, "ALFKI");
   dbCommandWrapper.AddOutParameter("@CompanyName", DbType.String, 40);
   dbCommandWrapper.AddOutParameter("@ContactName", DbType.String, 30);
   //Execute the stored procedure
   db.ExecuteNonQuery(dbCommandWrapper);
   //Display results of the query
   string results = string.Format("Company Name : {0}, Contact Name {1},",
        dbCommandWrapper.GetParameterValue("@CompanyName"),
        dbCommandWrapper.GetParameterValue("@ContactName"));
   Response.Write(results);
}
catch (Exception ex)
{
   Response.Write(ex.ToString());
}

In the above code, I passed a customer ID value to the stored procedure as an input parameter. As the stored procedure is designed to return two values, CompanyName and ContactName, these were identified as output parameters.

To display the returned values we used another method of dbCommandWrapper, which is GetParameterValue(“ParameterName”). This method returns the value of the parameter specified.