Tuesday, September 20, 2016

[ Ado.net ] - Populate DataGridControl using DataTable and DataAdapter

1. Introduction

In this Article, we will see an example which pulls data from the database and displays that in the form using “DataTable”, “DataAdapter” and “DataGridControl”.

1.1 Ado.net DataAdapter

A DataAdapter can hold one or more SQLCommand(s) as well as connection information to the database. Also, it can fill the DataTable or Dataset with the data pulled from the database. It can also be used for updating the database.  It acts as a bridge between the database and the data container objects like DataTable, DataSet, etc. The movement of data is controlled by two important methods called “Fill” and “Update”. The Fill method moves the data from the database to the Ado.net data objects (Ex: DataTable). Whereas, the Update method moves the data from the Ado.net data objects to the underlying database.

1.2 Ado.net DataTable

The Ado.net DataTable is an In-Memory representation of Table Rows and Table Columns. A data Adapter is used to fill the data of DataTable. One can use DataTable as a standalone object or it can participate as part of “Ado.net DataSets”.  Simply, a collection of DataTables with the relationship in between them (PrimaryKey & ForeignKey pairs) is called DataSets.

1.3 DataGridControl

This is a Dotnet control used to present the table of data to the user. In our example, we are going to use this control to display the data which is taken from the author's table of the Microsoft supplied pubs database.

2. About the Example

Have a look at the example screenshot below:

Fig 1. DataTable, DataGrid and DataAdapter Example
Fig 1. DataTable, DataGrid and DataAdapter Example

The control at the top of the form is what called “DataGridControl”. This control is used to display the data in the DataTable. In this example, we are going to load the data to the DataTable through DataAdapter and then going to display it in the DataGridControl. This action happens twice in this example and one is at the form load and another one is at the Reload button click.

Note that this article is limited to populating the DataTable and we will see updating the database in another article.

3. Source Code – Explanation

1) First, all the required namespaces are included in the code module:

//Sample 00: NameSpace Required for the Example
using System.Data.SqlClient;

2) A function called LoadData is written to populate the DataGridControl and the function shown below:

Fig 2. Code Explained
Fig 2. Code Explained

01) First, an SQL Query which retrieves the data from the SQL Server Database is defined and the string is stored in a string variable called AuthorsSql (Marked as 1).  The author's table is available in the Microsoft supplied Pubs database. You can download the Database script from SQL 2005 Section of this website.

02) After the SQL String creation, a “SqlDataAdapter” object AuthTable_adapter is created (Marked as 2). This object creation takes two parameters asking what needs to be retrieved and from where it needs to retrieve.  We are passing the SQL String (Marked as 2.1) created in the first step to this function and this will tell the adapter what needs to be retrieved. For the second parameter, we are passing Connection String which is stored as application property and which is explained in the video link https://youtu.be/MBUkXDjH0Fc.

03) Now, the pipeline to the water source is ready and we need a pot to fill it. Right, now we are creating the “DataTable” data container called Authors and then supplying that to the SqlDataAdapter object by calling the method “Fill()” (Marked as 3). This will populate the DataTable Authors with authors found in the author's table.

04) Finally, this populated DataTable object is assigned to the DataSource property of the DataGridControl, which is placed on the form. The DataGridControl takes the responsibility of displaying the “authors table” content in the form. The code for this entire function is given in Listing 1
5) The above function LoadData is called on the “Form Load Event” as well as the in the “Click Event” of reload button. The code for this is shown in the Listing 2.

How this sample work is shown in the below video:

4. Code Listings

4.1 Listing 1

//Sample 01: Load data to the DataGrid
public void LoadData()
    //Sample 1.1: Create Data Adapter
    string AuthorsSql = "Select au_lname,city,State from authors";

    SqlDataAdapter AuthTable_adapter  = new
        SqlDataAdapter(AuthorsSql, Properties.Settings.Default.ConPubs);

    //Sample 1.2: Create and Fill Data Table through Adapter
    DataTable Authors = new DataTable();

    //Sample 1.3: Supply the Populated Data Table to the DataGrid
    DataGridControl.DataSource = Authors;

4.2 Listing 2

//Sample 02a: Populate the Grid while displaying the form
private void frmDataTableEx_Load(object sender, EventArgs e)

//Sample 02b: Reload the Data from Database
private void btnReload_Click(object sender, EventArgs e)
private void btnClose_Click(object sender, EventArgs e)

Source Code: Download
Like this site? Tell it to your Firend :)