Database Reference
In-Depth Information
How It Works
This code is a bit different from what you've written earlier. When the call to the
GetSchemaTable
method
is made, a populated instance of a data table is returned.
// Store Person's schema in a data table
DataTable schema = rdr.GetSchemaTable();
You can use a data table to represent a complete table in a database, either in the form of a table
that represents its schema or in the form of a table that holds all its original data for offline use.
In this example, once you grab hold of a schema table, you retrieve a collection of rows through the
Rows
property of
DataTable
and a collection of columns through the
Columns
property of
DataTable
. (You
can use the
Rows
property to add a new row into the table altogether or remove one, and you can use the
Columns
property for adding or deleting an existing column—we'll cover this in Chapter 15.) Each row
returned by the table describes one column in the original table, so for each of these rows, you traverse
through the column's schema information one by one, using a nested
foreach
loop.
// Display info from each row in the data table.
// Each row describes a column in the database table.
foreach (DataRow row in schema.Rows)
{
foreach (DataColumn col in schema.Columns)
{
txtSchema.AppendText(col.ColumnName + " = " + row[col]);
txtSchema.AppendText("\n");
}
txtSchema.AppendText("-----------------");
}
Notice how you use the
ColumnName
property of the
DataColumn
object to retrieve the current schema
column name in the loop, and then you retrieve the value related to that column's definition by using
the familiar indexer-style method that uses a
DataRow
object.
DataRow
has a number of overloaded
indexers, and this is only one of several ways of doing it.
Using Multiple Result Sets with a Data Reader
Sometimes you may really want to get a job done quickly and also want to query the database with two
or more queries at the same time. And, you wouldn't want the overall application performance to suffer
in any way either by instantiating more than one command or data reader or by exhaustively using the
same objects over and over again, adding to the code as you go.
So, is there a way you can get a single data reader to loop through multiple result sets? Yes, data
readers have a method,
NextResult()
, that advances the reader to the next result set.
Try It: Handling Multiple Result Sets
In this example, you'll use
NextResult()
to process multiple result sets.
1.
Select the DataReader project, right-click, and choose Add
Windows Form.
From the opened dialog, make sure the Windows form is selected, and rename
Form1.cs to MultipleResults.cs. Click OK to add this form to the DataReader
project.
