DataReader in ADO.NET

DataReader:

A DataReader is a lightweight object that provides read-only, forward-only data in a very fast and efficient way. Using a DataReader is efficient than using a DataAdapter, but it is limited. Data access with DataReader is read-only meaning to that, we cannot make any changes (update) to data into Database and forward-only, which means we cannot go back to the previous record which was accessed. A DataReader requires the exclusive use of an active connection for the entire time it is in existence. We instantiate a DataReader by making a call to a Command object's ExecuteReader command. When the DataReader is first returned it is positioned before the first record of the result set. To make the first record available we need to call the Read method. If a record is available, the Read method moves the DataReader to next record and returns True. If a record is not available the Read method returns False.

Instantiating the DataReader:

The DataReader is very easy to use. To get an instance of the DataReader object. You call the ExecuteReader() of the Command Object, rather then using the DataAdapter. The ExecuteReader()  returns a new instance of a DataReader object ready to display data starting at the first record returned.

Syntax of OleDbDataReader:

Function CreateDataReader_OleDb(ByVal connString As String, _
    ByVal sql As String) As System.Data.OleDb.OleDbDataReader
    ' open the connection
    Dim cn As New System.Data.OleDb.OleDbConnection(connectionString)
    connection.Open()
    ' prepare the SQL SELECT command
    Dim cmd As New System.Data.OleDb.OleDbCommand(sql, connection)
    ' create a DataReader
    Dim dr As System.Data.OleDb.OleDbDataReader = cmd.ExecuteReader _
        (CommandBehavior.CloseConnection)
    ' dispose the Command object and return the result
    cmd.Dispose()
    Return dr
End Function 

Syntax of SqlDataReader:

Function CreateDataReader_OleDb(ByVal connString As String, _
    ByVal sql As String) As System.Data.OleDb.OleDbDataReader
    ' open the connection
    Dim cn As New System.Data.OleDb.OleDbConnection(connectionString)
    connection.Open()
    ' prepare the SQL SELECT command
    Dim cmd As New System.Data.SqlClient.SqlCommand(sql, connection)
    ' create a DataReader
    Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader _
        (CommandBehavior.CloseConnection)
    ' dispose the Command object and return the result
    cmd.Dispose()
    Return dr
End Function

Coding for OleDbDataReader:

Imports System.Data.OleDb
Module Module1
    Public constring As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\my_db2.accdb")
End Module

  Imports System.Data.OleDb 
Public Class Form1
    Dim cmd As OleDbCommand
    Dim reader As OleDbDataReader
    Dim Da As OleDbDataAdapter
    Dim SqlString As String
    Dim Da1 As OleDbCommandBuilder
    Dim Ds As DataSet
    Dim pTable As DataTable
    Dim Dr As OleDbDataReader       
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Me.Close() 
    End Sub 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            constring.Open()
            SqlString = "insert into info2 values("
            SqlString += """" & TextBox2.Text & """"
            SqlString += ","
            SqlString += """" & TextBox3.Text & """"
            SqlString += ","
            SqlString += """" & TextBox4.Text & """"
            SqlString += ")"
            Dim Da As New OleDbDataAdapter() 
            Da.InsertCommand = New OleDbCommand(SqlString, constring)
            Da.InsertCommand.ExecuteNonQuery()
            TextBox2.Text = ""
            TextBox3.Text = ""
            TextBox4.Text = ""
            MessageBox.Show("A Record inserted through DataAdopter ")            
        Catch ex As Exception
            MessageBox.Show(ex.Message) 
        End Try               
    End Sub 
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click 
        Try
            constring.Open()
            Ds = New DataSet
            SqlString = "select * from info2"
            Da = New OleDbDataAdapter(SqlString, constring)
            Da.Fill(Ds, "info2")
            DataGridView1.DataSource = Ds.Tables(0)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 
        Try
            constring.Open()
            Dim Sq As String
            Sq = "select* from info2"
            cmd = New OleDbCommand(Sq, constring) 
            Dr = cmd.ExecuteReader()
            Dim x, count As Integer 
            count = 0 
            Do While Dr.Read()
                For x = 0 To Dr.FieldCount - 1
                    TextBox1.Text = Dr(2)
                    TextBox5.Text = Dr(1)
                    TextBox6.Text = Dr(0)                    
                Next
                Console.WriteLine()
                x += 1
            Loop  
        Catch ex As Exception
            MessageBox.Show(ex.Message)  
        End Try  
    End Sub
End Class

Output:

Additional Resource:  MSDN