/// <summary>
/// Essential the DIVIDE operator is the inverse of Product
/// </summary>
/// <param name="DEND"></param>
/// <param name="DOR"></param>
/// <param name="BY"></param>
/// <returns></returns>
public static DataTable Divide(DataTable DEND, DataTable DOR, DataColumn BY)
{
//First Create Distinct DEND table projected over BY column
DataTable distinct = SQLOps.Distinct(DEND, BY);
//Product of distinct and DOR
DataTable product = SQLOps.Product(distinct, DOR);
//Difference of product and DEND
DataTable difference = SQLOps.Difference(product, DEND);
//Project over BY column
difference = SQLOps.Project(difference, new DataColumn[] { difference.Columns[BY.ColumnName] });
//Difference of distinct AND difference
DataTable table = SQLOps.Difference(distinct, difference);
table.TableName = "Divide";
return(table);
}
/// <summary>
/// INTERSECT is simply all rows that are in the First table and the Second table
/// </summary>
/// <param name="First"></param>
/// <param name="Second"></param>
/// <returns></returns>
/// <remarks>
/// In summary the code works as follows:<br>
/// Get a reference to all columns<br>
/// Join on all columns<br>
/// Return table<br>
/// </remarks>
public static DataTable Intersect(DataTable First, DataTable Second)
{
//Get reference to Columns in First
DataColumn[] firstcolumns = new DataColumn[First.Columns.Count];
for (int i = 0; i < firstcolumns.Length; i++)
{
firstcolumns[i] = First.Columns[i];
}
//Get reference to Columns in Second
DataColumn[] secondcolumns = new DataColumn[Second.Columns.Count];
for (int i = 0; i < secondcolumns.Length; i++)
{
secondcolumns[i] = Second.Columns[i];
}
//JOIN ON all columns
DataTable table = SQLOps.Join(First, Second, firstcolumns, secondcolumns);
table.TableName = "Intersect";
return(table);
}
/// <summary>
/// Removes the equal rows
/// </summary>
/// <param name="Table"></param>
/// <param name="Columns"></param>
/// <returns></returns>
public static DataTable Distinct(DataTable Table, DataColumn[] Columns)
{
//Empty table
DataTable table = new DataTable("Distinct");
//Sort variable
string sort = string.Empty;
//Add Columns & Build Sort expression
for (int i = 0; i < Columns.Length; i++)
{
table.Columns.Add(Columns[i].ColumnName, Columns[i].DataType);
sort += Columns[i].ColumnName + ",";
}
//Select all rows and sort
DataRow[] sortedrows = Table.Select(string.Empty, sort.Substring(0, sort.Length - 1));
object[] currentrow = null;
object[] previousrow = null;
table.BeginLoadData();
foreach (DataRow row in sortedrows)
{
//Current row
currentrow = new object[Columns.Length];
for (int i = 0; i < Columns.Length; i++)
{
currentrow[i] = row[Columns[i].ColumnName];
}
//Match Current row to previous row
if (!SQLOps.RowEqual(previousrow, currentrow))
{
table.LoadDataRow(currentrow, true);
}
//Previous row
previousrow = new object[Columns.Length];
for (int i = 0; i < Columns.Length; i++)
{
previousrow[i] = row[Columns[i].ColumnName];
}
}
table.EndLoadData();
return(table);
}
/// <summary>
/// Join operator
/// </summary>
/// <param name="First"></param>
/// <param name="Second"></param>
/// <param name="FJC"></param>
/// <param name="SJC"></param>
/// <returns></returns>
public static DataTable Join(DataTable First, DataTable Second, string FJC, string SJC)
{
return(SQLOps.Join(First, Second, new DataColumn[] { First.Columns[FJC] }, new DataColumn[] { Second.Columns[SJC] }));
}
/// <summary>
/// Join operator
/// </summary>
/// <param name="First"></param>
/// <param name="Second"></param>
/// <param name="FJC"></param>
/// <param name="SJC"></param>
/// <returns></returns>
public static DataTable Join(DataTable First, DataTable Second, DataColumn FJC, DataColumn SJC)
{
return(SQLOps.Join(First, Second, new DataColumn[] { FJC }, new DataColumn[] { SJC }));
}