DataTable Relational Operators in C# - The Northwind Example

The following is an example of the methods we have outlined in the previous posts.

Before we can use the database, we have to a change the data to ensure no NULLs are returned.
Specifically, the ReportsTo column in Employees contains a NULL for "no parent".
We will change it to be the same as the EmployeeID

Personally I think it is a terrible design decision to use a NULL here.
Consider the proposition of each design.

Employee X reports to ..... VS Employee X reports to himself.

So.. here is the TSQL to do that...
UPDATE Employees SET ReportsTo = EmployeeID WHERE ReportsTo IS NULL

When I told the other developers that I was going to build these sets of methods, one of them said "Won't that be hard to do?". When I showed him the completed class, he said "They aren't difficult at all!." And that is the big point I'd like to make. The code is not rocket science. It is a shame on the industry that a lot of so called "developers" couldn't tell you at least 4 of the relational operators. It's like they hear the word "algebra" and panic.

The code I have posted throughout the series is first generation code. There have been improvements made that are now in production (Only a subset of the methods are used in production). The code is running on 2500 thick clients with no inaccuracys or exceptions reported so far. I would do some benchmarking before I considered this code to be server side ready.

For the following to work you must ensure that both the Union and Project methods remove duplicates.

Bugs/Improvements:

  • Tables need to have different names for the Join, Difference and Intersect methods. Before adding the tables to the DataSet, add a check to ensure that they are. Notice in this code I have to rename certain tables.
  • The first loop in the Union method is redundant. We can simply take a copy of the first table and then loop through the second.
  • Add more overloads for the Join method to allow identical join column names to be expressed once.
  • Add a overload for Project that strips out any column with "_Second" postifx.
  • Capitilize method names
After the code is the equivalent TSQL expressions...

Get the entire database into a data set

string conn = "Server=XXXXXX;Database=Northwind;User ID=sa;PWD=sablank";

string sql = "Select * from Categories;";

      sql += "Select * from CustomerCustomerDemo;";

      sql += "Select * from CustomerDemographics;";

      sql += "Select * from Customers;";

      sql += "Select * from Employees;";

      sql += "Select * from EmployeeTerritories;";

      sql += "Select * from [Order Details];";

      sql += "Select * from Orders;";

      sql += "Select * from Products;";

      sql += "Select * from Region;";

      sql += "Select * from Shippers;";

      sql += "Select * from Suppliers;";

      sql += "Select * from Territories;";

 

DataSet ds = new DataSet("SQLOps");

using (SqlCommand DBCommand = new SqlCommand(sql,new SqlConnection(conn)))

{

      using(SqlDataAdapter adapter = new SqlDataAdapter(DBCommand))

      {

            adapter.Fill(ds);

      }

}

 

DataTable emp = ds.Tables[4];

DataTable cust = ds.Tables[3];

DataTable empterr = ds.Tables[5];

DataTable terr = ds.Tables[12];

DataTable regions = ds.Tables[9];

DataTable sup = ds.Tables[11];

DataTable orders = ds.Tables[7];

DataTable products = ds.Tables[8];

DataTable categories = ds.Tables[0];

 

DataTable qry;

 

//Join

qry = SQLOps.Join(emp,empterr,"EmployeeID","EmployeeID");

qry = SQLOps.Join(qry,terr,"TerritoryID","TerritoryID");

qry = SQLOps.Join(qry,regions,"RegionID","RegionID");

qry = SQLOps.Project(qry,"LastName","RegionDescription");

 

//Union

qry = SQLOps.Union(SQLOps.Project(sup,"City"),SQLOps.Project(orders,"ShipCity"));

 

//Intersect

DataTable temp = SQLOps.Project(orders,"ShipCity");

temp.TableName = "temp";

qry = SQLOps.Intersect(temp, SQLOps.Project(sup,"City"));

 

//Difference

temp = SQLOps.Project(orders,"ShipCity");

temp.TableName = "temp";

qry = SQLOps.Difference(temp, SQLOps.Project(sup,"City"));

 

//Divide

DataTable dend = SQLOps.Project(products,"SupplierID", "CategoryID");

DataTable dor = SQLOps.Project(SQLOps.Restrict(categories,"CategoryName IN ('Produce', 'Seafood', 'Condiments')"),"CategoryID");

dend.TableName = "DEND";

dor.TableName = "DOR";

qry = SQLOps.Divide(dend,dor,dend.Columns["SupplierID"]);

 

//Product

qry = SQLOps.Product(sup, products);

 

//TClose

qry = SQLOps.Restrict(SQLOps.TClose(emp,"ReportsTo","EmployeeID"),"EmployeeID = 7");

 

 

Operator

English

SQL

Union

Show all the Cities we ship to or where a supplier is located

SELECT City FROM SUPPLIERS

Union

SELECT ShipCity FROM ORDERS

Join

Find the RegionDescription for Each Employee

SELECT DISTINCT E.LastName,  R.RegionDescription

