如何在C#中将List<int>用作SQL参数

Question

我正在尝试使用这段代码将 List< int> 作为SQL参数：

var listID= new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9 };

        using (var sqlConnection = new SqlConnection(_connectionstring))
        {
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = sqlConnection;
                cmd.CommandText = "delete from MyTable where TableID in ( @tableID)";

                string param = String.Join(",", listID.ToArray());
                cmd.Parameters.Add("@tableID", param);
                sqlConnection.Open();
                cmd.ExecuteNonQuery();
            }
            sqlConnection.Close();
        }

问题在于，这段代码将生成以下内容:

exec sp_executesql N'delete from MyTable where TableID in ( @tableID)',N'@tableID nvarchar(17)',@tableID =N'1,2,3,4,5,6,7,8,9'

这将失败，因为:

将nvarchar值“1,2,3,4,5,6,7,8,9”转换为int数据类型时转换失败。

有什么解决办法吗？谢谢。

编辑：我正在使用MS SQL 2012。

- Chatumbabub

---

这是SQL Server吗？如果是，它的版本是什么？ - Jodrell

是的，抱歉，它是SQL Server 2012。我将扩展我的问题。 - Chatumbabub

多个由逗号分隔的参数和一个包含逗号的单个字符串参数之间存在逻辑差异。T-SQL（像其他任何合理的语言一样）不会尝试拆开该单个参数。 - Damien_The_Unbeliever

你确定 IN 可以只用一个参数吗？（我的意思是：一个参数适用于所有情况，而不是一对一的关系...） - user57508

请查看以下问题：stackoverflow.com/questions/182060/where-in-array-of-ids - VeNoMiS

Answer 1

你应该使用TVP，这样你就可以保持查询的精确性。它们在SQL 2008中被引入。

表值参数，带有示例

- Matthew

---

鉴于OP正在谈论SQL2012，这是正确的方法。+1 - Jodrell

+1 没错。同意。当我写下我的答案时，我们还不知道他在谈论哪个版本的SQL。 - Tom Chantler

好的信息！仅提供链接的答案存在一些问题：http://meta.stackoverflow.com/tags/link-only-answers/info。您能否考虑将信息复制/粘贴到您的答案中？ - JumpingJezza

Answer 2

在SQL Server 2005中，我编写了一个CLR函数，将逗号分隔的列表转换为一组nvarchars。

[SqlFunction(DataAccess = DataAccessKind.None,
                IsDeterministic = true,
                SystemDataAccess = SystemDataAccesskind.None,
                IsPrecise = true,
                FillRowMethodName = "SplitFillRow",
                TableDefinition = "s NVARCHAR(MAX)")]
public static IEnumerable Split(SqlChars seperator, SqlString s)
{
    if (s.IsNull)
    {
        return new string[0];
    }

    return s.ToString().Split(seperator.Buffer);
}

public static void SplitFillRow(object row, out SqlString s)
{
    s = new SqlString(row.ToString());
}

我认为使用.Net CLR比非CLR替代方案使用CTE函数更有效。

---

在SQL Server 2008+中，您可以使用表值参数。

- Jodrell

Answer 3

我通常会使用某种ORM，但如果您想继续使用类似于您拥有的东西，您可以尝试这个：

    var listID= new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
    using (var sqlConnection = new SqlConnection(_connectionstring))
    {
        using (var cmd = new SqlCommand())
        {
            cmd.Connection = sqlConnection;
            cmd.CommandText = "delete from MyTable where TableID in (" + String.Join(",",listID) + ")";
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
        }
        sqlConnection.Close();
    }

- Tom Chantler

---

为什么这个被踩了？鉴于问题，它似乎是合理的。 - Michael Todd

如果你正在使用动态SQL，它会非常有效。 - Jodrell

我猜这是因为即使当前形式是安全的，但微小的修改（例如使用List<string>）可能会使其变得非常不安全。个人而言，我并不一定反对动态SQL，但如果需要使用它，一个通常更好的替代方案是构建一个where TableID in (@p0, @p1, @p2, ...)的查询，并将列表值存储在参数中。 - user743382

@hvd 我知道 SQL 注入的危害，但我不确定这种动态 SQL 的使用方式可能存在哪些安全问题。有什么微小的修改可以让一个整数列表足以改变 SQL 语句以至于会引起问题吗？（如果你是指在以后的某个时候该列表被转换为字符串，那么当然会有问题。） - Michael Todd

@MichaelTodd 没错，就是这样：将List<int>更改为List<string>以便在另一个表格上使用，其中主键是字符串而不是整数，我认为这是一项微不足道的修改。 我认为我们大多数人都同意：如果您只处理int，并且没有在int之前加入任何可能与负值符号结合的内容，则不可能发生SQL注入。 - user743382

@hvd 只是想确保我没有漏掉什么。总有新的黑客或漏洞；如果不问，我永远赶不上。 - Michael Todd

Answer 4

在 SQL Server 2008 及以上版本中，您可以使用表值参数。由于它们只是逗号分隔的整数，我建议尝试将删除语句保留在存储过程中，并将 XML 字符串传递或直接解析为表变量。有很多方法可以实现此操作，如其他答案中所提到的，表值参数似乎更为简洁明了。

- Sunny

---

不要使用XML来做这件事。这正是TVP的用途。TVP也可以作为参数发送到存储过程中。 - Matthew

你说得对，tvp看起来很干净。但是只有逗号分隔的整数让我提供了额外的选项。在SQL Server 2005中，我更喜欢使用XML而不是CLR函数。 - Sunny