The IDbCommand interface represents a command that
can be executed against a data source. Some examples include queries
that retrieve rows (a SQL SELECT statement), statements that retrieve
a single piece of information (such as SQL aggregate functions), or
statements designed to modify rows (such as a SQL UPDATE, DELETE, or
INSERT statement). ADO.NET providers that access relational data
implement this interface.
Before, using the command, you must set the
Connection property to a valid
IDbConnection and open the connection. You can
then execute the command in one of three ways. Use
ExecuteNonQuery( ) to execute a statement such as
INSERT, DELETE, or UPDATE, which returns the number of rows affected.
Use ExecuteScalar( ) to execute a statement that
returns a single value, such as an aggregate SQL function (SUM, MAX,
MIN, AVG, and so on). Finally, use ExecuteReader(
) to perform a query and return a
IDataReader that provides access to the result
set. The CommandTimeout property identifies the
number of seconds ADO.NET will wait while trying to execute the
command before throwing a provider-specific exception. The default is
30, and 0 represents an infinite wait. Finally, if you are using a
stored procedure, you can use the Prepare( )
method to compile the stored procedure in the data source, which may
improve performance with some providers if you are executing the same
stored procedure multiple times with different parameters.
(It's recommended that you profile this approach to
gauge if it offers any improvement.)
There are three types of commands (as identified by the
CommandType property). The default is
CommandType.Text , which represents a SQL text
statement set in the CommandText property). You
can also use CommandType.TableDirect to directly
retrieve a single table (if the provider supports it) or
CommandType.StoredProcedure to invoke a stored
procedure. In either case, you identify the table or stored procedure
by name in the CommandText property. For stored
procedures that use parameters, you must add a single
IDataParameter object for each parameter to the
Parameters collection. The order is sometimes
important with parameterized queries. See the description for the
IDataParameter type for more information. Commands
can be enlisted in a client-initiated transaction by setting the
Transaction property to an
IDbTransaction instance.
The UpdatedRowSource property specifies how
command results are applied to the DataRow when a
DataSet is reconciled with the data source using
the Update( ) method of a
DbDataAdapter . The default value,
UpdateRowSource.None , does nothing. However, you
can use UpdateRowSource.OutputParameters to ensure
that the values set in the output parameters of a stored procedure
are applied automatically to the changed DataRow
when the row update is completed. Alternatively, you can use
UpdateRowSource.FirstReturnedRecord to map an
entire row returned by a stored procedure to the
DataRow . This technique is useful if you are
inserting a record that has an identity (or timestamp) value, which
was set or changed during the update.
public interface IDbCommand : IDisposable {
// Public Instance Properties
public string CommandText{set; get; }
public int CommandTimeout{set; get; }
public CommandType CommandType{set; get; }
public IDbConnection Connection{set; get; }
public IDataParameterCollection Parameters{get; }
public IDbTransaction Transaction{set; get; }
public UpdateRowSource UpdatedRowSource{set; get; }
// Public Instance Methods
public void Cancel( );
public IDbDataParameter CreateParameter( );
public int ExecuteNonQuery( );
public IDataReader ExecuteReader( );
public IDataReader ExecuteReader(
CommandBehavior behavior);
public object ExecuteScalar( );
public void Prepare( );
}
