Passing DBNull.Value and Empty textbox value to database

Question

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.

abatishchev · Accepted Answer · 2015-08-11T21:19:07.640

13

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

edited Aug 11 '15 at 21:19

answered Sep 16 '12 at 07:05

abatishchev

98,240
88
296
433

I don't have access to the command object because I abstracted it away into a Base class so I can use it with several database [avoiding dependency on a particular database]. I like the Extension methods approach. Thanks. – codingbiz Sep 16 '12 at 15:18
I think something is wrong with `DbNullIfNull`. I couldn't understand the logic there, so I typed it into my .NET 4.0 project, and it is certainly wrong! My guess is you meant to code `return (obj != null) ? obj : DBNull.Value;` instead of `return obj ? = null ? obj : DBNull.Value;` Just an FYI. – Feb 27 '13 at 16:01
I know this is old, but the second function there doesn't work. To use the ternary operator, `str` and `DBNull.Value` must have an implicit conversion between one another (should be the same type). You'd have to use an if/else in this case. – Curmudgeon Aug 11 '15 at 14:07
@Curmudgeon: Thanks, updated, check it out. – abatishchev Aug 11 '15 at 21:19
@abatischev: Thanks, I submitted an edit but it got rejected -_-' I forgot about the (object) trick though, so it wasn't as pretty anyways :) – Curmudgeon Aug 11 '15 at 21:25

score 5 · Answer 2 · answered Sep 16 '12 at 06:27

A little re-factoring might make code less messy. Try this

dbParams.Add(SetDBNullIfEmpty("@FirstName", FirstName));
dbParams.Add(SetDBNullIfEmpty("@LastName", LastName));
dbParams.Add(SetDBNullIfEmpty("@DisplayName", DisplayName));
dbParams.Add(SetDBNullIfEmpty("@BirthDate", BirthDate));
dbParams.Add(SetDBNullIfEmpty("@Gender", Gender));

private SqlParameter SetDBNullIfEmpty(string parmName, string parmValue)
{
    return new SqlParameter(parmName, String.IsNullOrEmpty(parmValue) ? DBNull.Value : (object)parmValue));
}

score 2 · Answer 3 · answered Sep 16 '12 at 04:21

2

You can default the parameters in the stored procedure, making them optional.

create procedure XXX
(
   @FirstName nvarchar(50) = null,
   @LastName nvarchar(50) = null,
   ...
)

answered Sep 16 '12 at 04:21

Mufaka

2,333
1
18
25

this also works but won't make my DAL portable, so I have to handle the null values in my code. I don't know if ORACLE allow default values for parameters. (+1) anyway – codingbiz Sep 16 '12 at 15:18

Passing DBNull.Value and Empty textbox value to database

3 Answers3

Linked