Pass Array Parameter dans SqlCommand

Question

J'essaie de passer le paramètre de tableau à SQL commnd en C # comme ci-dessous, mais cela ne fonctionne pas. Est-ce que quelqu'un le rencontre avant?

 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);
StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
{
     if (item.Selected)
     {
         sb.Append(item.Text + ",");
     }
}

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

Answer 1

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

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: Voici 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="values">The array of strings that need to be added as parameters.</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="start">The beginning number to append to the end of paramNameRoot for each value.</param>
    /// <param name="separator">The string that separates the parameter names in the sql command.</param>
    public static SqlParameter[] AddArrayParameters<T>(this SqlCommand cmd, IEnumerable<T> values, string paramNameRoot, int start = 1, string separator = ", ")
    {
        /* 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 = start;
        foreach(var value in values)
        {
            var paramName = string.Format("@{0}{1}", paramNameRoot, paramNbr++);
            parameterNames.Add(paramName);
            parameters.Add(cmd.Parameters.AddWithValue(paramName, value));
        }

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

        return parameters.ToArray();
    }
}

Il est appelé comme ça...

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

Notez le "{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.

Answer 2

Je voulais étendre sur la réponse que Brian a contribué à les rendre facilement utilisables dans d'autres endroits.

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

Vous pouvez utiliser cette nouvelle fonction comme suit:

SqlCommand cmd = new SqlCommand();

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

cmd.CommandText = sql;

---

Edit: Voici un générique variation qui fonctionne avec un tableau de valeurs de tout type et est utilisable comme une extension de la méthode:

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

Vous pouvez ensuite utiliser cette extension de la méthode comme suit:

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

Assurez-vous de définir le CommandText avant d'appeler AddArrayParameters.

Assurez-vous également de votre nom de paramètre ne sera pas partiellement correspondre à quelque chose d'autre dans votre déclaration (c'est à dire @AgeOfChild)

Answer 3

désolé comment vous ajoutez le code?

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

Answer 4

Utilisez .AddWithValue() , donc:

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

Alternativement, vous pouvez utiliser ceci:

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

Votre exemple de code total se présentera comme suit:

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

Answer 5

essayez comme ça

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