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
