byrmol Blog


A gift from Alistair

After my series on implementing relational operators on.NET DataTables, I received several requests to post the whole code library.

I was reluctant to do it for 2 reasons
1. The code is already there. <SCREAMING MAN> Just copy and paste it you lazy bastards!</SCREAMING MAN>
2. The initial library was designed for a specific application with many specialised routines.

In the haze of comments received, only one poster choose to donate time and effort to refactor the SQLOps routines into a convenient (and IMO, elegant) set of classes.

Alistair Bayley’s implementation makes significant improvements in functionality and usage.

Here is a short summary from Alistair:

- subclassed DataTable and added the static methods as instance/object methods. This allows nice infix usage of operators, instead of the prefix-style that static methods force.

- added various API overloadings, and a few changes (I think Project is a bit different; Divide too).

- made some of the suggested improvements to GroupBy. I used an additional class "GroupByExpr" to avoid the problem where the array arguments might have different lengths. Also added type inference for
the aggregate expressions.

- added a suite of Nunit tests. These are not complete or exhaustive; they largely test the stuff I've been fiddling with.

- fixed a bug (depending on how you look at it) with Project, where the ordering of the result-set columns was not the same as the specified columns. I needed to be able to re-order columns for output purposes.
This not really in the Relational spirit (where ordering doesn't normally matter), but still useful.

- added a more general Divide.

- added a left-outer-join (OuterJoin), which I needed. Haven't bothered with right- or full- yet.

- added NaturalJoin.

- added Extend (append a literal column).

- added Sort (obvious).

- added Rename, but note that this is different from the other operators, in that it modifies the argument i.e. it's destructive. It seemed overkill to copy the table just to rename a column... I don't like this, because it doesn't return a new table like all of the other operators, and so it destroys the nice closure property.

As a small gesture, I have added operator overloading into the class as well.

The file (zipped VS2003 IDE compatible solution (C#) – 20Kb) can be found at SQLTeam here thanks to graz..

Thankyou Alistair!

Legacy Comments

re: A gift from Alistair
>> added a left-outer-join (OuterJoin), which I needed. Haven't bothered with right- or full- yet.

I think you know my take on right- and full-outer joins; only implementing a left outer join is *perfect*. Tell him not to change a thing!

re: A gift from Alistair
Thank you David & Alistair !

Tecky Lee
re: A gift from Alistair
thank you very much!

re: A gift from Alistair
Heck yeah this is exactly what I neeedd.