19.2 Properties Reference

String commandText = Command.CommandText;
Command.CommandText = commandText;

Defines the action that's taken when this command executes. The meaning of the CommandText property depends on the value of the CommandType property. If CommandType is Text (the default), it's the text of a SQL statement (such as SELECT * FROM Customers). If CommandType is StoredProcedure, it's the name of the stored procedure that's executed. If CommandType is TableDirect, it's the name of a table that's returned or a comma-delimited list of tables that's joined and returned.

Example

The following example defines a Command and sets the CommandText with a SQL INSERT statement. When executed, this Command creates a new row.

string connectionString = "Data Source=localhost;" +
  "Initial Catalog=Northwind;Integrated Security=SSPI";

string sQL = "INSERT INTO Categories (CategoryName, Description) " +
              "VALUES ('Beverages', 'Soft drinks')";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();

cmd.Connection = con;
cmd.CommandText = sQL;

int rowsAffected;
try
{
    con.Open();

    // Execute the command.
    rowsAffected = cmd.ExecuteNonQuery();
}
finally
{
    con.Close();
}

Notes

You can set two properties of the Command object—the linked Connection and the CommandText—using an overloaded constructor when you create the Command:

SqlCommand cmd = new SqlCommand(commandText, con);

This is usually the easiest approach to setting these properties. You can then reuse the Command to perform a different task with the same data source by modifying the CommandText property.

Some providers support batch queries, in which case you can execute multiple commands at once and even return multiple result sets by separating each command with a semicolon:

cmd.CommandText = "SELECT * FROM Products;SELECT * FROM Orders";

For some exotic providers (those to a data source other than a database), the CommandText may not contain a SQL statement; in fact, it can contain something entirely different and proprietary. The only guarantee is that the CommandText property must contain a string.

Int32 timeout = Command.CommandTimeout;
Command.CommandTimeout = timeout;

Configures the time in seconds that a command will wait once you execute it. If the command hasn't completed once the timeout is reached, the attempt is aborted, and a provider-specific exception (such as SqlException or OleDbException) is thrown.

The default timeout is 30 seconds. You can set the timeout to 0 to specify an infinite timeout, but this isn't recommended because it can stall your application indefinitely. You can call the Command.Cancel( ) method from a separate thread to halt an in-progress command.

Example

The following code fragment defines a timeout of 15 seconds:

SqlCommand cmd = new SqlCommand(commandText, con);
cmd.CommandTimeout = 15;

try
{
    con.Open();
    // (Now execute the command)
}
catch (SqlException err)
{
    // This could indicate a timeout after 15 seconds. 
}
finally
{
    con.Close();
}

CommandType commandType = Command.CommandType;
Command.CommandType = commandType;

Indicates how the CommandText property should be interpreted, using the values from the System.Data.CommandType enumeration. Table 19-2 lists possible values.

Table 19-2. CommandType values

Value	Description
StoredProcedure	CommandText holds the name of the stored procedure that will be invoked. Input and output parameters for the stored procedure are contained in the Command.Parameters collection.
TableDirect	CommandText holds the name of the table that will be queried. All the columns and rows will be retrieved from this table when the command is executed. You can also use a comma-delimited list of tables that will be automatically joined together.
Text	CommandText holds the full text of the SQL command. The command may be a direct SQL statement, a stored procedure call with inline parameters, or a parameterized query, in which case the Command.Parameters collection holds the input and output parameters.

Example

The following code snippet configures a Command object to call a stored procedure called GetCustomers. The CommandText and Connection properties are set using a Command constructor.

SqlCommand cmd = new SqlCommand("GetCustomers", con);
cmd.CommandText = CommandType.StoredProcedure;

Note

TableDirect isn't supported by all providers and isn't suitable for an enterprise-level application because it returns all the information from all the rows of a table. This wastes bandwidth and server time retrieving information that may not be important. A much better approach is to selectively limit the rows you return with a WHERE clause and the columns of information you need. Ideally, database access should be performed through a stored procedure, which can be precompiled and optimized on the database server.

IDbConnection con = Command.Connection;
Command.Connection = con;

Identifies the connection that the Connection uses to execute the Command.

Note

You can't modify this property if the Command is enlisted in a client-initiated transaction that hasn't yet been committed or rolled back. (A command is enlisted in a client-initiated transaction by setting its Transaction property.)

