Data comes from multiple resources. Maybe sometimes it’s a similar data type but sometimes the information is different. Consider a bus reservation system with a different vendor from which similar bus seats are reserved and those are various sources. If the bus owner wants to see a single result set then we need to merge the data into a single set. Here i just giving a sample scenario, it also may be used for a different scenario.
Step-by-step how to merge multiple tables into a single table.
Step 1
Create an ASP.Net web application as in the following:
- “Start” – “All Programs” – “Microsoft Visual Studio”.
- “File” – “New Project” – “C#” – “Empty Project” (to avoid adding a master page).
- Provide the project a name such as “MergeMultipleDataTable” or another as you wish and specify the location.
- Then right-click on Solution Explorer and select “Add New Item” then select the Default.aspx page.
- Drag and drop three Grid Views to bind the records after joining the two tables.
Now the Default.aspx source code will be as follows:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Defafult.aspx.cs" Inherits="MergeMultipleDataTable.Defafult" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body style="background-color: Blue">
<h4 style="color: White">
Article by Vithal Wadje</h4>
<form id="form1" runat="server">
<div>
<h4 style="color: White">
DataTable First Records Before Merging
</h4>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<EditRowStyle BackColor="#7C6F57" />
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#E3EAEB" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F8FAFA" />
<SortedAscendingHeaderStyle BackColor="#246B61" />
<SortedDescendingCellStyle BackColor="#D4DFE1" />
<SortedDescendingHeaderStyle BackColor="#15524A" />
</asp:GridView>
<br />
<h4 style="color: White">
DataTable second Records Before Merging
</h4>
<asp:GridView ID="GridView2" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<EditRowStyle BackColor="#7C6F57" />
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#E3EAEB" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F8FAFA" />
<SortedAscendingHeaderStyle BackColor="#246B61" />
<SortedDescendingCellStyle BackColor="#D4DFE1" />
<SortedDescendingHeaderStyle BackColor="#15524A" />
</asp:GridView>
<br />
</div>
<h4 style="color: White">
DataTable second Records after Merging
</h4>
<asp:GridView ID="GridView3" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<EditRowStyle BackColor="#7C6F57" />
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#E3EAEB" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F8FAFA" />
<SortedAscendingHeaderStyle BackColor="#246B61" />
<SortedDescendingCellStyle BackColor="#D4DFE1" />
<SortedDescendingHeaderStyle BackColor="#15524A" />
</asp:GridView>
<br />
</form>
</body>
</html>Step 2
Create the records for the table (you can also bind to records from the database).
Scenario
We have a different vendor from which we hire employees and they provide the employee records to our software development team using a web service and our job is to merge all the vendor records into one single data table so we can insert them into our database.
Open the Default.aspx.cs page and create a Vendor table (consider they are provided) as in the following:
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("Id", typeof(int));
dt.Columns[0].AutoIncrementSeed = 1;
dt.Columns[0].AutoIncrement = true;
dt.Columns.Add("Name");
dt.Columns.Add("Employer");
dr = dt.NewRow();
dr["Name"] = "Alexia Pamelov";
dr["Employer"] = "LT";
dt.Rows.Add(dr);
DataRow dr2 = null;
dr2 = dt.NewRow();
dr2["Name"] = "friedrich Eisenhauer";
dr2["Employer"] = "Microsoft";
dt.Rows.Add(dr2);Now create the second vendor table (consider they are provided) as in the following:
DataTable dt2 = new DataTable();
DataRow dr1 = null;
dt2.Columns.Add("Id", typeof(int));
dt2.Columns[0].AutoIncrementSeed = 1;
dt2.Columns[0].AutoIncrement = true;
dt2.Columns.Add("Name");
dt2.Columns.Add("Employer");
dr1 = dt2.NewRow();
dr1["Name"] = "Anjali Punjab";
dr1["Employer"] = "Goverment";
dt2.Rows.Add(dr1);Now we have a two tables from two different vendors, now we want to merge these two table’s records into one table, then just use the merge method of DataTable and pass the table as in the following:
//merging first data table into second data table
dt2.Merge(dt);
dt2.AcceptChanges();Now from the preceding example it’s clear that we can merge two tables into a single table. Now let us learn about some of the merge rules of DataTables.
- If the number of columns do not match the second table
When the number of columns do not match the second table then it creates blank columns for the table for the column(s) that do not match, as in the following
In the preceding you saw that the first data table only has two columns, Id and Employer, and the second table has the three columns Id, Employer and Name so the first table is created with a blank column.
- If the data type of a column does not match the second table
The data types must match. If the column names are the same and if the column name in both tables are the same and the data type is different then it shows the following error.
From preceding image it’s clear that it must match the data type of both the columns.
- If the column name does not match any in the second table
If a column name does not match in the second table then it creates records with a blank in each column that does not match and keeps their own orignal column names as follows.
So let us bind three Grid Views from three tables so we can understand the difference. Now the entire code of Defualt.aspx.cs will look as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
namespace MergeMultipleDataTable
{
public partial class Defafult : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("Id", typeof(int));
dt.Columns[0].AutoIncrementSeed = 1;
dt.Columns[0].AutoIncrement = true;
dt.Columns.Add("Name");
dt.Columns.Add("Employer");
dr = dt.NewRow();
dr["Name"] = "Alexia Pamelov";
dr["Employer"] = "LT";
dt.Rows.Add(dr);
DataRow dr2 = null;
dr2 = dt.NewRow();
dr2["Name"] = "friedrich Eisenhauer";
dr2["Employer"] = "Microsoft";
dt.Rows.Add(dr2);
GridView1.DataSource = dt;
GridView1.DataBind();
DataTable dt2 = new DataTable();
DataRow dr1 = null;
dt2.Columns.Add("Id", typeof(int));
dt2.Columns[0].AutoIncrementSeed = 1;
dt2.Columns[0].AutoIncrement = true;
dt2.Columns.Add("Name");
dt2.Columns.Add("Employer");
dr1 = dt2.NewRow();
dr1["Name"] = "Anjali Punjab";
dr1["Employer"] = "Goverment";
dt2.Rows.Add(dr1);
GridView2.DataSource = dt2;
GridView2.DataBind();
//merging first data table into second data table
dt2.Merge(dt);
dt2.AcceptChanges();
GridView3.DataSource = dt2;
GridView3.DataBind();
}
}
}From the preceding example it’s clear that we can merge two tables into a single table.
Notes
- Download the Zip file from the attachment for the full source code of the application.
- You can also bind the tables from the database.
- The data type of a column must be match if the column names are the same.
Summary
I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.