DataTable Relational Operators in C# - PRODUCT Method

The Product Method is the equivalent of the CROSS JOIN expression in TSQL.
Because a DataTable does not allow anonymous or duplicate column names, we must rename the Second tables columns if they already exist in the First table.

Conflicting Columns have "_Second" appended to them.
eg CustomerID becomes CustomerID_Second

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

  Create new empty table
  Add columns from First table to empty table.
  Add columns from Secondtable to empty table. Rename if necessary
  Loop through First table and for each row loop through Second table and add rows via array manipulation.
  Return Table.

public static DataTable Product(DataTable First, DataTable Second)

{

      DataTable table = new DataTable("Product");

      //Add Columns from First

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

      {

            table.Columns.Add(new DataColumn(First.Columns[i].ColumnName,First.Columns[i].DataType));

      }

                 

      //Add Columns from Second

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

      {

            //Beware Duplicates

            if(!table.Columns.Contains(Second.Columns[i].ColumnName))

                  table.Columns.Add(new DataColumn(Second.Columns[i].ColumnName,Second.Columns[i].DataType));

            else

                  table.Columns.Add(new DataColumn(Second.Columns[i].ColumnName + "_Second",Second.Columns[i].DataType));

      }

 

      table.BeginLoadData();       

      foreach(DataRow parentrow in First.Rows)

      {

            object[] firstarray = parentrow.ItemArray;

            foreach(DataRow childrow in Second.Rows)

            {

                  object[] secondarray = childrow.ItemArray;

                  object[] productarray = new object[firstarray.Length+secondarray.Length];

                  Array.Copy(firstarray,0,productarray,0,firstarray.Length) ;

                  Array.Copy(secondarray,0,productarray,firstarray.Length,secondarray.Length) ;

 

                  table.LoadDataRow(productarray,true);

            }

      }

      table.EndLoadData();

     

      return table;

}

Print | posted on Friday, January 16, 2004 9:41 AM

Feedback

# DataTable and Relational Operators

left by Ashutosh Nilkanth's .NET Blog at 1/17/2004 6:13 PM Gravatar

# re: DataTable Relational Operators in C# - PRODUCT Method

left by Ron Green at 1/18/2004 3:08 AM Gravatar
Your RSS is invalid. Open it in the browser and scroll all the way to the bottom.

# re: DataTable Relational Operators in C# - PRODUCT Method

left by DavidM at 1/19/2004 3:21 PM Gravatar
RSS should be valid now....

# DataTable and Relational Operators

left by Ashutosh Nilkanth's .NET Blog at 1/19/2004 10:04 PM Gravatar
Comments have been closed on this topic.