删除在SqlCommand的PARAMS [英] Delete with params in SqlCommand

问题描述

我使用ADO.NET来删除数据库的一些数据是这样的：

I use ADO.NET to delete some data from DB like this:

    using (SqlConnection conn = new SqlConnection(_connectionString))
    {
        try
        {
            conn.Open();

            using (SqlCommand cmd = new SqlCommand("Delete from Table where ID in (@idList);", conn))
            {
                cmd.Parameters.Add("@idList", System.Data.SqlDbType.VarChar, 100);
                cmd.Parameters["@idList"].Value = stratIds;

                cmd.CommandTimeout = 0;
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception e)
        {
            //_logger.LogMessage(eLogLevel.ERROR, DateTime.Now, e.ToString());
        }
        finally
        {
            conn.Close();
        }
    }

这code无一例外地执行，但数据未从数据库中删除。 当我使用相同的算法插入或更新数据库一切正常。 有谁知道是什么问题？

That code executes without Exception but data wasn't deleted from DB. When I use the same algorithm to insert or update DB everything is OK. Does anybody know what is the problem?

推荐答案

您可以这样做，在常规TSQL，作为服务器会将 @idList 为<强>单值恰好包含逗号。但是，如果你使用名单，其中，INT＆GT; ，您可以用短小精悍点网，以

You can't do that in regular TSQL, as the server treats @idList as a single value that happens to contain commas. However, if you use a List<int>, you can use dapper-dot-net, with

connection.Execute("delete from Table where ID in @ids", new { ids=listOfIds });

衣冠楚楚的人物了你的意思，并产生相应的参数设置。

dapper figures out what you mean, and generates an appropriate parameterisation.

另一种选择是发送一个字符串，写一个UDF来执行一个分裂操作，然后使用该UDF在您的查询：

Another option is to send in a string and write a UDF to perform a "split" operation, then use that UDF in your query:

delete from Table where ID in (select Item from dbo.Split(@ids))

这篇关于删除在SqlCommand的PARAMS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！