Visiting SQL - Part 2
In part 1 I explained how I got a "typed model" of a database schema for building SQL in code. By itself I found this useful, but I still hated the string concatenation involved (not to speak of the scattering of all this string manipulation). So I came up with the idea to build a hand-made object model for all SQL query related objects. This model would allow me to build SQL statements in a typed and database-agnostic fashion, integrating nicely with my typed database schema model. To give you a rough idea I included some schematics:
- The core interfaces -
- The core classes (and some derived ones to give you an idea) - 
The main interfaces of interest are ISqlSerializable (a.k.a. IVisitable with the accept method replaced with the more appropriate WriteSql) and ISqlWriter (a.k.a. the Visitor). Basically, I've taken the visitor pattern and applied it to writing SQL, allowing for extensibility (for each of the SQL dialects out there) and refraining developers (in theory) from polluting the code with string concatenated SQL. The not so apparent added value is the amount of reuse one can achieve with this type of model (from caching to creating dynamic views).
The only drawback (maybe there are more, but this is the major one) to using an object model is that your SQL becomes very much unreadable as complexity grows. Then again, this may be a sign that you're not doing enough in your domain model or (e.g. for performance, security reasons) it might be wise to push this logic into a stored procedure. But even this can be solved, by simply modelling each query statement as a class, which can be easily serialized into SQL to see if you get the desired SQL statement (the truly brave might even parse a SQL string and reverse engineer it into the object model).
I think there's enough food for thought in here. Sorry if I haven't gone into too much detail, but a high-level overview is what I had in mind. Feel free to inquire for more.