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.
Lets see few examples to use DataReader from accessing
the data.
The below example will get the
employee firstname and last name from the table
employeeInfo from 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 shown in the 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.
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.
Summary:
From this article you can able to learn how to uses the
DataReader for accessing the data.