ADO.NET in a Nutshell

Command Object Overview

The Command object is the heart of data processing with ADO.NET. Typically, the Command object wraps a SQL statement or a call to a stored procedure. For example, you might use a Command object to execute a SQL UPDATE, DELETE, INSERT, or SELECT statement. However, ADO.NET providers that don’t represent databases may use their own nomenclature. The only rule is that the Command.CommandText property, which defines the command, must be a string.

As with the Connection object, the Command object is specific to the data provider. Two examples are:

• System.Data.SqlClient.SqlCommand executes commands against SQL Server Version 7.0 or later.

• System.Data.OleDb.OleDbCommand executes commands against an OLE DB data provider.

Each Command object implements the System.Data.IDbCommand interface. That means it is guaranteed to support the members shown in Tables 4-1 and 4-2. At a minimum, you must set the CommandText and a reference to a valid Connection before using a Command. In addition, you must modify the CommandType default value if you wish to invoke a stored procedure.

Table 4-1. IDbCommand properties

Member	Description
CommandText	Contains the SQL statement, stored procedure name, or table name. For an unusual provider (one that doesn’t work with a database), this can contain something entirely different and proprietary; the only requirement is that is must be formatted as a string.
CommandTimeout	The amount of time (in seconds) to wait for a command to complete before giving up and throwing an exception. The default is 30 seconds.
CommandType	Indicates the format used for the CommandText property. You can use Text (the default) for a SQL command, StoredProcedure for a stored procedure call, or TableDirect for one or more tables (which is a poor scalability choice because it returns all rows and columns from the named table).
Connection	References the IDbConnection object to use for this command. The connection must be open before you execute the command.
Parameters	A collection of input, output, or bidirectional parameter objects. This is used only for parameterized queries or stored procedure calls.
Transaction	Gets or sets the transaction that this command is part of. Transactions are examined in Chapter 16.
UpdatedRowSource	Specifies how this command updates a data source when it is used with a DataSet and IDbDataAdapter. We’ll return to this topic in the next chapter.

Table 4-2. IDbCommand methods

Member	Description
Cancel( )	Tries to stop a running command. In order to invoke this method, you must start the command on a separate thread, because all commands execute synchronously. Otherwise, your code will be stalled and won’t have a chance to call the Cancel( ) method.
CreateParameter( )	Creates a new Parameter object, which can be added to the Command.Parameters collection.
ExecuteReader( )	Executes the command and returns a forward-only read-only cursor in the form of a DataReader.
ExecuteNonQuery( )	Executes the command and returns the number of rows that were affected. Often used with record UPDATE, DELETE, or INSERT statements.
ExecuteScalar( )	Executes the command, and retrieves a single value. Used with aggregate functions and in cases where you want to return the first column of the first row of a result set.
Prepare( )	If CommandType is StoredProcedure, you can use this method to precompile the command in the data source. If you perform this task before calling the same stored procedure with different parameters, you may achieve a small performance increase, depending on the provider. However, it requires an additional roundtrip to the data source, so don’t use it unless you have tested it and are sure it actually provides a measurable benefit.