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.