How to merge multiple data tables in C# into single DataTable?

Question

Before marking this question as duplicate can anybody help me in merging multiple data tables into a single datatable.

Here is my code:

     static void Main(string[] args)
            {
    
                var tblA = new DataTable();
                tblA.Columns.Add("ChannelNodeCategoryName");
                tblA.Columns.Add("CategoryName");
                tblA.Columns.Add("Language");
    
                var tblB = new DataTable();
                tblB.Columns.Add("ChannelNodeDescription");
                tblB.Columns.Add("CategoryName");
                tblB.Columns.Add("Language");
    
                DataRow row = tblA.NewRow();
                row["ChannelNodeCategoryName"] = "Comfort";
                row["CategoryName"] = "Solution";
                row["Language"] = "en";
                tblA.Rows.Add(row);
    
                DataRow row1 = tblA.NewRow();
                row1["ChannelNodeCategoryName"] = "Confort";
                row1["CategoryName"] = "Solution";
                row1["Language"] = "fr";
                tblA.Rows.Add(row1);
    
                DataRow row2 = tblA.NewRow();
                row2["ChannelNodeCategoryName"] = "Women";
                row2["CategoryName"] = "Test";
                row2["Language"] = "en";
                tblA.Rows.Add(row2);
    
                DataRow row3 = tblA.NewRow();
                row3["ChannelNodeCategoryName"] = "Femmes";
                row3["CategoryName"] = "Test";
                row3["Language"] = "fr";
                tblA.Rows.Add(row3);
    
    
                //---------------------------------------------------------------------------------
                DataRow sec = tblB.NewRow();
                sec["ChannelNodeDescription"] = "Men's T-shirts - Buy T-shirts for men online ";
                sec["CategoryName"] = "Solution";
                sec["Language"] = "en";
                tblB.Rows.Add(sec);
    
                DataRow sec1 = tblB.NewRow();
                sec1["ChannelNodeDescription"] = "T-shirts pour hommes - Achetez des";
                sec1["CategoryName"] = "Solution";
                sec1["Language"] = "fr";
                tblB.Rows.Add(sec1);
    
                DataRow sec2 = tblB.NewRow();
                sec2["ChannelNodeDescription"] = "Jeans";
                sec2["CategoryName"] = "Test";
                sec2["Language"] = "en";
                tblB.Rows.Add(sec2);
    
                DataRow sec3 = tblB.NewRow();
                sec3["ChannelNodeDescription"] = "jeans";
                sec3["CategoryName"] = "Test";
                sec3["Language"] = "fr";
                tblB.Rows.Add(sec3);

          //3rd table
            var tblC = new DataTable();
            tblC.Columns.Add("ChannelNodeMetaDetails");
            tblC.Columns.Add("CategoryName");
            tblC.Columns.Add("Language");

            DataRow tlbCRow = tblC.NewRow();
            tlbCRow["ChannelNodeMetaDetails"] = "t shirt for mens india";
            tlbCRow["CategoryName"] = "Solution";
            tlbCRow["Language"] = "en";
            tblC.Rows.Add(tlbCRow);

            DataRow tlbCRow1 = tblC.NewRow();
            tlbCRow1["ChannelNodeMetaDetails"] = "t-shirt pour hommes en Inde";
            tlbCRow1["CategoryName"] = "Solution";
            tlbCRow1["Language"] = "fr";
            tblC.Rows.Add(tlbCRow1);


            //4th table
            var tblD = new DataTable();
            tblD.Columns.Add("ChannelNodeMetaTitle");
            tblD.Columns.Add("CategoryName");
            tblD.Columns.Add("Language");

            DataRow tblDRow = tblD.NewRow();
            tblDRow["ChannelNodeMetaTitle"] = "MT- PRFT Test";
            tblDRow["CategoryName"] = "Solution";
            tblDRow["Language"] = "en";
            tblD.Rows.Add(tblDRow);

            DataRow tblDRow1 = tblD.NewRow();
            tblDRow1["ChannelNodeMetaTitle"] = "Meta titre";
            tblDRow1["CategoryName"] = "Solution";
            tblDRow1["Language"] = "fr";
            tblD.Rows.Add(tblDRow1);

            List<DataTable> dataTables = new List<DataTable>
            {
                tblA, tblB, tblC,tblD
            };
    
            DataTable dt = dataTables.MergeAll("CategoryName");
    
            Console.ReadLine();
            }

Note: I found the MergeAll function here Combining n DataTables into a Single DataTable

MergeAll function is not returning the rows belonging to "fr" language.

There are the chances that I can get n number of tables and those tables will always contain "CategoryName" and "Language" columns along with their own columns.(like here "ChannelNodeCategoryName" or "ChannelNodeDescription" are their own columns but have "Language" and "CategoryName" common).

Here is the output that I am trying to achieve

I am stuck badly. Can anybody help me to achieve this? Thanks

Answer 1

This won't solve your requirement regarding any arbitrary number of tables, but maybe you can draw inspiration from this solution for exactly two tables. You might consider normalizing the data into Lists, DataTables are messy to work with directly.

DataTable tblFinal = new DataTable();
tblFinal.Columns.Add("ChannelNodeCategoryName");
tblFinal.Columns.Add("ChannelNodeDescription");
tblFinal.Columns.Add("CategoryName");
tblFinal.Columns.Add("Language");

var qry = from a in tblA.AsEnumerable()
          join b in tblB.AsEnumerable()
              on new { cat = a.Field<string>("CategoryName"), lang = a.Field<string>("Language")} 
              equals new { cat = b.Field<string>("CategoryName"), lang = b.Field<string>("Language")}
              into j
          select new { CatName = a.Field<string>(0), j};

foreach (var item in qry)
{
    foreach (var grp in item.j)
    {
        tblFinal.Rows.Add(item.CatName, grp.Field<string>(0), grp.Field<string>(1), grp.Field<string>(2));
    }
}

Answer 2

If you have a List with your data tables :

private DataTable MyMethod(List<DataTable> listOfTablesToMerge)
{
    var myNewDataTable = new DataTable();
    myNewDataTable = YourMethodToCreateColumns();

    //faz merge de todas as tabelas na nova tabela unificada
    listOfTablesToMerge.ForEach(table => myNewDataTable.Merge(table));

    //saving changes
    myNewDataTable.AcceptChanges();
    
    return myNewDataTable;
}