Data from 2nd DataTable.Merge not available

Question

I'm working on a C# 4.0 WinForms application, with SQL Server 2012 Express.

I need to combine the data from 2-tables into a single DataTable. I've tried using the 2-suggestions found below, in creating a DataSet, passing 2-queries to a SqlDataAdapter and then filling the DataSet.

How to fill Dataset with multiple tables?

Merge 2 DataTables and store in a new one

I've created 2-DataTables, i.e. dt1 and dt2, assigning the first table "Table" to the DataTable dt1. I've assigned the 2nd table "Table1" to the next DataTable,i.e. dt2. I created a 3rd DataTable dtAll and attempted to merge the tables, as shown below.

//Other variables not shown
string dateIN = "";
string dateOUT = "";
DataSet ds = new DataSet();
//SqlConnection here
conn.Open();

SqlDataAdapter adap = new SqlDataAdapter(
"SELECT * FROM AssignmentsIN WHERE ScheduledDateIN BETWEEN @start AND @end ORDER BY ScheduledDateIN ASC; SELECT * FROM AssignmentsOUT WHERE ScheduledDateOUT BETWEEN @start AND @end ORDER BY ScheduledDateOUT ASC", conn);

adap.SelectCommand.Parameters.Add("@start", SqlDbType.NVarChar).Value = dStart;
adap.SelectCommand.Parameters.Add("@end", SqlDbType.NVarChar).Value = dEnd;

adap.TableMappings.Add("Table", "AssignmentsIN");
adap.TableMappings.Add("Table1", "AssignmentsOUT");
adap.Fill(ds);

DataTable dt1 = ds.Tables["AssignmentsIN"];
DataTable dt2 = ds.Tables["AssignmentsOUT"];
DataTable dtAll;
dtAll = dt1.Copy();
dtAll.Merge(dt2);
                
foreach (DataRow row in dtAll.Rows)
{
    dateIN = row.Field<string>("ScheduledDateIN").Trim();//The data here shows up!
    //Data from 1st table shows up correctly. 

    dateOUT = row.Field<string>("ScheduledDateOUT").Trim();
    //^^ This begins the line where it throws the exception ^^
    //However, when the code attempts to access data from the 2nd table it errors.
}

The problem I'm running into, is that when my code gets to the place where I'm trying to access the data which resides in dt2, it throws the exception "Object reference not set to an instance of an object."

Both DateIN and DateOUT are fields of type string, not DateTime. As stated below, I can actually "See" data from the 2nd table, if I look into the DataSet. It shows up in a row.ItemArray. My question remains, how to successfully Merge those 2-tables into one DataTable. dtAll has the data from the 1st table. How can I get the data from dt2 into dtAll?

I can see the data from the 2nd table if I drill-down into the DataSet ds. How can I populate both DataTables, i.e. dt1 & dt2 and merge them into dtAll and successfully access data from the 2nd table?

UPDATE: After researching further on datatable merges; because the 2-database tables have dissimilar data structures, I've realized that attempting to Merge them into one DataTable may be impractical. Therefore, I am closing out this question without a resolution. I appreciate the responses I have rec'd.

Answer 1

From what I can tell, this is what may be happening. It is unknown “what” fields are in the DataTables dt1 and dt2. When you “Merge” tables, the column names that “match” will be “merged” into ONE (1) column. Example; if both tables have a string type column named “Col0” and execute…

dtAll = dt1.Copy();
dtAll.Merge(dt2);

Then, in the merged dtAll table, there will be one column called “Col0” and it will contain values from the columns named “Col0” from “BOTH” tables. Both table’s values will merge into ONE (1) column in the dtAll table since the column names are the same.

However, IF the merged table contains a column name that is NOT contained in the first table, then it will create a “new” column in the merged table with the second tables unique column name, AND it will place this column’s data starting “AFTER” the rows of the first table.

Example; looking at the posted code it appears that there is a column in dt1 named “ScheduledDateIN.” Also, in the table dt2 there appears to be a column named "ScheduledDateOUT." If these tables are merged you will end up with something that looks like…

As can be seen in the ScheduledDateOut column, that all the values for that field in the first tables rows are null. It can also be noted from the picture that columns with the “SAME” name are merged into one column. Therefore, I will assume that the picture above would represent what you are getting after executing the above commands.

It is unclear if this is what you want, however, this certainly would explain the null exception you are getting. The reason for the exception is….

If we look at the foreach loop…

foreach (DataRow row in dtAll.Rows)
{
  dateIN = row.Field<string>("ScheduledDateIN").Trim();
  dateOUT = row.Field<string>("ScheduledDateOUT").Trim();
}

This loops through all the rows in the merged dtAll table. So, using the picture above, on the first row…

dateIN = row.Field<string>("ScheduledDateIN").Trim();

This works without issue since there IS a “value” there in the "ScheduledDateIN" field on the first row. However, on the next line…

dateOUT = row.Field<string>("ScheduledDateOUT").Trim();

This line is going to throw the exception you noted because, as can be seen from the picture, the value of the "ScheduledDateOUT" field on the FIRST row is empty/null. Therefore, when you try to call the strings .Trim() method on a null value you will get the null exception as you already know.

If you could continue the loop, the same exception would get thrown on the first line when the loop reached the 11th row (from the picture) as the values for the “ScheduledDateIN" fields are empty/null.

Since it is not exactly clear what you are wanting to achieve when “merging” the tables, the only solution I can proffer is to “CHECK” for these null values “before” attempting to call the Trim() method. This solution may look like below.

foreach (DataRow row in dtAll.Rows) {
  if (row.Field<string>("ScheduledDateIN") != null) {
    var dateIN = row.Field<string>("ScheduledDateIN").Trim();//The data here shows up!
  }
  if (row.Field<string>("ScheduledDateOUT") != null) {
    var dateOUT = row.Field<string>("ScheduledDateOUT").Trim();
  }
}

I hope that makes sense.