如何使用List作为c#中的SQL参数

本文关键字：中的 SQL 参数 作为 何使用 List int | 更新日期: 2023-09-27 18:13:35

我试图使用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

如何使用List<int>作为c#中的SQL参数

应该使用TVP，这样可以完全按照指定的方式保持查询。

表值参数，带示例

在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的方法，并且优于使用CTE函数的非CLR替代方法。

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

我通常会使用某种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();
    }

在SQL Server 2008及以上版本中，可以使用表值参数。由于它们只是命令分隔的整数，所以我也会尝试在存储过程中保留删除语句，并传递xml字符串或按原样传递以解析为表变量。它在很多方面都是可行的，正如在其他答案中提到的，表值参数看起来很干净。

相关文章：