将 NULL 和表值传递给参数化 SQL 查询

引言

本技巧演示了如何创建一个通用的方法，将 null 和表值传递给动态但参数化的 SQL 查询，而无需在服务器端创建任何特殊类型，或依赖于 IN 子句的模式匹配。虽然它返回一个 DataSet，但可以采用它来返回 ResultSet<T> 或值类型，这部分练习留给读者。

背景

使用参数化 SQL 查询的重要性已被多次讨论，并且已经确定它有助于提高应用程序的安全性和性能。安全性，因为它防止了 SQL 注入攻击。性能，因为 SQL 会自动缓存创建的执行计划。建议读者自行研究这两个主题。

使用代码

下面您将找到一个方法的示例，该方法期望一个 SqlConnectionStringBuilder、一个 SQL 语句作为字符串，以及要作为参数传递给 SQL 的可变长度数组。该方法期望参数“索引化”，这意味着 params 数组中值的索引决定了参数名称，例如，数组中的第一个项目为 @0，第二个项目为 @1，依此类推。强制使用 IDictionary<TKey, TValue> 代替 object[] 会更容易，但是，在参数数量未知但顺序已知的情况下，此解决方案有时非常有用。

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;
}

该方法还实现了在参数只有一个且其值为 NULL 时的一种安全机制。它接受任何组合的值、表值和 null 值，而无需创建任何特殊类型、函数或动态连接。错误处理不在本解决方案的范围内。

示例用法

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"); 

 尽情享受。

历史  

• 初始版本发布于 2013 年 6 月 4 日。