将空参数发送到Sql Server [英] Sending null parameters to Sql Server
问题描述
我有一个SqlCommand
对象,我正在使用该对象来更新数据库表,但是它不能正确解释我的空值.
I have a SqlCommand
object that I'm using to update a database table but it doesn't interpret my null values correctly.
这是SQL:
UPDATE dbo.tbl
SET param1 = @param1,
param2 = @param2,
param3 = @param3,
param4 = @param4,
param5 = @param5,
param6 = @param6,
param7 = @param7,
param8 = @param8,
param9 = @param9,
param10 = @param10
WHERE param11 = @param11
我已经尝试过像这样可以为空的null合并参数,但是我没有取得任何成功.除非另有说明,否则所有参数均为字符串:
I have tried null coalescing parameters that are nullable like this, but I haven't had any success. Unless otherwise noted, all parameters are strings:
command.Parameters.AddWithValue("@param1", param1 ?? DBNull.Value);
command.Parameters.AddWithValue("@param2", param2 ?? DBNull.Value);
command.Parameters.AddWithValue("@param3", param3 ?? DBNull.Value);
command.Parameters.AddWithValue("@param4", param4 ?? DBNull.Value);
command.Parameters.AddWithValue("@param5", param5 ?? DBNull.Value);
// param6 is nullable type DateTime?
command.Parameters.AddWithValue("@param6", param6 ?? DBNull.Value);
command.Parameters.AddWithValue("@param7", param7 ?? DBNull.Value);
// param8 is nullable type DateTime?
command.Parameters.AddWithValue("@param8", param8 ?? DBNull.Value);
command.Parameters.AddWithValue("@param9", param9 ?? DBNull.Value);
// param10 nullable type float?
command.Parameters.AddWithValue("@param10", param10 ?? DBNull.Value);
command.Parameters.AddWithValue("@param11", param11 ?? DBNull.Value);
我遇到这样的异常:
The parameterized query '(@param1 nvarchar(4000),@param2 nvarchar(4000),@param3 nvarc' expects the parameter '@param4', which was not supplied.
在将每个参数添加到SqlCommand对象以设置DbNull.Value后,如果参数值为null,我还尝试遍历每个参数,如下所示:
I've also tried looping through each parameter after they've been added to the SqlCommand object to set DbNull.Value if the parameter value is null like this:
foreach (SqlParameter parameter in command.Parameters)
{
if (parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
}
但是,这种方法导致了异常:
However, this approach is causing the exception:
String or binary data would be truncated.
The statement has been terminated.
将空参数传递给SqlCommand
的最佳实践是什么?如果数据库的默认值是null,那么我就不想简单地传递这些默认值,因为数据库架构允许使用null值.
What is the best practice for passing null parameters to a SqlCommand
? I don't simply want to pass in default values if they're null since the database schema allows null values.
推荐答案
尝试一下:
command.Parameters.AddWithValue("@param1", param1 ?? Convert.DBNull);
这篇关于将空参数发送到Sql Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!