Combine two data tables with null values on both tables - C#

Question

I have two data tables as shown below.

datatable1:

table1_id(PK)  DriverID    Vehicle    
   111          Ram00       VRN01       
   112          Shyam00     VRN02
   113          Ram00       VRN03

datatable2:

 table2_id(PK)  DriverID     exit_time 
   AA1          Ram00        10.10AM      
   AA2          Hari00       11.20PM
  

Combined Output

table1_id     DriverID    Vehicle     table2_id   exit_time 
   111          Ram00       VRN01       AA1         10.10AM 
   112          Shyam00     VRN02       NULL        NULL
   113          Ram00       VRN03       AA1         10.10AM 
   NULL         Hari00       NULL       AA2         11:20PM

DriverID is common in both table. But just merging two datatable will not give this result. Please help to achieve this.

datatable1.Merge(datatable2);

Answer 1

You can use Two Data tables to combine into one Data table via Coding and Remove the Extra Column later the For loop ends,Check my code it will work.

string Qry = "select tab1.table_id,'' as DriverID,vehicle,tab1.driver_id Tab1DrvrID,exit_time from tab1 " +
              "full join tab2 on tab2.driver_id=tab1.driver_id";
            cmd = new SqlCommand(Qry, con);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);

            //string DrvrID;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string Qry2 = "select tab1.table_id,'' as DriverID,vehicle,tab1.driver_id Tab1DrvrID,tab2.driver_id Tab2DrvrID,exit_time from tab1 " +
                   "full join tab2 on tab2.driver_id=tab1.driver_id ";
                cmd = new SqlCommand(Qry2, con);
                SqlDataAdapter daa = new SqlDataAdapter();
                DataTable dtt = new DataTable();
                daa = new SqlDataAdapter(cmd);
                daa.Fill(dtt);

                if (dtt.Rows.Count > 0)//
                {
                    string s=dtt.Rows[i]["Tab1DrvrID"].ToString();
                    if (s=="")
                    {
                        dt.Rows[i]["DriverID"] = dtt.Rows[i]["Tab2DrvrID"].ToString();
                    }
                    else
                    {
                        dt.Rows[i]["DriverID"] = dtt.Rows[i]["Tab1DrvrID"].ToString();
                    }
                }
                else
                {

                }
                dt.AcceptChanges();
            }
            dt.Columns.Remove("Tab1DrvrID");

Answer 2

Merge works properly if columns from two DATATABLES are matched in the same DATATYPE. If the column has a NULL value in the first row of DATATABLE the column DATATYPE will be String. So the second DATATABLE, if have value Date or any other type, will miss the match on merge . To resolve this problem you need to make the two DATATABLES columns same DATATYPE.

    private DataTable MergeDataTable(DataTable dataTable1, DataTable dataTable2)
    {
        var dtCloned = dataTable2.Clone();

        foreach (DataColumn column in dataTable2.Columns)
        {
            var col = dataTable1.Columns[column.ColumnName];
            if (col != null && col.DataType != column.DataType )
            {
                dtCloned.Columns[column.ColumnName].DataType = col.DataType;
            }
        }
        foreach (DataRow d in dataTable2.Rows)
        {
            dtCloned.ImportRow(d);
        }

        dataTable1.Merge(dtCloned);

        return dataTable1;
    }