Retrieve Data Using Data Readers

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

Comments

Submitted byAnonymous (not verified)on Tue, 04/28/2009 - 11:19

You can used also this command txtStatus.Text = txtStatus.Text & rdr!CompanyName for more simplistic approach... :)
Submitted byAnonymous (not verified)on Sun, 08/02/2009 - 09:09

The code was very useful, I have just started learning the language and was experiencing some amount of difficulty retreiving the data from the database for enquiry, the code really helped.
Submitted byAnonymous (not verified)on Mon, 01/11/2010 - 02:02

Hello sir... ur site is really good and very helpfull....but i wish it cud be for C#.NET ,am not into Visual Basic actually...can u plss help me out...i mean u dnt hav d same wid C# codes....??? Thanks....
Submitted byAnonymous (not verified)on Mon, 01/11/2010 - 02:04

Hello sir... ur site is really good and veryhelpfull,,,but i am not into VIsual Basic actually,,can u please provide the same site wid C#.NET codes......i wud b vry gratefull to u...!!!
Submitted byAnonymous (not verified)on Tue, 05/18/2010 - 19:01

very useful
Submitted byAnonymous (not verified)on Thu, 09/30/2010 - 11:36

Thnx sir.. BUT WITHOUT GIVING, SELECT * FROM Customers WHERE City = 'Seattle' for the WHERE condition i want to retrieve under any City name given from the CityTable.. sir how to do that??? Thank you
Submitted byAnonymous (not verified)on Sat, 11/19/2011 - 19:47

hi i just wanna ask i have a thesis about monitoring and assets management system i just wanna know ho will i connect two datagrid to each other, and in monitoring how will i do it using RFID?thanks in advance!
Submitted byFastgeek (not verified)on Mon, 02/20/2017 - 20:49

Line 25 in the example for Access connection is wrong. It should be an OleDbException. Otherwise it doesn't catch the errors. Just saying! Otherwise nice code! Cheers!

Add new comment