28 05 2014

Olá caro(a) leitor(a)!

Algumas semanas atrás eu estava respondendo algumas questões no fórum de C# da MSDN e me deparei com a seguinte questão: De que maneira consigo passar um List<T> como parâmetro em um SqlCommand? Eu já tinha passado por esse problema no passado, e também já não era a primeira vez que eu via uma pergunta sobre esse assunto, então, no final da thread acabei compartilhando uma solução para esse problema, e eu gostaria de compartilhá-la aqui com vocês.

O problema

Antes de tudo, vamos entender qual é o problema nesse caso. Suponha que você tenha uma tabela de clientes no seu banco de dados e você gostaria de selecionar todos os clientes cuja coluna “Cidade” seja igual a um dos valores de um array (por exemplo, “São Paulo”, “Rio de Janeiro”, “Belo Horizonte”). Esse é um caso típico onde podemos utilizar a cláusula IN do SQL, e a query ficaria parecida com isto:

SELECT * FROM Cliente
WHERE (Cidade IN ('São Paulo', 'Rio de Janeiro', 'Belo Horizonte'))

Entretanto, obviamente essa lista de valores não é fixa, então, a saída seria prover essa lista através de um parâmetro do ADO.NET. Pensando nisso, você escreve o seguinte código C#:

            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
            command.CommandText = "SELECT * FROM Cliente WHERE (Cidade IN (@Cidade))";
            command.Parameters.AddWithValue("@Cidade", new string[] { "São Paulo", "Rio de Janeiro", "Belo Horizonte" });
            var reader = command.ExecuteReader();

Porém, ao executar esse código, você percebe que ele não produz o resultado esperado. Pior ainda, esse código resulta em uma exception:

No mapping exists from object type System.String[] to a known managed provider native type.

A primeira tentativa do usuário Eugenio Junior (autor da thread que eu mencionei anteriormente) foi concatenar o array em uma só string separada por vírgula e passar essa string como parâmetro do comando:

            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
            command.CommandText = "SELECT * FROM Cliente WHERE (Cidade IN (@Cidade))";
            string[] cidades = new string[] { "São Paulo", "Rio de Janeiro", "Belo Horizonte" };
            command.Parameters.AddWithValue("@Cidade", string.Join(", ", cidades));
            var reader = command.ExecuteReader();

Essa tentativa, apesar de não resultar em um erro (caso a coluna sendo pesquisada seja do tipo varchar), acaba retornando nenhuma linha como resultado, pois, no final das contas, essa é a sentença que acaba sendo executada:

SELECT * FROM Cliente
WHERE (Cidade IN ('São Paulo, Rio de Janeiro, Belo Horizonte'))

Percebeu o problema? A sentença acaba realizando uma busca na coluna “Cidade” por “São Paulo, Rio de Janeiro, Belo Horizonte“, e obviamente não existirá nenhum cliente que tenha esse valor no campo “Cidade“.

A solução

Para resolver esse problema, precisamos criar um parâmetro para cada item da cláusula IN. Ou seja, nesse nosso exemplo teríamos que criar três parâmetros. Veja como ficaria o resultado:

            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
            StringBuilder commandTextBuilder = new StringBuilder();

            commandTextBuilder.Append("SELECT * FROM Cliente WHERE (Cidade IN (");
            string[] cidades = new string[] { "São Paulo", "Rio de Janeiro", "Belo Horizonte" };
            List<string> paramNames = new List<string>();
            for (int counter = 0; counter < cidades.Length; counter++)
            {
                string paramName = string.Format("@Cidade{0}", counter);
                paramNames.Add(paramName);
                command.Parameters.AddWithValue(paramName, cidades[counter]);
            }
            commandTextBuilder.Append(string.Join(", ", paramNames));
            commandTextBuilder.Append("))");
            
            command.CommandText = commandTextBuilder.ToString();
            var reader = command.ExecuteReader();

No entanto, essa solução é um tanto quanto dificultosa, pois para cada cláusula IN que você tiver, um “for” é necessário para gerar os parâmetros. De tão inconveniente que essa solução acaba sendo, o autor da thread considerou utilizar concatenação de strings na própria sentença SQL. Como eu sou extremamente contra concatenação de strings em sentenças SQL com o ADO.NET (porque isso abre demais para a possibilidade de SQL injection), resolvi dar uma estudada melhor no problema e acabei chegando a uma solução menos inconveniente.

A solução genérica

O resultado que eu queria obter era criar um método que recebesse o SqlCommand, a lista de valores e o nome a ser utilizado nos parâmetros. E então esse método ficaria responsável por criar os SqlParameters e adiciona-los ao CommandText do SqlCommand. Eu acabei chamando esse método de HandleInClauseParameter. Veja como ele ficou:

        private static string HandleInClauseParameter<T>(System.Data.SqlClient.SqlCommand command, IEnumerable<T> listOfValues, string parameterName)
        {
            List<string> parameterNames = new List<string>();
            int parameterCounter = 0;

            foreach (var value in listOfValues)
            {
                string currentParameterName = string.Format("@{0}_{1}", parameterName, parameterCounter);
                parameterNames.Add(currentParameterName);
                command.Parameters.AddWithValue(currentParameterName, value);
                parameterCounter++;
            }

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

Basicamente ele faz o que o nosso código anterior fazia, porém, de forma genérica, podendo ser utilizado em qualquer cláusula IN que você tiver nos seus projetos. Veja como fica o nosso código ao utilizar esse novo método:

            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
            string[] cidades = new string[] { "São Paulo", "Rio de Janeiro", "Belo Horizonte" };
            command.CommandText = "SELECT * FROM Cliente WHERE (Cidade IN (" + HandleInClauseParameter(command, cidades, "Cidade") + "))";
            var reader = command.ExecuteReader();

Bem mais simples, não? Espero que vocês tenham achado útil como o autor da thread achou.

Até a próxima!

André Lima

PS.: Gostou desse artigo? Então deixe um comentário aqui embaixo. E não se esqueça de inscrever-se para receber as novidades do blog, além de ficar por dentro da newsletter que estou para lançar em breve.

Syntatic Line Compression: Visualize uma quantidade maior de código no Visual Studio Retornando à atividade!