John Meyer John Meyer - 1 month ago 30
C# Question

DataTable.Merge not merging

I'm trying to merge two .NET DataTables

DataTable dtCalls = new DataTable();
dtCalls.Columns.Add("EmpID",typeof(string));
dtCalls.Columns.Add("Date",typeof(DateTime));
dtCalls.Columns.Add("Calls",typeof(int));
DataTable dtPages = new DataTable();
dtPages.Columns.Add("EmpID",typeof(string));
dtPages.Columns.Add("Date",typeof(DateTime));
dtPages.Columns.Add("Orders",typeof(int));

SqlCommand commRetrieveCalls = new SqlCommand(strRetrieveCalls,conn);
SqlDataAdapter adpRetrieveCalls = new SqlDataAdapter(commRetrieveCalls);
DataSet dsRetrieveCalls = new DataSet();
adpRetrieveCalls.Fill(dsRetrieveCalls);
DataTable dtRetrieveCalls = dsRetrieveCalls.Tables[0];

foreach(DataRow dr in dtRetrieveCalls.Rows) {
dtCalls.Rows.Add(dr[0].ToString(),DateTime.Parse(dr[1].ToString()),Convert.ToInt32(dr[2].ToString()));
}

SqlCommand commRetrieveOrders = new SqlCommand(strRetrievePages,conn);
SqlDataAdapter adpRetrieveOrders = new SqlDataAdapter(commRetrieveOrders);
DataSet dsRetrieveOrders = new DataSet();
adpRetrieveOrders.Fill(dsRetrieveOrders);
DataTable dtRetrieveOrders = dsRetrieveOrders.Tables[0];
foreach(DataRow drOrder in dtRetrieveOrders.Rows) {
dtPages.Rows.Add(drOrder[0].ToString(),DateTime.Parse(drOrder[1].ToString()),Convert.ToInt32(drOrder[2].ToString()));
}


The result, unfortunately is one table with null fields for each respective table's lacking column.

Answer

It looks like the problem is that you haven't established primary keys for the tables, so Merge doesn't know how to merge the rows for the same employee (and date?) into a single row. The result is likely that you end up with a union of the two tables. Try setting the primary key in each table via something like dtCalls.PrimaryKey = new DataColumn[] { dtCalls.Columns[0], dtCalls.Columns[1] }; (assuming Date is part of the PK here). Do the same for dtPages, then try your merge again.