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:
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.
|
1 2 |
//Sample 00: NameSpace Required for the Example using System.Data.SqlClient; |
3.2 LoadData Custom Function
A function called LoadData is written to populate the DataGridControl and the function shown below:
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.
3.2.4 Link DataGridControl With ADO DataTable
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
//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(); AuthTable_adapter.Fill(Authors); //Sample 1.3: Supply the Populated Data Table to the DataGrid DataGridControl.DataSource = Authors; } |
4.2 Listing2: Calling LoadData Custom Function
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
//Sample 02a: Populate the Grid while displaying the form private void frmDataTableEx_Load(object sender, EventArgs e) { LoadData(); } //Sample 02b: Reload the Data from Database private void btnReload_Click(object sender, EventArgs e) { LoadData(); } private void btnClose_Click(object sender, EventArgs e) { Application.Exit(); } |
Ado.net DataTable Example : Download
Categories: Ado.Net
Tags: ADO DataTable, DataGridControl, SqlDataAdapter
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.