ASP.NET

Merging Multiple DataTables Into Single DataTable Using ASP.Net C#

Background
 
Data often 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. I am just giving a samle scenario, it also may be used for a different scenario.
 
So let us learn step-by-step how to merge multiple tables into a single table.
 
Step 1
 
Create an ASP.Net web application as in the following:
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).
  3. Provide the project a name such as "MergeMultipleDataTable" or another as you wish and specify the location.
  4. Then right-click on Solution Explorer and select "Add New Item" then select the Default.aspx page.
  5. 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:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Defafult.aspx.cs" Inherits="MergeMultipleDataTable.Defafult" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title></title>  
  8. </head>  
  9. <body style="background-color: Blue">  
  10.     <h4 style="color: White">  
  11.         Article by Vithal Wadje</h4>  
  12.     <form id="form1" runat="server">  
  13.     <div>  
  14.         <h4 style="color: White">  
  15.            DataTable First Records Before Merging  
  16.         </h4>  
  17.         <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">  
  18.             <AlternatingRowStyle BackColor="White" />  
  19.             <EditRowStyle BackColor="#7C6F57" />  
  20.             <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
  21.             <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
  22.             <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />  
  23.             <RowStyle BackColor="#E3EAEB" />  
  24.             <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />  
  25.             <SortedAscendingCellStyle BackColor="#F8FAFA" />  
  26.             <SortedAscendingHeaderStyle BackColor="#246B61" />  
  27.             <SortedDescendingCellStyle BackColor="#D4DFE1" />  
  28.             <SortedDescendingHeaderStyle BackColor="#15524A" />  
  29.         </asp:GridView>  
  30.         <br />  
  31.         <h4 style="color: White">  
  32.              DataTable second  Records Before Merging  
  33.         </h4>  
  34.         <asp:GridView ID="GridView2" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">  
  35.             <AlternatingRowStyle BackColor="White" />  
  36.             <EditRowStyle BackColor="#7C6F57" />  
  37.             <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
  38.             <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
  39.             <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />  
  40.             <RowStyle BackColor="#E3EAEB" />  
  41.             <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />  
  42.             <SortedAscendingCellStyle BackColor="#F8FAFA" />  
  43.             <SortedAscendingHeaderStyle BackColor="#246B61" />  
  44.             <SortedDescendingCellStyle BackColor="#D4DFE1" />  
  45.             <SortedDescendingHeaderStyle BackColor="#15524A" />  
  46.         </asp:GridView>  
  47.         <br />  
  48.     </div>  
  49.     <h4 style="color: White">  
  50.          DataTable second  Records after  Merging  
  51.     </h4>  
  52.     <asp:GridView ID="GridView3" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">  
  53.         <AlternatingRowStyle BackColor="White" />  
  54.         <EditRowStyle BackColor="#7C6F57" />  
  55.         <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
  56.         <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
  57.         <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />  
  58.         <RowStyle BackColor="#E3EAEB" />  
  59.         <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />  
  60.         <SortedAscendingCellStyle BackColor="#F8FAFA" />  
  61.         <SortedAscendingHeaderStyle BackColor="#246B61" />  
  62.         <SortedDescendingCellStyle BackColor="#D4DFE1" />  
  63.         <SortedDescendingHeaderStyle BackColor="#15524A" />  
  64.     </asp:GridView>  
  65.     <br />  
  66.     </form>  
  67. </body>  
  68. </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:
  1. DataTable dt = new DataTable();  
  2.            DataRow dr = null;  
  3.            dt.Columns.Add("Id", typeof(int));  
  4.            dt.Columns[0].AutoIncrementSeed = 1;  
  5.            dt.Columns[0].AutoIncrement = true;  
  6.            dt.Columns.Add("Name");  
  7.            dt.Columns.Add("Employer");  
  8.            dr = dt.NewRow();  
  9.            dr["Name"] = "Vithal Wadje";  
  10.            dr["Employer"] = "LT";  
  11.            dt.Rows.Add(dr);  
  12.   
  13.            DataRow dr2 = null;  
  14.            dr2 = dt.NewRow();  
  15.            dr2["Name"] = "Dinesh";  
  16.            dr2["Employer"] = "Microsoft";  
  17.            dt.Rows.Add(dr2); 
