Executing a Command

Executing a Command

In general, the most efficient way to work with your data stores is to execute commands directly on the database server—that is, you allow the database server to handle all requests for the data directly. Doing this will allow the database server to do what it does best, and what it’s designed for. Typically, when you’re working with a database system, it will be able to optimize commands to be performed more efficiently. This is especially the case when you’re working with stored procedures on the server rather than writing ad hoc queries.

ADO.NET lets you execute commands directly on the database server by initializing a Command object. The Command object is used to execute a variety of commands to the database server and to handle data that’s returned as a result of those commands.

Constructing Command Objects

When you instantiate the Command object, you’ll have the option of using one of four constructors. The first constructor doesn’t provide any parameters, as shown here:

SqlCommand cmd = new SqlCommand();

This constructor sets the properties of the Command object to their default values. You’ll learn how to change these values on your own in the following sections.

You can also initialize the object with the command text at the start, by providing it as a parameter in the constructor, as shown in the following code. The command text can be the name of the stored procedure, a table name, or a SQL statement.

SqlCommand cmd = new SqlCommand("myCommandText");

You can also add the Connection object that this Command object will use to the constructor, as shown in the following code. This will define the connection that the Command object will use to access the database.

SqlConnection conn = new SqlConnection();
//Initialize connection.
SqlCommand cmd = new SqlCommand("myCommandText", conn);

Last, if your commands are also going to be executed within the context of a transaction, you can initialize the command with the transaction object as well, as shown here:

SqlConnection conn = new SqlConnection();
//Initialize connection.
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand("myCommandText", conn, trans);
Providing Command Text

When you create a Command object, you can initialize the object with command text, as shown in the previous section. You can also specify the text of the command using the CommandText property, as shown in the following code. As mentioned, the command text might be the name of a stored procedure, a table name, or a SQL statement.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "myStoredProcedureName";    //Stored procedure
cmd.CommandText = "authors"                   //Table name
cmd.CommandText = "SELECT * FROM authors"     //SQL statement
Associating with a Connection

You’ll need to associate the Command object with a database connection, which the object will use to communicate with the database, as shown here:

SqlConnection conn = new SqlConnection();
//Initialize connection.
SqlCommand cmd = new SqlCommand("myCommandText");
cmd.Connection = conn;

As you can see from this code, if you don’t initialize the Command object with the connection, you’ll need to assign the Connection object to the Command object’s Connection property.

Associating with a Transaction

If you want to execute the command within the context of a database transaction, you’ll need to associate the Command object with a Transaction object. You can do so when you instantiate the object, as shown earlier in this chapter, in the section “Constructing Command Objects,” or you can assign an existing transaction to the Command object’s Transaction property, as shown here:

SqlConnection conn = new SqlConnection();
//Initialize connection.
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand("myCommandText", conn);
cmd.Transaction = trans;
Configuring the Command Time-Out

Each command has an associated time-out value. If the command doesn’t return a result within that time, a time-out error is triggered. This prevents the calling application from getting caught up in a command that takes too long to execute. When you create a Command object, you can set the time-out value using the CommandTimeout property, as shown in the following code. The default value for this property is 30 seconds.

SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 60;
Specifying Command Types

Before you execute a command through the Command object, you’ll need to specify the type of command that will be executed using the CommandType property. The available types are listed here:

  • StoredProcedure  The text you’ve provided as the command is the name of the stored procedure on the server that will be executed. If the stored procedure requires parameters, you’ll need to add the required parameters to the Parameters property of the Command object.

  • TableDirect  The text you’ve provided is the name of a table in the data store. You can specify multiple tables by providing a comma-delimited list of tables without any spaces. In the case of multiple tables, a JOIN will be performed with the returned tables.

  • Text  The text you’ve provided is an ad hoc SQL command. This is the default command type.

You can use the enumerated command types to specify the type of any Command object, as shown here:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "myStoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
Passing Parameters

When you’re making use of stored procedures in particular, you’ll often pass parameters to the procedure to define how the command should be executed. You do this by creating and adding parameters and their associated values to the Command object. In ADO.NET, you use the Parameter object to create and add these parameters. You’ll learn how to create and add parameters later in this chapter, in the section “Creating and Adding Parameters.”

Each Command object has a Parameters property. This property holds a SqlParameterCollection or an OleDbParameterCollection object (depending on your data provider), which holds all the parameters for the Command object.

