Reader Level:
ARTICLE

Merge the Content of DataSets in ASP.NET

Posted by Abhimanyu K Vatsa Articles | JQuery June 17, 2012
This article shows the use of the Merge() method in C# to merge two or more DataSet objects that have largely similar schemas to exist in the same Data Container.
  • 0
  • 0
  • 8681

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

COMMENT USING