How to use SqlParameter to define a complete conditional statement where after'where ' ？

Question

I want to define a complete SQL statement condition after where through the linking implementation of string, because I am not sure how many conditions after where there are.

for (int i = 0; i < listView2.Items.Count; i++)
{
    if (!is_first)
    {
        para += "maccount" +" "+ "=" + listView2.Items[i].Text;
        is_first = true;
    }
    else
    {
        para += " or "  + "maccount"+"="+ listView2.Items[i].Text;
    }
}

MessageBox.Show(para);

string sql3 = "select maccount,msum from pharmaceutical_stocks where @para";

SqlParameter[] parameters3 = new SqlParameter[]
{
    new SqlParameter("@para",para)
};

DataTable dt = sqlcontent.dt(sql3, parameters3);

I want to find data in the database by the information saved in each item in listview2。

But I get this exception:

System.Data.SqlClient.SqlException: An expression of non-Boolean type is specified in the context in which the condition should be used (near '@para').

You should use the IN clause. Parameterize the IN clause, while doable, isn't easy. See: https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause#duplicate=0 — Steve, Nov 23 '22 at 12:52
You are not using parametrization correctly. The @para should be the variable. Basically your where cluse is just a strin "where 'para'" What you are building with para is dynamic SQL. Try string sql3 = "select maccount,msum from pharmaceutical_stocks where " + para — Søren Kongstad, Nov 23 '22 at 13:00
Parameters are *parameters*, not code or string interpolation placeholders. They're used to pass values to the compiled query, not modify the query itself. If you want to retrieve rows for a specific `macaccount` the query should be `... WHERE macaccount=@account`. In this case `@account` is a parameter. When the compiled query gets executed the database engine will pass the parameter value to it — Panagiotis Kanavos, Nov 23 '22 at 13:08
Perhaps a table type parameter would be a better choice here. — Thom A, Nov 23 '22 at 13:24

Steve · Accepted Answer · 2022-11-24T08:18:11.727

The code above cannot work!.
Parameters cannot be used to replace blocks of text within the query, whether the text is a column name, a table name, operator or some combination of the previous elements.

They can be used only to transfer values to the database engine where they are properly used with the query text and the placeholders inside that query without a text-replace operation. So instead of trying to build a series of OR statements around the maccount field, you should use the IN clause and build an array of parameters. The single parameters placeholders can be constructed by code internally concatenating a string without worrying about Sql Injection.
At the end you insert the parameters placeholders (not the values) in the query text and pass the list with all defined parameters to your method

StringBuilder inText  = new StringBuilder();
List<SqlParameter> prms = new List<SqlParameter>();
for(int i = 0; i < listView2.Items.Count; i++)
{
    SqlParameter p = new SqlParameter("@p" + i, SqlDbType.NVarChar);
    p.Value = listView2.Items[i].Text;
    prms.Add(p);
    inText.Append($"@p{i},");
}
if(inText.Length > 0) inText.Length--;
string sql3 = $@"select maccount,msum 
                from pharmaceutical_stocks 
                where maccount in({inText.ToString()})";
DataTable dt = sqlcontent.dt(sql3, prms.ToArray());

thank you very much!but I get another exception :System. Data. SqlClient. SqlException: Syntax error near "." — yoga, Nov 23 '22 at 14:17
@yoga my bad, forgotten an $ before the query text. See the updated code. _string sql3 = $@"select maccount,msum ....._ — Steve, Nov 23 '22 at 15:38

vadim · Answer 2 · 2022-11-23T13:28:15.457

-1

The idea behind my code is same as Steve's answer but my suggestion to use string.Join for building params string. Assuming parameters is SqlParameter[]

const string sql = "select maccount, msum from pharmaceutical_stocks where maccount in ({0})";
string sqlCommand = string.Format(sql, string.Join(",", parameters.Select(p => p.ParameterName)));

This way you don't worry about comma in the end. The only thing you still want to check if there are indeed some items in listView2

edited Nov 23 '22 at 13:28

answered Nov 23 '22 at 13:21

vadim

1,698
1
10
19

2

That's how SQL injection attacks happen. What one of those non-parameters contained `'); drop table pharmaceutical_stocks;--` ? – Panagiotis Kanavos Nov 23 '22 at 13:21
No, its not. Injected are param Name and not the value. The result query is like `select maccount, msum from pharmaceutical_stocks where maccount in ('@account1','@account2')` – vadim Nov 23 '22 at 13:23
2

In that case the code doesn't work at all. Those are strings. Not parameters. `'@account2'` is a string value starting with `@` followed by `a` etc. – Panagiotis Kanavos Nov 23 '22 at 13:23
Obviously you need to use this command text with parameters array like Steve does `DataTable dt = sqlcontent.dt(sqlCommand, parameters.ToArray());` – vadim Nov 23 '22 at 13:26
Ah, I see. I need to remove some quotes – vadim Nov 23 '22 at 13:27

How to use SqlParameter to define a complete conditional statement where after'where ' ？

2 Answers2