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 parameterised 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.

1. 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 affects the column name used by
2. 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].
3. 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 Eg. I would be 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

Out of curiousity, have you considered using an ORM for managing your data access. A lot of the functionality you're trying to implement could already be there. It may be something to look at because its best not to re-invent the wheel.

Answer 2

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.

Answer 3

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 4

Gulzar and Ryan Lanciaux make good points in mentioning CodeSmith and ORM. Either of those might reduce or eliminate your current burden when it comes to generating dynamic SQL. Your current approach of using parameterized SQL is wise, simply because it protects well against SQL injection attacks.

Without an actual code sample to comment on, it's difficult to provide an informed alternative to the loops and switch statements you're currently using. But since you mention that you're setting a CommandText property, I would recommend the use of string.Format in your implementation (if you aren't already using it). I think it may make your code easier to restructure, and therefore improve readability and understanding.

Answer 5

Usually it's something like this:

string query= "SELECT {0} FROM .... WHERE {1}"
StringBuilder selectclause = new StringBuilder();
StringBuilder wherecaluse = new StringBuilder();

// .... the logic here will vary greatly depending on what your system looks like

MySqlcommand.CommandText = String.Format(query, selectclause.ToString(), whereclause.ToString());

I'm also just getting started out with ORMs. You might want to take a look at one of those. ActiveRecord / Hibernate are some good keywords to google.

Answer 6

ORMs have already solved the problem of dynamic SQL generation (I prefer NHibernate/ActiveRecord). Using these tools you can create a query with an unknown number of conditions by looping across user input and generating an array of Expression objects. Then execute the built-in query methods with that custom expression set.

List<Expression> expressions = new List<Expression>(userConditions.Count);
foreach(Condition c in userConditions)
{
    expressions.Add(Expression.Eq(c.Field, c.Value));
}
SomeTable[] records = SomeTable.Find(expressions);

There are more 'Expression' options: non-equality, greater/less than, null/not-null, etc. The 'Condition' type I just made up, you can probably stuff your user input into a useful class.

Answer 7

If you really need to do this from code, then an ORM is probably the way to go to try to keep it clean.

But I'd like to offer an alternative that works well and could avoid the performance problems that accompany dynamic queries, due to changing SQL that requires new query plans to be created, with different demands on indexes.

Create a stored procedure that accepts all possible parameters, and then use something like this in the where clause:

where...
and (@MyParam5 is null or @MyParam5 = Col5)

then, from code, it's much simpler to set the parameter value to DBNull.Value when it is not applicable, rather than changing the SQL string you generate.

Your DBAs will be much happier with you, because they will have one place to go for query tuning, the SQL will be easy to read, and they won't have to dig through profiler traces to find the many different queries being generated by your code.

Answer 8

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.