SqlParameter和IN语句

Question

我需要以下查询：

createList(string commaSeparatedElements) {
    ...
    SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN ("+commaSeparatedElements+")");
    ...
}

我希望使用参数化查询来编写它，因此检查字符串中的每个元素，以防止Sql注入。

伪码：

createList(string commaSeparatedElements) {
    ...
    SqlParameterList elements = new SqlParameterList("@elements", SqlDbType.Int);
    SqlParameterList.Values = commaSeparatedElements.split(new Char[1] {','});
    SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN (@elements)");
    query.Parameters.Add(elements);
    ...
}

这样的东西是否存在于C#中，还是我必须自己写呢？

编辑:谢谢你所有的答案。由于我试图不使用代码，我不明白(过去的日子里有太多不好的经历)，dapper和表值参数，尽管它们可能完美地满足我的需要，是不允许的。我刚做了个循环。

string[] elements = commaSeparatedElements.split(new Char[1] {','});
StringList idParamList = new StringList();
for(int i=0;i<elements.Count;i++) {
    query.Parameters.AddWithValue("@element"+i,Convert.ToInt32(elements[i]));
    idParamList.Add("@element" + i);
}
SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN ("+String.Join(",",idParamList)+")");

Answer 1

我使用像这样的帮助方法

/// <summary>
/// Adds a sequence of parameters to an existing parameter collection
/// </summary>
/// <typeparam name="T">Type of parameter values</typeparam>
/// <param name="parameters">Existing parameter collection</param>
/// <param name="pattern">Name pattern of parameters. Must be a valid <see langword="int"/> format string</param>
/// <param name="parameterType">Database type of parameters</param>
/// <param name="length">Length of parameter. 0 for numeric parameters</param>
/// <param name="values">Sequence of values</param>
/// <returns>Comma separated string of parameter names</returns>
public static string AddParameters<T>(SqlParameterCollection parameters,
                                      string pattern,
                                      SqlDbType parameterType,
                                      int length,
                                      IEnumerable<T> values) {
    if (parameters == null)
        throw new ArgumentNullException("parameters");
    if (pattern == null)
        throw new ArgumentNullException("pattern");
    if (values == null)
        throw new ArgumentNullException("values");
    if (!pattern.StartsWith("@", StringComparison.CurrentCultureIgnoreCase))
        throw new ArgumentException("Pattern must start with '@'");

    var parameterNames = new List<string>();
    foreach (var item in values) {
        var parameterName = parameterNames.Count.ToString(pattern, CultureInfo.InvariantCulture);
        parameterNames.Add(parameterName);
        parameters.Add(parameterName, parameterType, length).Value = item;
    }

    return string.Join(",", parameterNames.ToArray());
}

它是这样使用的

string sql = "SELECT col1 " +
             "FROM Table " +
             "WHERE col2 IN ({@Values}) ";
var paramNames = SqlHelper.AddParameters(command.Parameters,
                                         "@Value0",
                                         SqlDbType.Int,
                                         0,
                                         listOfInts);
command.CommandText = sql.Replace("{@Values}", paramNames);
...

(结果是类似于SELECT ... IN (@Value0, @Value1, @Value2)的查询)

Answer 2

“衣冠楚楚”在这方面有一些伏都教：

var ids = new List<int> {1,2,3,4,5};
var rows = conn.Query<SomeType>("select * from table where id in @ids",
      new { ids }).ToList();

您将注意到in用法中缺少括号；"dapper“指出了这一点，并自动执行参数扩展。此外，它还为您处理所有的命令细节，包括将数据映射回SomeType实例。

在上面的例子中，它将扩展为相当于(大约)：

int ids0 = 1, ids1 = 2, ids2 = 3, ids3 = 4, ids4 = 5;
var rows = conn.Query<SomeType>(
    "select * from table where id in (@ids0,@ids1,@ids2,@ids3,@ids4)",
      new { ids0, ids1, ids2, ids3, ids4 }).ToList();

Answer 3

Server有一个数据结构，它非常适合保存同一“类型”的多个值--这就是一个表。

值得庆幸的是，近年来，他们引入了表值参数，以便您可以在客户端代码中构造一个表，并以一种自然的方式将表传递给Server，例如：

// Assumes connection is an open SqlConnection.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories = CategoriesDataTable.GetChanges(
    DataRowState.Added);

// Define the INSERT-SELECT statement.
string sqlInsert = 
    "INSERT INTO dbo.Categories (CategoryID, CategoryName)"
    + " SELECT nc.CategoryID, nc.CategoryName"
    + " FROM @tvpNewCategories AS nc;"

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(
    sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

// Execute the command.
insertCommand.ExecuteNonQuery();
}