0

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.

Improve this question
1
  • You still need to use a query parameter, even for an int, just like with the @methodID parameter. Commented May 21, 2023 at 1:34

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Needs more supporting information
2

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.

Improve this answer

1 Comment

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

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;
Improve this answer

3 Comments

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.
That's got SQL injection right there
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.