CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
// Create another command object via properties.
SqlCommand testCommand = new SqlCommand();
testCommand.Connection = cn;
testCommand.CommandText = strSQL;
Realize that, at this point, you have not literally submitted the SQL query to the AutoLot database,
but instead prepared the state of the command object for future use. Table 21-6 highlights some
additional members of the DbCommand type.
Table 21-6. Members of the DbCommand Type
Member
Meaning in Life
CommandTimeout
Gets or sets the time to wait while executing the command before
terminating the attempt and generating an error. The default is 30 seconds.
Connection
Gets or sets the DbConnection used by this instance of the DbCommand.
Parameters
Gets the collection of DbParameter objects used for a parameterized query.
Cancel()
Cancels the execution of a command.
ExecuteReader()
Executes a SQL query and returns the data provider’s DbDataReader object,
which provides forward-only, read-only access for the result of the query.
ExecuteNonQuery()
Executes a SQL non-query (e.g., an insert, update, delete, or create table).
ExecuteScalar()
A lightweight version of the ExecuteReader() method that was designed
specifically for singleton queries (e.g., obtaining a record count).
Prepare()
Creates a prepared (or compiled) version of the command on the data
source. As you might know, a prepared query executes slightly faster and is
useful when you need to execute the same query multiple times (typically
with different parameters each time).
Working with Data Readers
After you establish the active connection and SQL command, the next step is to submit the query to the
data source. As you might guess, you have a number of ways to do this. The DbDataReader type (which
implements IDataReader) is the simplest and fastest way to obtain information from a data store. Recall
that data readers represent a read-only, forward-only stream of data returned one record at a time.
Given this, data readers are useful only when submitting SQL selection statements to the underlying
data store.
Data readers are useful when you need to iterate over large amounts of data quickly and you do not
need to maintain an in-memory representation. For example, if you request 20,000 records from a table
to store in a text file, it would be rather memory-intensive to hold this information in a DataSet (because
a DataSet holds the entire result of the query in memory at the same time).
835