Compare two DataTables to determine rows in one but not the other

would I have to iterate through each row on each DataTable to check if they are the same.

Seeing as you've loaded the data from a CSV file, you're not going to have any indexes or anything, so at some point, something is going to have to iterate through every row, whether it be your code, or a library, or whatever.

Anyway, this is an algorithms question, which is not my specialty, but my naive approach would be as follows:

1: Can you exploit any properties of the data? Are all the rows in each table unique, and can you sort them both by the same criteria? If so, you can do this:

• Sort both tables by their ID (using some useful thing like a quicksort). If they're already sorted then you win big.
• Step through both tables at once, skipping over any gaps in ID's in either table. Matched ID's mean duplicated records.

This allows you to do it in (sort time * 2 ) + one pass, so if my big-O-notation is correct, it'd be (whatever-sort-time) + O(m+n) which is pretty good.
(Revision: this is the approach that ΤΖΩΤΖΙΟΥ describes )

2: An alternative approach, which may be more or less efficient depending on how big your data is:

• Run through table 1, and for each row, stick it's ID (or computed hashcode, or some other unique ID for that row) into a dictionary (or hashtable if you prefer to call it that).
• Run through table 2, and for each row, see if the ID (or hashcode etc) is present in the dictionary. You're exploiting the fact that dictionaries have really fast - O(1) I think? lookup. This step will be really fast, but you'll have paid the price doing all those dictionary inserts.

I'd be really interested to see what people with better knowledge of algorithms than myself come up with for this o

Just FYI:

Generally speaking about algorithms, comparing two sets of sortable (as ids typically are) is not an O(M*N/2) operation, but O(M+N) if the two sets are ordered. So you scan one table with a pointer to the start of the other, and:

other_item= A.first()
only_in_B= empty_list()
for item in B:
    while other_item > item:
        other_item= A.next()
        if A.eof():
             only_in_B.add( all the remaining B items)
             return only_in_B
    if item < other_item:
         empty_list.append(item)
return only_in_B

The code above is obviously pseudocode, but should give you the general gist if you decide to code it yourself.

public DataTable compareDataTables(DataTable First, DataTable Second)
{
        First.TableName = "FirstTable";
        Second.TableName = "SecondTable";

        //Create Empty Table
        DataTable table = new DataTable("Difference");
        DataTable table1 = new DataTable();
        try
        {
            //Must use a Dataset to make use of a DataRelation object
            using (DataSet ds4 = new DataSet())
            {
                //Add tables
                ds4.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() });

                //Get Columns for DataRelation
                DataColumn[] firstcolumns = new DataColumn[ds4.Tables[0].Columns.Count];
                for (int i = 0; i < firstcolumns.Length; i++)
                {
                    firstcolumns[i] = ds4.Tables[0].Columns[i];
                }
                DataColumn[] secondcolumns = new DataColumn[ds4.Tables[1].Columns.Count];
                for (int i = 0; i < secondcolumns.Length; i++)
                {
                    secondcolumns[i] = ds4.Tables[1].Columns[i];
                }
                //Create DataRelation
                DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);
                ds4.Relations.Add(r);
                //Create columns for return table
                for (int i = 0; i < First.Columns.Count; i++)
                {
                    table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);
                }
                //If First Row not in Second, Add to return table.
                table.BeginLoadData();
                foreach (DataRow parentrow in ds4.Tables[0].Rows)
                { 
                    DataRow[] childrows = parentrow.GetChildRows(r);

                    if (childrows == null || childrows.Length == 0)
                        table.LoadDataRow(parentrow.ItemArray, true);
                    table1.LoadDataRow(childrows, false)