Creating a DbCommand Object

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:

• Those methods that represent stored procedure calls (for example, GetCustomers)
• Those 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.

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.

Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "Select CustomerID, LastName, FirstName From Customers";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim sqlCommand As String = "Select CustomerID, LastName, FirstName From Customers"
Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)

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 on the method call.

The following code shows how to use GetStoredProcCommand.

Database db = DatabaseFactory.CreateDatabase();

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

Dim db As Database = DatabaseFactory.CreateDatabase()

Dim dbCommand As DbCommand 
dbCommand = db.GetStoredProcCommand("GetProductsByCategory")