Introduction

You can merge two or more DataSet objects that have largely similar schemas to exist in the same Data Container. In my case, I have two database tables and I want to display all records from both tables in a single Data Container. For this we can use the Merge() method in C#.

Rules to display two DataSets in a GridView:

Look at the image and we are going to achieve the same in this quick article:

image1.png

Table Structure

image2.png

Code-behind (Default.aspx.cs)

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseConnectionString1"].ConnectionString);

DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();

SqlDataAdapter SqlDA = new SqlDataAdapter();

SqlDA = new SqlDataAdapter("SELECT * FROM Table1", myConn);
SqlDA.Fill(ds1, "MyTable");

SqlDA = new SqlDataAdapter("SELECT * FROM Table2", myConn);
SqlDA.Fill(ds2, "MyTable");

ds1.Merge(ds2);

GridView3.DataSource = ds1.Tables[0].DefaultView;
GridView3.DataBind();
}
}

Note: The name of the source table should be the same as in the above code, SqlDA.Fill(ds1, "MyTable");.

ASPX Page (Default.aspx)

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
 <title></title>
</head>
<body>
 <form id="form1" runat="server">
 <h2>First Table</h2>
 <div>
 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="SqlDataSource1"
EmptyDataText="There are no data records to display.">
 <Columns>
 <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
 <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
 <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
 <asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" />
 </Columns>
 </asp:GridView>
 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString1 %>" DeleteCommand="DELETE FROM [Table1] WHERE [Id] = @Id" InsertCommand="INSERT INTO [Table1] ([Id], [Name], [City], [Mobile]) VALUES (@Id, @Name, @City, @Mobile)" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString1.ProviderName %>" SelectCommand="SELECT [Id], [Name], [City], [Mobile] FROM [Table1]" UpdateCommand="UPDATE [Table1] SET [Name] = @Name, [City] = @City, [Mobile] = @Mobile WHERE [Id] = @Id">
 <DeleteParameters>
 <asp:Parameter Name="Id" Type="Int32" />
 </DeleteParameters>
 <InsertParameters>
 <asp:Parameter Name="Id" Type="Int32" />
 <asp:Parameter Name="Name" Type="String" />
 <asp:Parameter Name="City" Type="String" />
 <asp:Parameter Name="Mobile" Type="String" />
 </InsertParameters>
 <UpdateParameters>
 <asp:Parameter Name="Name" Type="String" />
 <asp:Parameter Name="City" Type="String" />
 <asp:Parameter Name="Mobile" Type="String" />
 <asp:Parameter Name="Id" Type="Int32" />
 </UpdateParameters>
 </asp:SqlDataSource>

</div>
 <h2>Second Table</h2>
 <div>
 <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="SqlDataSource2" EmptyDataText="There are no data records to display.">
 <Columns>
 <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
 <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
 <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
 <asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" />
 </Columns>
 </asp:GridView>
 <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString1 %>" DeleteCommand="DELETE FROM [Table2] WHERE [Id] = @Id" InsertCommand="INSERT INTO [Table2] ([Id], [Name], [City], [Mobile]) VALUES (@Id, @Name, @City, @Mobile)" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString1.ProviderName %>" SelectCommand="SELECT [Id], [Name], [City], [Mobile] FROM [Table2]" UpdateCommand="UPDATE [Table2] SET [Name] = @Name, [City] = @City, [Mobile] = @Mobile WHERE [Id] = @Id">
 <DeleteParameters>
 <asp:Parameter Name="Id" Type="Int32" />
 </DeleteParameters>
 <InsertParameters>
 <asp:Parameter Name="Id" Type="Int32" />
 <asp:Parameter Name="Name" Type="String" />
 <asp:Parameter Name="City" Type="String" />
 <asp:Parameter Name="Mobile" Type="String" />
 </InsertParameters>
 <UpdateParameters>
 <asp:Parameter Name="Name" Type="String" />
 <asp:Parameter Name="City" Type="String" />
 <asp:Parameter Name="Mobile" Type="String" />
 <asp:Parameter Name="Id" Type="Int32" />
 </UpdateParameters>
 </asp:SqlDataSource>
 </div>
 <h2>Merged Data</h2>
 <div>
 <asp:GridView ID="GridView3" runat="server"></asp:GridView>
 </div>
 </form>
</body>
</html>

Let me tell you one thing; there are a couple of ways by which you can achieve the same thing, even quickly.

We can also merge two DataTables or even DataRows. Here you go..

http://msdn.microsoft.com/en-us/library/system.data.dataset.merge%28v=vs.71%29.aspx