How to specify to use Convert.DBNull automatically as sql parameter value when the original parameter is null in c#?

Question

I need to use Convert.DBNull for every single null value while adding parameters of sqlCommands. It's tedious because I have a lot of sql commands. So it would be lot easier if there is any way like setting this Object(Convert.DBNull)as parameter whenever it gets a null value as parameter.

Example: I have a code like this:

cmd.Parameters.AddWithValue("@MasterLastModifiedBy", Master.LastModifiedBy);

As variables like Master.LastModifiedBy might be null sometimes, therefore I have to reformat that into this:

cmd.Parameters.AddWithValue("@MasterLastModifiedBy", Master.LastModifiedBy?? Convert.DBNull);

I don't want to do this reformatting in all parameters. So, what else can I do to resolve this problem?

Write your own extension method AddWithValueWithNiceNullHandling (or whatever name you want) which does the ?? for you. This will thus allow you to still pass null when you explicit want to, but call your new method to use DBNull.Value instead. — mjwills
– mjwills, Commented Jan 21, 2019 at 10:38
Please provide the body of the method as answer so that I can mark that as correct one — Exception handler
– Exception handler, Commented Jan 21, 2019 at 10:47

ste-fu · Accepted Answer · 2019-01-21 11:27:10Z

1

I would suggest using an ORM Library of some sort. Dapper or Entity Framework would be good places to start.

Or you can write your own method to add a null check to each parameter.

Finally (specifically) thinking about last modified date, you could always set it, which would probably simplify a bunch of queries

EDIT:

Your extension method could look like this:

public static class SqlExtensions // needs to be in a static class
{
    public static void AddWithValueAndNullCheck(this SqlParameterCollection collection, string parameterName, object value)
    {
        if (object == null)
        {
            collection.AddWithValue(parameterName, DBNull.Value);
        }
        else
        {
            collection.AddWithValue(parameterName, value);
        }
    }
}

edited Jan 21, 2019 at 11:27

answered Jan 21, 2019 at 10:36

ste-fu

7,5273 gold badges31 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Exception handler Over a year ago

I am not allowed to use any ORM. Would you please provide the body of that extension method?

mjwills Over a year ago

Why are you not allowed to use an ORM @Exceptionhandler?

Exception handler Over a year ago

Thanks a lot for the method

Collectives™ on Stack Overflow

How to specify to use Convert.DBNull automatically as sql parameter value when the original parameter is null in c#?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related