CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
***** Fun with Data Readers *****
***** Info about your connection *****
Database location: (local)\SQLEXPRESS
Database name: AutoLot
Timeout: 30
Connection state: Open
***** Record *****
CarID = 83
Make = Ford
Color = Rust
PetName = Rusty
***** Record *****
CarID = 107
Make = Ford
Color = Red
PetName = Snake
Obtaining Multiple Result Sets Using a Data Reader
Data reader objects can obtain multiple result sets using a single command object. For example, if you
want to obtain all rows from the Inventory table, as well as all rows from the Customers table, you can
specify both SQL select statements using a semicolon delimiter, like so:
string strSQL = "Select * From Inventory;Select * from Customers";
After you obtain the data reader, you can iterate over each result set using the NextResult() method.
Note that you are always returned the first result set automatically. Thus, if you want to read over the
rows of each table, you can build the following iteration construct:
do
{
while (myDataReader.Read())
{
Console.WriteLine("***** Record *****");
for (int i = 0; i < myDataReader.FieldCount; i++)
{
Console.WriteLine("{0} = {1}",
myDataReader.GetName(i),
myDataReader.GetValue(i).ToString());
}
Console.WriteLine();
}
} while (myDataReader.NextResult());
At this point, you should be more aware of the functionality data reader objects bring to the table.
Always remember that a data reader can only process SQL Select statements; you cannot use them to
modify an existing database table using Insert, Update, or Delete requests. Modifying an existing
database requires additional investigation of command objects.
837