问题描述
我使用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>
,您可以用短小精悍点网,以
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!