Pass Array Parameter in Sqlcommand

Pass Array Parameter in SqlCommand

You will need to add the values in the array one at a time.

var parameters = new string[items.Length];
var cmd = new SqlCommand();
for (int i = 0; i < items.Length; i++)
{
    parameters[i] = string.Format("@Age{0}", i);
    cmd.Parameters.AddWithValue(parameters[i], items[i]);
}

cmd.CommandText = string.Format("SELECT * from TableA WHERE Age IN ({0})", string.Join(", ", parameters));
cmd.Connection = new SqlConnection(connStr);

UPDATE: Here is an extended and reusable solution that uses Adam's answer along with his suggested edit. I improved it a bit and made it an extension method to make it even easier to call.

public static class SqlCommandExt
{

    /// <summary>
    /// This will add an array of parameters to a SqlCommand. This is used for an IN statement.
    /// Use the returned value for the IN part of your SQL call. (i.e. SELECT * FROM table WHERE field IN ({paramNameRoot}))
    /// </summary>
    /// <param name="cmd">The SqlCommand object to add parameters to.</param>
    /// <param name="paramNameRoot">What the parameter should be named followed by a unique value for each value. This value surrounded by {} in the CommandText will be replaced.</param>
    /// <param name="values">The array of strings that need to be added as parameters.</param>
    /// <param name="dbType">One of the System.Data.SqlDbType values. If null, determines type based on T.</param>
    /// <param name="size">The maximum size, in bytes, of the data within the column. The default value is inferred from the parameter value.</param>
    public static SqlParameter[] AddArrayParameters<T>(this SqlCommand cmd, string paramNameRoot, IEnumerable<T> values, SqlDbType? dbType = null, int? size = null)
    {
        /* An array cannot be simply added as a parameter to a SqlCommand so we need to loop through things and add it manually. 
         * Each item in the array will end up being it's own SqlParameter so the return value for this must be used as part of the
         * IN statement in the CommandText.
         */
        var parameters = new List<SqlParameter>();
        var parameterNames = new List<string>();
        var paramNbr = 1;
        foreach (var value in values)
        {
            var paramName = string.Format("@{0}{1}", paramNameRoot, paramNbr++);
            parameterNames.Add(paramName);
            SqlParameter p = new SqlParameter(paramName, value);
            if (dbType.HasValue)
                p.SqlDbType = dbType.Value;
            if (size.HasValue)
                p.Size = size.Value;
            cmd.Parameters.Add(p);
            parameters.Add(p);
        }

        cmd.CommandText = cmd.CommandText.Replace("{" + paramNameRoot + "}", string.Join(",", parameterNames));

        return parameters.ToArray();
    }

}

It is called like this...

var cmd = new SqlCommand("SELECT * FROM TableA WHERE Age IN ({Age})");
cmd.AddArrayParameters("Age", new int[] { 1, 2, 3 });

Notice the "{Age}" in the sql statement is the same as the parameter name we are sending to AddArrayParameters. AddArrayParameters will replace the value with the correct parameters.

Passing a dynamic array of SqlParameters into a SQL string IN clause

Let SQL Server do all dirty work. Something like this.

var SQL0 = "SELECT * FROM [database] WHERE material_id IN (select value from string_split('{0}',','))";

var idList = new List<int> { 11, 53, 125};
int[] idListArray = idList.ToArray();
sqlCommand.CommandText = String.Format(SQL0, string.Join(",", idListArray));
// now execute the command

EDIT

More secure and performat way.

var SQL0 = "SELECT * FROM [database] WHERE material_id IN (select value from string_split(@ids,','))";

var idList = new List<int> { 11, 53, 125};
int[] idListArray = idList.ToArray();
sqlCommand.CommandText = SQL0;
sqlCommand.Parameters.Add("@ids", SqlDbTypes.VarChar, -1).Value = string.Join(",", idListArray);
// now execute the command

Passing Array Parameter to SQL command javascript

Assuming that your string values do not contain embedded single quotes, you use map and join to generate the IN list, as follows:

let lor_in_list = lor_text_Array.map(function (a) { return "'" + a + "'"; }).join(",");
let sql_query = 'SELECT * FROM SUBMISSION WHERE REV IN (' + lor_in_list + ')ORDER BY ID DESC';

To handle possible embedded single quotes:

let lor_in_list = lor_text_Array.map(function (a) { 
    return "'" + a.replace("'", "''") + "'"; 
}).join(",");

How to pass string array in SQL parameter to IN clause in SQL

Introduction: Even though the OP already accepted an answer, I thought it would be better to share my experience, because I belive the approach I'm about to show is better then the one accepted.

I find that the best way to pass Arrays to sql server database is using a user defined table type and c# DataTable.
In your case, since you want to pass a string array of one dimension, it's fairly easy:

First you need to create a user defined table type in your database:

 CREATE TYPE dbo.StringArray As Table (
    StringItem varchar(50) -- you can use any length suited for your needs
)

Then you need to create a datatable in your c# code:

DataTable dt = new DataTable();
dt.Columns.Add("StringItem", typeof(System.String));

