DavidM

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