CODEWITHSUNDEEP
c#asp.netsql
Dave Mroz
Dave Mroz

Reputation: 1489

Conditionally null values inside of a list of SqlParameters

We have a block of code that creates a list of sqlParameters and then passes them to the stored procedure. A requirement came in to conditionally null a few of the values based on whether or not the input textbox is blank.

Base code:

var SqlParams = new List<SqlParameter> {
    new SqlParameter("@SomeFloat", SqlDbType.FLoat) {Value = f},
    ....
}

I tried a few variations on something, but it didn't work and VS gives an error about no implicit conversion between float and null.

new SqlParameter("@SomeFloat", SqlDbType.Float) {Value = (!string.IsNullOrEmpty(tb.Text) ? double.Parse(tb.Text) : DBNull.Value)},

Is there a way to do this maintaining the list of parameters so that we dont have to rewrite the whole module?

Thanks

Upvotes: 2

Views: 1365

Answers (3)

O.O
O.O

Reputation: 11317

This is a good case for an extension:

    public static object TryParseSqlValue(this object input)
    {
        if (null == input)
            return DBNull.Value;
        if (input.GetType() == typeof(string) && input.ToString() == string.Empty)
            return DBNull.Value;
        else
            return input;
    }

myTextBox.Text.TryParseSqlValue();  // returns either the non-empty 
                                    // string or DBNull.Value

You can modify it for other datatypes...

Upvotes: 1

Ray
Ray

Reputation: 46595

You're almost there, you just have to make sure the conditional operator knows what its result should be. Cast one of the double.Parse or DBNull.Value to object

I've also removed some brackets and swapped the condition to make it easier to remove.

new SqlParameter("@SomeFloat", SqlDbType.Float) 
    {
        Value = string.IsNullOrEmpty(tb.Text) 
            ? DBNull.Value
            : (object)double.Parse(tb.Text)
    }

However I would normally do something like this

if (!string.IsNullOrEmpty(tb.Text))
     command.Parameters.AddWithValue("@SomeFloat", double.Parse(tb.Text));

And not specify the value if the textbox is blank and let the stored procedure handle that case.

Upvotes: 4

Eric Frick
Eric Frick

Reputation: 857

I would totally recommend to point each call to a method and let it decide the value that's going to be passed as a parameter.

Upvotes: 1

Related Questions