Add a new where clause using AddWithValue method

Question

My question is quite simple but still, I'm searching over 2 hours to find the solution. In c# I have a where clause: where ?hospitalID and then I am binding this value (hospitalID) with my condition:

 cmd.Parameters.AddWithValue("?hospitalID", (filters.hospitalID != 0) ? "operation.hospitalID=" + filters.hospitalID : "true");

So what I am saying here is: if the variable filters.hospitalID is not zero, go ahead and create a condition (where operation.hospitalID=filters.hospitalID). Otherwise "nullify" the condition (where true).

If I change manually the string where operation.hospitalID=2 it works. But with the AddWithValue method, it simply doesn't work.

replace `true` with `1=1` because `where true` doesn't work in SQL. — Majid Akbari, Aug 14 '18 at 13:12
Btw. some people have strong opinions about `AddWithValue`; so this might interest you: [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Corak, Aug 14 '18 at 13:15

score 2 · Accepted Answer · answered Aug 14 '18 at 13:26

You can pass SQL Code inside parameters values, but the SQL engine will not treat it as code - so it will not run.
That is the reason why using parameters protects you from SQL Injection.
For a more detailed explanation, read this SO post.

However, this does not mean you can't ignore a specific value passed in a parameter, you just need to change your SQL code a bit:

SELECT * -- Note that best practice is to specify the columns list, not *
FROM operation
WHERE hospitalID = @hospitalID
OR @hospitalID IS NULL

Please note I've changed the 0 to null - since the 0 value might be a valid value (even if hospitalID starts with 1, you might want to use the same technique for some other column where 0 is a valid value).

Amazing!! That makes absolute sense! I've changed the code to follow your suggestion and it works perfectly! Thank you sooooo much! — Dimitris Selalmazidis, Aug 14 '18 at 13:57

Antoine V · Answer 2 · 2018-08-14T13:29:13.950

I think you have the confused about using AddWithValue

Supposed this is my query

string sql = "SELECT * FROM SomeTable WHERE hospitalID=@ParamValue";

To replace ParamValue, I can do :

using (SqlConnection conn = new SqlConnection())
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.Add(new SqlParameter("ParamValue", someValue));
    da.Fill(ds);
}

That means, in your case, you just pass the value, not the statement operation.hospitalID=filters.hospitalID.

So the code likes:

string sqlParam = "SELECT * FROM SomeTable WHERE hospitalID=@ParamValue";
string sqlWithoutParam = "SELECT * FROM SomeTable";

To replace ParamValue, I can do :

    if(filters != null && filters.hospitalID != 0)
    {
        using (SqlConnection conn = new SqlConnection())
        using (SqlCommand cmd = new SqlCommand(sqlParam, conn))
        {
            cmd.Parameters.Add(new SqlParameter("ParamValue", filters.hospitalID ));
            da.Fill(ds);
        }
    }
    else
    {
         using (SqlConnection conn = new SqlConnection())
         using (SqlCommand cmd = new SqlCommand(sqlWithoutParam, conn))
         {
            da.Fill(ds);
         }
    }

Add a new where clause using AddWithValue method

2 Answers2