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): 115757/ 135

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       



User Comments

Title: amin   
Name: asansans
Date: 8/14/2012 3:42:35 AM
Comment:
nnansasasas
Title: dataset   
Name: Alok Singh
Date: 7/22/2012 2:08:02 AM
Comment:
Cooool
Title: Data set Merger   
Name: happy
Date: 4/18/2012 1:02:56 AM
Comment:
Nice
Title: @ Naveen   
Name: Riks
Date: 2/15/2012 7:27:01 AM
Comment:
I think you are a noob to this. you don't know how to code. you also don't know programming standards..
Title: hi   
Name: hi
Date: 9/3/2011 9:54:03 AM
Comment:
Thanks for your information..
Title: thankx   
Name: india
Date: 4/16/2011 1:07:57 AM
Comment:
thanks for proveding useful script
Title: thanx   
Name: Fani..
Date: 1/19/2011 4:48:18 AM
Comment:
thanx for save my time time...
Title: Merging two datasets into a single datagrid   
Name: mala
Date: 10/23/2009 7:52:00 AM
Comment:
very nice......
Title: issue with Dataser merging -- Out of Memory Exception   
Name: Naveen
Date: 9/9/2009 4:43:58 PM
Comment:
Article is good and is working too...

I have a strange Scenario with one my application..

When I Fill Dataset with Records "572936" using DataAdapter I'm receving Error
--- Description: Exception of type 'System.OutOfMemoryException' was thrown. ----

FYI: Ours is Disconnected Application and we cannot use DataReader in our Scenarios PLS!!!

In order to achieve my issue i'm implenting as below: and so i'm not sure how i loop thru all these datasets to populate into .CSV file(though it has Size limitations)

oDA.Fill(oDS99, 0, 143234, "Tb1");
DataSet ds1 ,ds2,ds3,ds4 = new DataSet();
ds1 = oDS;
oDS.Clear();
//oDA.A
oDA.Fill(oDS99, 143235, 286468, "Tb1");
ds2 = oDS;
oDS.Clear();

oDA.Fill(oDS99, 286469, 429702, "Tb1");
ds3 = oDS;
oDS.Clear();

oDA.Fill(oDS99, 429703, 572936, "Tb1");
ds4 = oDS;
oDS.Clear();


Please suggest/Assist me the Better way... email me at snaveenkumar@hotmail.com as i am not registered user for this site...

Thanks in advance...


Naveen
Title: Combining 2 datasets   
Name: Jayant
Date: 7/8/2009 6:18:09 AM
Comment:
Thanks buddy.......it works
Title: code when tables with different fields   
Name: Ramesh
Date: 6/21/2009 10:33:54 PM
Comment:
Hi, u r doing great job dude. keep it up.
Title: code when tables with different fields   
Name: harry
Date: 3/18/2009 2:53:47 AM
Comment:
no code given when the tables with different fields
Title: Combining 2 datasets   
Name: AngelaG
Date: 11/12/2008 3:36:41 AM
Comment:
Thank you very much. This helped alot.
Title: fine comment   
Name: hariom
Date: 10/17/2008 9:57:04 AM
Comment:
nice job.
Title: merge 2 table different values in a datagrid   
Name: ArunK
Date: 6/25/2008 1:32:20 AM
Comment:
I want to know merge 2 table different values in a datagrid
Title: Merging Datasets   
Name: Aseem
Date: 4/23/2008 8:53:53 AM
Comment:
i need to say that this article is helpful for me but to some extent coz its scope is limited
Title: bind the dataset   
Name: abdul
Date: 4/21/2008 12:50:45 AM
Comment:
i have to bind two dataset throgh condition
Title: Merging Two Dataset Value into Single   
Name: Susmit Singh
Date: 2/16/2008 7:40:50 AM
Comment:
This Code is really Useful. Thanx
Title: Very UseFull   
Name: Rajasekhar
Date: 11/27/2007 7:13:28 AM
Comment:
this article is very use full
Title: Merging of DatsSet -- Correct Preconditions   
Name: vaibhav
Date: 8/13/2007 5:27:06 AM
Comment:
Pre-conditions for displaying two datasets in a single datagrid.
1) The data type of all columns in the datasets must be the same.
2) The column names should match.

Above 2 Conditions mentioned u are not at all required.
So please remove them as early as possible

Here is the link which explains in a better fashion.
http://msdn2.microsoft.com/en-us/library/aszytsd8(VS.80).aspx

