Is there a standard approach to generating sql dynamically?

Question

I want to ask how other programmers are producing Dynamic SQL strings for execution as the CommandText of a SQLCommand object.

I am producing parameterized queries containing user-generated WHERE clauses and SELECT fields. Sometimes the queries are complex and I need a lot of control over how the different parts are built.

Currently, I am using many loops and switch statements to produce the necessary SQL code fragments and to create the SQL parameters objects needed. This method is difficult to follow and it makes maintenance a real chore.

Is there a cleaner, more stable way of doing this?

Any Suggestions?

EDIT: To add detail to my previous post:

1. I cannot really template my query due to the requirements. It just changes too much.
2. I have to allow for aggregate functions, like Count(). This has consequences for the Group By/Having clause. It also causes nested SELECT statements. This, in turn, effects the column name used by
3. Some Contact data is stored in an XML column. Users can query this data AS WELL AS and the other relational columns together. Consequences are that xmlcolumns cannot appear in Group By clauses[sql syntax].
4. I am using an efficient paging technique that uses Row_Number() SQL Function. Consequences are that I have to use a Temp table and then get the @@rowcount, before selecting my subset, to avoid a second query.

I will show some code (the horror!) so that you guys have an idea of what I'm dealing with.

sqlCmd.CommandText = "DECLARE @t Table(ContactId int, ROWRANK int" + declare
      + ")INSERT INTO @t(ContactId, ROWRANK" + insertFields + ")"//Insert as few cols a possible
      + "Select ContactID, ROW_NUMBER() OVER (ORDER BY " + sortExpression + " "
      + sortDirection + ") as ROWRANK" // generates a rowrank for each row
      + outerFields
      + " FROM ( SELECT c.id AS ContactID"
      + coreFields
      + from         // sometimes different tables are required 
      + where + ") T " // user input goes here.
      + groupBy+ " "
      + havingClause //can be empty
      + ";"
      + "select @@rowcount as rCount;" // return 2 recordsets, avoids second query
      + " SELECT " + fields + ",field1,field2" // join onto the other cols n the table
      +" FROM @t t INNER JOIN contacts c on t.ContactID = c.id" 
      +" WHERE ROWRANK BETWEEN " + ((pageIndex * pageSize) + 1) + " AND " 
      + ( (pageIndex + 1) * pageSize); // here I select the pages I want

In this example, I am querying XML data. For purely relational data, the query is much more simple. Each of the section variables are StringBuilders. Where clauses are built like so:

// Add Parameter to SQL Command
AddParamToSQLCmd(sqlCmd, "@p" + z.ToString(), SqlDbType.VarChar, 50, ParameterDirection.Input, qc.FieldValue);
// Create SQL code Fragment
where.AppendFormat(" {0} {1} {2} @p{3}", qc.BooleanOperator, qc.FieldName, qc.ComparisonOperator, z);

Answer 1

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.

Answer 2

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.