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