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

Solution 1

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.

Solution 2

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);

Solution 3

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

Solution 4

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.

View more solutions