Thanks
Title: Merging Datsets   
Name: venki
Date: 6/28/2007 1:53:56 AM
Comment:
Im learning more thing
Title: Merging Datsets   
Name: Nitin Sharma(.Net Developer)
Date: 6/26/2007 6:31:52 AM
Comment:
It is very very helpful..!!
Title: Software Developer   
Name: Mohani Ranjan
Date: 6/22/2007 3:03:43 AM
Comment:
I'm mohani ranjan very happy to know that ur tutorial classes
Title: Software Engineer   
Name: Uma Mahesh
Date: 5/30/2007 1:13:17 AM
Comment:
Article is average. But providing basic information
Thanks
Title: Thanks   
Name: Snehal
Date: 4/26/2007 4:18:16 AM
Comment:
Really nice
Title: very good   
Name: sampath
Date: 2/17/2007 5:39:57 AM
Comment:
Its very good article.With this article I was completed my task thank q
Title: software   
Name: suresh kumar
Date: 1/20/2007 3:03:14 PM
Comment:
it is very much helpfull thanx
Title: .NET Programmer   
Name: Dhaval
Date: 1/18/2007 6:44:59 AM
Comment:
hi this is really pretty cool for me
it helped me a lot in my project
thanks a lot
Title: Alternate Rows   
Name: Kamal Garg
Date: 11/17/2006 7:14:55 AM
Comment:
Can we merge it like we have alternate rows from both tables
Title: Dataset Merge   
Name: Ender
Date: 9/22/2006 11:29:04 AM
Comment:
Jesudas,

Excellent! Your article really helped me out!

Ender
Title: programmer   
Name: omendra
Date: 8/18/2006 3:45:58 PM
Comment:
it is fine
thanks
Title: very good   
Name: sreekanthreddy kurri
Date: 8/18/2006 7:18:11 AM
Comment:
this helps me thanks for giving to this
Title: Software Developer   
Name: Yaaeesh Khan
Date: 8/11/2006 6:05:48 AM
Comment:
This Code is very Usefull, Thanks.
Title: Mr   
Name: Yaaeesh Khan
Date: 7/7/2006 8:00:35 AM
Comment:
This Piece of Code is very good. I rated it "Very Good". Thanks.
Title: Good One   
Name: Keval Solanki
Date: 7/7/2006 12:38:27 AM
Comment:
Very good article and explanation also. really helped me. thax
Title: Very Nice, Keep it up.   
Name: Vijay Krishna
Date: 6/14/2006 4:52:45 AM
Comment:
explanation is good but apart from this u've to explain master and transaction tables usage practically...with more info...
Title: Student   
Name: Justice
Date: 5/26/2006 5:08:19 PM
Comment:
Very Good
Title: Mereging two datasets   
Name: Geeta
Date: 5/16/2006 1:23:14 AM
Comment:
Hi the Merging of dataset is explained nicely..but cud u explain what is the difference between Dataset.Copy and Dataset Merge. Cud u also explain when these 2 methods are used .
Title: s/w engineer   
Name: shalabh gupta
Date: 4/28/2006 9:32:20 AM
Comment:
please tell me how to place a datagrid inside the cell of another datagrid
Title: datagrid inside a datagrid   
Name: asp.net
Date: 3/20/2006 6:26:07 AM
Comment:
pls help me how to place a datagrid inside a datagrid
Title: Help Full   
Name: Umair Shahid
Date: 3/9/2006 8:20:21 AM
Comment:
Artical made my life very easy, Every help gives 10 out of 10.

cheers

Cold Flame
Title: Web Consultant   
Name: Kevin
Date: 9/8/2005 1:40:05 PM
Comment:
Jesudas,

Excellent! Your article really helped me out!

Kevin
Title: Merging two Datasets into a single Datagrid   
Name: Ronda
Date: 6/15/2005 11:11:33 AM
Comment:
You have a typo .... (but thanks otherwise)... great article - you declard Dim DS1 twice instead of Dim DS1 and Dim DS2
Title: Web Developer   
Name: Srini
Date: 4/4/2005 10:45:19 AM
Comment:
It helped me understand the merge. Nice explanation.
Title: Merging datasets   
Name: akoranteng@msn.com
Date: 1/6/2005 5:32:10 AM
Comment:
COuld you e-mail me the sql statements for the following
(1) exec s_get_table1, (2) exec s_get_table2, (3) execs_update_forward,
Title: Dataset Copy   
Name: Suresh
Date: 9/30/2004 9:02:21 AM
Comment:
Hi the Merging of dataset is explained nicely..but cud u explain what is the difference between Dataset.Copy and Dataset Merge. Cud u also explain when these 2 methods are used .
Title: Electrical Engineer   
Name: Kevin
Date: 7/22/2004 6:46:03 PM
Comment:
Thank you very much for the info.
I was extremely frustrated trying to merge 2 datasets!
Your post helped me emensely.
Thank you!






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 4/16/2014 2:57:18 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search