Free mag vol1 | Page 889

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