Executing a Command

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);

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

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.

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;

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;

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;

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

BinInt	Int	SmallMoney
Binary	Money	Text
Bit	NChar	TimeStamp
Char	NText	TinyInt
DateTime	NVarChar	UniqueIdentifier
Decimal	Real	VarBinary
Float	SmallDateTime	VarChar
Image	SmallInt	Variant

	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.

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

cmd.Parameters.Add(parm);

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

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

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.

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

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

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

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