DataTable Relational Operators in C# - DIFFERENCE Method

The DIFFERENCE Method has no equivalent in TSQL.
It is also refered to as MINUS and is simply all the rows that are in the First table but not the Second.

The argument order of the method is important. That is: Difference(First, Second) != Difference(Second, First)

There is only the one signature for this method.
In summary the code works as follows:

  Create new empty table
  Create a DataSet and add tables.
  Get a reference to all columns in both tables
  Create a DataRelation
  Using the DataRelation add rows with no child rows.
  Return table

public static DataTable Difference(DataTable First, DataTable Second)

{

      //Create Empty Table

      DataTable table = new DataTable("Difference");

 

      //Must use a Dataset to make use of a DataRelation object

      using(DataSet ds = new DataSet())

      {

            //Add tables

            ds.Tables.AddRange(new DataTable[]{First.Copy(),Second.Copy()});

            //Get Columns for DataRelation

            DataColumn[] firstcolumns  = new DataColumn[ds.Tables[0].Columns.Count];

            for(int i = 0; i < firstcolumns.Length; i++)

            {

                  firstcolumns[i] = ds.Tables[0].Columns[i];

            }

 

            DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];

            for(int i = 0; i < secondcolumns.Length; i++)

            {

                  secondcolumns[i] = ds.Tables[1].Columns[i];

            }

            //Create DataRelation

            DataRelation r = new DataRelation(string.Empty,firstcolumns,secondcolumns,false);

            ds.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 ds.Tables[0].Rows)

            {

                  DataRow[] childrows = parentrow.GetChildRows(r);

                  if(childrows == null || childrows.Length == 0)

                        table.LoadDataRow(parentrow.ItemArray,true);   

            }

            table.EndLoadData();

      }

      

      return table;

}

Print | posted on Monday, January 19, 2004 9:44 AM

Feedback

# DataTable and Relational Operators

left by Ashutosh Nilkanth's .NET Blog at 1/19/2004 10:04 PM Gravatar

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by Johnny at 1/30/2004 4:22 AM Gravatar
again, a simple left outer join will do it...

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by DavidM at 1/30/2004 8:15 AM Gravatar
Of course it can be done in TSQL, but its equivalent is a LEFT JOIN and a RESTRICT operation. My defintion of equivalence is a single keyword.

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by MajorRugburn at 8/17/2005 8:22 AM Gravatar
fantastic piece of code! just what i was looking for :)) yes, it can be done in SQL if your code accesses a database. but when you're dealing with XML files loaded into DataSets and you're trying to compare them for row additions/modifications this code is priceless!!!! all i had to do was pass in a string array of my relation fields (the ones i care about in terms of modifications/additions), make some changes in assigning these relations only (not all fields become relations) and viola! i get a table of new and modified records signifying the difference between the two DataSets. thank you so much, you've made my week!

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by David van Leerdam at 12/12/2005 10:20 PM Gravatar
Also, datatable is often used for having data from other sources than SQL. So a c# implementation comes in handy always

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by Fernando Canto Hernandez at 5/26/2006 4:47 AM Gravatar
Gracias... me sacaste de un apuro
y me ayudaste ya que empiezo a estudiar .net

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by Nitin at 6/30/2007 1:25 AM Gravatar
Thanks buddy, I knew i would find such functionality somewhere on some site :)

I am storing a lot of data in datatables, and now i need to do some intersects/minus/join operations.

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by Bill Clinton at 7/6/2007 2:53 AM Gravatar
Excellent information!!

Thanks!!

BTW, did you work with Josh on this???

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by Luca at 8/16/2007 4:17 PM Gravatar
Very interesting but...
i need that the details appair under the selected row and not in a different table.

Is it possible?

Thank you!

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by kekos at 8/24/2007 9:05 AM Gravatar
Thank a lot! Intelegent solution instead of very big datatables looping

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by SAMIR SHAH at 10/26/2007 4:05 PM Gravatar
Excellent...Ammmazing...
Perfect Code To GEt difference between rows of two datatables.
Good...

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by Gonçalo at 11/16/2007 4:06 AM Gravatar
Fantastic! That was just what i was looking for! I pasted the code in my project and it worked rigth away.

Now i am going to understabd it...

Good work!

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by aalegria at 7/18/2008 8:52 AM Gravatar
Excellent Code...
but.. fail with more 32 columns
display message error in line
//Create DataRelation
DataRelation r = new DataRelation(string.Empty,firstcolumns,secondcolumns,false);

any idea ?

thanks

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by Matt at 9/10/2008 9:19 PM Gravatar
Thanks, Well written, clean code that does exactly what I needed for working with spreadsheet data.

I love cut-and-paste coding ;-)

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by SSL: Sunny at 9/23/2008 5:27 AM Gravatar
public DataTable GetDifferences(DataTable dtA, DataTable dtB)
{
dtA.Merge(dtB);
return dtA.GetChanges();
}

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by Pavan Kumar P at 9/29/2008 8:30 PM Gravatar
thanks dude.. its works for me.

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by Codemate at 1/25/2009 6:33 PM Gravatar
Great piece of code!! helped me a lot. It would be great if anyone can tell me how to color the discrepant columns.

Thanks !!

# re: DataTable Relational Operators in C# - DIFFERENCE Method

left by karthikeyan at 2/17/2009 8:25 PM Gravatar
hi,

i went through this code. i am facing some problem in datatable operation.

i like to do the intersect operation between two datatables.now currently i am using combination like AB, BC, CA etc.,

i travel the each row and check with IF() condition. then add the common think into new datatable.

so my program efficiency will become slow.
so any direct method presents for intersect between two tables.
Comments have been closed on this topic.