How to insert DBNull.Value in Parameters.AddWithValue(), instead of an empty string, if the string value from the application is empty

Question

I've been searching for a couple of hours now and cannot find a solution.

I am inserting some strings into SQL, however on on occasions, the method I use to do this may contain strings that are empty, i.e ""

I therefore want to insert a null value into SQL Server instead.

Firstly I test my method to make sure I am able to insert a DBNull.Value manually by using the following:

cmd.Parameters.AddWithValue("@surname", DBNull.Value);  //THIS WORKS OK

HOWEVER when I try the following below, the method runs without any error exception, BUT the SQL Server column does not show a null value, it's just empty with nothing in it:

cmd.Parameters.AddWithValue("@surname", (object)surname ?? DBNull.Value);

I just want an efficient solution without messing about with any more common class extensions and writing lots of extra code. Every answer I see on the forums is just over complicated for what should be such a simple task.

Here is the full method:

public static void AddUserToUserTable(string username, 
        string forename, 
        string surname, 
        string emailAddress, 
        string password, 
        string accountActive, 
        string userGroup)
{
    string cmdText1 = "INSERT INTO Users (Username, Forename, Surname, EmailAddress, Password, AccountActive, UserGroup) VALUES (@username, @forename, @surname, @emailAddress, @password, @accountActive, @userGroup)";

    try
    {
        // The using statement will take care of the disposing of the reader and the command object.
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand(cmdText1, connection);

            cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@forename", forename);
            cmd.Parameters.AddWithValue("@surname", surname, null);
            cmd.Parameters.AddWithValue("@emailAddress", emailAddress);
            cmd.Parameters.AddWithValue("@password", password);
            cmd.Parameters.AddWithValue("@userGroup", accountActive);
            cmd.Parameters.AddWithValue("@accountActive", userGroup);

            connection.Open();
            cmd.ExecuteNonQuery();

            log.Info("NEW User [" + username + "] Created");
        }
    }
    catch (Exception ex)
    {
        log.Error(ex);
    }
}

It may be worth reading can we stop using AddWithValue. P.S. SqlCommand is also IDisposable each should also be in a using block. — Richardissimo
– Richardissimo, Commented Apr 4, 2019 at 5:30
@DrewChapin Richardissimo provided a good link, but in short, the main problem is that it doesn't specifies the data type and other important options for the parameter. Creating a new SqlParameter using one of its constructors is a far better choice. — Alejandro
– Alejandro, Commented Apr 4, 2019 at 11:56

Drew Chapin · Accepted Answer · 2019-04-03 22:59:45Z

1) The simplest solution everyone has already mentioned:

cmd.Parameters.AddWithValue("@surname", 
    String.IsNullOrEmpty(surname) ? DBNull.Value : surname);

2) If you can modify the database itself, you could add a trigger to replace empty strings with NULL on INSERT and UPDATE operations. This has an advantage of ensuring consistency if there are other developers and/or applications altering the database..

CREATE TRIGGER User_ReplaceEmptyWithNull
    ON 
        Users 
    AFTER 
        INSERT, 
        UPDATE 
    AS
        UPDATE 
            Users
        SET 
            Users.Forename = IIF(inserted.Forename != '', inserted.Forename, NULL),
            Users.Surname = IIF(inserted.Surname != '', inserted.Surname, NULL)
        FROM 
            inserted INNER JOIN Users
                ON inserted.Username = Users.Username

Disclaimer: I'm not an expert on database triggers. I adapted this from answers on another SO question

3) You could make an extension method for String objects.

namespace YOUR_NAMESPACE
{
    public static class MyExtensions
    {
        public static object OrDBNull( this String value )
        {
            return String.IsNullOrEmpty(value) ? DBNull.Value : value;
        }
    }   
}

...

cmd.Parameters.AddWithValue("@surname", surname.OrDBNull());

4) You could make an extension method for SqlParameterCollection objects.

namespace YOUR_NAMESPACE
{
    public static class MyExtensions
    {
        public static void AddString( this SqlParameterCollection collection, string parameterName, string value )
        {
            collection.AddWithValue(parameterName, String.IsNullOrEmpty(value) ? DBNull.Value : value);
        }
    }   
}

...

cmd.Parameters.AddString("@surname", surname);

Disclaimer: Untested. I probably screwed this up somewhere.

TylerH · Accepted Answer · 2025-08-20 15:42:46Z

2

I've just seen another thread and tried the following code below which works for me, not sure if it's the best way but appears fairly similar to what I was trying to achieve:

cmd.Parameters.AddWithValue("@surname", string.IsNullOrEmpty(surname) ? (object)DBNull.Value : surname);

I was missing the string.IsNullOrEmpty part.

edited Aug 20 at 15:42

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Apr 3, 2019 at 22:02

OJB1

2,8657 gold badges43 silver badges77 bronze badges

Comments

TylerH · Accepted Answer · 2025-08-20 15:41:23Z

1

Just check from the Microsoft documentation:

public System.Data.SqlClient.SqlParameter AddWithValue (string parameterName, object value);

DBNull and string class both Inherited from Object so you can cast them and pass an object type in the second parameter.

One simple suggestion to add DBNull.Value to SQL server if string is empty is the following

Try the following in your code. You also need to know the ?? operator:

string sql = "INSERT INTO [table] (columnName) VALUES (?);";
cmd.Parameters.AddWithValue("@columnName", (object)model.value ?? DBNull.Value);

edited Aug 20 at 15:41

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jun 1, 2020 at 10:17

kfwongao

212 bronze badges

Comments

Hakunamatata · Accepted Answer · 2019-04-03 22:02:59Z

0

You can do something like this to cover both empty string and null values.

 String.IsNullOrWhiteSpace(paramvalue) ? DBNull.Value : paramvalue

answered Apr 3, 2019 at 22:02

Hakunamatata

1,27313 silver badges19 bronze badges

Comments

JogiKalpesh · Accepted Answer · 2019-04-04 03:02:54Z

0

try this

string surname = string.Empty;

cmd.Parameters.AddWithValue("@surname", string.IsNullOrEmpty(surname) ? DBNull.Value :(object)surname);

edited Apr 4, 2019 at 3:02

answered Apr 3, 2019 at 22:45

JogiKalpesh

11 bronze badge

Comments

TylerH · Accepted Answer · 2025-08-20 15:41:28Z

0

Try this

string.IsNullOrEmpty(surname)  ? DBNULL.Value : surname

edited Aug 20 at 15:41

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Apr 3, 2019 at 21:56

Alex M

1462 silver badges6 bronze badges

2 Comments

OJB1 Over a year ago

Hi, thanks for your input. I just tried that but i get the squiggly lines along most of the line with the message "Type of conditional expression cannot be determined because there is no implicit conversion between System.DBNull and string

Alex M Over a year ago

That sounds reasonable. AddWithValue is expecting a string value, and getting null. But the string.isnullorempty will evaluate your string correctly. The other problem is inserting null into the database. I reckon you would evaluate the value of your string and then skip uploading it if it's null. That will leave your field NULL in the database. Something like if (! string.isnullorempty) then do your addwithvalue() for surename

Collectives™ on Stack Overflow

How to insert DBNull.Value in Parameters.AddWithValue(), instead of an empty string, if the string value from the application is empty

6 Answers 6

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related