Podaj parametr tablicy w SqlCommand

Chciałem rozwinąć odpowiedź, że Brian przyczynił się do tego, aby to było łatwe do wykorzystania w innych miejscach.

/// <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 (returnValue))
/// </summary>
/// <param name="sqlCommand">The SqlCommand object to add parameters to.</param>
/// <param name="array">The array of strings that need to be added as parameters.</param>
/// <param name="paramName">What the parameter should be named.</param>
protected string AddArrayParameters(SqlCommand sqlCommand, string[] array, string paramName)
{
    /* 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 string[array.Length];
    for (int i = 0; i < array.Length; i++)
    {
        parameters[i] = string.Format("@{0}{1}", paramName, i);
        sqlCommand.Parameters.AddWithValue(parameters[i], array[i]);
    }

    return string.Join(", ", parameters);
}

Możesz użyć tej nowej funkcji w następujący sposób:

SqlCommand cmd = new SqlCommand();

string ageParameters = AddArrayParameters(cmd, agesArray, "Age");
sql = string.Format("SELECT * FROM TableA WHERE Age IN ({0})", ageParameters);

cmd.CommandText = sql;

public static class Extensions
{
    public static void AddArrayParameters<T>(this SqlCommand cmd, string name, IEnumerable<T> values) 
    { 
        name = name.StartsWith("@") ? name : "@" + name;
        var names = string.Join(", ", values.Select((value, i) => { 
            var paramName = name + i; 
            cmd.Parameters.AddWithValue(paramName, value); 
            return paramName; 
        })); 
        cmd.CommandText = cmd.CommandText.Replace(name, names); 
    }
}

Możesz użyć tej metody rozszerzenia w następujący sposób:

var ageList = new List<int> { 1, 3, 5, 7, 9, 11 };
var cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE Age IN (@Age)";    
cmd.AddArrayParameters("Age", ageList);

Upewnij się, że ustawiłeś Tekst polecenia przed wywołaniem AddArrayParameters.

Upewnij się również, że nazwa parametru nie będzie częściowo pasować do niczego innego w instrukcji (np. @AgeOfChild)

Jeśli możesz użyć narzędzia takiego jak "dapper", może to być po prostu:

int[] ages = { 20, 21, 22 }; // could be any common list-like type
var rows = connection.Query<YourType>("SELECT * from TableA WHERE Age IN @ages",
          new { ages }).ToList();

Dapper zajmie się rozpakowaniem tego do indywidualnych parametrów dla Ciebie .

Ponieważ istnieje metoda na

SqlCommand.Parameters.AddWithValue(parameterName, value)

Wygodniejsze może być utworzenie metody przyjmującej parametr (nazwę) do zastąpienia i listę wartości. Nie jest on na poziomie parametrów (Jak AddWithValue), ale na samym poleceniu, więc lepiej nazwać go AddParametersWithValues, a nie tylko AddWithValues :

Zapytanie:

SELECT * from TableA WHERE Age IN (@age)

Użycie:

sqlCommand.AddParametersWithValues("@age", 1, 2, 3);

Metoda rozszerzenia:

public static class SqlCommandExtensions
{
    public static void AddParametersWithValues<T>(this SqlCommand cmd,  string parameterName, params T[] values)
    {
        var parameterNames = new List<string>();
        for(int i = 0; i < values.Count(); i++)
        {
            var paramName = @"@param" + i;
            cmd.Parameters.AddWithValue(paramName, values.ElementAt(i));
            parameterNames.Add(paramName);
        }

        cmd.CommandText = cmd.CommandText.Replace(parameterName, string.Join(",", parameterNames));
    }
}

Jeśli używasz MS SQL Server 2008 i wyżej, możesz użyć parametrów o wartości tabelkowej, jak opisano tutaj http://www.sommarskog.se/arrays-in-sql-2008.html .

1. Utwórz typ tabeli dla każdego typu parametru, którego będziesz używać

Następujące polecenie tworzy typ tabeli dla liczb całkowitych:

create type int32_id_list as table (id int not null primary key)

2. Implementacja metod pomocniczych

public static SqlCommand AddParameter<T>(this SqlCommand command, string name, IEnumerable<T> ids)
{
  var parameter = command.CreateParameter();      

  parameter.ParameterName = name;
  parameter.TypeName = typeof(T).Name.ToLowerInvariant() + "_id_list";
  parameter.SqlDbType = SqlDbType.Structured;
  parameter.Direction = ParameterDirection.Input;

  parameter.Value = CreateIdList(ids);

  command.Parameters.Add(parameter);
  return command;
}

private static DataTable CreateIdList<T>(IEnumerable<T> ids)
{
  var table = new DataTable();
  table.Columns.Add("id", typeof (T));

  foreach (var id in ids)
  {
    table.Rows.Add(id);
  }

  return table;
}

3. Użyj go w ten sposób

cmd.CommandText = "select * from TableA where Age in (select id from @age)"; 
cmd.AddParameter("@age", new [] {1,2,3,4,5});

Przekazanie tablicy elementów jako zwiniętego parametru do WHERE..IN klauzula nie powiedzie się, ponieważ zapytanie przyjmie formę WHERE Age IN ("11, 13, 14, 16").

Ale możesz przekazać swój parametr jako tablicę serializowaną do XML lub JSON:

Stosując metodę nodes():

StringBuilder sb = new StringBuilder();

foreach (ListItem item in ddlAge.Items)
  if (item.Selected)
    sb.Append("<age>" + item.Text + "</age>"); // actually it's xml-ish

sqlComm.CommandText = @"SELECT * from TableA WHERE Age IN (
    SELECT Tab.col.value('.', 'int') as Age from @Ages.nodes('/age') as Tab(col))";
sqlComm.Parameters.Add("@Ages", SqlDbType.NVarChar);
sqlComm.Parameters["@Ages"].Value = sb.ToString();

Stosując metodę OPENXML:

using System.Xml.Linq;
...
XElement xml = new XElement("Ages");

foreach (ListItem item in ddlAge.Items)
  if (item.Selected)
    xml.Add(new XElement("age", item.Text);

sqlComm.CommandText = @"DECLARE @idoc int;
    EXEC sp_xml_preparedocument @idoc OUTPUT, @Ages;
    SELECT * from TableA WHERE Age IN (
    SELECT Age from OPENXML(@idoc, '/Ages/age') with (Age int 'text()')
    EXEC sp_xml_removedocument @idoc";
sqlComm.Parameters.Add("@Ages", SqlDbType.Xml);
sqlComm.Parameters["@Ages"].Value = xml.ToString();

To trochę więcej po stronie SQL i potrzebujesz odpowiedniego XML (z rootem).

Użycie metody OPENJSON (SQL Server 2016+):

using Newtonsoft.Json;
...
List<string> ages = new List<string>();

foreach (ListItem item in ddlAge.Items)
  if (item.Selected)
    ages.Add(item.Text);

sqlComm.CommandText = @"SELECT * from TableA WHERE Age IN (
    select value from OPENJSON(@Ages))";
sqlComm.Parameters.Add("@Ages", SqlDbType.NVarChar);
sqlComm.Parameters["@Ages"].Value = JsonConvert.SerializeObject(ages);

Zauważ, że dla ostatniej metody również musisz mieć Poziom kompatybilności na poziomie 130+.

Użyj .AddWithValue(), Więc:

sqlComm.Parameters.AddWithValue("@Age", sb.ToString().TrimEnd(','));

Alternatywnie możesz użyć tego:

sqlComm.Parameters.Add(
    new SqlParameter("@Age", sb.ToString().TrimEnd(',')) { SqlDbType = SqlDbType. NVarChar }
    );

Twoja całkowita próbka kodu będzie wyglądać następująco:

string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)";
SqlConnection sqlCon = new SqlConnection(connectString);
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlCon;
sqlComm.CommandType = System.Data.CommandType.Text;
sqlComm.CommandText = sqlCommand;
sqlComm.CommandTimeout = 300;

StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
{
     if (item.Selected)
     {
         sb.Append(item.Text + ",");
     }
}

sqlComm.Parameters.AddWithValue("@Age", sb.ToString().TrimEnd(','));

// OR

// sqlComm.Parameters.Add(new SqlParameter("@Age", sb.ToString().TrimEnd(',')) { SqlDbType = SqlDbType. NVarChar });

Try

sqlComm.Parameters["@Age"].Value = sb.ToString().Replace(","," ");

Try it like this

StringBuilder sb = new StringBuilder(); 
foreach (ListItem item in ddlAge.Items) 
{ 
     if (item.Selected) 
     { 
          string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)"; 
          SqlConnection sqlCon = new SqlConnection(connectString); 
          SqlCommand sqlComm = new SqlCommand(); 
          sqlComm.Connection = sqlCon; 
          sqlComm.CommandType = System.Data.CommandType.Text; 
          sqlComm.CommandText = sqlCommand; 
          sqlComm.CommandTimeout = 300; 
          sqlComm.Parameters.Add("@Age", SqlDbType.NVarChar);
          sb.Append(item.Text + ","); 
          sqlComm.Parameters["@Age"].Value = sb.ToString().TrimEnd(',');
     } 
}