Alex Rivera | Logout

Is there a standard approach to generating sql dynamically?

Asked 2008-09-09T13:08:30.730
12

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         // s
.net sql
Edit
Report

2 Answers

1

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.

answered 2008-09-09T15:04:26.847
0

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.

answered 2008-09-09T13:13:06.507

Your Answer