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