Merge 2 DataTables

Question

I have 2 Datatable with these fields:

DataTable1

Hour - Value1
0 - 34
1 - 22
2 - NULL
3 - 12
..
..
23 - 10

DataTable2

Hour - Value1
0 - NULL
1 - 22
2 - 35
3 - 11
..
..
23 - NULL

I need to populate an unique DataTable that has the same "Hour" fields (the hours of the day) and for the values has to get the values from DataTable1 if is not null. If the values in Datatable1 is null, I have to take the correspondet values from DataTable2. If also the value from DataTable2 is null, I have to log the error.

For my example, I need to get:

DataTableResult

Hour - Value1
0 - 34
1 - 22
2 - 35
3 - 12
..
..
23 - 10

How can I get this?

Answer 1

This is question follows a simple conditional logic you would need to process each element in a foreach statement.

You want to process this so every time an element in a row has null. You go to that row in datatable 2 and check if that has a value if so this becomes the new value in datatable 1. If this does not then throw an error. I have provided this link how to compare but really you don't need this as all you are doing is testing for null in a field in a row.

Answer 2

Using Linq to objects and assuming dataTable1 and dataTable2 have the same columns:

var hoursMap1 = dataTable1.Rows.Cast<DataRow>().ToDictionary(row => row[0]);
var hoursMap2 = dataTable2.Rows.Cast<DataRow>().ToDictionary(row => row[0]);

var resultTable = new DataTable();

// Clone the column from table 1
for (int i = 0; i < dataTable1.Columns.Count; i++)
{
  var column = dataTable1.Columns[i];
  resultTable.Columns.Add(column.ColumnName, column.ColumnType);
}

foreach (var entry in hoursMap1)
{
  int hours = entry.Key;
  DataRow row1 = entry.Value;
  DataRow row2 = null;
  if (!hoursMap2.TryGetValue(hours, out row2))
  {
    // Hours in table 1 but not table 2, handle error
  }

  var fields = new object[resultTable.Columns.Count];
  int fieldIndex = 0;
  fields[fieldIndex++] = hours;

  for (int i = 1; i < row1.ItemsArray.Length; i++)
  {
    var field1 = row1.ItemsArray[i];
    var field2 = row2.ItemsArray[i];
    var newField = field1 ?? field2;
    if (newField == null)
    {
      // Field neither in table 1 or table 2, handle error
    }

    fields[fieldIndex++] = newField;
  }

  resultTable.Rows.Add(fields);
}