Displaying data in a DataGridView control using Dataset and DataAdapter in Visual Basic.Net

This article explains how to display data in a DataGridView control using DataSet. In this sample program, we will use a SQL Server database, FinAccounting and we will access Accounts table of FinAccounting database and display records in the datagridview control.




Datasets are part of the DataAccess Layer. How do you use Dataset object in a DataAccess Layer of a database application? For all this, read Database programming using visual basic, c# (c sharp) sql server.

Preparation:

Create a Windows form and add a DataGridView control to the form. Tasks:

• Declare and setup the Connection string
• Declare and build a query string
• Create SqlDataAdapter Object
• Create a DataSet Object and Fill it with data
• Bind DataSet to the DataGridView control

Declare and setup the Connection string

This task declares, instantiates and sets up the connection. We need a connection when we want to access a data source. We will use a connection string to set up the connection with the database. We have used the SqlConnection class which is only to be used with MS SQL Server database as it has been specially optimized for connecting to the SQL Server database.

Declare and build a query string

All query statements used in the program are declared in this section and assigned to string variables.

Create SqlDataAdapter Object

The DataAdapter is used for retrieving data from the database and populating the dataset. The DataAdapter is the connector class that sits between the disconnected and the connected parts of ADO.Net. The DataAdapter class is instantiated using the New constructor, which is overloaded. If we use the default constructor for the DataAdapter, we need to specify the Command object for the actions performed. This means that if we want to retrieve rows from the Data Source, we will have to set the Select command property.

Create a DataSet Object and Fill it with Accounts data

Unlike the managed provider objects, the DataSet object do not diverge between the OleDb and SqlClient .net namespaces. We declare a DataSet object the same way regardless of which .NET data provider we are using:

myDataSet = New DataSet()

Once we have set up the DataAdapter and the DataSet, we need to populate the DataSet. The Fill method of the DataAdapter class is used for populating and refreshing the DataSet object.

Attach DataSet to DataGrid

Now we use DataSource method of DataGridView control to attach the DataSet data to the datagridview control.

Program Code

Imports System.Data.SqlClient

Public Class Form1
'Declare the string variable 'connectionString' to hold the ConnectionString        
Dim connectionString As String = "server=SYS2;" + "integrated security=SSPI;" +
 "database=FinAccounting"
'Declare the string variable 'str_Account_Select' to hold the SQL statement
Dim str_Account_Select As String = "SELECT * FROM AccountsTable "

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myAdapter As SqlDataAdapter
'Declare the DataSet object
Dim myDataSet As DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles MyBase.Load
 
    'Instantiate the Connection object
    myConnection = New SqlConnection(connectionString)

    'Instantiate the Command object
    myCommand = New SqlCommand(str_Account_Select, myConnection)

    myConnection.Open()

    'Instantiate  DataSet object
    myDataSet = New DataSet()

    'Instantiate  DataAdapter object
    myAdapter = New SqlDataAdapter()

    'Set DataAdapter command properties
    myAdapter.SelectCommand = myCommand

    'Populate the Dataset
    myAdapter.Fill(myDataSet, "AccountsTable")

    If (myDataSet.Tables("AccountsTable").Rows.Count = 0) Then
       MessageBox.Show("There are currently no registries in the database.")
    Else
       DataGridView1.DataSource = myDataSet.Tables("AccountsTable")
    End If
    End Sub
End Class

Summary:

This program demonstrates how to access data using a DataAdapter and store it in a dataset. This DataSet is attached to the datagridview control and displayed on the form.