When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, March 27, 2002

Efficiently Iterating Through Results from a Database Query using ADO.NET, Part 2

By Scott Mitchell


  • Read Part 1

  • 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.

    - continued -

    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!)

    Happy Programming!

  • By Scott Mitchell



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article