Add string array to SQL query

Question

I have a string array which consists of identifiers. I want to get some values from SQL using these identifiers . Is there a way of adding them with a string value to SqlCommand parameters?

I want to create a query like:

select CaseList from MasterReportData where Id = 1 OR Id = 2 OR Id = 3

This is my C# code:

public static List<string> GetCaseList(string[] masterIdList)
    {

        try
        {
            string query = "  select CaseList from MasterReportData where @masterId";
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("masterId", ***);
            conn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add(reader[0].ToString());
                }
            }
            conn.Close();
        }
        catch (Exception e)
        {
            var err= 0;
        }
        return list;
    }

Possible duplicate of [SQL Parameters Inside A Loop](https://stackoverflow.com/questions/24561055/sql-parameters-inside-a-loop) — Rufus L, Sep 12 '17 at 19:48
See [Pass Array Parameter in SqlCommand](https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand) — Ton Plooij, Sep 12 '17 at 19:54
Use `SqlParameter` and [Table-Valued Parameter Types](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters). All other approaches are just "hacky workarounds" — Fabio, Sep 12 '17 at 20:05
On something like this I would say you need to maybe re-evaluate why you are using cmd.parameters. This is useful when you are shipping in values you are going to store in the database. This is less useful in your case. I would just use a string builder to create the query on the fly without the use of parameters. — Carter, Sep 12 '17 at 20:16
True. Looks like the @wdomains answer is about right for using params, though a smidge hard to read. — Carter, Sep 12 '17 at 20:39
You could consider using a light-weight ORM - they make this class of problem a little simpler. As long as you don't have **heaps** of IDs (> 2000) then you could use https://stackoverflow.com/a/6957584/34092 to build an IN clause (which is equivalent to using ORs). — mjwills, Sep 12 '17 at 21:36

score 1 · Answer 1 · answered Sep 12 '17 at 19:49

1

There are many different ways you can go about doing this but I prefer to create a temp table of possible values. That way you can do something like

select CaseList from MasterReportData where Id IN(select Id from tempTable)

answered Sep 12 '17 at 19:49

Sam Marion

690
1
4
17

3

There actually only one "correct" approach - by using `SqlParameter` and [Table-Valued Parameter Types](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters) – Fabio Sep 12 '17 at 20:02
1

In query will fetch all result subQuery. I think you will use Exist query. Exists faster then In query. – Hüseyin Burak Karadag Sep 12 '17 at 20:21
Good point both of you. To be honest its been awhile since I've done some serious SQL so admittedly I'm a bit rusty! – Sam Marion Sep 12 '17 at 20:27

score 0 · Answer 2 · answered Sep 12 '17 at 20:40

The best approach (with sql optimization) would be:

Create your Type:

CREATE TYPE dbo.IntTTV AS TABLE  
( Id int )

Your Ids:

var ids = new List<int>
{
  1,
  2,
  3,
}

Create a schema:

var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.Int) // I think it's Int
}.ToArray();

Create the table in C#

var table = ids
  .Select(i => 
  {
    var row = new SqlDataRecord(tableSchema);
    row.SetInt32(0, i);
    return row;
  })
  .ToList();

Create the SQL Parameter

var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@Ids";
parameter.Value = table;
parameter.TypeName = "dbo.IntTTV";

var parameters = new SqlParameter[1]
{
  parameter
};

Slightly change your query (this is just an example:)

string query = "select mrd.CaseList from MasterReportData mrd"
  + " inner join @ids i on mrd.Id = i.id";

score -1 · Accepted Answer · answered Sep 12 '17 at 20:18

    public static List<string> GetCaseList(string[] masterIdList)
    {
        List<string> list = new List<string>();
        try
        {
            string query = "select CaseList from MasterReportData where ";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                int i = 0;
                SqlCommand cmd = new SqlCommand(query, conn);
                for(i = 0; i < masterIdList.Length; i++)
                {
                    var parm = "@ID" + i;
                    cmd.Parameters.Add(new SqlParameter(parm, masterIdList[i]));
                    query += (i > 0 ? " OR " : "") + " Id = " + parm;
                }
                cmd.CommandText = query;
                //cmd.Parameters.AddWithValue("masterId", ***);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(reader[0].ToString());
                    }
                }
            }
        }
        catch (Exception e)
        {
            e.ToString();
        }
        return list;
    }

SQL Server will allow up to 2100 parameters so you may wish to verify that `masterIdList.Length` is > 0 and <= 2100. — mjwills, Sep 12 '17 at 21:38
Very true - the above is not the ideal way to do this if the parameters list will be long — wdomains, Sep 13 '17 at 00:10

Add string array to SQL query

3 Answers3