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.

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.

QuickStart

For an extended example of how to use the ExecuteScalar method to retrieve a single-item result, see the QuickStart walkthrough, Walkthrough: Executing a Command and Accessing a Single-Item Result.

Using ExecuteScalar

The following code shows how to use the ExecuteScalar method to pass a DbCommand object.

Database db = DatabaseFactory.CreateDatabase();

string sqlCommand = "GetProductName";
int productId = 7;
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand,  productId);

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

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 Adding Application Code.

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. 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 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 to retrieve the value as an integer, you must use code in your client application to convert it. 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)
    
    

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.
Show: