Merge the content of DataSets in ASP.NET
Introduction
Happy Coding!!
You can merge two or more DataSet objects that have
largely similar schemas to present in the same Data Container. In my case, I
have two different database tables and I am wishing to display all records from
both tables in a single Data Container. For this we can take the advantage of
Merge() method in C#.
Rules to display two DataSets in a
GridView:-
(i) All the columns
specified in the datagrid must be present in both datasets.
(ii) The data type of all columns in the datasets must be the same.
(iii) The column names should match.
(ii) The data type of all columns in the datasets must be the same.
(iii) The column names should match.
Look at the image and we are going to achieve the same
in this quick post.
Table
Structure
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 same as in
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 couple of ways by
which you can achieve the same thing, even quickly.
We can also merge between two DataTables or even DataRows.
Here you go..
Happy Coding!!
Comments
Post a Comment