我有以下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);
}
问
能否请您解释一下为什么它是无法接受NULL从logSearch.LogID价值code 1(但能够接受的DBNull)?
有没有更好的code来处理呢?
参考
如何分配空到的SqlParameter? Datatype返回的变化的基础上表中的数据 Conversion错误从数据库中SMALLINT到C#可空INT 什么是DBNull的意义呢?code
公文集<登录> GetLogs(LogSearch logSearch)
{
收藏<登录>日志=新的集合<登录>();
使用(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)
{
收藏<对象> entityList =新的集合<对象>();
entityList.Add(新记录());
ArrayList的纪录= EntityDataMappingHelper.SelectRecords(entityList,读卡器);
的for(int i = 0; I< records.Count;我++)
{
日志中记录=新的日志();
字典<字符串,对象> currentRecord =(词典<字符串,对象>)的记录[I]
EntityDataMappingHelper.FillEntityFromRecord(日志,currentRecord);
logs.Add(日志);
}
}
//reader.Close();
}
}
}
返回日志;
}
解决方案
恼人的,是吧。
您可以使用:
command.Parameters.AddWithValue(@ application_ex_id
((对象)logSearch.LogID)?的DBNull.Value);
或者,使用像短小精悍的工具,这将尽一切搞乱你。
例如:
VAR数据= conn.Query< SOMETYPE>(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
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?
Reference:
How to 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;
}
解决方案
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.