Merging two Datasets into a single Datagrid: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Merging two Datasets into a single Datagrid

page 1 of 1

Published: 04 Nov 2003

Unedited - Community Contributed

Abstract
Merge is one of the method of Dataset. The merge feature is basically used in applications where the concept of Master and Transaction table exists. In this article we will see, how can we display two datasets in a single datagrid.

by Jesudas Chinnathampi (Das)
Feedback

Average Rating:

Views (Total / Last 10 Days): 32404/ 51

Merging two Datasets into a single Datagrid

Written on: Mar, 16th 2002.

Introduction

Merge is one of the method of Dataset. The merge feature is basically used in applications where the concept of Master and Transaction table exists. In this article we will see, how can we display two datasets in a single datagrid. For the merge to happen, we need to have the following pre-conditions.

Pre-conditions for displaying two datasets in a single datagrid.

1) All the columns specified in the datagrid must be present in both datasets.
2) The data type of all columns in the datasets must be the same.
3) The column names should match.

A Merge walthrough.

Assume that we have two tables with the following structure:

Table1	Table2
Field1 int	Field1 int
Field2 varchar(10)	Field2 varchar(10)
Field3 varchar(20)	Field3 varchar(20)

You may be thinking how in the world we will have two table structures with the same structure and same field names. Yes, in batch processing, we will have two tables--master and transaction. Both of these tables will have the same number of columns, same data types, and same field names.

Now, we need a datagrid to display records from the above tables. We assume that we have a datagrid that contains the definition for all columns. If you wish, you can see the example now: Click here to see an aspx page that uses a datagrid. The data grid that we are talking about is the same as the datagrid that is in the above sample (editdatagrid.html).

We are mainly going to see the BindGrid method that binds the datasets with the datagrid. We will see how we can bind two datasets with a single datagrid.

The BindGrid method.

Sub BindGrid()
Dim myConnection as New SqlConnection (strConn)

Dim DS1 As DataSet
Dim DS1 As DataSet
Dim MyCommand As SqlDataAdapter

MyCommand = new SqlDataAdapter("exec s_get_table1", MyConnection)
DS1 = new DataSet()
MyCommand.Fill(DS1, "Table1")

MyCommand = new SqlDataAdapter("exec s_get_table2", MyConnection)
DS2 = new DataSet()
MyCommand.Fill(DS2, "Table2")

'This code won't work--the merge will not take place with the above code.

ds1.merge(ds2)

MyDataGrid.DataSource=DS1.tables(0).DefaultView
MyDataGrid.DataBind()
End Sub

The above code will not work. Can you guess the reason? For the merge to take place between the datasets, apart from the data type and column name, the table name should also be the same.

So what should we do? In order for the merge to take place, we should name both the tables with same name. So we have to modify the MyCommand.Fill method for both DS1 and DS2 as follows:

The BindGrid method.

Sub BindGrid()
Dim myConnection as New SqlConnection (strConn)

Dim DS1 As DataSet
Dim DS1 As DataSet
Dim MyCommand As SqlDataAdapter

MyCommand = new SqlDataAdapter("exec s_get_table1", MyConnection)
DS1 = new DataSet()
MyCommand.Fill(DS1, "MyTable")

MyCommand = new SqlDataAdapter("exec s_get_table2", MyConnection)
DS2 = new DataSet()
MyCommand.Fill(DS2, "MyTable")

'Now this code works because the table name for both datasets are the same. 'Also the data type and column name for both tables are the same.

ds1.merge(ds2)

MyDataGrid.DataSource=DS1.tables(0).DefaultView
MyDataGrid.DataBind()
End Sub

What, if the schema of two tables are not the same?

In this example we saw that the table structure of both tables (table1 and table2) are the same. If the datatype of the columns in table1 and table2 are not the same, then what will happen? It is obvious that the merge will not take place. Even the compiler will give an error, such as:

<target>.destination and <source>.destination have conflicting properties: DataType property mismatch.

How can we create an editable Datagrid with two datasets?

Well, we very well can have an editable datagrid with two datasets sharing a same datagrid. The only necessity for this is that we should have some special values in at least one of the fields in each table which depicts that this data belongs to table1. To be more clear, in our first table, table1, the field3 can be used to store information that belongs to its own table. E.g.: for table1, the field3 can contain a value called "master" which tells that this data belongs to the table, master. So the records in the table1 will be as follows:

Field1  Field2  Field3
1       test1   master
2       test2   master
3       test3   master

We will also have similar records in table2 except for field3, where the value would be "transaction."

We need to have some hidden columns which store the values of field3, so that in the datagrid update method, we can know which table that we need to update by retrieving the value of field3. Once we know the value of field3, we can easily invoke appropriate update statements or stored procedures to update the table.

Summary

Thus, we have gone through how to merge two datasets into a single datagrid. We can also merge between two datatables or even datarows. To know more about this, read the first link given in the links section.

Links

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataSetClassMergeTopic2.asp http://authors.aspalliance.com/das/editdatagrid.aspx

Send your comments to das@aspalliance.com