Specifying Parameter Direction

Each parameter you define will have an associated direction. This direction is specified in the Direction property of the Parameter object and indicates whether you’re providing data to the stored procedure or you’re expecting data to be returned from the procedure. The allowable directions are listed here:

  • Input

  • InputOutput

  • Output

  • ReturnValue

Specifying Parameter Types

The parameter type you’ll use will be provided in either the DbType or the SqlDbType property of the Parameter object, for the OLE DB or SQL Server data provider, respectively. The DbType values are enumerated as follows:

The SqlDbType values are enumerated as follows:


























Both SqlDbType and DbType are properties of the SqlParameter class. They’re linked, so setting the SqlDbType will automatically set the DbType property. Likewise, setting the DbType will automatically set the SqlDbType.

Creating and Adding Parameters

To create a parameter, you can explicitly declare a Parameter object, set the required properties, and then add the parameter to the parameter collection of the Command object, as shown here:

SqlParameter parm = new SqlParameter();
parm.ParameterName = "@username";
parm.Direction = ParameterDirection.Input;
parm.SqlDbType = SqlDbType.Varchar;
parm.Value = "myParamValue";


This example code uses the base constructor of the Parameter object to create a parameter. It specifies the parameter’s name using the ParameterName property in the form @paramname. Then it sets other property values using the Direction, SqlDbType, and Value properties of the Parameter object.

This object’s constructor is overloaded, so you can initialize a new instance of the object in several ways. You can provide the parameter name and the value as the object’s parameters, or you can provide the parameter name and the data type as the object’s parameters. Both approaches are shown here:

SqlParameter parm = new SqlParameter("@username", "myParamValue");
SqlParameter parm = new SqlParameter("@username", SqlDbType.Varchar);

To add a parameter to the parameter collection, you can also perform most of these steps in a single line, by using another overloaded version of the Add method, as follows:

cmd.Parameters.Add("@username", SqlDbType.VarChar).Value 
    = "MyParamValue";
Executing Commands

After the CommandText and CommandType properties and any required parameters have been defined and added to the command, you’ll be able to execute the command. The Command object uses several different methods to execute commands, based on the type of command and expected return data.


If your query isn’t going to return a result set or any value other than the number of rows that were affected, you’ll use the ExecuteNonQuery method, as shown here:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "updateUserStatus";
cmd.CommandType = CommandType.StoredProcedure;
recordsAffected = cmd.ExecuteNonQuery();

This example creates a Command object and uses the object with a stored procedure named updateUserStatus.

The result of the ExecuteNonQuery method for INSERT, UPDATE, and DELETE statements will be a report of the number of rows affected. For other types of statements, it will return -1 on successful execution.


The ExecuteScalar method is typically used to execute a command that will return a value, as shown in the following code. In fact, the method is used to execute any command, and it returns the value in the first column of the first row of the result set.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "getInventoryLevel";
cmd.CommandType = CommandType.StoredProcedure;
inventoryLevel = cmd.ExecuteScalar();

The ExecuteReader method is used to execute the command and returns the result set as the DataReader object, as shown here:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "getProductList";
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader();

The DataReader object gives you fast and efficient access to data, in a forward-only, read-only mode. (DataReader is covered in more detail later in this chapter, in the section “Using a DataReader Object to Retrieve Data.”)

Alternatively, you can specify how the ExecuteReader method should operate by providing a parameter to the method that defines the Command­Behavior. The available members of the CommandBehavior enumeration are listed in Table 18-4.

Table 18-4.  CommandBehavior Enumeration Members




The connection is closed after the command has been executed.


No behavior is defined. The command can return multiple result sets and can affect the database state.


The command returns column and primary key information. It doesn’t lock any rows of data.


The command returns column information and doesn’t affect database state.


The command allows the DataReader object to load the data as a stream and retrieve data from large binary values using GetBytes or GetChars methods.


The command will return only a single result set.


The command will return only a single row in a single result set.


The ExecuteXMLReader method is provided only by the SQL Server .NET Data Provider and will return the results as an XMLReader object. To return the data in XML format from Microsoft SQL Server 2000, the CommandText property usually specifies a SQL statement with the FOR XML clause, as shown here:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM authors FOR XML";
XMLReader reader = cmd.ExecuteXMLReader();

Part III: Programming Windows Forms