Sending null Parameter into stored procedure

Question

I have Subject class. It's members are id_subject (int) and subjectName (string)

Them I create a list

List<Subject> listSubject = new List<Subject>();

and send it to this stored procedure:

 public bool AddNewNews(News news, List<Subject> subject)
 {
        SqlParameter[] parameters = new SqlParameter[]
        {
           new SqlParameter ("@title",news.Title),
           new SqlParameter ("@text",news.Text),
           new SqlParameter ("@year",news.Year),    
           new SqlParameter ("@month",news.Month) ,    
           new SqlParameter ("@day",news.Day) ,   
           new SqlParameter ("@id_writer",news.Id_writer)  ,
           new SqlParameter ("@id_subject1",subject[0]!=null? subject[0].Id_subject:null),
           new SqlParameter ("@id_subject2",subject[1]!=null? subject[1].Id_subject:null) ,
           new SqlParameter ("@id_subject3",subject[2]!=null? subject[2].Id_subject:null), 
           new SqlParameter ("@id_subject4",subject[3]!=null? subject[3].Id_subject:null),
           new SqlParameter ("@id_subject5",subject[4]!=null? subject[4].Id_subject:null)
    };

    return SqlDBHelper.ExecuteNonQuery("AddNewNews", CommandType.StoredProcedure, parameters);
}

First: short if is not true

I cast them to object and send to the stored procedure

BEGIN TRANSACTION;

   DECLARE @last_id_news int

   BEGIN TRY
      insert into news (title, text, year, month, day, id_writer)
      values(@title, @text, @year, @month, @day, @id_writer)

      set @last_id_news = SCOPE_IDENTITY()

      if(@id_subject1 <> null)
      begin
           insert into news_subject (id_news, id_subject) 
           values (@last_id_news, @id_subject1)
      end

      if(@id_subject2 <> null)
      begin
         insert into news_subject (id_news, id_subject) 
         values (@last_id_news, @id_subject2)
      end

      if(@id_subject3 <> null)
      begin
         insert into news_subject (id_news, id_subject) 
         values (@last_id_news, @id_subject3)
      end

if(@id_subject4<>null)
begin
    insert into news_subject (id_news,id_subject) values (@last_id_news,@id_subject4)
end
if(@id_subject5<>null)
begin
    insert into news_subject (id_news,id_subject) values (@last_id_news,@id_subject5)
end

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH

ROLLBACK TRANSACTION;
END CATCH;

RETURN

Why does it not work?

Answer 1

As already noted in comments and an answer, DBNull is the annoying requirement here; you can use the null-coalescing operator to make it fairly readable, though:

   new SqlParameter ("@id_subject1",((object)subject[0]) ?? DBNull.Value),
   new SqlParameter ("@id_subject2",((object)subject[1]) ?? DBNull.Value),
   new SqlParameter ("@id_subject3",((object)subject[2]) ?? DBNull.Value),
   new SqlParameter ("@id_subject4",((object)subject[3]) ?? DBNull.Value),
   new SqlParameter ("@id_subject5",((object)subject[4]) ?? DBNull.Value),

Or better: perhaps your ExecuteNonQuery method could loop over the parameters and check whether .Value == null, replacing it with DBNull.Value if it is.

Alternatively alternatively, consider a tool like "dapper" that will do it all for you conveniently:

connection.Execute("AddNewNews", new {
    title = news.Title,
    //...
    id_writer = news.Id_writer,
    id_subject1 = subject[0],
    //...
    id_subject5 = subject[4],
}, commandType: CommandType.StoredProcedure);

which is then fully parameterized with nulls handled appropriately.

Answer 2

You are looking for DBNull.Value

Similar question on SO

1. Assign null to a SqlParameter
2. Passing DBNull.Value and Empty textbox value to database