c# parameterized query not recognized

Question

I have a query that is expected to return a few rows but is not returning any results in code. I have some value from an arraylist that I'm wrapping around single quotes and comma to be use in the 'in' operator.

Am I doing something wrong here?

AseCommand cmd = new AseCommand("select * from Customer where ID in (@parmCustId)", conn);
cmd.Parameters.AddWithValue("@parmCustId", string.Join(",", arrList.Cast<string>().Select(x => string.Format("'{0}'", x))));
using (AseDataReader dr = cmd.ExecuteReader())
{
   if (dr.HasRows)
   {
      //do something
   }
}

Note that the accepted answer to the duplicate is not necessarily the best. Look at all of the answers ans see which is best for you. — D Stanley, Dec 30 '17 at 16:30

score 1 · Answer 1 · answered Dec 30 '17 at 16:24

It's really stupid what you have to do in this situation. You have to add a parameter for each value in the IN list.

So your query would end up something like this:

SELECT * FROM Customer WHERE ID in (@parmCustId1, @parmCustId2, @parmCustId3 ... @parmCustId<N>)

Then your code would look something like this:

var query = "select * from Customer where ID in (@parmCustId)";
using (AseCommand cmd = new AseCommand("", conn))
{
    var replacement = "";
    for (int i = 0; i < arrList.Length; i++)
    {
        var id = arrList[i];
        var p = "@parmCustId" + i.ToString();
        cmd.Parameters.AddWithValue(p, id);
        replacement += p;
        if (i != arrList.Length - 1)
        {
            replacement += ",";
        }
    }

    cmd.CommandText = query.Replace("@parmCustId", replacement);
    using (AseDataReader dr = cmd.ExecuteReader())
    {
        if (dr.HasRows)
        {
            //do something
        }
    }
}

There's also some libraries that will support this. For example, Dapper, written by the folks here at StackOverflow, will support this with ease. It's also a super fast and efficient object relational mapper (ORM) that can make some of the DataReader logic simpler if you're putting into an object. You can get it on NuGet.

It's not stupid: if databases dug into the parameter values passed and broke out/parsed them as if they were sql then they wouldn't offer any protection against injection attacks! :/ kudos for your example of adding N number of parameters and then adding a value for each though — Caius Jard, Dec 30 '17 at 16:27
Eh.. You're correct. Poor choice of words, but you get what I'm saying. Inconvenient would be more accurate. — Cameron, Dec 30 '17 at 16:29
I agree, though anyone who puts sql into a string in a Hutton click handler is arranging the "rod of inconvenience" for their own back :) . Entity framework et al exist for a good reason,. No one should be writing mundane sqls like this, in their code any more — Caius Jard, Dec 30 '17 at 16:30

score 0 · Answer 2 · answered Dec 30 '17 at 16:24

Here's what you wanted to run:

SELECT * FROM table WHERE ID IN(1,2,3,4)

Here's what you actually ran:

SELECT * FROM table WHERE ID IN ('1,2,3,4')

If you want to parameterise an IN, you'll have to put N number of parameters in, and give a value to each:

SELECT * FROM table WHERE ID IN (@p1, @p2, @p3, @p4)

//c# code here to populate 4 named parameters with an id each

c# parameterized query not recognized

2 Answers2