The Vendor table's records will be such as follows: 
 
 
 
Now create the second vendor table (consider they are provided) as in the following:
  1. DataTable dt2 = new DataTable();  
  2.            DataRow dr1 = null;  
  3.           
  4.            dt2.Columns.Add("Id", typeof(int));  
  5.            dt2.Columns[0].AutoIncrementSeed = 1;  
  6.            dt2.Columns[0].AutoIncrement = true;  
  7.            dt2.Columns.Add("Name");  
  8.            dt2.Columns.Add("Employer");  
  9.            dr1 = dt2.NewRow();  
  10.            dr1["Name"] = "Sudhir Wadje";  
  11.            dr1["Employer"] = "Goverment";  
  12.            dt2.Rows.Add(dr1);  
  13.             
Now the second vendor table records will be such as follows:
 
 
 
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 the table as in the following:
 
  1. //merging first data table into second data table  
  2.          dt2.Merge(dt);  
  3.          dt2.AcceptChanges();  
Now we have merged the two tables into a single table and now the records will look such as follows:
 
 
 
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:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8.   
  9. namespace MergeMultipleDataTable  
  10. {  
  11.     public partial class Defafult : System.Web.UI.Page  
  12.     {  
  13.         protected void Page_Load(object sender, EventArgs e)  
  14.         {  
  15.   
  16.   
  17.   
  18.             DataTable dt = new DataTable();  
  19.             DataRow dr = null;  
  20.             dt.Columns.Add("Id", typeof(int));  
  21.             dt.Columns[0].AutoIncrementSeed = 1;  
  22.             dt.Columns[0].AutoIncrement = true;  
  23.             dt.Columns.Add("Name");  
  24.             dt.Columns.Add("Employer");  
  25.             dr = dt.NewRow();  
  26.             dr["Name"] = "Vithal Wadje";  
  27.             dr["Employer"] = "LT";  
  28.             dt.Rows.Add(dr);  
  29.   
  30.             DataRow dr2 = null;  
  31.             dr2 = dt.NewRow();  
  32.             dr2["Name"] = "Dinesh";  
  33.             dr2["Employer"] = "Microsoft";  
  34.             dt.Rows.Add(dr2);  
  35.   
  36.             GridView1.DataSource = dt;  
  37.             GridView1.DataBind();  
  38.   
  39.   
  40.   
  41.             DataTable dt2 = new DataTable();  
  42.             DataRow dr1 = null;  
  43.            
  44.             dt2.Columns.Add("Id", typeof(int));  
  45.             dt2.Columns[0].AutoIncrementSeed = 1;  
  46.             dt2.Columns[0].AutoIncrement = true;  
  47.             dt2.Columns.Add("Name");  
  48.             dt2.Columns.Add("Employer");  
  49.             dr1 = dt2.NewRow();  
  50.             dr1["Name"] = "Sudhir Wadje";  
  51.             dr1["Employer"] = "Goverment";  
  52.             dt2.Rows.Add(dr1);  
  53.             GridView2.DataSource = dt2;  
  54.             GridView2.DataBind();  
  55.   
  56.             //merging first data table into second data table  
  57.            dt2.Merge(dt);  
  58.            dt2.AcceptChanges();  
  59.            GridView3.DataSource = dt2;  
  60.            GridView3.DataBind();  
  61.   
  62.         }  
  63.     }  
  64. } 
Now run the application. Then the Girdview records will be such as follows before merging:
 
 
Now see the third Giridview records will be such as follows after merging:
 
 
 
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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs