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");