AddWithValue 参数为 NULL 时的异常 [英] Exception when AddWithValue parameter is NULL

问题描述

我有以下代码用于指定 SQL 查询的参数.使用 Code 1 时出现以下异常；但是当我使用 Code 2 时工作正常.在 Code 2 中，我们检查 null 并因此检查 if..else 块.

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 2 we have a check for null and hence a if..else block.

例外:

参数化查询(@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. 您能否解释一下为什么它无法从代码 1 中的 logSearch.LogID 值中获取 NULL(但能够接受 DBNull)?

1. 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. 将 null 分配给 SqlParameter
2. 返回的数据类型因表中的数据而异
3. 来自数据库 smallint 的转换错误转换为 C# 可为空的 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;
    }

推荐答案

烦人，不是吗.

你可以使用:

command.Parameters.AddWithValue("@application_ex_id",
       ((object)logSearch.LogID) ?? DBNull.Value);

或者，也可以使用dapper"之类的工具，它会帮你搞定所有这些事情.

Or alternatively, use a tool like "dapper", which will do all that messing for you.

例如:

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

我很想向 dapper 添加一个方法来获取 IDataReader...目前还不确定这是否是个好主意.

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

这篇关于AddWithValue 参数为 NULL 时的异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！