CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
A better approach is to create a data reader that spins over each record as rapidly as possible. Be
aware, however, that data reader objects (unlike data adapter objects, which you’ll examine later)
maintain an open connection to their data source until you explicitly close the connection.
You obtain data reader objects from the command object using a call to ExecuteReader().The data
reader represents the current record it has read from the database. The data reader has an indexer
method (e.g, [] syntax in C#) that allows you to access a column in the current record. You can access
the column either by name or by zero-based integer.
The following use of the data reader leverages the Read() method to determine when you have
reached the end of your records (using a false return value). For each incoming record that you read
from the database, you use the type indexer to print out the make, pet name, and color of each
automobile. Also note that you call Close() as soon as you finish processing the records, which frees up
the connection object.
static void Main(string[] args)
{
...
// Obtain a data reader via ExecuteReader().
using(SqlDataReader myDataReader = myCommand.ExecuteReader())
{
// Loop over the results.
while (myDataReader.Read())
{
Console.WriteLine("-> Make: {0}, PetName: {1}, Color: {2}.",
myDataReader["Make"].ToString(),
myDataReader["PetName"].ToString(),
myDataReader["Color"].ToString());
}
}
Console.ReadLine();
}
In the preceding snippet, you overload the indexer of a data reader object to take either a string
(representing the name of the column) or an int (representing the column’s ordinal position). Thus, you
can clean up the current reader logic (and avoid hard-coded string names) with the following update
(note the use of the FieldCount property):
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();
}
If you compile and run your project at this point, you should see a list of all automobiles in the
Inventory table of the AutoLot database. The following output shows the initial few records from my own
version of AutoLot:
836