Data searches in two datasets

hog

hog

Well-known member
Joined
Mar 17, 2003
Messages
984
Location
UK
Ive never done this as yet with VB.Net but now find I might have to....

If I have dataset1 that contains freshly imported data from a corp system and dataset2 that contains previously imported and actioned data, is there an efficient way to to search dataset2 for any record that exists in dataset1 so that these records will be dropped as they have previously been actioned upon :confused:
 
My first thought is to loop through the rows of dataset2 and add there IDs to a comma-delimited list. You could then use the RowFilter on DataSet1 along with the "IN" operator to filter down to either the rows you want to delete or negate it and get the rows you want to keep. If you use a StringBuilder the first part should be quick, but I have been unable to really determine just how quick applying rowfilters are thus far.

Note: Just to clarify, Im following your lead and using the term Dataset loosely. I assume the context will tell you above whether Im talking DataTable or DataView.
 
Thnx chaps...enligthening:)

Mehyar, this will be of use I feel at some later stage, but what Im trying to achieve is this:

Dataset1 contains the latest import
Dataset2 contains an ever growing collection of previous imports

I need to end up with Dataset1 only containing records that do not exist in Dataset2. These would be actioned and then appended to Dataset2 ready for the next run.

Im currently building this into an object setup whereby the object containing Dataset1 will have a method to search an object containing Dataset2. This would mean calling object with Dataset1s MoveNext method then do the search again.

Not sure if this is the most efficient way:)
 
Im not sure what youre looking for in terms of duplicates so this may or may not help. Assuming you have one column in both datasets and you can use that one column as a duplicate check (you can expand this to multiple columns fairly easily):
C#: 
DataSet ds1; // ds1 contains the original records
DataSet ds2; // ds2 contains all records, including potential dupes
DataRow[] dupes; // An array of duplicate rows

foreach(DataRow row in ds1.Tables["Table1"].Rows)
{
    // Get the unique ID from the original dataset
    int dupeID = row["UniqueColID"];

    dupes = ds2.Tables["Table1"].Select("UniqueColID = " + dupeID.ToString());
    for(int i=dupes.Length-1; i>=0; i--) dupes[i].Delete();
}

That loops through each row in the original dataset and finds duplicates in the new dataset. All matches found will be deleted.

You can do this in one shot if you use XSLT (XML transformations), which is like a language in and of itself. But it requires more work and might not be as readable, though might be faster.

Of course, I like to use actual SQL tables for this kind of thing. Insert all the rows from the new dataset, with potential dupes, into a scratch table (a table only used for this process). Then SELECT out DISTINCT rows into the real table. Or, delete the duplicates (delete from scratch table where IDs match those in the original table), then insert the rows into the real table.

-Nerseus
 
Back
Top