Learn ASP.NET Web Forms and ASP.NET MVC. Intensive courses for professional developers. Conducted by Bipin Joshi in Thane. Read more details here.
<%@ Page %>

Displaying Two DataTables In One DataGrid

Introduction

Typically one dataGrid is bound with one DataTable. However, consider the case of two tables both having a common key column called ID. Say one table contains personal information about employees and another table contains some official information. EmployeeID is the common in both the tables and both tables have same number of rows (equal to the no of employees). How to display such tables in one DataGrid? Read on to know more.

SQL Server Databases

In order to work with this example you need to have following tables in SQL server database.
CREATE TABLE [dbo].[Table1] (
	[EmployeeID] [char] (10) 
	COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Name] [char] (10) 
	COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2] (
	[EmployeeID] [char] (10) 
	COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Department] [char] (10) 
	COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Note, that the two tables can be linked via EmployeeID column. This is very simplified view of the tables to keep the example simple.

Filling DataSets

Next we will fill two separate DataSets from these two tables. If you wish you can also populate them in the same DataSet.
Dim connstr As String = "Integrated Security=SSPI;
User ID=sa;Initial Catalog=Northwind;Data Source=SERVER\netsdk"
Dim cnn As New SqlConnection(connstr)
Dim da1 As New SqlDataAdapter("select * from table1", cnn)
Dim da2 As New SqlDataAdapter("select * from table2", cnn)
Dim ds1 As New DataSet()
Dim ds2 As New DataSet()

da1.Fill(ds1, "table1")
da2.Fill(ds2, "table1")
This is a typical code that you must have used many times. Note that in both the DataSets the DataTable name is same.

Setting Primary Keys

Now, we will set the primary keys for both of the tables. Why is it necessary? When we merge results of these two DataSets (discussed in next section) primary key plays important role.
Dim pk1(0) As DataColumn
Dim pk2(0) As DataColumn

pk1(0) = ds1.Tables(0).Columns("EmployeeID")
ds1.Tables(0).PrimaryKey = pk1

pk2(0) = ds2.Tables(0).Columns("EmployeeID")
ds2.Tables(0).PrimaryKey = pk2

Merging DataSets

In this step, we will merge the two DataSets by calling the Merge method of the DataSet class.
ds1.Merge(ds2, False, MissingSchemaAction.Add)
In the above method we are merging data from ds2 with ds1. While merging the DataSets they will be checked for schema information. Depending on the MissingSchemaAction set by us we can add the missing columns, raise an error or ignore the error. The second argument - True/False - indicates whether changes made to ds1 to be preserved while merging operation or not. Previously I mentioned that setting primary key is important. This is because if we do net set it the rows from ds2 will be appended to ds1. After we set the primary key they we be really merged based on the primary key.

Binding to DataGrid

Finally we will bind our DataGrid with the above merged dataset.
DataGrid1.DataSource=ds1
DataGrid1.DataBind()
That's it. See you soon with some more interesting stuff.

Bipin Joshi is the founder of BinaryIntellect Consulting and conducts professional training programs on ASP.NET in Thane. He is a published author and has authored or co-authored books for Apress and Wrox press. To know more about him click here. To know more about his training programs go here.

Get connected : Twitter  Facebook  Google+  LinkedIn


Tags : ASP.NET ADO.NET Server Controls Data Controls
Posted On : 10 May 2002
Current Rating :
Rate this article :


This page is protected by copyright laws. Copying in any form is strictly prohibited. For Copyright notice and legal terms of use click here.

Protected by Copyscape