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