Can I merge two .NET DataTables, without merging null values, without having to loop through the rows myself?

Question

Suppose I have two .NET DataTables populated as follows, where ID is the primary key, and where null means a DBNull.Value (not the string "null"):

    |Table A                |           |Table B
    |ID   |Column1 |Column2 |           |ID   |Column1 |Column2 |
    |1    |foo     |10      |           |1    |zzz     |null    |
    |2    |baz     |20      |           |2    |null    |99      |

I want to do a merge of these tables, where the result will be:

    |ID   |Column1 |Column2 |
    |1    |zzz     |10      |
    |2    |baz     |99      |

In other words, I want to merge the data from Table B into Table A, just like DataTable.Merge does, but instead of copying the entire row from table B when the IDs match, I only want to copy the fields where the data is not null.

I could write my own loops through the rows of both tables to do this, but I'm wondering if there is already a method in the libraries, so that I don't have to rewrite my own version of DataTable.Merge.

Answer 1

You could try this:

public static DataTable Union (DataTable First, DataTable Second)
{

      //Result table
      DataTable table = new DataTable("Union");

      //Build new columns
      DataColumn[] newcolumns = new DataColumn[First.Columns.Count];

      for(int i=0; i < First.Columns.Count; i++)
      {
          newcolumns[i] = new DataColumn(
          First.Columns[i].ColumnName, First.Columns[i].DataType);
      }

      table.Columns.AddRange(newcolumns);
      table.BeginLoadData();

      foreach(DataRow row in First.Rows)
      {
           table.LoadDataRow(row.ItemArray,true);
      }

      foreach(DataRow row in Second.Rows)
      {
          table.LoadDataRow(row.ItemArray,true);
      }

      table.EndLoadData();
      return table;
}

From here (not tested).

Answer 2

Rats...I don't think what I'm looking for exists.

For what it's worth, if anyone looking at this has the same need as I did, this is the "roll my own" code that I ended up with:

        // Merge 
        foreach (DataRow br in tableB.Rows)
        {
            // This assumes a one-column primary key. In my case,
            // that's valid, but not for a general-purpose function.
            DataRow ar = tableA.Rows.Find(br[tableB.PrimaryKey[0]]);
            if (ar == null)
            {
                continue;
                // If I really wanted it to act like DataTable.Merge, it would
                // add the missing row to tableA rather than just skipping it.
                // again, I didn't implement this because I didn't need it.
            }
            foreach (DataColumn ac in tableA.Columns)
            {
                DataColumn bc = tableB.Columns[ac.ColumnName];
                if (bc != null && !br.IsNull(bc))
                    ar[ac] = br[bc];
            }
        }

As the comments indicate, this doesn't do all of what DataTable.Merge does, and makes some assumptions that aren't valid in general. It wouldn't be hard to fix those issues though. I leave it here in case it helps somebody.