Exception when AddWithValue parameter is NULL

Question

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. 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;     } 

Answer 1

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.

Answer 2

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