DataGridView Binding- VB.Net Tutorial
Binding DataGridView is very simple in .Net. Provided the knowledge of connecting to database, executing the SQL Select Queries and filling the dataset with the data fetched is necessary. While data reader also can be used for light weight read only purposes, I am explaining here with dataset. Since the coming articles in this website will refer back this article for DataBinding samples
Connect To DataBase to bind VB.Net DataGridView
We are going to use the following namespaces for using the database related objects in ADO.Net,
Import System.Data
Import System.Data.SqlClient
Next step is to get the connection to the database. Since we have already decided to use the SqlClient, we have to use SqlConnection to open a connection as follows.
1. Initialize the connection object
Dim ConnectionToFetch As New SqlConnection
2. Specify the connection string
ConnectionToFetch.ConnectionString = "Server= localhost\SQLEXPRESS;Database=Northwind;Trusted_Connection=true"
3. Open the connection
ConnectionToFetch.Open()
Note: Northwind database can be obtained from MSDN
http://blogs.msdn.com/smartclientdata/archive/2005/11/02/488258.aspx
Next step is to use the opened connection to fetch data. We have several options to fetch the data. But in this example I am going to use SqlDataAdapter and DataSet.
Read the Data
1. Initialize the SqlDataAdapter and DataSet
Dim SampleDataAdapter As New SqlDataAdapter
Dim SampleSource As New DataSet
2. Create a command object
3. Connect the command object with the opened active connection
4. Assign the command object to the SqlDataAdapter
Dim SelectQry = "SELECT * FROM Products "
Dim SampleCommand As New SqlCommand()
Dim SampleDataAdapter = New SqlDataAdapter()
SampleCommand.CommandText = SelectQry
SampleCommand.Connection = Connection
SampleDataAdapter.SelectCommand = SampleCommand
5. Use SqlDataAdapter’s fill method to fill the dataset
ConnectionToFetch.Fill(SampleSource)
6. Close the connection
ConnectionToFetch.Close()
7. Assign the dataset to the GridView’s Datasource to bind VB.Net DataGridView
GridView1.DataSource = dsGrid.Tables(0)
8. Bind the GridView with Dataset
GridView1.DataBind()
Source Code for VB.Net DataGridView
Code Behind
Imports System.Data.SqlClient
Public Class Form1
Private Const ConnectionString As String = "Server=.\SQLEXPRESS;" & _
"Database=NorthWind;Trusted_Connection=True"
Private ReadOnly Property Connection() As SqlConnection
Get
Dim ConnectionToFetch As New SqlConnection(ConnectionString)
ConnectionToFetch.Open()
Return ConnectionToFetch
End Get
End Property
Public Function GetData() As DataView
Dim SelectQry = "SELECT * FROM Products "
Dim SampleSource As New DataSet
Dim TableView As DataView
Try
Dim SampleCommand As New SqlCommand()
Dim SampleDataAdapter = New SqlDataAdapter()
SampleCommand.CommandText = SelectQry
SampleCommand.Connection = Connection
SampleDataAdapter.SelectCommand = SampleCommand
SampleDataAdapter.Fill(SampleSource)
TableView = SampleSource.Tables(0).DefaultView
Catch ex As Exception
Throw ex
End Try
Return TableView
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
DataGridView1.DataSource = GetData()
End Sub
End Class