Null References, A Billion Dollar Mistake

And ADO.NET promotes that billion dollar mistake ;-)



Given this code:

DateTime? periodBeginDate = null;
DateTime? periodEndDate = null;
 
var person = m_db.ExecuteScalar(CommandType.Text, "select * from person where CreatedDate between @periodBeginDate and @periodEndDate",
            new SqlParameter("@periodBeginDate", periodBeginDate),
            new SqlParameter("@periodEndDate", periodEndDate)
            );

That would result to this SQL:

exec sp_executesql N'
select * from person where CreatedDate between @periodBeginDate and @periodEndDate
',N'@periodBeginDate nvarchar(4000),@periodEndDate nvarchar(4000)',@periodBeginDate=default,@periodEndDate=default

And that gives this error:

Msg 8178, Level 16, State 1, Line 0
The parameterized query '(@periodBeginDate nvarchar(4000),@periodEndDate nvarchar(4000))
' expects the parameter '@periodBeginDate', which was not supplied.

Passing default is as good as not passing a parameter, to force passing NULL values as parameter, use DBNull.Value:

DateTime? periodBeginDate = null;
DateTime? periodEndDate = null;
 
var person = m_db.ExecuteScalar(CommandType.Text, "select * from person where CreateDate between @periodBeginDate and @periodEndDate",
            new SqlParameter("@periodBeginDate", periodBeginDate ?? (object) DBNull.Value),
            new SqlParameter("@periodEndDate", periodEndDate ?? (object) DBNull.Value)
            ); 

That would result to this SQL:

exec sp_executesql N'
select * from person where CreatedDate between @periodBeginDate and @periodEndDate
',N'@periodBeginDate nvarchar(4000),@periodEndDate nvarchar(4000)',@periodBeginDate=null,@periodEndDate=null

Answer from here: http://forums.asp.net/t/1611162.aspx/1



Happy Coding! ツ