IDbTransaction tran = Command.Transaction;
Command.Transaction = tran;

Allows you to enlist the command in a client-initiated transaction. For example, if you set the Transaction property of three Command objects with the same Transaction object, and then invoke these commands, they all execute in the same transaction. When you commit or roll back the transaction, the work performed by all three Command objects are committed or rolled back as a unit.

The actual Transaction object is a provider-specific object that implements IDbTransaction and is created using the Connection.BeginTransaction( ) method.

Example

The following example creates two OleDbCommand objects and places them in the same client-initiated transaction using the Transaction property. Both commands are then executed, but their changes are rolled back immediately afterward.

string connectionString = "Data Source=localhost;Provider=SQLOLEDB;" +
  "Initial Catalog=Northwind;Integrated Security=SSPI ";

string SQL1 = "INSERT INTO Categories (CategoryName, Description) " +
              "VALUES ('Beverages', 'Soft drinks')";
string SQL2 = "UPDATE Categories SET Description ='Coffee and tea' " +
              "WHERE CategoryName='Beverages'";

OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmdA = new OleDbCommand(SQL1, con);
OleDbCommand cmdB = new OleDbCommand(SQL2, con);

con.Open();

// Start the transaction.
OleDbTransaction tran = con.BeginTransaction();

// Enlist the commands.
cmdA.Transaction = tran;
cmdB.Transaction = tran;

// Execute the commands.
cmdA.ExecuteNonQuery();
cmdB.ExecuteNonQuery();

// Roll back the transaction (cancelling the changes).
tran.Rollback();
con.Close();

Note

You will receive an exception if you attempt to execute a Command with a Transaction object created for a different Connection.

UpdateRowSource rowSource = Command.UpdateRowSource;
Command.UpdateRowSource = rowSource;

When this Command is used to commit changes with a DataAdapter, the UpdateRowSource property defines how the results from the Command will be applied to the original DataRow. This is primarily useful if your Command is invoking a stored procedure, and the stored procedure returns some type of generated information (such as a new value for a unique identifier column).

To specify how this returned information will be applied, use one of the values from the UpdateRowSource enumeration, as shown in Table 19-3.

Table 19-3. CommandType values

Value	Description
None	All output parameters and any returned rows are ignored.
FirstReturnedRecord	The column values from the first returned record are applied to the source DataRow. In other words, the stored procedure uses a SELECT statement to return the changed row after performing the update.
Parameters	The information from the output parameters is applied to the source DataRow, based on the Parameter.SourceColumn property.
Both	Information from the output parameters and the first returned record is applied to the source DataRow. This is the default. Note that it is uncommon for a stored procedure to return both types of information; the Both value simply ensures that the source information is updated no matter which approach you use.

Example

The following code snippet shows a Command that calls a CategoryAdd stored procedure when the DataAdapter inserts a new category record. This stored procedure returns an output parameter with the new unique CategoryID, which the Command maps to the source DataRow. For a full example of this technique, refer to Chapter 15.

// Create the command.
SqlCommand cmdInsert = new SqlCommand("CategoryAdd", con);
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.UpdatedRowSource = UpdateRowSource.OutputParameters;

SqlParameter param;

// Add an input parameter to the command.
param = cmdInsert.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
param.SourceColumn = "CategoryName";
param.SourceVersion = DataRowVersion.Current;

// Add an output parameter to the command. The value returned by this 
// parameter will be applied to the source DataRow once the insertion is
// complete.
param = cmdInsert.Parameters.Add("@CategoryID", SqlDbType.Int);
param.SourceColumn = "CategoryID";
param.SourceVersion = DataRowVersion.Original;
param.Direction = ParameterDirection.Output;

// Assign the command to the DataAdapter.
adapter.InsertCommand = cmdInsert;

Notes

You can also use this technique to map the information from a stored procedure return value. Remember, the return value is represented by a Parameter object with a Direction of ParameterDirection.ReturnValue. If you set the Command.UpdateRowSource property to Both or Parameters, this value updates the DataRow.

The UpdateRowSource property is almost always used with a stored procedure. Ordinary SQL statements simply returns the number of affected rows, not the new row. Similarly, parameterized queries use only input parameters, not output parameters. To use the UpdateRowSource property effectively, you must add stored procedure code to return the updated information you need.