DataGridView 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

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

GridView1.DataSource = dsGrid.Tables(0)

8. Bind the GridView with Dataset

GridView1.DataBind()

Please find the source code here (VB.Net DataGridView)