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?
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.
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.
Public Class Form1
'Declare the string variable 'connectionString' to hold the ConnectionString
Dim connectionString As String = "server=SYS2;" + "integrated security=SSPI;" +
'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)
'Instantiate the Connection object
myConnection = New SqlConnection(connectionString)
'Instantiate the Command object
myCommand = New SqlCommand(str_Account_Select, myConnection)
'Instantiate DataSet object
myDataSet = New DataSet()
'Instantiate DataAdapter object
myAdapter = New SqlDataAdapter()
'Set DataAdapter command properties
myAdapter.SelectCommand = myCommand
'Populate the Dataset
If (myDataSet.Tables("AccountsTable").Rows.Count = 0) Then
MessageBox.Show("There are currently no registries in the database.")
DataGridView1.DataSource = myDataSet.Tables("AccountsTable")