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 .MovePrev
calls.)
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.
Calling the 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
OleDbCommand object's ExecuteReader method returns an
OleDbDataReader object; the SqlCommand object's
ExecuteReader method returns a SqlDataReader object.
...
' Set an OleDbDataReader to the command's results
Dim objDataReader as OleDbDataReader
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
...
The code above calls the ExecuteReader method of the OleDbCommand class,
passing in 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
DataSource property of the DataGrid to our OleDbDataReader and
then call the DataBind() method. The complete code can be seen below.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
sub Page_Load(sender as Object, e as EventArgs)
'Create a connection string
Dim connString as String
connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & _
"C:\Inetpub\wwwrot\Projects.mdb;"
'Open a connection
Dim objConnection as OleDbConnection
objConnection = New OleDbConnection(connString)
objConnection.Open()
'Specify the SQL string
Dim strSQL as String = "SELECT * FROM Department"
'Create a command object
Dim objCommand as OleDbCommand
objCommand = New OleDbCommand(strSQL, objConnection)
'Get a datareader
Dim objDataReader as OleDbDataReader
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
'Do the DataBinding
dgResults.DataSource = objDataReader
dgResults.DataBind()
'Close the datareader/db connection
objDataReader.Close()
end sub
</script>
<asp:DataGrid id="dgResults" runat="server" />
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 EOF property was True (thereby signalling the end of the Recordset
had been reached), doing a MoveNext at 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: DataReaderObject("ColumnName").
The following code shows how to iterate through the contents of our data reader. Note that
the Read method in the While loop functions like both the EOF
and 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
any .MoveNext-type method in the body of the While loop.
...
While objDataReader.Read()
do something
' e.g.: str = objDataReader("DepartmentName")
End While
...
Conclusion
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 ExecuteReader method 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!)
