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");