sql null in c# and null coalescing operator

Question

i have the following sql command

var Query = new SqlCommand(@"SELECT [file] FROM [ApplicationSettings] WHERE [id] = @id And username=@username", con);
Query.Parameters.AddWithValue("@id", fileName);
Query.Parameters.AddWithValue("@username", userName ?? //then what?! );

the userName is a string (NVARCHAR in the sql), sometimes it has a value and sometimes its set to null, so i have 2 cases

username='someUserName'
username=Null

how can i achieve this in the current syntax without if statements or any additional checking

you can try something like IsDBNull(userName)?UserName:"" while adding the parameter. not sure with syntax. but its a try — Shiva Saurabh
– Shiva Saurabh, Commented Dec 7, 2013 at 18:57
If you want to know how to set a parameter to null: command.Parameters.Add("@Parameter", SqlDbType.VarChar).Value = parameterValue ?? DBNull.Value; — adrianm
– adrianm, Commented Dec 8, 2013 at 11:03
@adrianm please try it and you will find out why it wont work — FPGA
– FPGA, Commented Dec 8, 2013 at 11:40

Allan S. Hansen · Accepted Answer · 2013-12-07 18:49:49Z

2

Try something like:

SELECT [file] FROM [ApplicationSettings] 
WHERE [id] = @id And username=ISNULL(@username,username)

If @username is NULL, it'll compare username to username which always will be true; and if it is not null, it'll then compare username = @username. You could also use a CASE for comparison, but I find the above syntax more clean myself.

answered Dec 7, 2013 at 18:49

Allan S. Hansen

4,09126 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

sql null in c# and null coalescing operator

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related