Is there a standard approach to generating sql dynamically?

We created our own FilterCriteria object that is kind of a black-box dynamic query builder. It has collection properties for SelectClause, WhereClause, GroupByClause and OrderByClause. It also contains a properties for CommandText, CommandType, and MaximumRecords.

We then jut pass our FilterCriteria object to our data logic and it executes it against the database server and passes parameter values to a stored procedure that executes the dynamic code.

Works well for us ... and keeps the SQL generation nicely contained in an object.

I had the need to do this on one of my recent projects. Here is the scheme that I am using for generating the SQL:

• Each component of the query is represented by an Object (which in my case is a Linq-to-Sql entity that maps to a table in the DB). So I have the following classes: Query, SelectColumn, Join, WhereCondition, Sort, GroupBy. Each of these classes contains all details relating to that component of the query.
• The last five classes are all related to a Query object. So the Query object itself has collections of each class.
• Each class has a method that can generate the SQL for the part of the query that it represents. So creating the overall query ends up calling Query.GenerateQuery() which in turn enumerates through all of the sub-collections and calls their respective GenerateQuery() methods

It is still a bit complicated, but in the end you know where the SQL generation for each individual part of the query originates (and I don't think that there are any big switch statements). And don't forget to use StringBuilder.

You could try the approach used by code generation tools like CodeSmith. Create a SQL template with placeholders. At runtime, read the template into a string and substitute the placeholders with actual values. This is only useful if all the SQL code follow a pattern.