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. |