C# & Sql Server : How To Insert Dbnull.value In Command Parameter If String Value Is Empty?

June 16, 2024 Post a Comment

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 str

Solution 1:

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..

CREATETRIGGER User_ReplaceEmptyWithNull
    ON 
        Users 
    AFTER 
        INSERT, 
        UPDATEASUPDATE 
            Users
        SET 
            Users.Forename = IIF(inserted.Forename !='', inserted.Forename, NULL),
            Users.Surname = IIF(inserted.Surname !='', inserted.Surname, NULL)
        FROM 
            inserted INNERJOIN 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
{
    publicstaticclassMyExtensions
    {
        publicstaticobjectOrDBNull( thisString value )
        {
            returnString.IsNullOrEmpty(value) ? DBNull.Value : value;
        }
    }   
}

...

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

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

namespaceYOUR_NAMESPACE
{
    publicstaticclassMyExtensions
    {
        publicstaticvoidAddString(this SqlParameterCollection collection, string parameterName, stringvalue)
        {
            collection.AddWithValue(parameterName, String.IsNullOrEmpty(value) ? DBNull.Value : value);
        }
    }   
}

...

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

Disclaimer: Untested. I probably screwed this up somewhere.

Solution 2:

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.

Solution 3:

Just check from microsoft doc

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

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

Solution 4:

Try this

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

Solution 5:

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

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

Lab Sql