C# - Dynamic SQL

30. June 2011 18:00

Over the last number of years I have seen a lot of code which has been used to generate lots of dynamic sql. There has always been one thing in common with the way that I have seen this be done. Typically the approach is to have a string and just directly append everything to the end of the string during a button press or some such from the interface. I have seen cases where the c# code to produce a dynamic query have been over 200 lines. It would typically resemble something along the lines of this.

 

void MyEvent_Click() {
	string sql = string.Empty;
	sql += "ALTER INDEX [" + IndexName + "] "
	sql += "ON [" + TableName + "] " + Action;
}

 

The above is a simple case of making some dynamic sql. It is quite common to have something like above buy using dynamic joins and multiple optional conditions. Other bad things also start to happen where the code is then copied from place to place. It becomes difficult to maintain and almost impossible to test all the posisble combinations of options in the dynamic query. Not the mention that the code becomes more complex when you start to add join's and where clauses at the same time it causes problem because they both need to be added to different locations to the string.

 

Here is an alternative method. Simply come up with a class which sole purpose is to generate the sql for the query. Then the usage in the user interface code becomes significatly cleaner because it is only moving options / choices into the structure that will eventually build the query. This does also work with any other sql statement. The basic principle is to store all the required information in non sql form until the sql is actually required to be executed. This has a number of benifit's. It will also allow you to modified any part of the sql statement at any time in your code.

 

Here is a simple example that I pulled out of the index rebuild tool I made.

 

public class IndexWorkItem
{
    public string TableName = null;
    public string IndexName = null;
    public IndexWorkItemAction Action = IndexWorkItemAction.None;

    public SqlCommand ToSqlCommand()
    {
        if (Action == IndexWorkItemAction.None)
            return;

        SqlCommand sql = new SqlCommand(string.Format(@"
            ALTER INDEX [{1}] ON [{0}] {2}
        ", TableName, IndexName, ActionToSQL(Action)));

        return sql;
    }

    private string ActionToSQL(IndexWorkItemAction Action)
    {
        switch (Action)
        {
            case IndexWorkItemAction.Rebuild:
                return "REBUILD";
            case IndexWorkItemAction.Reorganize:
                return "REORGANIZE";
            default:
                throw (new NotImplementedException());
        }
    }
}

public enum IndexWorkItemAction
{
    None = 0,
    Reorganize = 1,
    Rebuild = 2
}

 

The main benifits of doing this are quiet clear.

 

  1. Easy debugging. You can seperate the dynamic query away from the interface code.
  2. Easy to test. Unit tests can be created to test almost every possible combination of the query.
  3. Testing has a major advantage because you can ensure backwards compatibility from existing code if the class changes.
  4. Cleaner / easy to maintain code.
  5. Possible to support multiple database engine's by adding additional functions. ToSqlCommand / ToMySql / ToOracle / etc..
  6. Easy to expand to support more options.
E-mail Kick it! DZone it! del.icio.us Permalink