Share via

Executing a Command and Accessing a Single Item Result

  • Article

There are many situations in which you have to perform a single-item lookup. For example, an online retailer may want to use a product ID to retrieve a product name or use a customer ID to retrieve a credit rating.

Typical Goals

The goal in this scenario is to return a single value as the result of a query.

Solution

An efficient way to return a single value is to use the ExecuteScalar method and a unique identifier. For example, in an online catalog, you can retrieve a product name by using the product ID or you can retrieve a customer's credit rating by using the customer ID.

Using ExecuteScalar

The following code shows how to use the ExecuteScalar method to pass 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 = "GetProductName";
int productId = 7;
DbCommand cmd = db.GetStoredProcCommand(sql,  productId);

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

'Usage
Dim sql As String = "GetProductName"
Dim productId As Integer = 7
Dim cmd As DbCommand = db.GetStoredProcCommand(sql, productId)

Dim productName As String = DirectCast(db.ExecuteScalar(cmd), String)

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

Usage Notes

Consider the following when using the ExecuteScalar method overloads:

  • An alternate method for retrieving a single item is to use a stored procedure output parameter or return value, coupled with the ExecuteNonQuery method. This approach works well across a range of stress levels. 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 using 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 the value to be an integer, you can use code in your client application to convert it. Alternatively, 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)