DataTable Relational Operators in C# - Overview & the Project Method

All the methods that follow are static and the class name will be called SQLOps (SQL Operations). This is important when we start to implement the non-primitive relational operators and overloaded methods

I will use column/table instead of attribute/relation simply because a DataTable is not a relation.

A particular requirement for these methods is not to "disturb" the original DataSet or DataTable, while maintaining closure (Datatable IN, Datatable OUT).
Therefore, before most operations, a copy of the DataTable is taken before processing.
Luckily there is a DataTable Method called Copy().
This is a deep copy of the DataTable object and not a shallow copy as is performed by the Clone() method.

The first operator we will code is the Project operator.

The Project operator is used to "project" columns from a table.
In TSQL, this equates to explicitly naming the column/s we want in a Select clause.

Our implementation has no renaming ability (The AS keyword in TSQL) but does include an option to exclude the columns versus the default of including the columns.

There are a total of 4 signatures for this method, but it could be extended.
In summary the code works as follows:

 Create a copy of the table
 Prepare array for column names to remove.
 Find Columns to remove and add to array.
 Loop through array and remove from table
 Return Table.

public static DataTable Project(DataTable Table, DataColumn[] Columns, bool Include)

{

      DataTable table = Table.Copy();

      table.TableName = "Project";

      int columns_to_remove = Include ? (Table.Columns.Count - Columns.Length) : Columns.Length ;

      string[] columns = new String[columns_to_remove];

      int z = 0;

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

      {

            string column_name = table.Columns[i].ColumnName;

            bool is_in_list = false;

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

            {

                  if(column_name == Columns[x].ColumnName)

                  {

                        is_in_list = true;

                        break;

                  }                      

            }

            if(is_in_list ^ Include)

                  columns[z++] = column_name;

      }

 

      foreach(string s in columns)

      {

            table.Columns.Remove(s);

      }

 

      return table;

 

}

 

public static DataTable Project(DataTable Table, DataColumn[] Columns)

{

      return Project(Table,Columns,true);

}    

 

public static DataTable Project(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 Project(Table, columns, true);

}

public static DataTable Project(DataTable Table, bool Include, params string[] Columns)

{

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

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

      {

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

      }

      return Project(Table, columns, Include);

}

You are probably asking why we need this operator at all, but you will see that several of the other operators require identical DataTable columns to work. As an example, if we have 2 tables that need to be UNIONed but do not have identical columns, you would first apply the project operator to each/either table before UNIONing.
Print | posted on Wednesday, January 14, 2004 11:42 AM

Feedback

# DataTable and Relational Operators

left by Ashutosh Nilkanth's .NET Blog at 1/20/2004 8:29 PM Gravatar
Comments have been closed on this topic.