Inline If-else statement for an SQL query in C#

Question

This might be silly, but I'm trying to reduce an if-else statement to a single string using an inline if-statement for an SQL query:

private void Populate_compoundDGV(int splitID = 0)
{
    //sql
    if (splitID==0)
        {
        string sql = "SELECT split_compound_id, " +
                        "CONCAT(nanogen_code, ' ', common_name) AS Compound, " +
                        "split_label AS Split, " +
                        "LOD, " +
                        "LOQ " +
                        "FROM compound INNER JOIN split_compound ON compound.compound_id = split_compound.compound_id INNER JOIN split ON split_compound.split_id = split.split_id " +
                        "WHERE method_id = @methodID " +
                        "ORDER BY nanogen_code";
    }
    else
    {
        string sql = "SELECT split_compound_id, " +
                        "CONCAT(nanogen_code, ' ', common_name) AS Compound, " +
                        "split_label AS Split, " +
                        "LOD, " +
                        "LOQ " +
                        "FROM compound INNER JOIN split_compound ON compound.compound_id = split_compound.compound_id INNER JOIN split ON split_compound.split_id = split.split_id " +
                        "WHERE method_id = @methodID " +
                    $"AND split.split_id = {splitID} " +
                        "ORDER BY nanogen_code";
    }
}

I know T-SQL has a Case-statement. Would that work here? Can this be done with parameters or is there something simpler? I used to use an IIF statement in VB.Net.

You still need to use a query parameter, even for an int, just like with the @methodID parameter. — Joel Coehoorn, May 21 '23 at 01:34

Joel Coehoorn · Answer 1 · 2023-05-22T00:55:49.057

You can do this:

private void Populate_compoundDGV(int splitID = 0)
{
    var splitPredicate = splitID == 0 ? "" : "AND s.split_id = @splitID";

    string sql = $@"
        SELECT split_compound_id, 
            CONCAT(nanogen_code, ' ', common_name) AS Compound, 
            split_label AS Split, 
            LOD, 
            LOQ 
        FROM compound c
        INNER JOIN split_compound sc ON c.compound_id = sc.compound_id 
        INNER JOIN split s ON sc.split_id = s.split_id 
        WHERE method_id = @methodID 
           {splitPredicate}
        ORDER BY nanogen_code";
}

But you also need to check again when setting the query parameters. NEVER substitute data directly into an SQL command, not even for integers.

I implemented this first and it worked great. I ultimately went with Sir Rufo's approach for no particular reason - pure SQL, I guess. Learned a lot, though. Thank you. — , May 21 '23 at 04:33

score 1 · Accepted Answer · answered May 21 '23 at 02:35

You can handle all with a single SQL statement and parameters

SELECT split_compound_id, 
       CONCAT(nanogen_code, ' ', common_name) AS Compound, 
       split_label AS Split, 
       LOD, 
       LOQ 
FROM compound 
INNER JOIN split_compound ON compound.compound_id = split_compound.compound_id 
INNER JOIN split ON split_compound.split_id = split.split_id 

WHERE method_id = @methodID

  AND ( 0 = @splitID OR split.split_id = @splitID )

ORDER BY nanogen_code

Needs more supporting information – Son of Man May 21 '23 at 06:36 — Son of Man, May 21 '23 at 06:36

abolfazl sadeghi · Answer 3 · 2023-05-21T10:58:13.743

It is better to do this with linq and EF linq-where-with-and-or-condition

If you want to do it with Tsql queries,

it is better to write the sql server as a Procedure and call it with ef working-with-stored-procedure-in-ef-core or ado.net how-to-call-a-stored-procedure-using-ado-net , DynamicsqlWithAdo.

But if you want to write in C#, it is better to write like this(This method is not recommended because it has security and maintenance and development problems More Expling)

string sqlselect = "SELECT split_compound_id, " +
                   "CONCAT(nanogen_code, ' ', common_name) AS Compound, " +
                   "split_label AS Split, " +
                   "LOD, " +
                   "LOQ " +
                   "FROM compound INNER JOIN split_compound ON compound.compound_id = split_compound.compound_id INNER JOIN split ON split_compound.split_id = split.split_id " +
                   "WHERE method_id = @methodID ";

string sqlorder = "ORDER BY nanogen_code";

string sql = sqlselect+(splitID == 0 ? "" : $"AND split.split_id = {splitID} ") + sqlorder;

I'm still new at this, but I think I need to investigate LINQ. The whole delegate and lambda thing is a bit intimidating. You've inspired me. Thank you. — , May 21 '23 at 04:36
If you have read the whole text of post, I gave another suggestion(EF and Proc). I said that if you insist on doing this, you can reduce the code with these codes.I Add Problem Last Ways — abolfazl sadeghi, May 21 '23 at 10:59

Inline If-else statement for an SQL query in C#

3 Answers3