C# & SQL Server : how to Insert DBNull.Value in command parameter if string value 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.

Any ideas? Thanks in advance...

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, @userGroup, @accountActive)";

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

Note: Your `VALUES` are out of order. You have `, AccountActive, UserGroup) VALUES (..., @userGroup, @accountActive)` — Drew Chapin, Apr 03 '19 at 22:13
`AddWithValue` is a bad practice, specially for string fields. — Alejandro, Apr 03 '19 at 22:49
It may be worth reading [can we stop using AddWithValue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). P.S. SqlCommand is also IDisposable each should also be in a `using` block. — Richardissimo, Apr 04 '19 at 05: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, Apr 04 '19 at 11:56

Drew Chapin · Accepted Answer · 2019-04-03T22:59:45.610

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.

score 2 · Answer 2 · answered Apr 03 '19 at 22:02

quick update, I've just seen another thread and tried the following code below which works for me, not sure if its the bets 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.

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

Just check from microsoft doc

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 (it works): you also need to know the ?? operator

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

score 0 · Answer 4 · answered Apr 03 '19 at 21:56

0

Try this

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

answered Apr 03 '19 at 21:56

Alex M

141
2
6

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 – OJB1 Apr 03 '19 at 21:59
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 – Alex M Apr 03 '19 at 22:03

score 0 · Answer 5 · answered Apr 03 '19 at 22:02

0

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

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

answered Apr 03 '19 at 22:02

Hakunamatata

1,275
13
18

JogiKalpesh · Answer 6 · 2019-04-04T03:02:54.243

0

try this

string surname = string.Empty;

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

edited Apr 04 '19 at 03:02

answered Apr 03 '19 at 22:45

JogiKalpesh

1
1

C# & SQL Server : how to Insert DBNull.Value in command parameter if string value is empty?

6 Answers6

Try the following in your code (it works): you also need to know the ?? operator

Linked