How to convert SqlCommand query to use parameters built using concatenated strings including variables using ternary operators

Question

this is my first question, hopefully I do things correctly. Also to preface, I'm a programming noob tasked with developing and fixing some code where I work since the programmer we've had until now left. And if done quite well for now with my limited knowledge.

I'm trying to fix as much as possible the structure of some of the code in various programs, and part of it is converting all (or most) the SqlCommand to use parameters. Where I'm stuck is when the code is calling string variables using the ternary operator (?:) and using multiple of those strings concatenated to build the full SQL command string.

For example:

string RecogidosCoresString    = seeCORES ? " and [PICKUPTYPE] = 2 " : " ";
string RecogidosSinCoresString = seeSINCORES ? " and [PICKUPTYPE] <> 2 " : " ";
string RecogidosHolds          = seeHOLDS ? " and [PICKUPSTATUS] = 1 " : " and [PICKUPSTATUS] > 1 ";

string stringreadHeader = "SELECT * FROM [RecogidosHeader] WHERE [ENTRYDATE] >= @EntryDate1 AND [ENTRYDATE] < @EntryDate2 AND ([PICKUPNMBR] = @RecogidoID OR CUSTNMBR = @CustNumb )";

SqlCommand readHeader = new SqlCommand(stringreadHeader + RecogidosHolds + RecogidosCoresString + RecogidosSinCoresString + "Order By [PICKUPNMBR] ASC", AppsConnect);

readHeader.Parameters.Add("@EntryDate1", SqlDbType.DateTime).Value = dateTimePicker1.Value.ToShortDateString();
readHeader.Parameters.Add("@EntryDate2", SqlDbType.DateTime).Value = dateTimePicker2.Value.AddDays(1).ToShortDateString();
readHeader.Parameters.Add("@RecogidoID", SqlDbType.VarChar, 50).Value = textBox1.Text;
readHeader.Parameters.Add("@CustNumb", SqlDbType.VarChar, 50).Value = textBox1.Text;

As you can see, I've been able to replace a lot of the command with parameters, I just don't know how to implement it with the other string variables. The command above works as is, but if possible, I'd like to replace the rest of the strings with parameters instead of doing the concatenating I have currently. I have other applications using similar structures, but hopefully if I can see how to fix this one I'll be able to apply it to the rest.

Not sure if the way I have been going about it is good current practice, but I can't over-complicate it either.

The string variables get their values depending on some checkboxes' status.

Any help would be appreciated. I've seen similar questions, but I haven't been able to grasp the solutions properly, so hopefully with a personal example I'll be able to make it work.

Or, maybe it's fine as it is and I shouldn't change it?

Edit: Did a preliminary testing of the answer @pwilcox gave and it seems to be working, at the very least the SQL query is going through and I have no errors, and the results seem like the ones I'm expecting. If correct, then hopefully now I'll be able to apply this logic to the other situations that need improving.

Thanks!

Answer 1

Handle the different possibilities of seeCORES, seeSINCORES and seeHOLDS with or statements inside the SQL statement itself. In reading below it may help to remember that in sql server, and is processed first before or.

string stringreadHeader = @"
    select      * 
    from        recogidosHeader
    where       entrydate >= @EntryDate1 
    and         entrydate < @EntryDate2 
    and         (pickupnmbr = @RecogidoID or custnmbr = @CustNumb)

    and         (@seeCORES = 1 and pickuptype = 2 or @seeCORES = 0)
    and         (@seeSINCORES = 1 and pickuptype <> 2 or @seeSINCORES = 0)
    and         (
                       @seeHOLDS = 1 and pickupstatus = 1
                    or @seeHOLDS = 0 and pickupstatus > 1
                )

    order by    pickupnmbr
";

...
readHeader.Parameters.Add("@seeCORES", SqlDbType.Bit).Value = seeCORES ? 1 : 0;
readHeader.Parameters.Add("@seeHOLDS", SqlDbType.Bit).Value = seeHOLDS ? 1 : 0;

I don't know if you need the ? 1 : 0 part, conversion from boolean to bit may just work with the variables as they are.

Just to note though, in your original code, I believe setting seeCORES and seeSINCORES both to true will give very similar output to setting them both to false. The only difference will be that setting them both to false will give MORE records if there are any null values in pickuptype. Is this the intended behavior? (rhetorical question).