?
Solved

need help with algorithm comparing two datasets

Posted on 2005-05-12
11
Medium Priority
?
6,601 Views
Last Modified: 2010-05-18
heya,

having a hard time comparing two datasets and producing the difference between the two.

For example, let's say I have two dataSets called DS1 and DS2.

DS1 contains the following rows:
Buckeyes
Wolverines  <---not in DS2
Hoosiers
Hawkeyes
Boilermakers

DS2 contains the following rows:
Buckeyes
Hoosiers
Hawkeyes
Boilermakers
WildCats  <----not in DS1

how can I create a dataSet called dsResult that will contain the following rows:
Wolverines
Wildcats

thanks you very much



0
Comment
Question by:brdrok
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 7

Author Comment

by:brdrok
ID: 13986608
below is my first attemp but wasn't successful...

DataSet dsAccess = new DataSet();
DataSet dsSql = new DataSet();
DataSet dsResult = new DataSet();

DataTable tblResult = new DataTable("Result");
DataColumn colResult = new DataColumn("colResultNumber", System.Type.GetType("System.String"));
DataColumn colResultName = new DataColumn("colResultName", System.Type.GetType("System.String"));
tblResult.Columns.Add(colResult);
tblResult.Columns.Add(colResultName);
dsResult.Tables.Add(tblResult);

OleDbConnection accessConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\toll\Desktop\SWOL_ProjDataExtract_2.mdb;Persist Security Info=False");
SqlConnection sqlConn = new SqlConnection(@"Server=xxx.xx.x.xxx;Database=SWOL2000;User ID=xxxxx;Password=xxxxx;Trusted_Connection=False");

OleDbDataAdapter daAccess = new OleDbDataAdapter("SELECT * FROM TblActiveSurveyProjsWithProfCtrAndNameFinal", accessConn);
SqlDataAdapter daSql = new SqlDataAdapter("SELECT * FROM Project", sqlConn);

daAccess.Fill(dsAccess, "Access");
daSql.Fill(dsSql, "SQL");

dgAccess.DataSource = dsAccess;
dgSql.DataSource = dsSql;

foreach(DataRow sqlDr in dsSql.Tables[0].Rows)
{
      foreach(DataRow accessDr in dsAccess.Tables[0].Rows)
      {
            if(sqlDr[2].ToString() == accessDr[0].ToString())
            {
                  DataRow newRow = dsResult.Tables[0].NewRow();
                  newRow[0] = accessDr[0].ToString();  
                  newRow[1] = accessDr[2].ToString();
                  dsResult.Tables[0].Rows.Add(newRow);
            }
      }
}
            
dgResult.DataSource = dsResult;
0
 
LVL 3

Accepted Solution

by:
Diego Pazos earned 2000 total points
ID: 13987533
Try this:

DataSet DSComp(DataSet DS1, DataSet DS2) {
    DataTable DT, DTAux;
    DataRow[] DRA;
    DT = DS1.Tables[0];
    DTAux = DS2.Tables[0].Copy();

    foreach (DataRow DR in DT.Rows) {
        DRA = DTAux.Select("Text='" + DR["Text"] + "'");
        switch(DRA.Length){
            case 0:
                DTAux.Rows.Add(DR.ItemArray);
                break;
            case 1:
                DTAux.Rows.Remove(DRA[0]);
                break;
            default:
                throw new Exception("Duplicates found");
        }
    }
    DataSet DSAux=new DataSet();
    DSAux.Tables.Add(DTAux);
    return DSAux;
}
0
 
LVL 3

Expert Comment

by:Diego Pazos
ID: 13987564
Forgot to say: this code uses the first table in each dataset (by indexing them), and queries the field named 'Text'. Change this to suit your case.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Author Comment

by:brdrok
ID: 13987757
thanks very much for your response....

I get an error that says:
Additional information: Cannot find column [team1] at the follwoing line:
DRA = DTAux.Select("team1='" + DR["team1"] + "'");

perhaps I am not building my dataColumsn, dataTable not correctly?  here is what I got so far:

//this assume i have a dataset called ds1
//create a new data table
DataTable ds1Table = new DataTable("table1");
//create a data column and name it "team1"
DataColumn colTeam1 = new DataColumn("team1", System.Type.GetType("System.String"));
//add column to ds1Table table
ds1Table.Columns.Add(colTeam1);
//add table to my dataset ds1
ds1.Tables.Add(ds1Table);

//create a new row based on table[0] of ds1
DataRow dr = ds1.Tables[0].NewRow();
//assign some vallue
dr[0] = "Buckeyes";
//add the new row to the table
ds1.Tables[0].Rows.Add(dr);

//do that for other teams...etc.

0
 
LVL 7

Author Comment

by:brdrok
ID: 13987815
nevermind...got it to run w/o errors...will keep you posted ont he outcome...
0
 
LVL 7

Author Comment

by:brdrok
ID: 13987856
hopefully didn't jinx myself....but it seems to work GREAT.

thanks a bunch

btw...posted the same question in the asp.net section for 50 points at the following link...not sure how to assign those points to you as well w/o you adding a comment...

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21422037.html
0
 
LVL 7

Expert Comment

by:NipNFriar_Tuck
ID: 13987879
One thing you may want to try is to convert the two datasets to XML strings then compare the strings.  This works very well and depending on the size of the dataset may be faster.  The one caviot that I ran into deals with an empty string and null.
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 13987884
just out of curiosity how many rows are we talking here in real life ?

also can their be duplicates i.e.

table 1
wolverines
wolverines
wolverines
wolverines
tanks
tanks

table 2
wolverines
flying fish

this should produce Tanks Flying Fish as differences ?

or should it produce
Wolverines,Wolverines,Wolverines,Tanks,Tanks,Flying Fish as differences ?!

if you are leaning towards the first and have significant data I would make a single pass through the table and build a hash table as opposed to doing n select operations which are all linear (should be SIGNIFICANTLY faster).

Greg
0
 
LVL 7

Author Comment

by:brdrok
ID: 13987970
thanks for the input...feel terribly replying although i already assigned points....but to answer some of the questions...I really have not worked extensively with XML files and/or the System.XML namesapce bit.

in real life...we are probably talking about 600-700 records inside the sql server and ms access database.  not terribly much as far as databases are concerned...

regarding duplicates...I don't think there are any duplicates...i won't have to worry about this issue...(caught a break there =)  

0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 13988203
ok so you will have 600-700 records inside of each dataset ?!

if you have 700 ....

thats 700^2 comparisons that will be performed using hte above algorithm O(n^2)

by moving to a hash table implementation you should be looking closer to O(N log N)

0
 

Expert Comment

by:motorlife
ID: 15110655
So, can you please explain, how to work with the xml data sets into a hash table?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question