Efficiently Iterating Through Results from a Database Query using ADO.NET, Part 2By Scott Mitchell
In Part 1 we examined the important first steps of connecting to a database and issuing a database query using ADO.NET. In this final part, we'll look at how to retrieve the values from the database query and how to both display and iterate through the results.
Getting the Database Results
Now that you have created a connection and specified a command we need to get the database results conforming to the query. There are two options here that are both worth mentioning, the option you choose depending on what you plan on doing with the data. If you are just needing read, forward-only access to the database query result, then you'll want to use a data reader object. The data reader is akin to the read-only, firehose cursor in classic ADO. (Here, forward-only and firehose cursors mean that when iterating through the cursor you can only move forward through the results. That is, you can't issue
If you need more flexible access to the data, you'll want to use the DataSet object (which
can be found in the
System.Data namespace. DataSets are much "bulkier" than
data readers, and are akin to disconnected data stores. You can dump multiple queries into
a DataSet, iterate through it however you want, etc. Of course this extra functionality comes
at a performance cost - it is not nearly as efficient as the data reader. Therefore, if you
plan on just iterating through the results in a firehose, read-only manner, use the data
reader. In this article, I'm only going to address the data reader approach; see the documentation
for information on the DataSet.
ExecuteReader method of the Command object returns an appropriate
data reader that is ready to iterate through the results of the query. I say "appropriate"
because there are different data readers for each data provider! Therefore, an
ExecuteReader method returns an
OleDbDataReader object; the
ExecuteReader method returns a
The code above calls the
ExecuteReader method of the
CommandBehavior.CloseConnection. Note that this is an
optional parameter; including it specifies that when the data reader is closed the connection
the data reader is tied to (
objConnection) should also be closed.
Need to Display the Data?
At this point, if you want to display the database query results, you should use data binding with an appropriate ASP.NET data Web control. While in classic ASP you would iterate through the Recordset, systemmatically building up the HTML to display the results, with ASP.NET this technique is highly discouraged. The following example shows how to bind a data reader to a DataGrid Web control. (Including the needed code for the DataGrid, the total number of lines needed is just three.) Note that to perform data binding we just set the
DataSourceproperty of the DataGrid to our
OleDbDataReaderand then call the
DataBind()method. The complete code can be seen below.
Iterating through the Data Reader Results
Displaying the results of a database query using ADO.NET through an ASP.NET Web page is simple: just use data binding. However, if you need to iterate through the results (for whatever reason) data binding can't help. With classic ADO, you would loop until the Recordset's
EOFproperty was True (thereby signalling the end of the Recordset had been reached), doing a
MoveNextat the end of each loop iteration.
With ADO.NET the protocol is similar, although the syntax differs slightly. The data reader
class contains a
Read method that reads in the next record from the data store.
If there are no more records,
Read returns False; otherwise, if there are records,
it returns True. If there are records available, calling
Read loads the columns of
the record into a collection that can then be accessed the same way as you always have with
classic ADO's Recordset object:
The following code shows how to iterate through the contents of our data reader. Note that
Read method in the
While loop functions like both the
MoveNext methods of the classic ADO Recordset object. It loads the next
record from the data store and returns False if there are no more records; hence, we do not need
.MoveNext-type method in the body of the
In this article we examined how to perform a rather common task, iterating through the results of a database query, using ADO.NET. We also looked at how to display the results of a database query through an ASP.NET Web page, which does not require looping through the contents of a database query. Before connecting to a database with ADO.NET you must first decide what data provider to use, because that decision dictates what set of classes we'll use. Next, you need to create the appropriate command object, specifying the SQL query or stored procedure you wish to run. Finally, you use the appropriate command object's
ExecuteReadermethod to get back an appropriate data reader object. Once you have this data reader object you can use it in data binding (to display the database contents) or iterate through its contents. (To learn more about ASP.NET be sure to check out: ASPNET.4GuysFromRolla.com!)