C# SQL parameter query with in [duplicate]

Question

Possible Duplicate:
Parameterizing a SQL IN clause?

i have the follwing code:

var myCommand = new SqlCommand();
myCommand.Parameters.Add("@username", SqlDbType.NVarChar);
myCommand.Parameters["@username"].Value = username;
return _dbConnection.ExecuteQuery("Select * from customer where username = @username");

now i need to adapt the query for more values. I want to do something like this:

var myCommand = new SqlCommand();
foreach (string username in usernames){
  myCommand.Parameters.Add("@username", SqlDbType.NVarChar);
  myCommand.Parameters["@username"].Value = username;
}
return _dbConnection.ExecuteQuery("Select * from customer where username in @username");

Is that possible? How?

Thank you!

BR Stefan

Answer 1

Each parameter needs to be unique:

var paramNames = new List<string>();
var myCommand = new SqlCommand();
foreach (string username in usernames){
  var paramName = "@user" + paramNames.Count;
  myCommand.Parameters.Add(paramName, SqlDbType.NVarChar);
  myCommand.Parameters[paramName].Value = username;
  paramNames.Add(paramName);
}
return _dbConnection.ExecuteQuery("Select * from customer where username in (" + string.Join(",", paramNames) + ")");

You will get sql like this:

SELECT * from customer where username in (@user0, @user1, @user2)

It doesn't help much from a query plan caching standpoint, but you will get the benefits of not being vulnerable to sql injection attacks.