DavidM

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 the code that would have already been there if the IMDBMS project went ahead.

Lately I have been at the sharp end of the stick when in comes to manipulating DataSet and DataTable data.
Our data sources are DB2 on the mainframe and SQL Server. Data is delivered to us via a web service call.

Because business changes, sometimes the middle tier cannot be updated in time to consolidate these 2 data sources,
So it is left to us to manipulate 2 different data sources on the client to reflect these business changes.

Very simple cases can be handled by the in built functionality of the DataTable.
The SELECT method in a DataTable is basically the WHERE equivalent in TSQL.
But beyond simple restriction, the effort needed to perform any of the other relational operators is a bothersome task.

In our case, we needed JOIN, INTERSECT, MINUS (Difference) and DIVIDE.
A search on the net found that MS had already written a "helper" class that handles the JOIN operator MS Site

Personally I didn't like the code so I decided to re-write the JOIN method as well as implement the other operators.
I have left out RESTRICT as it is already implemented as the SELECT method of the DataTable.

So that left us with: JOIN, UNION, INTERSECT, DIFFERENCE, PRODUCT, DIVIDE, PROJECT.

Each of the methods have various overloaded signatures and could easily be extended to make life a little bit easier.
I am hoping to dedicate a post for each of these operators if enough people are interested. Leave a comment if your are.