Passing NULL and table values to a parametrized SQL query

Introduction

This tip demonstrates how to create a universal method to pass null and table values to dynamic, but parametrized SQL queries, without a need to create any special types on the server side, or relying on pattern matching for IN clauses. While it returns a DataSet, it can be adopted to return ResultSet<T> or a value type, which exercise is left to the reader.

Background

The importance of using parametrized SQL queries, has been discussed many times, and it is well established that it helps in increasing application security and performance. Security, because it prevents SQL injection attacks. Performance, because SQL will automatically cache execution plans created. It is recommended that the reader will investigate these two subjects at their own pace.

Using the code

Below you will find an example of a method expecting an SqlConnectionStringBuilder, an SQL statement as a string, and a variable length array of parameters to be passed as arguments to the SQL. The method expects the parameters to be "indexed", meaning that the position of the value in params array, dictates the parameter name, for example, @0 for first item in the array, @1 for second, etc. It would be even easier to enforce IDictionary<TKey, TValue> instead of object[], but, quite a few times this solution comes quite handy, if the number of parameters is unknown, but their order is given.

private static DataSet GetDataSet(
   SqlConnectionStringBuilder scsb, string strSql, params object[] pars)
{
    var ds = new DataSet();
    using (var sqlConn = new SqlConnection(scsb.ConnectionString))
    {
        var sqlParameters = new List<SqlParameter>();
        var replacementStrings = new Dictionary<string, string>();
        if (pars != null)
        {
            for (int i = 0; i < pars.Length; i++)
            {
                if (pars[i] is IEnumerable<object>)
                {
                    List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();
                    replacementStrings.Add("@" + i, String.Join(",", 
                      enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));
                    sqlParameters.AddRange(enumerable.Select((value, pos) => 
                      new SqlParameter(String.Format("@_{0}_{1}", i, pos), 
                      value ?? DBNull.Value)).ToArray());
                }
                else
                {
                    sqlParameters.Add(new SqlParameter(
                       String.Format("@{0}", i), pars[i] ?? DBNull.Value));
                }
            }
        }
        strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => 
          current.Replace(replacementString.Key, replacementString.Value));
        using (var sqlCommand = new SqlCommand(strSql, sqlConn))
        {
            if (pars != null)
            {
                sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
            }
            else
            {
                //Fail-safe, just in case a user intends to pass a single null parameter
                sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
            }
            using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
            {
                sqlDataAdapter.Fill(ds);
            }
        }
    }
    return ds;
}

The method also implements a fail-safe mechanism in scenarios when there is a single parameter and its value is NULL. It accepts any combination of values, table values and null values, without a requirement to create any special types, functions, or dynamic joins. Error handling is not in scope for this solution.

Sample use:

var scsb = new SqlConnectionStringBuilder { DataSource = "localhost", IntegratedSecurity = true };
string sqlStr = "SELECT * FROM LOGTABLE WHERE LEVEL IN (@0) AND APP = @1";
DataSet result = GetDataSet(scsb, sqlStr, new List<string> { "ERROR", "WARN" }, "PROD"); 

 Enjoy.

History  

• Initial version posted on 6/4/2013.