Walkthrough: Executing a Command and Accessing a Single-Item Result

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 a single item. It assumes that the following stored procedure has been created in the database to which the connection string in the code refers.

CREATE PROCEDURE GetProductName
@ProductID int
AS
SELECT ProductName
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 GetProductName. It takes one input parameter, ProductID, and returns the product name as the result.

   string sqlCommand = "GetProductName";
   DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, productID);
   

   'Usage
   Dim sqlCommand As String = "GetProductName"
   Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand, productID)
   

4. Call ExecuteScalar, and pass the DbCommand, indicating that the GetProductName stored procedure will retrieve the product name.

   string productName = (string)db.ExecuteScalar(dbCommand);
   

   'Usage
   Dim productName As String = db.ExecuteScalar(dbCommand)
   

Usage Notes

Consider the following when you use a command to access a single item:

• An alternative method for retrieving a single item is to use a stored procedure output parameter or a return value, coupled with the ExecuteNonQuery method. This approach works well across a range of stress levels. In this case, the code is similar to that shown in the Executing a Command and Accessing Output Parameters scenario. For more information about choosing an appropriate approach for looking up a single item, see the .NET Data Access Architecture Guide.

• When you use the ExecuteScalar method to return a SQL Server @@Identity variable by way of a result set, you should be aware that SQL Server returns the @@Identity value as a decimal data type, not as an integer. If you need to retrieve this value as an integer, you must either use code in your client application to convert the returned object to an integer or convert it when returning the @@Identity value from a stored procedure. You can use the Transact-SQL CAST function to return the value as an integer, as shown in the following example.

  SELECT CAST(@@Identity AS INTEGER)