C# AddWithValue 参数为 NULL 时的异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13451085/
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
Exception when AddWithValue parameter is NULL
提问by LCJ
I have following code for specifying parameters for SQL query. I am getting following exception when I use Code 1; but works fine when I use Code 2. In Code 2we have a check for null and hence a if..elseblock.
我有以下代码用于指定 SQL 查询的参数。我在使用时遇到以下异常Code 1；但是当我使用Code 2. 在Code 2我们检查空值，因此是一个if..else块。
Exception:
例外：
The parameterized query '(@application_ex_id nvarchar(4000))SELECT E.application_ex_id A' expects the parameter '@application_ex_id', which was not supplied.
参数化查询 '(@application_ex_id nvarchar(4000))SELECT E.application_ex_id A' 需要参数 '@application_ex_id'，但未提供该参数。
Code 1:
代码 1：
command.Parameters.AddWithValue("@application_ex_id", logSearch.LogID);
Code 2:
代码 2：
if (logSearch.LogID != null)
{
command.Parameters.AddWithValue("@application_ex_id", logSearch.LogID);
}
else
{
command.Parameters.AddWithValue("@application_ex_id", DBNull.Value );
}
QUESTION
题
Can you please explain why it is unable to take NULL from logSearch.LogID value in Code 1 (but able to accept DBNull)?
Is there a better code to handle this?
你能解释一下为什么它不能从代码 1 中的 logSearch.LogID 值中获取 NULL（但能够接受 DBNull）？
有没有更好的代码来处理这个问题？
Reference:
参考：
- Assign null to a SqlParameter
- Datatype returned varies based on data in table
- Conversion error from database smallint into C# nullable int
- What is the point of DBNull?
CODE
代码
public Collection<Log> GetLogs(LogSearch logSearch)
{
Collection<Log> logs = new Collection<Log>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string commandText = @"SELECT *
FROM Application_Ex E
WHERE (E.application_ex_id = @application_ex_id OR @application_ex_id IS NULL)";
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.CommandType = System.Data.CommandType.Text;
//Parameter value setting
//command.Parameters.AddWithValue("@application_ex_id", logSearch.LogID);
if (logSearch.LogID != null)
{
command.Parameters.AddWithValue("@application_ex_id", logSearch.LogID);
}
else
{
command.Parameters.AddWithValue("@application_ex_id", DBNull.Value );
}
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
Collection<Object> entityList = new Collection<Object>();
entityList.Add(new Log());
ArrayList records = EntityDataMappingHelper.SelectRecords(entityList, reader);
for (int i = 0; i < records.Count; i++)
{
Log log = new Log();
Dictionary<string, object> currentRecord = (Dictionary<string, object>)records[i];
EntityDataMappingHelper.FillEntityFromRecord(log, currentRecord);
logs.Add(log);
}
}
//reader.Close();
}
}
}
return logs;
}
采纳答案by Marc Gravell
Annoying, isn't it.
烦人是不是。
You can use:
您可以使用：
command.Parameters.AddWithValue("@application_ex_id",
((object)logSearch.LogID) ?? DBNull.Value);
Or alternatively, use a tool like "dapper", which will do all that messing for you.
或者，使用“dapper”之类的工具，它会为您解决所有这些麻烦。
For example:
例如：
var data = conn.Query<SomeType>(commandText,
new { application_ex_id = logSearch.LogID }).ToList();
I'm temptedto add a method to dapper to get the IDataReader... not really sure yet whether it is a good idea.
我很想为 dapper 添加一个方法来获得IDataReader......还不确定这是否是一个好主意。
回答by user1599225
some problem, allowed with Necessarily set SQLDbType
一些问题，允许有必要设置 SQLDbType
command.Parameters.Add("@Name", SqlDbType.NVarChar);
command.Parameters.Value=DBNull.Value
where SqlDbType.NVarChar you type. Necessarily set SQL type.
您键入的 SqlDbType.NVarChar 在哪里。必须设置 SQL 类型。
回答by AxiomaticNexus
I find it easier to just write an extension method for the SqlParameterCollectionthat handles null values:
我发现为SqlParameterCollection处理空值的编写扩展方法更容易：
public static SqlParameter AddWithNullableValue(
this SqlParameterCollection collection,
string parameterName,
object value)
{
if(value == null)
return collection.AddWithValue(parameterName, DBNull.Value);
else
return collection.AddWithValue(parameterName, value);
}
Then you just call it like:
然后你就可以这样称呼它：
sqlCommand.Parameters.AddWithNullableValue(key, value);
回答by z00l
Just in case you're doing this while calling a stored procedure: I think it's easier to read if you declare a default value on the parameter and add it only when necessary.
以防万一您在调用存储过程时执行此操作：我认为如果您在参数上声明默认值并仅在必要时添加它，则更容易阅读。
SQL:
查询语句：
DECLARE PROCEDURE myprocedure
@myparameter [int] = NULL
AS BEGIN
C#:
C＃：
int? myvalue = initMyValue();
if (myvalue.hasValue) cmd.Parameters.AddWithValue("myparamater", myvalue);
