Passar Parâmetro Matriz no SqlCommand

Eu estou tentando passar parâmetro de matriz para SQL commnd em C # como abaixo, mas não funciona. Alguém conhece isso antes?

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

Você precisará adicionar os valores na matriz, um de cada vez.

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

ATUALIZAÇÃO: Aqui está uma solução estendida e reutilizável que usa a resposta de Adam junto com sua edição sugerida. Eu melhorei um pouco e o tornei um método de extensão para facilitar ainda mais a chamada.

 public static class SqlCommandExt { ///  /// 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. (ie SELECT * FROM table WHERE field IN ({paramNameRoot})) ///  /// The SqlCommand object to add parameters to. /// What the parameter should be named followed by a unique value for each value. This value surrounded by {} in the CommandText will be replaced. /// The array of strings that need to be added as parameters. /// One of the System.Data.SqlDbType values. If null, determines type based on T. /// The maximum size, in bytes, of the data within the column. The default value is inferred from the parameter value. public static SqlParameter[] AddArrayParameters(this SqlCommand cmd, string paramNameRoot, IEnumerable 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(); var parameterNames = new List(); 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(); } } 

É chamado assim ...

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

Observe que o "{Age}" na instrução sql é o mesmo que o nome do parâmetro que estamos enviando para AddArrayParameters. AddArrayParameters replaceá o valor pelos parâmetros corretos.

Eu queria expandir a resposta que Brian contribuiu para tornar isso facilmente utilizável em outros lugares.

 ///  /// 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. (ie SELECT * FROM table WHERE field IN (returnValue)) ///  /// The SqlCommand object to add parameters to. /// The array of strings that need to be added as parameters. /// What the parameter should be named. 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); } 

Você pode usar essa nova function da seguinte maneira:

 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: Aqui está uma variação genérica que funciona com uma matriz de valores de qualquer tipo e é utilizável como um método de extensão:

 public static class Extensions { public static void AddArrayParameters(this SqlCommand cmd, string name, IEnumerable 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); } } 

Você pode então usar este método de extensão da seguinte maneira:

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

Certifique-se de definir o CommandText antes de chamar AddArrayParameters.

Além disso, verifique se o nome do seu parâmetro não corresponde parcialmente a qualquer outra coisa em sua instrução (ou seja, @AgeOfChild)

Se você pode usar uma ferramenta como “dapper”, isso pode ser simplesmente:

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

Dapper lidará com desembrulhar isso para parâmetros individuais para você .

Desde que há um método em

 SqlCommand.Parameters.AddWithValue(parameterName, value) 

pode ser mais conveniente criar um método que aceite um parâmetro (nome) para replace e uma lista de valores. Ele não está no nível parameters (como AddWithValue ), mas no próprio comando, portanto, é melhor chamá-lo de AddParametersWithValues e não apenas de AddWithValues :

inquerir:

 SELECT * from TableA WHERE Age IN (@age) 

uso:

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

o método de extensão:

 public static class SqlCommandExtensions { public static void AddParametersWithValues(this SqlCommand cmd, string parameterName, params T[] values) { var parameterNames = new List(); 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)); } } 

Se você estiver usando o MS SQL Server 2008 e acima, você pode usar parâmetros com valor de tabela, como descrito aqui http://www.sommarskog.se/arrays-in-sql-2008.html .

1. Crie um tipo de tabela para cada tipo de parâmetro que você usará

O comando a seguir cria um tipo de tabela para inteiros:

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

2. Implementar methods auxiliares

 public static SqlCommand AddParameter(this SqlCommand command, string name, IEnumerable 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(IEnumerable ids) { var table = new DataTable(); table.Columns.Add("id", typeof (T)); foreach (var id in ids) { table.Rows.Add(id); } return table; } 

3. Use assim

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

Passar uma matriz de itens como um parâmetro recolhido para a cláusula WHERE..IN falhará, pois a consulta tomará forma de WHERE Age IN ("11, 13, 14, 16") .

Mas você pode passar seu parâmetro como um array serializado para XML ou JSON:

Usando o método nodes() :

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

Usando o método 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(); 

Isso é um pouco mais no lado do SQL e você precisa de um XML adequado (com root).

Usando o método OPENJSON (SQL Server 2016+):

 using Newtonsoft.Json; ... List ages = new List(); 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); 

Observe que, para o último método, você também precisa ter o nível de compatibilidade de 130+.

Use .AddWithValue() , então:

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

Alternativamente, você poderia usar isto:

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

Sua amostra de código total será a seguinte:

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

experimentar

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

tente assim

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

Ou você poderia tentar o caminho mais simples:

 var ages = new int[] { 1, 2, 3, 4, 6 }; var selectSQL = "SELECT * from TableA WHERE Age IN ({0})"; var sql = String.Format(selectSQL , String.Join(",", ages)); using (var command = CreateCommand(connection, sql)) { command.CommandType = CommandType.Text; using (var reader = command.ExecuteReader()) { ... } }