C# SQL parameter query with in

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.

Answer 2

I like to do stuff like this in two methods.

public void ReadDatabase(string[] values)
{
    foreach (var value in values)
    {
        using (var rd = GetData(value))
        {
            if (!rd.Read())
                throw new OMGException("FAILED!");

            Console.WriteLine(rd["UserId"].ToString());
        }
    }
}

public IDataReader GetData(string value)
{
    using(var cm = _connectionWhatever.CreateCommand())
    {
        cm.CommandText = @"
            Select UserId
            From MyTable
            Where UserName = @User
        ";
        cm.CommandType = CommandType.Text;
        cm.Parameters.AddWithValue("User", value);
        return cm.ExecuteReader();
    }
}

This is just to give you some ideas. Hopefully this will help.

Answer 3

Change the code to this

    var myCommand = new SqlCommand();
    int i = 0;
    string param = string.Empty;

    foreach (string username in usernames)
    {
        string paramName = string.Format("@username{0}", i);
        myCommand.Parameters.Add(paramName, SqlDbType.NVarChar);
        myCommand.Parameters[paramName].Value = username;
        param += string.Format("  (username={0}) or", paramName);

        i++;
    }
    param = param.Substring(0, param.Length - 2);
    return _dbConnection.ExecuteQuery("Select * from customer where " + param);

Answer 4

This could be done by passing an XML data type to the Query and searching within that.

declare @x xml
set @x = '<IDs><ID>1</ID><ID>2</ID></IDs>'

SELECT ID.value('.', 'int') AS ID
FROM @x.nodes('/IDs/ID') as IDS(ID)

So in this instance you could do:

var myCommand = new SqlCommand();
myCommand.Parameters.Add("@usernames", SqlDbType.Xml);
string usernames = "<Usernames>"
foreach (string username in usernames){
   usernames+= String.Format("<username>{0}</username>", username);
}
usernames += "</Usernames>"
myCommand.Parameters["@usernames"].Value = usernames;
return _dbConnection.ExecuteQuery("Select * from customer where username in (SELECT
    username.value('.', 'nvarchar') AS Username
    FROM @x.nodes('/Usernames/Username') as Usernames(Username))");

However, syntax may need checking

Answer 5

You cant do it exactly the way you are thinking with the loop because you would be adding the same parameter multiple times with different values, however this may be helpful:

http://support.microsoft.com/kb/555266 (its in VB but you can probably find a c# example or translate)

The way I would solve this myself is to make this into a stored procedure as its just good practice (benefits such as added security, efficiency, code reuse etc), then have your procedure accept a parameter of XML. In your C# application convert the Usernames collection to XML then pass that into the SP.

Answer 6

Well, if you really want to you could do this entirely through string manipulation.

string sql = "Select * from customer where username in (";

string comma = "";
foreach(string s in usernames)
{
   sql += comma + "'" + CleanSqlParameter(s) + "'";
   comma = ", ";
}

return _dbConnection.ExecuteQuery(sql);

This would be messy! But if you feel like you need to construct the query entirely in C#, here's a way to do it. I've had success with this method.