Database Reference
In-Depth Information
// Loop through result set
while (rdr.Read())
{
// Add to listbox - one row at a time
lbxProduct.Items.Add(rdr[0]);
}
Once you call the
Read
method, the next row is returned as a collection and stored in the
SqlDataReader
object. To access data from a specific column, you can use a number of methods (we'll
cover these in the next section), but for this application you use the ordinal indexer lookup method,
giving the column number to the reader to retrieve values (just as you'd specify an index for an array).
Since in this case you choose a single column from the Customers table while querying the database,
only the “zeroth” indexer is accessible, so you hard-code the index as
rdr[0]
.
To use the
connection
for another purpose or to run another query on the database, it's important to
call the
Close
method of
SqlDataReader
to close the reader explicitly. Once a reader is attached to an
active connection, the connection remains busy fetching data for the reader and remains unavailable for
other uses until the reader has been detached from it. That's why you close the reader in the
try
block
rather than in the
finally
block (even though this simple program doesn't need to use the connection
for another purpose).
// Close data reader
rdr.Close();
Using Ordinal Indexers
You use an ordinal indexer to retrieve column data from the result set. Let's learn more about ordinal
indexers. The following code:
rdr[0]
is a reference to the data reader's
Item
property and returns the value in the column specified for the
current row. The value is returned as an object.
Try It: Using Ordinal Indexers
In this example, you'll add a Windows form to the DataReader project, and then you will use an ordinal
indexer.
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 OrdinalIndexer.cs; then click OK to add this form to the
DataReader project.
2.
Select the OrdinalIndexer form by clicking the form's title bar, and set the Size
property's Width to 289 and Height to 351.
3.
Drag a TextBox control to the form, and position it toward the center of the
form. Select this TextBox control, navigate to the Properties window, and set
the following properties:
•
Set the Name property to txtValues.
•
Set the Location property's X to 12 and Y to 12.