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

Question

I have a way to write a SqlCommand which includes a dynamic list of parameters. My challenge is passing each of the new SqlParameter (@Param0, value0), new sqlParameter (@Param1, value1)... could be another 50 SQL parameters. It can be passed as a hard-coded string but passing the sb.ToString() understandably won't work (because of the commas - they are new arguments).

How do I write a loop or similar to pass the correct number of new arguments?

My attempt so far is below:

public ViewResult Index(int? ID)
{
    using (var context = new Projects201819Context())
       if (ID == null)
       {
           var sqlCommand = new SqlCommand();

           // Array of item numbers - will change and can be longer/shorter, as required.
           var SQL0 = "SELECT * FROM [database] WHERE material_id IN ({0})";

           var idList = new List<int> { 11, 53, 125};
           int[] idListArray = idList.ToArray();
           var idParameterList = new List<string>();
           var index = 0;
           int IL = idList.Count;

           // Create a SqlParameter for each element in the array called "@idParam0", "@idParam1"... and add to list idParameterList
           foreach (var id in idList)
           {
               var paramName = "@idParam" + index;
               sqlCommand.Parameters.AddWithValue(paramName, id);
               idParameterList.Add(paramName);
               index++;
           }

           // Finalise SQL String for datainput - DONE AND WORKS
           sqlCommand.CommandText = String.Format(SQL0, string.Join(",", idParameterList));

           var newSPList = new List<string>();
           var m = 0;

           foreach (var id in idList)
           {
               var SPName = " new SqlParameter(" + "\"" + "@idParam" + m + "\"" + "," + idListArray[m] + ")";
               newSPList.Add(SPName);
               m++;
           }

           string HELLO = string.Join(",", newSPList);
           string MM = "\"" + sqlCommand.CommandText + "\"" + "," + HELLO;

           var datainput = context.materials.SqlQuery(MM);
           var data = datainput.ToList();

           return View(data);
       }
}

where there is an id is fine and not given (the else part of if (id == null)).

The critical bit is the SPName - this successfully adds items to the newSPList list and the string.join returns the exact string I need (HELLO) but I can't then pass this long string as separate arguments - makes complete sense - I just don't know how to work around it!

Thank you for any support!

I have used stored procedures and passed them table valued parameters. I'm not sure if this works for ad hoc queries. You have to define the table type though, so its somewhat cumbersome. Would you like me to work it out as an answer? — Kasper van den Berg
– Kasper van den Berg, Commented Jan 6, 2019 at 11:17
If you use PetaPoco, it will do this for you out of the box. I assume others, like Dapper, will also do so. — mjwills
– mjwills, Commented Jan 6, 2019 at 11:30

Alex Kudryashev · Accepted Answer · 2019-01-06 20:02:44Z

3

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

edited Jan 6, 2019 at 20:02

answered Jan 6, 2019 at 18:52

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve · Accepted Answer · 2019-01-06 10:33:34Z

2

You cannot pass an array of parameters in that way. SqlQuery from EF6 has an overload that accepts, as second parameter, an array of SqlParameter.

All you have to do is:

SqlParameter[] pms = sqlCommand.Parameters.Cast<SqlParameter>().ToArray();
var datainput = context.materials.SqlQuery(sqlCommand.CommandText, pms);

Of course this means also that a lot of your current code is unnecessary and you can scrap it away

For example, you could write this without an SqlCommand object used just to store parameters and the command text.

var SQL0 = "SELECT * FROM [database] WHERE material_id IN ({0})";
var idList = new List<int> { 11, 53, 125 };
var idParameterList = new List<string>();
var pms = new List<SqlParameter>();
int count = 1;
foreach (var id in idList)
{
    var paramName = "@idParam" + count++;
    SqlParameter p = new SqlParameter(paramName, SqlDbType.Int);
    p.Value = id;
    pms.Add(p);
    idParameterList.Add(paramName);
}
string cmdText = String.Format(SQL0, string.Join(",", idParameterList));
var datainput = context.materials.SqlQuery(cmdText, pms);

edited Jan 6, 2019 at 10:33

answered Jan 6, 2019 at 10:28

Steve

217k22 gold badges242 silver badges296 bronze badges

3 Comments

Kasper van den Berg Over a year ago

Adding a dependency to EF6 just to have array parameters might give some unnessecary bloat.

Kasper van den Berg Over a year ago

Using custom command text, instead of querys with parameters aversely affects caching of the query in the query engine.

Steve Over a year ago

It is a command text fully parameterized. If you talk about some optimizations in EF then ok, but for the Sql optimizer is correct. Of course the variable number of parameters in the IN clause makes any optimization very difficult if not impossible. For the first comment I really don't know. Just for this code I agree but we don't know how EF is used in the rest of the program

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related