Then change your stored procedure to accept this data type as a parameter:

ALTER proc [dbo].[sp_Accessories]
(  
@Mode varchar(50)=null,
@id int=null,
@brand varchar(50)=null,
@department varchar(MAX)=null,
@season varchar(50)=null,
@groupname varchar(MAX)=null,
@styles varchar(50)=null,
@combo dbo.StringArray Readonly=null, -- NOTE THIS CHANGE
@combo_color nvarchar(max)=null,
)
as
if @Mode='getsizewise'
begin
select Sizes,SUM(Quantity) from tbl_SizeBreakup where (Brand=@brand) and
(Department=@department) and (Season=@season) and (Group_Name=@groupname) and (Style=@styles) 
and (Combo_Color=@color) and (Total_Add_Qty='Total Quantity') 
and comboColumn in(select StringItem from @Combo) -- NOTE THIS CHANGE
Group By Sizes
end

Then you need to convert the string array to a dataTable in your c# code.

foreach (string s in YourStringArray) {
    string[] temp = {s};
    dt.Rows.Add(temp);
}

Add the DataTable as a parameter to the stored procedure:

System.Data.SqlClient.SqlParameter sp = new Data.SqlClient.SqlParameter();
sp.SqlDbType = SqlDbType.Structured;
sp.Value = dt;
sp.ParameterName = "@Combo";
cmd.Parameters.Add(sp);

Build and run.

This approach should have better performance then using an sql user defined function, and also can be used for different data types. this is one of the best reasons to use it:
Consider a scenario where you need to pass an array of Dates: the csv approach requires sql to convert each string to a date, while with this approach you can simply pass the dates as is, without converting them to strings and then back to dates.Also, you can pass 2 dimensions array or dictionaries, all you have to do is create the appropriate user defined data type in your sql database.

Note: code written directly here, there might be some typos.

Pass string array as parameter in SQL query in C#

While I don't agree with the selected answer (or many of the tricky answers) for the "duplicate question", here is an answer to it which shows an approach very similar with my following recommendation.

(I've voted to close this question as a duplicate, because there are such answers, even if buried.)

---

Only one SQL value can be bound to any given placeholder.

While there ways to send all the data as "one value", I'd recommend creating the placeholders dynamically: it's simple, clean, and will work reliably in most cases.

Consider this:

ICollection<string> resources = GetResources();

if (!resources.Any()) {
    // "[Resource No_] IN ()" doesn't make sense
    throw new Exception("Whoops, have to use different query!");
}

// If there is 1 resource, the result would be "@res0" ..
// If there were 3 resources, the result would be "@res0,@res1,@res2" .. etc
var resourceParams = string.Join(",",
    resources.Select((r, i) => "@res" + i));

// This is NOT vulnerable to classic SQL Injection because resourceParams
// does NOT contain user data; only the parameter names.
// However, a large number of items in resources could result in degenerate
// or "too many parameter" queries so limit guards should be used.
var sql = string.Format("SELECT [Resource No_] where [Resource No_] In ({0})",
    resourceParams);

var cmd = conn.CreateCommand();
cmd.CommandText = sql;

// Assign values to placeholders, using the same naming scheme.
// Parameters prevent SQL Injection (accidental or malicious).
int i = 0;
foreach (var r in resources) {
   cmd.Parameters.AddWithValue("@res" + i, r);
   i++;
}

Array of Objects type parameters used in the parameterized SQL query

      ///this code can help you  
        static void Main(string[] args)
          {
               string query = "Select[MAKE],[YEAR] IN[FAKEDB].[FAKETABLE] WHERE[MAKE] " +
    "IN (*Makes*) AND [YEAR] IN (*Year*)";

         CarModel[] carModels = new CarModel[2] { new CarModel() { Make = "number1", Year = "1988" },
      new CarModel() { Make = "number2", Year = "2017" }
       };
        var result = SetQueryParameter(query, carModels);
      }

       public static string SetQueryParameter(string query, CarModel[] CarModels)
      {
        var makesStr = "(";
        var yearStr = "(";
        int counter = 1;
        foreach (var item in CarModels)
        {
            makesStr += item.Make; 
            yearStr += item.Year;
            if (CarModels.Count() != counter++)
            {
                makesStr += ",";
                yearStr += ",";
            }


        }

        makesStr += ")";
        yearStr += ")";

        return query.Replace("(*Makes*)", makesStr).Replace("(*Year*)", yearStr);


    }

How to pass sqlparameter to IN()?

You have to create one parameter for each value that you want in the IN clause.

The SQL needs to look like this:

SELECT userId, username 
FROM Users 
WHERE userId IN (@UserId1, @UserId2, @UserId3, ...) 

So you need to create the parameters and the IN clause in the foreach loop.

Something like this (out of my head, untested):

StringBuilder sb = new StringBuilder();
int i = 1;

foreach (User user in UserList)
{
    // IN clause
    sb.Append("@UserId" + i.ToString() + ",");

    // parameter
    YourCommand.Parameters.AddWithValue("@UserId" + i.ToString(), user.UserId);

    i++;
}

---

Related Topics