Kriya and Meditation for Software / IT Professionals. Conducted by Bipin Joshi in Thane. Read more...
Learn ASP.NET MVC, ASP.NET Core and ASP.NET Design Patterns. Courses conducted by Bipin Joshi on weekends. Click here for more details.

<%@ Page %>

Displaying Two DataTables In One DataGrid


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) 
	[Name] [char] (10) 
CREATE TABLE [dbo].[Table2] (
	[EmployeeID] [char] (10) 
	[Department] [char] (10) 
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.
That's it. See you soon with some more interesting stuff.

Bipin Joshi is a software consultant, an author and a yoga mentor having 22+ years of experience in software development. He also conducts online courses in ASP.NET MVC / Core and Design Patterns. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Meditation and Mindfulness to interested individuals. To know more about him click here.

Get connected : Twitter  Facebook  Google+  LinkedIn

Posted On : 10 May 2002

Tags : ASP.NET ADO.NET Server Controls Data Controls