Merge the Content of DataSets in ASP.NET

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:

  • All the columns specified in the datagrid must be present in both datasets.

  • The data type of all columns in the datasets must be the same.

  • The column names should match.

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