Sql "IN" operator with using SqlParameter on varchar field

Question

I couldn't find how to use IN operator with SqlParameter on varchar column. Please check out the @Mailbox parameter below:

using (SqlCommand command = new SqlCommand())
{
    string sql =
    @"select    
         ei.ID as InteractionID,
         eo.Sentdate as MailRepliedDate
      from    
         bla bla
      where  
         Mailbox IN (@Mailbox)";
    command.CommandText = sql;
    command.Connection = conn;
    command.CommandType = CommandType.Text;                    
    command.Parameters.Add(new SqlParameter("@Mailbox", mailbox));                    
    SqlDataReader reader = command.ExecuteReader();
}

I tried these strings and query doesn't work.

string mailbox = "'abc@abc.com','def@def.com'"
string mailbox = "abc@abc.com,def@def.com"

I have also tried changed query Mailbox IN('@Mailbox')
and string mailbox = "abc@abc.com,def@def.com"

Any Suggestions? Thanks

Answer 1

That doesn't work this way.

You can parameterize each value in the list in an IN clause:

string sql =
  @"select    
         ei.ID as InteractionID,
         eo.Sentdate as MailRepliedDate
      from    
         bla bla
      where  
         Mailbox IN ({0})";
string mailbox = "abc@abc.com,def@def.com";
string[] mails = mailbox.Split(',');
string[] paramNames = mails.Select((s, i) => "@tag" + i.ToString()).ToArray();
string inClause = string.Join(",", paramNames);

using (var conn = new SqlConnection("ConnectionString"))
using (SqlCommand command = new SqlCommand(sql, conn))
{
    for (int i = 0; i < paramNames.Length; i++)
    {
        command.Parameters.AddWithValue(paramNames[i], mails[i]);
    }
    conn.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    { 
        // ...
    }
}

Adapted from: https://stackoverflow.com/a/337792/284240

Answer 2

Since you are using MS SQL server, you have 4 choices, depending on the version. Listed in order of preference.

1. Pass a composite value, and call a custom a CLR or Table Valued Function to break it into a set. see here.

You need to write the custom function and call it in the query. You also need to load that Assembly into you database to make the CLR accessible as TSQL.

If you read through all of Sommarskog's work linked above, and I suggest you do, you see that if performance and concurrency are really important, you'll probably want to implment a CLR function to do this task. For details of one possible implementation, see below.

2. Use a table valued parameter. see here.

You'll need a recent version of MSSQL server.

3. Pass mutiple parameters.

You'll have to dynamically generate the right number of parameters in the statement. Tim Schmelter's answer shows a way to do this.

4. Generate dynamic SQL on the client. (I don't suggest you actually do this.)

You have to careful to avoid injection attacks and there is less chance to benefit from query plan resuse.

dont do it like this.

---

One possible CLR implementation.

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class CLR_adam
{
    [Microsoft.SqlServer.Server.SqlFunction(
       FillRowMethodName = "FillRow_char")
    ]
    public static IEnumerator CLR_charlist_adam(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
       )
    {
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));
    }

    public static void FillRow_char(object obj, out SqlString item)
    {
        item = new SqlString((string)obj);
    }

    [Microsoft.SqlServer.Server.SqlFunction(
       FillRowMethodName = "FillRow_int")
    ]
    public static IEnumerator CLR_intlist_adam(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
       )
    {
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));
    }

    public static void FillRow_int(object obj, out int item)
    {
        item = System.Convert.ToInt32((string) obj);
    }


    public class SplitStringMulti : IEnumerator
    {
        public SplitStringMulti(char[] TheString, char[] Delimiter)
        {
            theString = TheString;
            stringLen = TheString.Length;
            delimiter = Delimiter;
            delimiterLen = (byte)(Delimiter.Length);
            isSingleCharDelim = (delimiterLen == 1);

            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #region IEnumerator Members

        public object Current
        {
            get
            {
                return new string(
                    theString,
                    lastPos,
                    nextPos - lastPos).Trim();
            }
        }

        public bool MoveNext()
        {
            if (nextPos >= stringLen)
                return false;
            else
            {
                lastPos = nextPos + delimiterLen;

                for (int i = lastPos; i < stringLen; i++)
                {
                    bool matches = true;

                    //Optimize for single-character delimiters
                    if (isSingleCharDelim)
                    {
                        if (theString[i] != delimiter[0])
                            matches = false;
                    }
                    else
                    {
                        for (byte j = 0; j < delimiterLen; j++)
                        {
                            if (((i + j) >= stringLen) || 
                                (theString[i + j] != delimiter[j]))
                            {
                                matches = false;
                                break;
                            }
                        }
                    }

                    if (matches)
                    {
                        nextPos = i;

                        //Deal with consecutive delimiters
                        if ((nextPos - lastPos) > 0)
                            return true;
                        else
                        {
                            i += (delimiterLen-1);
                            lastPos += delimiterLen;
                        }
                    }
                }

                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;

                if ((nextPos - lastPos) > 0)
                    return true;
                else
                    return false;
            }
        }

        public void Reset()
        {
            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #endregion

        private int lastPos;
        private int nextPos;

        private readonly char[] theString;
        private readonly char[] delimiter;
        private readonly int stringLen;
        private readonly byte delimiterLen;
        private readonly bool isSingleCharDelim;
    }
};

Answer 3

Why not just concactenate your mailbox variable with your sql string variable