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

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

CodePudding user response：

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. 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 will be constructed concatenating a string.
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());

CodePudding user response：

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