none
How to merge 2 tables by the same column

    Question

  • Dear all,

    Here is a question to be solved. I've two C# functions to get dataset from different database, one is from SQL statement, the other is from MDX statement. Two results have the same column named "Groupname" in the tables of the db. So I want to merge the tables by the column name as follow:

    Main table from sql statement

    Group Name Set
    Group 1 10
    Group 2 12

     Sub table from MDX statement

    Group Name Turnover transaction size
    Group 1 $20,000.00 100
    Group 2 $12,000.00 85

     After mergeing, I want to get the table as follow:

    Group Name Set Turnover Transaction size
    Group 1 10 $20,000.00 100
    Group 2 12 $12,000.00 85

    whether the ADO.NET has a function to merge two tables or not. Could I use the DataRelation class to do it.

    DataRelation dr = new DataRelation("rel1", ds.Tables["main"].Columns[0], ds.Tables["sub"].Columns[0]); 

    Thanks & regards!

    Radeon Ling

     La Vie en Rose

    Monday, August 23, 2010 5:33 AM

Answers

  • Hi Radeon,

    Is the GroupName primary key of your table ? Could you please post your code ? The following code works for me.

    DataTable table1 = new DataTable("Items");
    
    // Add columns
    DataColumn column1 = new DataColumn("id", typeof(System.Int32));
    DataColumn column2 = new DataColumn("item", typeof(System.String));
    table1.Columns.Add(column1);
    table1.Columns.Add(column2);
    
    // Set the primary key column.
    table1.PrimaryKey = new DataColumn[] { column1 };
    
    // Add some rows.
    DataRow row;
    for (int i = 1; i <= 3; i++)
    {
      row = table1.NewRow();
      row["id"] = i;
      row["item"] = i.ToString() + "Alex" ;
      table1.Rows.Add(row);
    }
    
    // Accept changes.
    table1.AcceptChanges();
    
    this.dataGridView1.DataSource = table1;
    
    // Create a second DataTable.
    DataTable table2 = new DataTable("Items2");
    
    // Add columns
    DataColumn column11 = new DataColumn("id", typeof(System.Int32));
    DataColumn column21 = new DataColumn("item2", typeof(System.Int32));
    DataColumn column31 = new DataColumn("item3", typeof(System.String));
    table2.Columns.Add(column11);
    table2.Columns.Add(column21);
    table2.Columns.Add(column31);
    
    table2.PrimaryKey = new DataColumn[] { column11 };
    
    // Add three rows. 
    row = table2.NewRow();
    row["id"] = 1;
    row["item2"] = 774;
    row["item3"] = "Hello";
    table2.Rows.Add(row);
    
    row = table2.NewRow();
    row["id"] = 2;
    row["item2"] = 555;
    row["item3"] = "World";
    table2.Rows.Add(row);
    
    row = table2.NewRow();
    row["id"] = 3;
    row["item2"] = 665;
    row["item3"] = "Day";
    table2.Rows.Add(row);
    
    this.dataGridView2.DataSource = table2;
    
    // Merge table2 into the table1.
    Console.WriteLine("Merging");
    table1.Merge(table2);      
    


    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

    Thursday, August 26, 2010 8:45 AM
    Moderator

All replies

  • var Table3 = from c in table1.AsEnumerable() //Main table
                 join s in table2.AsEnumerable() //sub table
                 on c.Field<string>("GroupName") equals s.Field<string>("GroupName")
                 select new
                 {
                   GroupName = c.Field<string>("GroupName"),
                   Set = c.Field<int>("set"),
                   TurnOver = s.Field<double>("TurnOver"),
                   TransactionsSize = s.Field<int>("TransactionsSize")
                 };
          Console.ReadLine();
    

    Nanda - Misys Software Solutions,Bangalore
    Monday, August 23, 2010 8:43 AM
  • Hi Radeon,

    You can simply use DataTable.Merge Method (DataTable) if Group Name is the primary key of both your two tables. For example:

    dataTable1.Merge(dataTable2);

    For detailed information and more sample code about DataTable.Merge Method, please refer to the following link.
    http://msdn.microsoft.com/en-us/library/fk68ew7b.aspx

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, August 25, 2010 3:13 AM
    Moderator
  • Hi Radeon,

    You can simply use DataTable.Merge Method (DataTable) if Group Name is the primary key of both your two tables. For example:

    dataTable1.Merge(dataTable2);

    For detailed information and more sample code about DataTable.Merge Method, please refer to the following link.
    http://msdn.microsoft.com/en-us/library/fk68ew7b.aspx

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.


    Dear Alex

    The method has been used, but it can't merge two tables in the same row by the same column. I've refered the merge method in MSDN, Merge likes union of the sql statement.

    Thanks!

    Radeon Ling

    Thursday, August 26, 2010 7:54 AM
  • Hi Radeon,

    Is the GroupName primary key of your table ? Could you please post your code ? The following code works for me.

    DataTable table1 = new DataTable("Items");
    
    // Add columns
    DataColumn column1 = new DataColumn("id", typeof(System.Int32));
    DataColumn column2 = new DataColumn("item", typeof(System.String));
    table1.Columns.Add(column1);
    table1.Columns.Add(column2);
    
    // Set the primary key column.
    table1.PrimaryKey = new DataColumn[] { column1 };
    
    // Add some rows.
    DataRow row;
    for (int i = 1; i <= 3; i++)
    {
      row = table1.NewRow();
      row["id"] = i;
      row["item"] = i.ToString() + "Alex" ;
      table1.Rows.Add(row);
    }
    
    // Accept changes.
    table1.AcceptChanges();
    
    this.dataGridView1.DataSource = table1;
    
    // Create a second DataTable.
    DataTable table2 = new DataTable("Items2");
    
    // Add columns
    DataColumn column11 = new DataColumn("id", typeof(System.Int32));
    DataColumn column21 = new DataColumn("item2", typeof(System.Int32));
    DataColumn column31 = new DataColumn("item3", typeof(System.String));
    table2.Columns.Add(column11);
    table2.Columns.Add(column21);
    table2.Columns.Add(column31);
    
    table2.PrimaryKey = new DataColumn[] { column11 };
    
    // Add three rows. 
    row = table2.NewRow();
    row["id"] = 1;
    row["item2"] = 774;
    row["item3"] = "Hello";
    table2.Rows.Add(row);
    
    row = table2.NewRow();
    row["id"] = 2;
    row["item2"] = 555;
    row["item3"] = "World";
    table2.Rows.Add(row);
    
    row = table2.NewRow();
    row["id"] = 3;
    row["item2"] = 665;
    row["item3"] = "Day";
    table2.Rows.Add(row);
    
    this.dataGridView2.DataSource = table2;
    
    // Merge table2 into the table1.
    Console.WriteLine("Merging");
    table1.Merge(table2);      
    


    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

    Thursday, August 26, 2010 8:45 AM
    Moderator
  • Hi Radeon,

    How about the issue now ? Have you solved it ? Does my sample code work ?

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 30, 2010 1:24 PM
    Moderator