Query with C# with a list as parameter

Question

I'm want to refactor the following statement to change the where businessID = something to a statement where I provide a list of strings which contain all possible Ids e.g. where businessID in List. As now the query is executed in a for loop for each Id, which I'm guessing is not really performant. I can't seem to find information on how to use a List of strings as a parameter in a prepared statement.

using (SqlConnection myConnection = new SqlConnection("Data Source=.\\SERVER;Initial Catalog=DB;Integrated Security=True;TrustServerCertificate=True;User Instance=False"))
using (SqlCommand myCommand = myConnection.CreateCommand())
{
    myConnection.Open();
    myCommand.CommandText = "SELECT BusinessName FROM Businessess WHERE BusinessID = @Param2";
    myCommand.Parameters.AddWithValue("@Param2", myParam2);
    using (SqlDataReader reader = myCommand.ExecuteReader())
    {
        if (reader.Read())
        {
            string businessName = reader.GetString(reader.GetOrdinal("BusinessName"));
            MessageBox.Show(businessName);
        }
        else
        {
            MessageBox.Show(string.Format("Sorry, no business found with id = {0}", myParam2));
        }
    }
}

[It is recommended to not use AddWithValue().](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — BCdotWEB, Feb 18 '15 at 07:34
possible duplicate of [Pass Array Parameter in SqlCommand](http://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand) — BCdotWEB, Feb 18 '15 at 07:35

score 0 · Answer 1 · edited Apr 13 '17 at 12:42

0

You can use IN instead of equality, by adding comma separated ids in @Param2

"SELECT BusinessName FROM Businessess WHERE BusinessID IN(@Param2)";

IN Determines whether a specified value matches any value in a subquery or a list.

As @Prescott suggested you the way to make a comma separated string from list you can have your AddWithValue like

myCommand.Parameters.AddWithValue("@Param2", String.Join(",", list));

As a side not try using stored procedure instead of inline queries, see comparison Stored procedures vs. inline SQL.

edited Apr 13 '17 at 12:42

Community

1
1

answered Feb 18 '15 at 07:33

Adil

146,340
25
209
204

1

I will add, if you have list `list`, you can do `String.Join(",", list);` to create a comma delimited string to pass into your parameter – Prescott Feb 18 '15 at 07:34
@Prescott isn't that prone to sql injection? – Lucas Kauffman Feb 18 '15 at 07:35
1

Naw, you set the parameter to the comma separated id's (string), this is a parameter - so it's escaped properly. – Prescott Feb 18 '15 at 07:37

yazan · Answer 2 · 2015-02-18T13:42:36.003

0

first create convert your list to comma separated string

string commaSeparatedList = yourlist.Aggregate((a, x) => a + ", " + x);

change the (=) to (In) and put the @Param between parenthesis

myCommand.CommandText = "SELECT BusinessName FROM Businessess WHERE BusinessID IN ( @Param2 )";

add the string (commaSeparatedList ) as parameter to your command

myCommand.Parameters.AddWithValue("@Param2", commaSeparatedList );

edited Feb 18 '15 at 13:42

answered Feb 18 '15 at 07:40

yazan

1
1

Instead of `Aggregate` you can use `string.Join(", ", yourlist)`. – Jeppe Stig Nielsen Feb 18 '15 at 08:27
Welcome to Stack Overflow! Could you please [edit] in an explanation of why this code answers the question? Code-only answers are [discouraged](http://meta.stackexchange.com/questions/148272), because they don't teach the solution. – Scimonster Feb 18 '15 at 12:55

Query with C# with a list as parameter

2 Answers2