c# - How to merge multiple datatables (every datatable return one row) in one row -

i have multiple data tables : want merge them (my p.k : emp_num)

var tblemp_data = new datatable();//one row var tblemp_time = new datatable();//one row var tbl_emp_mission = new datatable();//one row  tblemp_data = getempinfo(empnum); tblemp_time = getemptime(empnum, start_period, end_period); tbl_emp_mission = getempmission(empnum, start_period, end_period);  tblemp_data.merge(tblemp_time, false, missingschemaaction.add); tblemp_data.acceptchanges(); tblemp_data.merge(tbl_emp_mission, false, missingschemaaction.add); tblemp_data.acceptchanges(); 

---

now data on multiple rows rather 1 row ! want data on 1 row ? how ?

---

note: want columns allow null except primary key avoid exception :

failed enable constraints. 1 or more rows contain values violating non-null, unique, or foreign-key constraints  

---

edited:

the third table cause problem:

public static datatable getempmission(int empnum, datetime start_period, datetime end_period)         {             using (ifxconnection con = new ifxconnection(configurationmanager.connectionstrings["tl"].tostring()))             {                 datatable dt = new datatable();                 stringbuilder cmdtxt = new stringbuilder();                 cmdtxt.append(" select count(emp_num)  ");                 cmdtxt.append(" hs_mission emp_num = ? , from_date between ? , ?  ");                  using (var myifxcmd = new ifxcommand(cmdtxt.tostring(), con))                 {                      myifxcmd.commandtype = commandtype.text;                     if (con.state == connectionstate.closed)                     {                         con.open();                     }                     myifxcmd.parameters.add("emp_num", ifxtype.smallint);                     myifxcmd.parameters.add("start_period", ifxtype.date);                     myifxcmd.parameters.add("end_period", ifxtype.date);                      myifxcmd.parameters[0].value = ((object)empnum) ?? dbnull.value;                     myifxcmd.parameters[1].value = ((object)start_period.date) ?? dbnull.value;                     myifxcmd.parameters[2].value = ((object)end_period.date) ?? dbnull.value;                      using (ifxdatareader dr = myifxcmd.executereader())                     {                         dt.load(dr);                         dt.columns.add("emp_num", typeof(int32));                         dt.rows[0]["emp_num"] = empnum;                         dt.acceptchanges();                     }                  }                 con.close();                 con.dispose();                 return dt;               }          } 

---

return data :

column1            emp_num    0                   6762 

and throw exception :

failed enable constraints. 1 or more rows contain values violating non-null, unique, or foreign-key constraints  

so empnum key column tables share? sounds if use my mergeall:

public static datatable mergeall(this ilist<datatable> tables, string primarykeycolumn) {     if (!tables.any())         throw new argumentexception("tables must not empty", "tables");     if(primarykeycolumn != null)         foreach(datatable t in tables)             if(!t.columns.contains(primarykeycolumn))                 throw new argumentexception("all tables must have specified primarykey column " + primarykeycolumn, "primarykeycolumn");      if(tables.count == 1)         return tables[0];      datatable table = new datatable("tblunion");     table.beginloaddata(); // turns off notifications, index maintenance, , constraints while loading data     foreach (datatable t in tables)     {         foreach (datacolumn col in t.columns)              col.readonly = false; // might required in case         table.merge(t); // same table.merge(t, false, missingschemaaction.add);     }     table.endloaddata();      if (primarykeycolumn != null)     {         // since might have no real primary keys defined, rows might have repeating fields         // we're going "join" these rows ...         var pkgroups = table.asenumerable()             .groupby(r => r[primarykeycolumn]);         var dupgroups = pkgroups.where(g => g.count() > 1);         foreach (var grpdup in dupgroups)         {              // use first row , modify             datarow firstrow = grpdup.first();             foreach (datacolumn c in table.columns)             {                 if (firstrow.isnull(c))                 {                     datarow firstnotnullrow = grpdup.skip(1).firstordefault(r => !r.isnull(c));                     if (firstnotnullrow != null)                         firstrow[c] = firstnotnullrow[c];                 }             }             // remove first row             var rowstoremove = grpdup.skip(1);             foreach(datarow rowtoremove in rowstoremove)                 table.rows.remove(rowtoremove);         }     }      return table; } 

then use in way:

var tables = new[] { tblemp_data, tblemp_time, tbl_emp_mission }; tblemp_data = tables.mergeall("empnum");