I have some textboxes on my page which can be empty because they are optional and I have this DAL code
parameters.Add(new SqlParameter("@FirstName", FirstName));
parameters.Add(new SqlParameter("@LastName", LastName));
parameters.Add(new SqlParameter("@DisplayName", DisplayName));
parameters.Add(new SqlParameter("@BirthDate", BirthDate));
parameters.Add(new SqlParameter("@Gender", Gender));
Any of those fields can be empty. The problem is when they are empty I receive Procedure XXX requires @FirstName which was not supplied
Then I changed my code to
parameters.Add(new SqlParameter("@FirstName", String.IsNullOrEmpty(FirstName) ? DBNull.Value : (object)FirstName));
parameters.Add(new SqlParameter("@LastName", String.IsNullOrEmpty(LastName) ? DBNull.Value : (object) LastName));
parameters.Add(new SqlParameter("@DisplayName", String.IsNullOrEmpty(DisplayName) ? DBNull.Value : (object) DisplayName));
parameters.Add(new SqlParameter("@BirthDate", BirthDate.HasValue ? (object)BirthDate.Value : DBNull.Value));
parameters.Add(new SqlParameter("@Gender", String.IsNullOrEmpty(Gender) ? DBNull.Value : (object) Gender));
But this looks messy to me especially the casting to object
because ternary statement requires both value to be the same type.
Why is empty string or null string not treated NULL
in the database? If I have to convert this to DBNull.Value
is there a cleaner way? Saving the value as empty string in the database could have helped but query for NULL
in the database will get messy too
Please give your advice on common practices or something close to that.
First, there are 2 more handy overloads:
command.Parameters.Add("@name").Value = value;
or
command.Parameters.AddWithValue("@name", value);
Personally I use the following extension method:
public static object DbNullIfNull(this object obj)
{
return obj != null ? obj : DBNull.Value;
}
command.Parameters.AddWithValue("@name", value.DbNullIfNull());
or
public static object DbNullIfNullOrEmpty(this string str)
{
return !String.IsNullOrEmpty(str) ? str : (object)DBNull.Value;
}