Using a ternary operator to fill a SqlCommand parameter

Question

I need to fill a parameter in a SqlCommand, but for some conditions I want to fill it with DBNull.Value and otherwise with a value.

What I need is that when the variable truckID == -1 then put DBNull.Value in the parameter, else put the value of truckID in it.

This is what I have tried:

using (SqlCommand command = new SqlCommand(sql.ToString()))
{
   command.CommandType = CommandType.Text;
   command.Parameters.AddWithValue("@TruckID", truckID == -1 ? DBNull.Value : truckID);

but the compiler tells me :

Error CS0173 Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DBNull' and 'int'

If I would write this:

command.Parameters.AddWithValue("@TruckID", truckID == -1 ? 0 : truckID);

then the compiler is happy. So it seems that for a ternary operator both possible values must be of the same type.

What would be the best way to do this ?

EDIT:

the working code for me is this :

command.Parameters.Add
    (new SqlParameter("@TruckID", SqlDbType.Int).Value = (import.TruckID == -1) ? (object)DBNull.Value : import.TruckID);

EDIT:
Actually the code above did not work after all.
At runtime I got this :

SqlParameterCollection only accepts non-null SqlParameter type objects, not DBNull objects

So I modified the code to this and that finally worked for me.

command.Parameters.Add
    (new SqlParameter("@PlaceID", SqlDbType.Int) { Value = (import.TruckID == -1) ? (object)DBNull.Value : import.TruckID });

They must be the same type or implicitly convert (IIRC). Just split it into actual if statements. — Broots Waymb, Jul 20 '17 at 13:33
`truckId == -1 ? (object) DBNull.value : truckID`. And don't use [`.AddWithValue`](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — Jeroen Mostert, Jul 20 '17 at 13:33
This is just *yet another* reason why you should **avoid** using `AddWithValue`! You should also check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Jul 20 '17 at 13:42

Rahul · Accepted Answer · 2017-07-25T10:26:19.010

3

You can try like this

SqlParameter param = new SqlParameter("@truckID", System.Data.SqlDbType.Int);
param.Value = (truckID == -1) ? DBNull.Value : truckID;
command.Parameters.Add(param);

Well one liner version would be

SqlParameter param = new SqlParameter("@truckID", System.Data.SqlDbType.Int) 
                          { Value = (truckID == -1) ? (object)DBNull.Value : truckID };
command.Parameters.Add(param);

Another version:

SqlParameter param = (truckID == -1) ? 
                       new SqlParameter("@truckID", System.Data.SqlDbType.Int) { Value = DBNull.Value } 
                      : new SqlParameter("@truckID", System.Data.SqlDbType.Int) { Value = truckID };
command.Parameters.Add(param);

edited Jul 25 '17 at 10:26

answered Jul 20 '17 at 13:37

Rahul

76,197
13
71
125

Yes but I want to keep it on one line of code – GuidoG Jul 20 '17 at 13:38
OK that is what I need thanks I just put the first line as parameter for the parameters.add() – GuidoG Jul 20 '17 at 13:41
@GuidoG Did this work? It should have the same problem you originally had and additionally the one liner version looks very odd to me. That shouldn't compile. It should be something like this: `SqlParameter param = new SqlParameter("@truckID", System.Data.SqlDbType.Int) { Value = (truckID == -1) ? (object)DBNull.Value : truckID }; – Romano Zumbé Jul 24 '17 at 15:14
@RomanoZumbé Yes I had to adapt the code a little to make it compile but the idea does works. I just edited the question and put the working one liner at the bottom – GuidoG Jul 24 '17 at 15:52
@RomanoZumbé The code did not work at runtime after all. Check the last edit in my question I have changed the code again and now it really worked – GuidoG Jul 25 '17 at 07:46
@GuidoG Thats what I expected. The code in this example simply can't compile – Romano Zumbé Jul 25 '17 at 07:48
@RomanoZumbé, you didn't seen my edit .. now it will compile fine. Thanks for pointing that – Rahul Jul 25 '17 at 10:02
@Rahul Absolutely not! `SqlParameter param = new SqlParameter("@truckID", System.Data.SqlDbType.Int).Value = (truckID == -1) ? (object)DBNull.Value : truckID;`. You can't access `Value` that way! – Romano Zumbé Jul 25 '17 at 10:04
@RomanoZumbé, hmm, missed it. anyway corrected now and as there is another one liner version. – Rahul Jul 25 '17 at 10:28

score 1 · Answer 2 · answered Jul 20 '17 at 13:33

1

You could explicitly cast DBNull.Value to object:

command.Parameters.AddWithValue("@TruckID", truckID == -1 ? (object)DBNull.Value : truckID);

answered Jul 20 '17 at 13:33

Romano Zumbé

7,893
4
33
55

Using a ternary operator to fill a SqlCommand parameter

2 Answers2