byrmol Blog

Garbage

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;

}

Legacy Comments


Johnny
2004-01-30
re: DataTable Relational Operators in C# - DIFFERENCE Method
again, a simple left outer join will do it...

DavidM
2004-01-30
re: DataTable Relational Operators in C# - DIFFERENCE Method
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.

MajorRugburn
2005-08-17
re: DataTable Relational Operators in C# - DIFFERENCE Method
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!

David van Leerdam
2005-12-12
re: DataTable Relational Operators in C# - DIFFERENCE Method
Also, datatable is often used for having data from other sources than SQL. So a c# implementation comes in handy always

Fernando Canto Hernandez
2006-05-26
re: DataTable Relational Operators in C# - DIFFERENCE Method
Gracias... me sacaste de un apuro
y me ayudaste ya que empiezo a estudiar .net

Nitin
2007-06-30
re: DataTable Relational Operators in C# - DIFFERENCE Method
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.


Bill Clinton
2007-07-06
re: DataTable Relational Operators in C# - DIFFERENCE Method
Excellent information!!

Thanks!!

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

Luca
2007-08-16
re: DataTable Relational Operators in C# - DIFFERENCE Method
Very interesting but...
i need that the details appair under the selected row and not in a different table.

Is it possible?

Thank you!

kekos
2007-08-24
re: DataTable Relational Operators in C# - DIFFERENCE Method
Thank a lot! Intelegent solution instead of very big datatables looping

SAMIR SHAH
2007-10-26
re: DataTable Relational Operators in C# - DIFFERENCE Method
Excellent...Ammmazing...
Perfect Code To GEt difference between rows of two datatables.
Good...

Gonçalo
2007-11-16
re: DataTable Relational Operators in C# - DIFFERENCE Method
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!

aalegria
2008-07-18
re: DataTable Relational Operators in C# - DIFFERENCE Method
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

Matt
2008-09-10
re: DataTable Relational Operators in C# - DIFFERENCE Method
Thanks, Well written, clean code that does exactly what I needed for working with spreadsheet data.

I love cut-and-paste coding ;-)

SSL: Sunny
2008-09-23
re: DataTable Relational Operators in C# - DIFFERENCE Method
public DataTable GetDifferences(DataTable dtA, DataTable dtB)
{
dtA.Merge(dtB);
return dtA.GetChanges();
}

Pavan Kumar P
2008-09-29
re: DataTable Relational Operators in C# - DIFFERENCE Method
thanks dude.. its works for me.

Codemate
2009-01-25
re: DataTable Relational Operators in C# - DIFFERENCE Method
Great piece of code!! helped me a lot. It would be great if anyone can tell me how to color the discrepant columns.

Thanks !!

karthikeyan
2009-02-17
re: DataTable Relational Operators in C# - DIFFERENCE Method
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.