Convert empty string into Null to save Null Values into SQL Server database

Question

I just read out very nice answers to save null into database column using

string.IsNullOrWhiteSpace(textbox.tex) ? (object)textbox.text: DBNull.Value;

or using

cmd.Parameters.AddWithValue("foo",
    foo == null ? (object)DBNull.Value : (object)foo);

and I tried with others too but it's not working for me as I am not very expert of C# language but I must want to save null into my SQL Server database column.

Here is my code which I tried to save null value but it sends empty string which is not my requirement.

oCourseRegistrationPaypal.UserTheoryTrainingDate4 = Convert.ToString(DBNull.Value);

So when I write

oCourseRegistrationPaypal.UserTheoryTrainingDate4 = null;

it gives me error that my procedure wants parameter which was not supplied but I know that I have supplied null to it.

When I write

oCourseRegistrationPaypal.UserTheoryTrainingDate4=Convert.ToString(DBNull.Value);

it saves the empty string in my database column but I need a null value there.

When I manually save null values in stored procedure it accepts null and save null but why it is not accepting

oCourseRegistrationPaypal.UserTheoryTrainingDate4 = null;

from my C# code?

Is there a better way to do this?

@attila its object of my custom type layer and i want to store null in this object and then i pass this object to data access layer where i have written down the saving procedure. — Wasay, Apr 25 '14 at 01:58

ElGavilan · Answer 1 · 2014-04-25T01:58:58.820

1

If you convert a DBNull.Value to a string, it's gonna give you an empty string.

Just store a DBNull.Value.

cmd.Parameters.AddWithValue("foo",
    foo == null ? DBNull.Value : foo);

or alternatively:

cmd.Parameters.AddWithValue("foo",
    foo ?? DBNull.Value);

EDIT: Per your edit to your question, replace foo with oCourseRegistrationPaypal.UserTheoryTrainingDate4. The code I pasted above will replace null with DBNull.Value.

edited Apr 25 '14 at 01:58

answered Apr 25 '14 at 01:46

ElGavilan

6,610
16
27
36

when i try to save oCourseRegistrationPaypal.UserTheoryTrainingDate2 = DBNull.Value; it gives me error that cant conver dbnull to string. – Wasay Apr 25 '14 at 01:47
Because DBNull.Value is not a string. – ElGavilan Apr 25 '14 at 01:48
I dont want to save null using sql parameters etc but i just want to save null in oCourseRegistrationPaypal.UserTheoryTrainingDate2 ? is that possible? – Wasay Apr 25 '14 at 01:49
Yes, `oCourseRegistrationPaypal.UserTheoryTrainingDate2 = null`. – ElGavilan Apr 25 '14 at 01:49
so what should i do ? let me update my question to understand it. – Wasay Apr 25 '14 at 01:49
1

Sounds like you might be confusing `null` with `DBNull`. The latter is used to specify null as a SQL/db parameter. The former is used just about everywhere else to represent null. – ElGavilan Apr 25 '14 at 01:53
okay so can you tell me how to do it with this cmd.Parameters.AddWithValue("@dt_UserTTrainingDate1", oCourseRegistrationPaypal.UserTheoryTrainingDate1 == null ? DBNull.Value:); – Wasay Apr 25 '14 at 02:14
I showed you how to do it in my answer. Replace `foo` with `oCourseRegistrationPaypal.UserTheoryTrainingDate4` in my answer code. – ElGavilan Apr 25 '14 at 02:14
its giving me error that if i replace according to your answer. – Wasay Apr 25 '14 at 02:18
What are you putting exactly and what error are you getting? – ElGavilan Apr 25 '14 at 02:18
cmd.Parameters.AddWithValue("@dt_UserTTrainingDate1", oCourseRegistrationPaypal.UserTheoryTrainingDate1 == null ? DBNull.Value : oCourseRegistrationPaypal.UserTheoryTrainingDate1); it gives error that "Type of conditional expression cant be determind becuase there is no conversion b/w System.DBnull and string" – Wasay Apr 25 '14 at 02:21
What about `oCourseRegistrationPaypal.UserTheoryTrainingDate1 == null ? (object)DBNull.Value : (object)oCourseRegistrationPaypal.UserTheoryTrainingDate1`? – ElGavilan Apr 25 '14 at 02:25
its saving empty string but not null. – Wasay Apr 25 '14 at 02:31

Joel Coehoorn · Answer 2 · 2014-04-25T02:51:59.697

There's a problem with the AddWithValue() sample that you saw: it forces ADO.Net to guess at the database type for the parameter. Often it will guess right, but sometimes it guesses wrong.

The good news it that it guesses okay most of the them, and even when it guesses wrong code still tends to work. However, very rarely, this will cause your code to blow up and throw an exception. A more common problem is that this will cause your database to need per-row conversions, or break an index use. It can take a query that returns instantly in testing and make it run for several minutes in production.

The problem is worse when combined with the conditional operator check, because it forces you to cast both sides to Object. This removes the most important hint that ADO.Net gets when guessing the database type. Instead of a string, Datetime, Double, etc, all ADO.Net gets to see is "Object" as the .Net type. This makes it much more likely to guess the wrong database type. If this is your go-to pattern for adding NULLs, the result is sub-optimal performance throughout your application.

Instead, I like to do this:

//explicit DB type, and don't worry about DBNull just yet
cmd.Parameters.Add("foo",  SqlDbType.Int).Value = foo;
cmd.Parameters.Add("bar", SqlDbType.NVarChar, 30).Value = bar;
//if you have more parameters, add them all this way

// after all parameters are added:
foreach(var p in cmd.Parameters.Where(p => p.Value == null))
{
    p.Value = DBNull.Value;
}

There is one caveat here: value types like int and DateTime will never match that condition, and so my actual code has to a little more complicated (involving Nullable<T>s). But hopefully it gives you the right idea.

Convert empty string into Null to save Null Values into SQL Server database

2 Answers2