Share via

Creating a DbCommand Object

  • Article

The Data Access Application Block provides a consistent way to retrieve ADO.NET DbCommand objects. The data access methods of the application block include overloads that accept a DbCommand object. If you use the overloads with DbCommand objects, you have more control when you call stored procedures. For example, if you use a DbCommand object, you can have a stored procedure that returns several results in the output parameters. In addition, a DbCommand object allows you to specify the stored procedure's timeout value.

The methods that create DbCommand objects are separated into two types:

  • Methods that represent stored procedure calls (for example, GetCustomers)
  • Methods that represent SQL text commands (for example, Select CustomerID, Fullname From Customers)

The method you call to retrieve a DbCommand object is determined by whether you want to execute inline SQL or call a stored procedure. The method that creates a DbCommand object for a stored procedure also provides parameter caching. For more information about parameter caching, see Handling Parameters.

All DbCommand objects are created using methods on the Database class. These methods are the following:

  • GetStoredProcCommand. This method is for stored procedures commands.
  • GetSqlStringCommand. This method is for SQL text commands.

Both methods return a DbCommand object.

Note

SQL Server CE does not support stored procedures. Instead, use inline SQL statements. For more information, see the section "Using SQL Server CE" in Creating a Database Object.

DbCommand Objects for SQL Statements

To create a DbCommand object for an inline SQL statement, use the GetSqlStringCommand method. The specific SQL command to be executed is passed as an argument on the method call.

The following code shows how to use GetSqlStringCommand. 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 = "Select CustomerID, LastName, FirstName From Customers";
DbCommand cmd = db.GetSqlStringCommand(sql);

'Usage
Dim sql As String = "Select CustomerID, LastName, FirstName From Customers"
Dim cmd As DbCommand = db.GetSqlStringCommand(sql)

DbCommand Objects for Stored Procedures

To execute a stored procedure, you must use the GetStoredProcCommand method to create the DbCommand object. The name of the stored procedure to be executed is passed as an argument to the method.

The following code shows how to use GetStoredProcCommand. 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.

DbCommand cmd = db.GetStoredProcCommand("GetProductsByCategory"); 

'Usage
Dim cmd As DbCommand = db.GetStoredProcCommand("GetProductsByCategory")

Note

Stored procedure parameters are supported by methods on the Database class. For information about how to use stored procedure parameters, see Handling Parameters.