Retrieve Data Using Data Readers

Submitted by admin on Monday, April 27, 2009 - 17:52.

This tutorial is part of Database Programming Made Easy Series. Retrieve Data Using Data Readers

Data reader is like an ado forward-only/read-only client side record set. Data reader is best suited if you only need to retrieve data, that is, you don't want to save the data back to the database. To retrieve and save the data you need a dataset which will be discuss in the next topic. When retrieving data using data reader you take advantage of loading your data faster than dataset. Data reader will loop through your recordset by means of SqlDataReader for SQL Server or OleDbDataReader for MS Access. Now what’s the use of a data reader if it cannot save data back to the database? This simply means that you need only to read the data like determining the number of record in a table or getting a value from one or more field. Say for example. If you want to know how many Customers are living in Seattle you would access it by using this SQL statement: SELECT * FROM Customers WHERE City = 'Seattle' And then execute it using a data reader. In this way the record set will return faster compare to dataset. Let’s try it out using the connection we’ve need in the previous topic. The following code will retrieve data in a Customer’s table where City is equal to Seattle. 1. Data Reader using MS Access

        'Set up connection string
        Dim cnString As String
 
        cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb"
 
        'create command (with both text and connection)
        Dim sqlQRY As String = "SELECT * FROM Customers WHERE City = 'Seattle'"
 
        'Create connection
        Dim conn As OleDbConnection = New OleDbConnection(cnString)
 
        Try
            ' Open connection
            conn.Open()
 
            Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, conn)
 
            'create data reader
            Dim rdr As OleDbDataReader = cmd.ExecuteReader
 
            While (rdr.Read)
                txtStatus.Text = txtStatus.Text & rdr("CompanyName").ToString() 
            End While
 
        Catch ex As SqlException
            txtStatus.Text = "Error: " & ex.ToString & vbCrLf
        Finally
            ' Close connection
            conn.Close()
            txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed."
        End Try

2. Data Reader using Microsoft SQL Server

        'Set up connection string
        Dim cnString As String
 
        cnString = "Data Source=localhost;Integrated Security=True;database=northwind"
 
        'create command (with both text and connection)
        Dim sqlQRY As String = "SELECT * FROM Customers WHERE City = 'Seattle'"
 
        'Create connection
        Dim conn As SqlConnection = New SqlConnection(cnString)
 
        Try
            ' Open connection
            conn.Open()
 
            Dim cmd As SqlCommand = New SqlCommand(sqlQRY, conn)
 
            'create data reader
            Dim rdr As SqlDataReader = cmd.ExecuteReader
 
            While (rdr.Read)
                txtStatus.Text = txtStatus.Text & rdr("CompanyName").ToString() 
            End While
 
        Catch ex As SqlException
            txtStatus.Text = "Error: " & ex.ToString & vbCrLf
        Finally
            ' Close connection
            conn.Close()
            txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed."
        End Try
    End Sub

To simplify this tutorial I remove some line of code which is not actually part of the discussion. You will, however, see it in the source code file. By examining the code above the only difference between MS Access and SQL Server is by connecting to a database, creating a command and executing a data reader as shown below.

Microsoft Access	Microsoft SQL Server
OleDbConnection	SqlConnection
OleDbCommand	SqlCommand
OleDbDataReader	SqlDataReader

The important part here is on how you can retrieve the data. This can be done using the code below:

While (rdr.Read)
     txtStatus.Text = txtStatus.Text & rdr("CompanyName").ToString() 
End While

Previous: How to Modify Data Next: How to Retrieve Data Using Datasets and Data Adapters