C# 将空参数发送到 Sql Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18963178/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sending null parameters to Sql Server
提问by Cameron Tinker
I have a SqlCommandobject that I'm using to update a database table but it doesn't interpret my null values correctly.
我有一个SqlCommand用于更新数据库表的对象，但它没有正确解释我的空值。
Here is the SQL:
这是 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
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);
I get an exception like this:
我得到这样的异常：
The parameterized query '(@param1 nvarchar(4000),@param2 nvarchar(4000),@param3 nvarc' expects the parameter '@param4', which was not supplied.
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:
如果参数值为空，我还尝试在将它们添加到 SqlCommand 对象后循环遍历每个参数以设置 DbNull.Value，如下所示：
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.
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.
将空参数传递给 a 的最佳做法是SqlCommand什么？如果它们为空，我不想简单地传递默认值，因为数据库架构允许空值。
采纳答案by Gaurav123
Try this :
尝试这个 ：
command.Parameters.AddWithValue("@param1", param1 ?? Convert.DBNull);
回答by Sai Avinash
hi try using the following synatx:
您好，请尝试使用以下语法：
command.parameters.add("@ParameterName",DBNull.value);
hope this helps
希望这可以帮助
回答by tHiNk_OuT_oF_bOx
commandObject.Parameters.AddWithValue("@parameterName",Convert.DBNull);
