当 AddWithValue 参数为 NULL 时发生异常

我有以下代码用于指定 SQL 查询的参数。我在使用 Code 1时遇到了下面的例外情况; 但是在使用 Code 2时工作得很好。在 Code 2中，我们有一个 null 检查，因此有一个 if..else块。

例外:

参数化查询’(@application _ ex _ id nvarchar (4000)) SELECT E.application _ ex _ id A’期望得到未提供的参数‘@application _ ex _ id’。

代码1 :

command.Parameters.AddWithValue("@application_ex_id", logSearch.LogID);

代码2 :

if (logSearch.LogID != null)
{
command.Parameters.AddWithValue("@application_ex_id", logSearch.LogID);
}
else
{
command.Parameters.AddWithValue("@application_ex_id", DBNull.Value );
}

问题

  1. 您能解释一下为什么它不能从 logSearch 中取出 NULL 吗。代码1中的 LogID 值(但能够接受 DBNull) ？

  2. 有更好的代码来处理这个吗？

参考 :

  1. 将 null 赋给 SqlParameter
  2. 返回的数据类型因表中的数据而异
  3. 从数据库 smallint 到 C # nullable int 的转换错误
  4. DBNull 的意义何在

密码

    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;
}
76620 次浏览
小开
最佳答案

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.

For example:

var data = conn.Query<SomeType>(commandText,
new { application_ex_id = logSearch.LogID }).ToList();

I'm tempted to add a method to dapper to get the IDataReader... not really sure yet whether it is a good idea.

小开

some problem, allowed with Necessarily set SQLDbType

command.Parameters.Add("@Name", SqlDbType.NVarChar);
command.Parameters.Value=DBNull.Value

where SqlDbType.NVarChar you type. Necessarily set SQL type.

小开

I find it easier to just write an extension method for the SqlParameterCollection that handles null values:

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);
小开

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#:

int? myvalue = initMyValue();
if (myvalue.hasValue) cmd.Parameters.AddWithValue("myparamater", myvalue);

提交答案