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?