CODEWITHSUNDEEP
c#sql-server
LightStamp
LightStamp

Reputation: 39

Insert empty params into decimal nullable columns

I have no values to insert into MaxPrice or MinPrice columns which can be null. The values when they don't exist are originally were seen as , , in the resulting Sql expression. I got a syntax error before and changed the code to this 

 (setItem.MinPrice.ToString()!="")?setItem.MinPrice.ToString() : null,
 (setItem.MaxPrice.ToString() != "") ? setItem.MaxPrice.ToString() : null

But when I execute this I get an error and the sql still looks like the , , . The columns are decimal but can be NULL.

Upvotes: 1

Views: 48

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063403

Use parameters. Always.

cmd.Parameters.AddWithValue("@minPrice",
    setItem.MinPrice == null ? DBNull.Value : (object)setItem.MinPrice);
cmd.Parameters.AddWithValue("@maxPrice",
    setItem.MaxPrice == null ? DBNull.Value : (object)setItem.MaxPrice);

or with a tool like Dapper:

// adds "@MinPrice" and "@MaxPrice" correctly as parameters, as long
// as the sql contains those tokens in some way
conn.Execute(sql, new { setItem.MinPrice, setItem.MaxPrice });

The bad fix - do not use - would be to supply the literal "null" in your concatenation. There are many many reasons not to do this:

// bad code; do not use
(setItem.MinPrice.ToString() != "") ? setItem.MinPrice.ToString() : "null",
(setItem.MaxPrice.ToString() != "") ? setItem.MaxPrice.ToString() : "null"

Upvotes: 2

Related Questions