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

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 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.

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

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;