Merging Multiple ADO.NET DataSets

Question

Scenario

I have a c# winforms application with a gridview. The gridview datasource is populated by a dataset. The contents of this dataset get updated every few seconds, and there are not always the same number of items in the dataset etc. The dataset actually gets populated (or needs to get populated) by multiple other datasets being passed in one at a time.These datasets update at completely random times that cannot be controlled.

The Issue

Since essentially the GridView dataset is being "CONTINUALLY UPDATED" - At the time of an update(The point that a new dataset with the latest information in it needs to be merged with the existing GridView DataSource), I need to take a copy of the current dataset populating the gridview, compare it to the incoming data from the other dataset and update ONLY the data that is new.

So Far

I've tried a series of different methods but I can't seem to get this to work. I either end up with the dataset just being added to continually with duplicates of slightly different data, or the rows randomly get deleted (I tried using a merge all then delete approach which only works occasionally).

Any ideas how I can get around this!?

At the moment the gridview just updates itself to show the latest incoming dataset without the previous dataset merged into it.....

CODE - This randomly deletes the rows even though it does at some points display all data

//If the existing GridView dataset contains any rows that 
//exist in the incoming latest dataset (matched on the two unique fields
//of the dataset), then remove that row from the existing GridView dataset
for (int i = 0; i < existingPriceResultsDTCopyForCompare.Rows.Count; i++)
{
    foreach (DataRow incomingRow in incomingDS.Tables[0].Rows)
    {
        string incomingDate = incomingRow["Date"].ToString();

        DataRow currentRow = existingPriceResultsDTCopyForCompare.Rows[i];
        if ((currentRow["CCY"].ToString().Contains(incomingCcy))
            && (currentRow["Date"].ToString().Contains(incomingDate)))
        {
            existingPriceResultsDT.Rows.RemoveAt(i);

        }
    }
}

//Then merge the existing GridView dataset (Minus the old data that 
//matches the data from the incoming Dataset with the latest information),
//With the brand new incoming data.
incomingDS.Merge(existingPriceResultsDT);

EDIT -

I'm beginning to wonder if the incoming dataset keeps being overwritten by the next incoming dataset before the iteration has time to complete. So I'm guessing I need to lock the incoming DataSet?

Answer 1

Have you tried something similar

  DataSet ds1 = new DataSet();
  DataSet ds2 = new DataSet();   

  ds1.Merge(ds2);    
  DataSet ds3 = ds1.GetChanges();

As per your Objects

  DataSet existingPriceResultsDT = new DataSet();
  DataSet incomingDS = new DataSet();

  incomingDS.Merge(existingPriceResultsDT);
  existingPriceResultsDT = incomingDS.GetChanges();

Answer 2

You might want to look into the Microsoft Sync Framework. This sounds like a perfect scenario for it. This video is a good introduction. You can also download the tutorials here.

Answer 3

That nested for-loop stuff is a big-O nightmare. You definitely want to get out of the business of looping through those rows - lots of unnecessary compares.

It looks like you've got a couple of columns there that you are comparing each time - "CCY" and "Date". Have you considered using them as the primary key for the table? If that makes sense for your scenario, you can make things considerably more efficient.

You could consider doing something like this:

Determine which columns are primary keys - it appears here that CCY and Date are for you.

DataColumn[] keys = new DataColumn[2];
keys[0] = dataTable.column["CCY"];
keys[1] = dataTable.column["Date"];
dataTable.PrimaryKey = keys;

Then when you read in a new DataSet - however you do that, call Merge on it like so:

dataSet.Merge(newDataSet, false, MissingSchemaAction.Add);

Assumption here is that dataTable is the Tables[0] of dataSet.

This, at least for my quick test program, will merge the two DataSets and update the rows that changed and add any new ones.

And, I just had my DataGridView set up like so:

dataGridView1.DataSource = dataSet.Tables[0];

Seemed to update the view well enough for my test program.

Hope that helps.