This INTERSECT method has no equivalent in TSQL

INTERSECT is simply all rows that are in the First table and the Second table
We can take a coding short cut and simple use our JOIN method.

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

  Get a reference to all columns
  Join on all columns
  Return table

I have included the DISTINCT method as a helper method to assist us in the DIVIDE method that will follow in a later post.
But in retrospect, it should have been one of the first methods I wrote for this class.

If you have been paying attention, you will notice that the PROJECT and UNION methods will return duplicates.
In my opinion the most serious was the UNION flaw as by default even SQL Server does not produce duplicates from this method.
ie:
  In SQL Server A UNION A = A but our methods produces A UNION A = A+A

Even though I warned you of duplicates I still feel bad about it! Sorry!

The PROJECT method was equivalent to the SQL Server implementation but perhaps it shouldn't have been.

Obviously it is trivial to change these methods to confirm now that the DISTINCT methods exist and I leave it up to you to do that.

My current version of these methods now has an overload that takes a boolean indicating wether to retain duplicates.
By default they do not! Redemption!

The DISTINCT method uses another method (RowEqual) for row equality. Even though only the one method uses this function, to incorporate it directly into the Distinct method would be a slight performance hit.

There are 5 signature for the DISTINCT method.
In summary the code works as follows:

  Create new table
  Add Distinct columns and prepare sort expression
  Select all sorted rows
  Loop over rows and check against previous row
  Add only unique rows
  Return table

If anybody can come up with a faster solution for Distinct it would be most appreciated.

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;

}

private static bool RowEqual(object[] Values, object[] OtherValues)

{

      if(Values == null)

            return false;

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

      {

            if(!Values[i].Equals(OtherValues[i]))

                  return false;

      }                      

      return true;

} 

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;

}

public static DataTable Distinct(DataTable Table, DataColumn Column)

{

      return Distinct(Table, new DataColumn[]{Column});

}

public static DataTable Distinct(DataTable Table, string Column)

{

      return Distinct(Table, Table.Columns[Column]);

}

public static DataTable Distinct(DataTable Table, params string[] Columns)

{

      DataColumn[] columns = new DataColumn[Columns.Length];

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

      {

            columns[i] = Table.Columns[Columns[i]];

      }

      return Distinct(Table, columns);

}

public static DataTable Distinct(DataTable Table)

{

      DataColumn[] columns = new DataColumn[Table.Columns.Count];

      for(int i = 0; i < Table.Columns.Count; i++)

      {

            columns[i] = Table.Columns[i];

      }

      return Distinct(Table, columns);

}