January 2004 Blog Posts

  • DataTable Relational Operators in C# - RESTRICT Method (WHERE)

    In the Introduction, I pointed out that the DataTable has a method which is similar to the WHERE clause in TSQL. This method (Select) returns an array of DataRow's that meet the criteria of your expression. To bring this method in line with our other operators in returning a DataTable, a simple helper method is used. public static DataTable RowsToTable(DataRow[] Rows) {       DataTable table = new DataTable("Converted");         foreach(DataColumn column in Rows[0].Table.Columns)       {             table.Columns.Add(new DataColumn(column.ColumnName,column.DataType));       }             table.BeginLoadData();       foreach(DataRow row in Rows)       {             table.LoadDataRow(row.ItemArray,true);       }       table.EndLoadData();         return table; }   public static DataTable Restrict(DataTable Table, string WhereClause) {       return SQLOps.RowsToTable(Table.Select(WhereClause)); } If you have stuck with this series of posts, I have one more post to...

  • DataTable Relational Operators in C# - TCLOSE Method (Recursion)

    This TCLOSE method has no equivalent in TSQL TCLOSE stands for Transitive Closure and is used to solve hierarchy representations, most notably the part explosion problem. The algorithm used here is the Semi naive method as outlined in "An Introduction to Database Systems 7th Edition." As a side note.. this book should be a must have for every DBA on the planet and is currently in its 8th Edition. To support DBDebunk purchase it via the link found here.. Books If you use NULL to represent the root node then you will receive incorrect results. This is simple to overcome by making the NULL value (Parent)...

  • DataTable Relational Operators in C# - Divide Method

    This DIVIDE method has no equivalent in TSQL. Essential the DIVIDE operator is the inverse of Product (or CROSS JOIN in SQL Server speak). ie: A PRODUCT B DIVIDE B = A In this method, instead of doing manual calculations, I decided to leverage existing methods using the following formulae. DIVIDE = PROJECT(A,Column) DIFFERENCE ((PROJECT(A,Column) PRODUCT B) DIFFERENCE A)[Column] There is only the one signature for this method. This version uses the DISTINCT method instead of PROJECT due to reasons outlined in the previous post DISTINCT & apology The inline comments are self explanatory and follow the above formulae... public static DataTable Divide(DataTable DEND, DataTable DOR, DataColumn BY) {       //First...

  • DataTable Relational Operators in C# - INTERSECT Method, DISTINCT helper method & an apology

    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...

  • DataTable Relational Operators in C# - JOIN Method

    This JOIN method is equivalent to the TSQL INNER JOIN expression using equality. This method returns all columns from both tables. Once again, column name collision is avoided by appending "_Second" to the columns affected. There are a total of 3 signatures for this method. In summary the code works as follows: Create new empty table Create a DataSet and add tables. Get a reference to Join columns Create a DataRelation Construct JOIN table columns Using the DataRelation add rows with matching related rows using array manipulation Return table //FJC = First Join Column //SJC = Second Join Column public...

  • DataTable Relational Operators in C# - DIFFERENCE Method

    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...

  • 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...

  • DataTable Relational Operators in C# - UNION Method

    The implementation of the UNION operator is equivalent to the TSQL expresion UNION ALL ***WARNING****This means that duplicates can appear The First table is used to construct the new DataTables columns, so in effect, the columns in the Second table only need to have the same Data Types and not both Name and Data Type. There is only the one signature for this method. In summary the code works as follows:   Create new empty table   Add columns to empty table.    Loop through First table and add rows    Loop through Second table and add rows    Return Table. public static DataTable Union (DataTable First, DataTable Second) {       //Result table       DataTable table =...

  • 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...

  • DataTable Relational Operators in C# - Introduction

    First off please excuse the title. A DataTable is to a relation as a dog is to a cat. For those not .NET aware, a DataTable is the fundamental result set returned from the ADO.NET providers. A DataSet is a collection of DataTables with a few features chucked in to simulate a In-Memory DBMS (IMDBMS). As a side note, I was very excited when I heard that MS was building a IMDBMS. But my hopes where shattered when I heard that they had dropped it to concentrate on the DataSet implementation. I blame XML for this decision and for making me write...