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

问题描述

我有以下code指定参数的SQL查询。我得到以下情况例外，当我使用 code 1 ;但是当我用正常工作 code 2 。在 code 2 我们已经为空，因此一个的if..else 块检查。

例外：

  

的参数化查询（@application_ex_id为nvarchar（4000））选择E.application_ex_id A预计参数'@application_ex_id，但未提供。的

code 1

  command.Parameters.AddWithValue（@ application_ex_id，logSearch.LogID）;
 

code 2

 如果（logSearch.LogID！= NULL）
{
         command.Parameters.AddWithValue（@ application_ex_id，logSearch.LogID）;
}
其他
{
        command.Parameters.AddWithValue（@ application_ex_id的DBNull.Value）;
}
 

问

1. 能否请您解释一下为什么它是无法接受NULL从logSearch.LogID价值code 1（但能够接受的DBNull）？

2. 有没有更好的code来处理呢？

参考

1. 如何分配空到的SqlParameter？
2. <一个href="http://stackoverflow.com/questions/13306275/datatype-returned-varies-based-on-data-in-table">Datatype返回的变化的基础上表中的数据
3. <一个href="http://stackoverflow.com/questions/13265704/conversion-error-from-database-smallint-into-c-sharp-nullable-int?lq=1">Conversion错误从数据库中SMALLINT到C＃可空INT
4. 什么是DBNull的意义呢？

code

 公文集＆LT;登录＆GT; GetLogs（LogSearch logSearch）
    {
        收藏＆LT;登录＆GT;日志=新的集合＆LT;登录＆GT;（）;

        使用（SqlConnection的连接=新的SqlConnection（的connectionString））
        {
            connection.Open（）;

            字符串的CommandText = @SELECT *
                从Application_Exê
                WHERE（E.application_ex_id = @application_ex_id或@application_ex_id IS NULL）;

            使用（SqlCommand的命令=新的SqlCommand（CommandText中，连接））
            {
                command.CommandType = System.Data.CommandType.Text;

                //参数值设置
                //command.Parameters.AddWithValue("@application_ex_id，logSearch.LogID）;
                如果（logSearch.LogID！= NULL）
                {
                    command.Parameters.AddWithValue（@ application_ex_id，logSearch.LogID）;
                }
                其他
                {
                    command.Parameters.AddWithValue（@ application_ex_id的DBNull.Value）;
                }

                使用（SqlDataReader的读卡器= Command.ExecuteReader却（））
                {
                    如果（reader.HasRows）
                    {
                        收藏＆LT;对象＆gt; entityList =新的集合＆LT;对象＆gt;（）;
                        entityList.Add（新记录（））;

                        ArrayList的纪录= EntityDataMappingHelper.SelectRecords（entityList，读卡器）;

                        的for（int i = 0; I＆LT; records.Count;我++）
                        {
                            日志中记录=新的日志（）;
                            字典＆LT;字符串，对象＆gt; currentRecord =（词典＆LT;字符串，对象＆gt;）的记录[I]
                            EntityDataMappingHelper.FillEntityFromRecord（日志，currentRecord）;
                            logs.Add（日志）;
                        }
                    }

                    //reader.Close（）;
                }
            }
        }

        返回日志;
    }
 

解决方案

恼人的，是吧。

您可以使用：

  command.Parameters.AddWithValue（@ application_ex_id
       （（对象）logSearch.LogID）？的DBNull.Value）;
 

或者，使用像短小精悍的工具，这将尽一切搞乱你。

例如：

  VAR数据= conn.Query＆LT; SOMETYPE＆GT;（CommandText中，
      新{application_ex_id = logSearch.LogID}）了ToList（）。
 

我的诱惑的添加方法短小精悍，以获得的IDataReader ......真的不知道还是否是个好主意

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.

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.

Code 1:

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

Code 2:

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

QUESTION

1. Can you please explain why it is unable to take NULL from logSearch.LogID value in Code 1 (but able to accept DBNull)?

2. Is there a better code to handle this?

Reference:

1. How to assign null to a sqlparameter?
2. Datatype returned varies based on data in table
3. Conversion error from database smallint into C# nullable int
4. 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;
    }

解决方案

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.

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