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) The primary thing is that, both 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 same.
3) Also the column name 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 that, how in the world we will have two table structures with
the same structure and same field name. Yes, in batch processing, we will
have two tables, master and transaction. Both these tables will have the same
number of columns, same data type and same field name.
Now, we need a datagrid to display records from the above tables. We assume that,
we have a datagrid which contains the definition for all columns. If you wish, you
can see the definiton of datagrid now itself.
Click here to see the
aspx page which uses a datagrid The data grid that we are talking about is the same
as the datagrid which exist in the above sample (editdatagrid.html)
We are mainly going to see the BindGrid method which binds the datasets with Datagrid.
We will see, how can we 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. I mean, 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 datasets, apart from the data type and column name, the table should also be the
same
So, what should we do, inorder for 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, as 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 columns in table1 and table2 are not the same, then
what will happen. It is obvious that 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 a editable datagrid with two datasets sharing a same
datagrid. The only necessity for this is that, we should have some special values
in atleast one of the field 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. For eg: 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 about how to Merge two datasets to a single datagrid.
We can also merge between two datatable or even datarow. 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://aspalliance.com/das/editdatagrid.aspx
Send your comments to das@aspalliance.com
Back to Article list
|
