Accessing data using DataReader

Introduction:
In this article you can see how to access the data using DataReader from the data source.

About DataReader:
DataReader is a readonly, forward only and connected recordset from the database. In DataReader, database connection is opened until the object is closed unlike DataSet. Using DataReader we can able to access one row at a time so there it is not required storing it in memory. In one DataReader we can get more than one result set and access it one by one. It will be faster for using simple purpose like populating the form. We should not use it for manipulating the records since it will always connect to database.
Let’s see few examples to use DataReader from accessing the data.


The below exaple will get the employee Firstname and Last name from the table EmployeeInfo form MS SQL server database. Using While loop the records are Fetched row by row and displayed in message box. After the last record While loop will end and then we need to close both the DataReader object and also connection object as show in the code.

CODE:

Dim connString As String = "server=local; database=xx; UID=xx; PWD=xx;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select Firstname, LastName from EmployeeInfo"
Dim myCommand As New SqlCommand(strQuery, myConn)
myConn.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
While (myReader.Read())
MessageBox.Show(myReader.GetString(0))
MessageBox.Show(myReader.GetString(1))
End While
myReader.Close()
myConn.Close()

In the above example we used myReader.GetString(0) this will return the first column value of the record set. The above code will result only one record set. Now let us consider an example the resultant set has more than one record set.

Code:

Dim connString As String = "server=local; database=xx; UID=xx; PWD=xx;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select Firstname, LastName from EmployeeInfo;Select * from EmployeeMaster"
Dim myCommand As New SqlCommand(strQuery, myConn)
myConn.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Dim bFlag As Boolean = True
Do Until Not bFlag
While (myReader.Read())
MessageBox.Show(vbTab & myReader.GetName(0) & vbTab & myReader.GetString(1))
End While
bFlag = myReader.NextResult()
Loop

The above code will return two record set. myReader.NextResult() will helps to move from one record set to another record set. From the above two example you will know how to use DataReader in you application.

Summray:
From this article you can able to learn how to uses the DataReader for accessing the data.