Comparing values in 2 DataTables

Question

I'm processing 2 DataTables:

1. SSFE: Contains the values I want to find
2. FFE: Is larger, smaller or equally large as SSFE, but does not necessarily contain every value of SSFE

The values I need to match between these tables are integers, both tables are sorted from small to large. My idea was to start searching on the first item in FFE, start looping through SSFE, and when I find a match -> remember current index -> save match -> select next item from FFE and continue from the previous index.

Also, FFE can contain integers, but can also contain strings. That is why I cast the values to a string and compare these.

I made some code, but it takes too much time. It will take about a minute to compare SSFE (1.000 items) to FFE (127.000 items).

int whereami = 0;
bool firstiteration = true;
for (int i = 0; i < FFEData.Rows.Count - 1; i++)
{
 for (int j = 0; j < SSFEData.Rows.Count - 1; j++)
 {
 if (firstiteration)
 {
 j = whereami;
 firstiteration = false;
 }
 if (SSFEData.Rows[j][0] == FFEData.Rows[i][0].ToString())
 {
 found++;
 whereami = j;
 firstiteration = true;
 break;
 }
 }
}

I'm only storing how many occurrences I have found for testing. In this example it will find 490 matches, not that this is relevant.

Any suggestions would be great!

• Malachi: what is the make up of the data table? I can only assume that each row only holds one item. in which case a DataTable is not a good Type for what you are doing.
• Malachi: you should give us a little more code, like maybe how you populate the DataTables. because you should be creating a List<t> or a Dictionary<Key, Value> and then go from there.

Answer 1

Your code is very confusing.

Try using a set of List<T> objects instead of DataTables.

foreach (var item in FFE)
{
 if (SSFE.Contains(item))
 {
 found++;
 }
}

Where FFE and SSFE are both List<string> when you add the items to FFE I would add them as strings to start out with.

Answer 2

I have actually found the solution on SO:

public int GetMatches(DataTable table1, DataTable table2)
{
 DataSet set = new DataSet();
 //wrap the tables in a DataSet.
 set.Tables.Add(table1);
 set.Tables.Add(table2);
 //Creates a ForeignKey like Join between two tables.
 //Table1 will be the parent. Table2 will be the child.
 DataRelation relation = new DataRelation("IdJoin", table1.Columns[0], table2.Columns[0], false);
 //Have the DataSet perform the join.
 set.Relations.Add(relation);
 int found = 0;
 //Loop through table1 without using LINQ.
 for(int i = 0; i < table1.Rows.Count; i++)
 {
 //If any rows in Table2 have the same Id as the current row in Table1
 if (table1.Rows[i].GetChildRows(relation).Length > 0)
 {
 //Add a counter
 found++;
 //For debugging, proof of match:
 //Get the id's that matched.
 string id1 = table1.Rows[i][0].ToString();
 string id2 = table1.Rows[i].GetChildRows(relation)[0][0].ToString();
 }
 }
 return found;
}

This code will join the 2 datatables like a join in an SQL query. This method will compare 10.000 rows in SSFE to 127.000 rows in FFE in 1,4 seconds.

• Jamal: SInce you've taken this straight from another SE post, you should also provide a link to it so that proper credit is given.