As an aside: Strings are often not strings

In my travels with ISVs I get to see a lot of code. A lot of code solving similar issues in similar ways. One such challenge is the dynamic construction of SQL. In all these examples the same approach was taken – build a string by appending many other strings to an initial string to make a bigger string which contains the SQL command. This is both easy and hard. Easy to get initially working but hard to develop once you need to generate complex SQL as you need to carefully get the SELECT, WHERE, ORDE BY, GROUP BY parts constructed in the right order and you need to carefully ensure that everything you do leads to a valid SQL command. Lots of nods of heads…

What is the alternative? There are several – but how about moving away from thinking about strings and start thinking about objects. SQL can be represented at run time as an object, therefore we should design a class. A class that has methods that let you set the WHERE, ORDER BY etc in any order. A class that can confirm that it represents valid SQL. A class that can be expanded in the future to generate SQL for different databases.

Obvious isn’t it? Very obvious to folks with a strong O-O background – but in many of the ISVs I meet with, their background is Visual Basic development and procedural development. They have moved to .NET and powerful O-O languages but they continue to develop the “old way”. The old way often surfaces itself as “everything is a string”. I see strings used everywhere. For SQL as above, for addresses, for telephone numbers, for postcodes, for National Insurance numbers. Yet all are great candidates to be represented as objects. Now I have got that of my chest – back to the day job 🙂

P.S. Anyone point me at a great SQL Class for doing the above?

Comments (2)

  1. Luke Foust says:

    As a developer who has used both string and objects to represent fairly complex sql statements, I have found that most of the time the string versions are much more maintainable. Maybe I have yet to see an OO Sql library that is well formed. I think the problem is that you have to deal with such a mismatch between languages (OO vs Sql) that any solution ends up being very awkward. Here is an example of an open source Sql Library I have tried:

    but in the end, I still find it a little too awkard and unreadable.

    ANSWER ERIC: Thanks Luke for sharing that. Useful stuff. After I posted this I reminded myself of LINQ – and made a note to myself to see how this might help.

  2. We are currently researching NHibernate to simplify up to 90% of the SQL Query that happens in one of our clients enterprise framework.  There are great OO framework out there that will generate the SQL Query using your domain model.  They’re worth taking a look.  I am currently considering NHibernate over LINQ.

    On a side note, not all the VB developers are Procedural junkies.  In the same wave, not all C# developer are great OO developers. 🙂  Ok, enough for the old battle.