Pass Paramètre de Tableau dans SqlCommand

Vous aurez besoin d'ajouter les valeurs dans le tableau une à une.

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);

Mise à JOUR: Ici est une longue et réutilisables solution qui utilise Adam réponse de la avec son a suggéré de modifier. J'ai amélioré un peu et en fait une méthode d'extension pour le rendre encore plus facile d'appeler.

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();
    }

}

Il est appelé comme ça...

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

Avis de la "{Age}" dans l'instruction sql est le même que le nom du paramètre que nous adressons à AddArrayParameters. AddArrayParameters remplacera la valeur avec les paramètres corrects.

Si vous pouvez utiliser un outil comme "dapper", cela peut être tout simplement:

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 traitera de déballage de ce des paramètres individuels pour vous.

Puisqu'il s'agit d'une méthode sur

SqlCommand.Parameters.AddWithValue(parameterName, value)

il peut être plus pratique de créer une méthode d'accepter un paramètre (nom), afin de remplacer et d'une liste de valeurs. Il n'est pas sur la Paramètres niveau (comme AddWithValue) mais sur la commande elle-même donc c'est mieux de l'appeler AddParametersWithValues et pas seulement AddWithValues:

requête:

SELECT * from TableA WHERE Age IN (@age)

utilisation:

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

la méthode d'extension:

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));
    }
}

Le passage d'un tableau d'éléments comme un effondrement de l'paramètre à la OÙ..DANS la clause échoue, car requête forme de WHERE Age IN ("11, 13, 14, 16").

Mais vous pouvez passer votre paramètre sous la forme d'un tableau sérialisé en XML ou JSON:

À l'aide de nodes() méthode:

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();

À l'aide de OPENXML méthode:

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();

C'est un peu plus sur le SQL côté et vous avez besoin d'un XML (avec la racine).

À l'aide de OPENJSON méthode (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);

Noter que pour la dernière méthode, vous devez également vous assurer une Compatibilité à 130+.

essayer

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