ADO DataTable & DataGridControl

ADO DataGridControl Example

1. Introduction to ADO DataTable and DataGridControl

In this Article, we will see an example which pulls data from the database and displays that in the Dotnet Windows Form using ADO DataTable, DataAdapter and DataGridControl.

1.1 ADO DataAdapter

A DataAdapter can hold one or more SQLCommand(s) as well as connection information to the DB. Also, it can fill the DataTable or Dataset with the data pulled from the DB. We can also use the DataAdapter for updating the DB. It acts like a bridge between the DB and the data container objects like DataTable and DataSet. The DataAdapter controls data move direction by two critical methods called Fill and Update. The Fill method moves the data from the DB to the Ado.net data objects (Ex: DataTable). Whereas, the Update method moves the data from the Ado.net data objects to the DB Table.

1.2 ADO DataTable

The Ado.net DataTable is an In-Memory image of Table Rows and Table Columns. We can use a Data Adapter to fill the data of DataTable. One can use DataTable as a standalone object or can engage it as part of Ado.net DataSets. Note, The DataSet is a DataTables collection with links in between them (Primary Key & Foreign Key pairs).

1.3 ADO DataGridControl

DataGridControl is a Dotnet control which displays the table of data in the row & column format. 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 This ADO DataTable Example

Have a look at the example screenshot below:

ADO DataGridControl Example
ADO DataGridControl Example

The control at the top of the form is DataGridControl. We will use this control 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.

3. Source Code – Explanation

3.1 SQL NameSpace

First we include the SqlClient Namespace to this example. We do this as we need to pull the data from the SQL Server DB.

3.2 LoadData Custom Function

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

ADO DataTable Example
ADO DataTable Example

3.2.1 Form SQL Query

First, we form an SQL Query which retrieves the data from the SQL Server Database. We store this in a variable called AuthorsSql (Marked as 1).  The author’s table is available in the Microsoft’s Pubs sample database. You can download the Database script from SQL 2005 Section of this website.

3.2.2 Construct SqlDataAdapter

After the SQL String creation, a  SqlDataAdapter object AuthTable_adapter is created (Marked as 2). This object creation takes two parameters to know what data to fetch and from where it need to fetch. We are passing the SQL String (Marked as 2.1) which we created in the first step to this function. 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. How to create it is explained in the video link below:

3.2.3 Fill ADO DataTable

Now, the pipeline to the water source is ready and we need a pot to fill it. We create 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.

Finally, we set this DataTable object to the DataGridControl via DataSource property. Note, the DataGridControl is residing in the form which presents the data to the users. The DataGridControl takes the duty of displaying the authors table content in the Windows Form. The code for this entire function is in Listing 1 at the end.

We call the above LoadData function on the “Form Load Event” as well as the in the “Click Event” of reload button. Refer Listing 2 for Form Load and button click handlers.

Making this example and how it works is shown in the below video:

4. Code Listings

4.1 Listing 1: Load data into DataGridControl Via ADO DataTable

4.2 Listing2: Calling LoadData Custom Function

Ado.net DataTable Example : Download

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.