FROM Employees E

INNER Join EmployeeTerritories ET ON ET.EmployeeID = E.EmployeeID

INNER Join Territories T ON T.TerritoryID = ET.TerritoryID

INNER Join Region R ON T.RegionID = R.RegionID

Divide

Find Suppliers that supply the categories 'Produce', 'Seafood', 'Condiments'

SELECT SupplierID, COUNT(P.CategoryID)

FROM (SELECT DISTINCT SupplierID, CategoryID FROM Products) P

INNER Join Categories C on C.CategoryID = P.CategoryID

WHERE CategoryName IN ('Produce', 'Seafood', 'Condiments')

GROUP BY SupplierID

HAVING COUNT(P.CategoryID)

            = (SELECT COUNT(CategoryID) from Categories WHERE CategoryName IN ('Produce', 'Seafood', 'Condiments'))

Intersect

Show Cities we ship to that also have a supplier located there.

SELECT DISTINCT ShipCity FROM ORDERS

WHERE EXISTS (SELECT 1 from SUPPLIERS WHERE ShipCity = City)

Difference

Show Cities we ship to that do not have a supplier located there.

SELECT DISTINCT ShipCity FROM ORDERS

WHERE NOT EXISTS (SELECT 1 from SUPPLIERS WHERE ShipCity = City)

Product

Show all possible Supplier Product Combinations

SELECT *

FROM Suppliers S CROSS Join Products

TClose

Show the supervisors of EmployeeID 7

CREATE TABLE #Tree(EmployeeID INT, ReportsTo INT, PRIMARY KEY(EmployeeID, ReportsTo))

INSERT #Tree

            SELECT EmployeeID, ReportsTO

            FROM Employees WHERE EmployeeID = 9

WHILE @@ROWCOUNT > 0

BEGIN

            INSERT #Tree

            SELECT T.EmployeeID, E.ReportsTo

            FROM #Tree T

            INNER Join Employees E ON T.ReportsTO = E.EmployeeID

            WHERE NOT EXISTS(SELECT 1 FROM #Tree WHERE EmployeeID = T.EmployeeID AND ReportsTO = E.ReportsTO)

END

SELECT * FROM #Tree

DROP TABLE #Tree

Print | posted on Monday, February 02, 2004 12:02 PM

Feedback

# re: DataTable Relational Operators in C# - The Northwind Example

left by Ariel Popovsky at 2/3/2004 2:52 AM Gravatar
Are you planning to post the SQLOps lib somewhere? It would be great tool!

# Signal-to-Noise #9

left by Ashutosh Nilkanth's .NET Blog at 2/3/2004 9:28 AM Gravatar

# Signal-to-Noise #9

left by Ashutosh Nilkanth's .NET Blog at 2/3/2004 9:29 AM Gravatar

# re: DataTable Relational Operators in C# - The Northwind Example

left by Chris at 2/7/2004 12:43 AM Gravatar
"Personally I think it is a terrible design decision to use a NULL here"

??? Why would you that be a bad idea ???

using NULL as the parent is the only right decision. Why would anyone without supervision report to himself???

# re: DataTable Relational Operators in C# - The Northwind Example

left by DavidM at 2/9/2004 8:18 AM Gravatar
Is this a troll Chris?

# re: DataTable Relational Operators in C# - The Northwind Example

left by james at 4/8/2004 3:09 AM Gravatar
Can we download this SQLOps library somewhere?

# re: DataTable Relational Operators in C# - The Northwind Example

left by Roger H at 6/17/2004 7:48 PM Gravatar
Using this, I note that I cannot use a table's field name directly...

qry = SQLOps.Intersect(temp, SQLOps.Project(sup,"City"));

would need to be
qry = SQLOps.Intersect(temp, SQLOps.Project(sup,sup.Columns["City"]));

At least to work in my context.

Thanks!

# re: DataTable Relational Operators in C# - The Northwind Example

left by p.gopinath at 6/16/2005 11:31 PM Gravatar
Good

# re: DataTable Relational Operators in C# - The Northwind Example

left by hamid at 3/21/2006 10:26 PM Gravatar
bbhjb

# re: DataTable Relational Operators in C# - The Northwind Example

left by Quan Dang Dinh at 9/22/2006 12:54 PM Gravatar
oh, greate code.

Thank you so much!

# re: DataTable Relational Operators in C# - The Northwind Example

left by PAPADOPOULOU at 2/20/2007 8:05 PM Gravatar
I FING SQLOPS LIB VERY USEFUL. CAN I DOWNLOAD IT FROM SOMEWHERE?

# re: DataTable Relational Operators in C# - The Northwind Example

left by masta p at 3/14/2007 11:53 AM Gravatar
Check out the 10 posts from the January 2004 archives - all the source code is there.

# re: DataTable Relational Operators in C# - The Northwind Example

left by kumaresamoorthy at 3/22/2007 4:26 PM Gravatar
i want to join to datatavle in c#
Comments have been closed on this topic.