Difference between Datareader, Dataset, DataTable and DataAdapter in VB.Net

Difference between Datareader, Dataset, DataTable and DataAdapter in VB.Net

DataReader :

It is read only format, we can't update records. It is connection oriented, whenever data bind from database that need connection and then connection is disconnected.

Dataset :

It is connectionless oriented. Whenever we bind data from database. It connects indirectly to the database and then disconnected. It’s easily read and write data from database.

DataTable :

DataTable represents a single table in the database. It has rows and columns. There is no much difference between dataset and datatable, dataset is simply the collection of datatables.

DataAdapter :

DataAdapter is a disconnected oriented architecture. DataAdapter is like a mediator between DataSet (or) DataTable and database. This DataAdapter is used to read the data from database and bind to dataset.

Here we've a DataGrid, Now we fill this DataGrid using DataReader, DataSet, DataTable and DataAdapter in VB.Net

Using DataAdepter (da) and Dataset (ds)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Try

        con = New SqlConnection("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=sa;Password=****")

        con.Open()

        sql = "SELECT * ItemCode,ItemName,FrgnName FROM OITM"

        cmd = New SqlCommand(sql, con)

        da = New System.Data.SqlClient.SqlDataAdapter(cmd)

        ds = New System.Data.DataSet

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

    con.Close()

End Sub

Using Dataset (ds)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Try

        con = New SqlConnection("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=sa;Password=****")

        con.Open()

        sql = "SELECT * ItemCode,ItemName,FrgnName FROM OITM"

        da = New System.Data.SqlClient.SqlDataAdapter(sql, con)

        ds = New System.Data.DataSet

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

    con.Close()

End Sub

Using DataTable (dt)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Try

        con = New SqlConnection("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=sa;Password=****")

        con.Open()

        sql = "SELECT * ItemCode,ItemName,FrgnName FROM OITM"

        da = New System.Data.SqlClient.SqlDataAdapter(sql, con)

        Dim dt As New DataTable

        dt.Clear()

        da.Fill(dt)

        DataGridView1.DataSource = dt

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

    con.Close()

End Sub

Using DataReader (dr)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    DataGridView1.Rows.Clear()

    Try

        con = New SqlConnection("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=sa;Password=****")

        con.Open()

        sql = "SELECT * ItemCode,ItemName,FrgnName FROM OITM"

        cmd = New SqlCommand(sql, con)

        dr = cmd.ExecuteReader()

        If dr.HasRows Then

            DataGridView1.ColumnCount = 3

            DataGridView1.Columns(0).Name = "ItemCode"

DataGridView1.Columns(1).Name = "ItemName"

DataGridView1.Columns(2).Name = "FrgnName"

        End If

        While dr.Read()

            DataGridView1.Rows.Add(dr(0), dr(1), dr(2))

        End While

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

    con.Close()

End Sub

Using DataReader (dr) and DataSouce

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Try

        con = New SqlConnection("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=sa;Password=****")

        con.Open()

        sql = "SELECT * ItemCode,ItemName,FrgnName FROM OITM"

        cmd = New SqlCommand(sql, con)

        dr = cmd.ExecuteReader

        While dr.Read()

            Dim source As New BindingSource

            source.DataSource = dr

            DataGridView1.DataSource = source

        End While

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

    con.Close()

End Sub

Using DataReader (dr) and DataTable (dt)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Try

        con = New SqlConnection("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=sa;Password=****")

        con.Open()

        sql = "SELECT * ItemCode,ItemName,FrgnName FROM OITM"

        cmd = New SqlCommand(sql, con)

        dr = cmd.ExecuteReader

        Dim dt As New DataTable

        dt.Clear()

        dt.Load(dr)

        DataGridView1.DataSource = dt

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

    con.Close()

End Sub

Using DataReader (dr) Dynamically

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    DataGridView1.Rows.Clear()

    Try

        con = New SqlConnection("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=sa;Password=****")

        con.Open()

        sql = "SELECT * ItemCode,ItemName,FrgnName FROM OITM"

        cmd = New SqlCommand(sql, con)

        dr = cmd.ExecuteReader()

        If dr.HasRows Then

            DataGridView1.ColumnCount = dr.FieldCount

            For i = 0 To dr.FieldCount - 1

                DataGridView1.Columns(i).Name = "Column" & i + 1 & ""

            Next

        End If

        Dim dgvCell As DataGridViewCell

        While dr.Read()

            Dim dgvRow As New DataGridViewRow

            For i = 0 To dr.FieldCount - 1

                dgvCell = New DataGridViewTextBoxCell()

                dgvCell.Value = dr(i)

                dgvRow.Cells.Add(dgvCell)

            Next

            DataGridView1.Rows.Add(dgvRow)

        End While

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

    con.Close()

End Sub