title: Passing a dynamic array of SqlParameters into a SQL string IN clause
body: <p>I have a way to write a <code>SqlCommand</code> which includes a dynamic list of parameters.
  My challenge is passing each of the <code>new SqlParameter (@Param0, value0), new sqlParameter (@Param1, value1)</code>... could be another 50 SQL parameters. It can be passed as a hard-coded string but passing the <code>sb.ToString()</code> understandably won't work (because of the commas - they are new arguments).</p>
  
  <p>How do I write a loop or similar to pass the correct number of new arguments? </p>
  
  <p>My attempt so far is below:</p>
  
  <pre><code>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&lt;int&gt; { 11, 53, 125};
             int[] idListArray = idList.ToArray();
             var idParameterList = new List&lt;string&gt;();
             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&lt;string&gt;();
             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);
         }
  }
  </code></pre>
  
  <p>where there is an id is fine and not given (the else part of <code>if (id == null)</code>).</p>
  
  <p>The critical bit is the <code>SPName</code> - this successfully adds items to the <code>newSPList</code> list and the <code>string.join</code> 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!</p>
  
  <p>Thank you for any support!</p>