Configurar parâmetros

  • Artigo

Aplicável a: .NET Framework .NET .NET Standard

Baixar ADO.NET

Objetos de comando usam parâmetros para passar valores para instruções SQL ou procedimentos armazenados, fornecendo verificação de tipo e validação. Diferentemente do texto de comando, o parâmetro de entrada é tratado como um valor literal, não como código executável. Esse comportamento ajuda a proteger contra ataques de "injeção de SQL", em que um invasor insere um comando que compromete a segurança no servidor em uma instrução SQL.

Os comandos parametrizados também podem melhorar o desempenho de execução da consulta, porque ajudam o servidor de banco de dados a corresponder exatamente ao comando de entrada com um plano de consulta em cache apropriado. Para obter mais informações, confira Reutilização e armazenamento em cache do plano de execução e Parâmetros e reutilização de plano de execução. Além dos benefícios de segurança e desempenho, os comandos parametrizados fornecem um método conveniente para organizar os valores passados para uma fonte de dados.

Um objeto DbParameter pode ser criado usando o construtor ou adicionando-o ao DbParameterCollection chamando o método Add da coleção DbParameterCollection. O método Add utilizará como entrada argumentos de construtor ou um objeto de parâmetro existente, dependendo do provedor de dados.

Fornecer a propriedade ParameterDirection

Ao adicionar parâmetros, você deverá fornecer uma propriedade ParameterDirection para parâmetros diferentes dos parâmetros de entrada. A tabela a seguir mostra os valores ParameterDirection que você pode usar com a enumeração ParameterDirection.

Nome do membro Descrição
Input O parâmetro é um parâmetro de entrada. Esse valor é o padrão.
InputOutput O parâmetro pode executar entrada e saída.
Output O parâmetro é um parâmetro de saída.
ReturnValue O parâmetro representa um valor de retorno de uma operação como um procedimento armazenado, uma função interna ou uma função definida pelo usuário.

Trabalhar com espaços reservados de parâmetro

A sintaxe para espaços reservados de parâmetro depende da fonte de dados. O Provedor de Dados do Microsoft SqlClient para SQL Server lida com a nomenclatura e a especificação de parâmetros e espaços reservados de parâmetros de maneira diferente. O provedor de dados do SqlClient usa parâmetros nomeados no formato @parametername.

Especificar tipos de dados de parâmetro

O tipo de dados de um parâmetro é específico para o Provedor de Dados do Microsoft SqlClient para SQL Server. Especificar o tipo converte o valor de Parameter para o tipo de Provedor de Dados Microsoft SqlClient para SQL Server antes de transmitir o valor à fonte de dados. Você também pode especificar o tipo de um Parameter genericamente definindo a propriedade DbType de um objeto Parameter para um DbType específico.

O tipo de objeto Parameter do Provedor de Dados do Microsoft SqlClient para SQL Server é inferido do tipo .NET Framework de Value do objeto Parameter ou de DbType do objeto Parameter. A tabela a seguir mostra o tipo inferido de Parameter baseado no objeto passado como o valor do Parameter ou DbType especificado.

Tipo .NET DbType SqlDbType
Boolean Boolean Bit
Byte Byte TinyInt
byte[] Binary VarBinary. Essa conversão implícita falhará se a matriz de bytes for maior que o tamanho máximo de um VarBinary, que é de 8.000 bytes. Para matrizes de bytes maiores que 8000 bytes, defina SqlDbType explicitamente.
Char Não há suporte para a inferência de um SqlDbType do char.
DateTime DateTime DateTime
DateTimeOffset DateTimeOffset DateTimeOffset no SQL Server 2008. Não há suporte para a inferência de um SqlDbType do DateTimeOffset em versões do SQL Server anteriores ao SQL Server 2008.
Decimal Decimal Decimal
Double Double Float
Single Single Real
Guid Guid UniqueIdentifier
Int16 Int16 SmallInt
Int32 Int32 Int
Int64 Int64 BigInt
Object Object Variant
String String NVarChar. Essa conversão implícita falhará se a cadeia de caracteres for maior que o tamanho máximo de um NVarChar, que é de 4.000 caracteres. Para cadeias de caracteres maiores que 4000 caracteres, defina explicitamente o SqlDbType.
TimeSpan Time Time no SQL Server 2008. Não há suporte para a inferência de um SqlDbType do TimeSpan em versões do SQL Server anteriores ao SQL Server 2008.
UInt16 UInt16 Não há suporte para a inferência de um SqlDbType do UInt16.
UInt32 UInt32 Não há suporte para a inferência de um SqlDbType do UInt32.
UInt64 UInt64 Não há suporte para a inferência de um SqlDbType do UInt64.
AnsiString VarChar
AnsiStringFixedLength Char
Currency Money
Date Date no SQL Server 2008. Não há suporte para a inferência de um SqlDbType do Date em versões do SQL Server anteriores ao SQL Server 2008.
SByte Não há suporte para a inferência de um SqlDbType do SByte.
StringFixedLength NChar
Time Time no SQL Server 2008. Não há suporte para a inferência de um SqlDbType do Time em versões do SQL Server anteriores ao SQL Server 2008.
VarNumeric Não há suporte para a inferência de um SqlDbType do VarNumeric.
tipo definido pelo usuário (um objeto com SqlUserDefinedAggregateAttribute O SqlClient sempre retorna um Objeto SqlDbType.Udt se SqlUserDefinedTypeAttribute estiver presente; caso contrário Variant

Observação

Conversões de decimal para outros tipos são conversões de limitação que arredondam o valor decimal para o valor inteiro mais próximo de zero. Se o resultado da conversão não for representável no tipo de destino, um OverflowException será lançado.

Observação

Quando você envia um valor de parâmetro nulo para o servidor, deve especificar DBNull, não null (Nothing no Visual Basic). O valor nulo no sistema é um objeto vazio que não tem nenhum valor. DBNull é usado para representar valores nulos.

Obter informações do parâmetro

Os parâmetros também podem ser derivados de um procedimento armazenado usando a classe DbCommandBuilder. A classe SqlCommandBuilder fornece um método estático, DeriveParameters, que preenche automaticamente a coleção de parâmetros de um objeto de comando que usa informações de parâmetro de um procedimento armazenado. DeriveParameters substituirá qualquer informação de parâmetro existente para o comando.

Observação

Derivar informações de parâmetro provoca uma penalidade de desempenho porque exige ida e volta adicional à fonte de dados para recuperar as informações. Se as informações de parâmetro forem conhecidas em tempo de design, você poderá melhorar o desempenho do seu aplicativo definindo os parâmetros explicitamente.

Para obter mais informações, confira Gerar comandos com CommandBuilders.

Usar parâmetros com um SqlCommand e um procedimento armazenado

Os procedimentos armazenados oferecem várias vantagens em aplicativos orientados a dados. Usando os procedimentos armazenados, as operações de banco de dados podem ser encapsuladas em um único comando, otimizadas para melhor desempenho e aprimoradas com segurança extra. Embora um procedimento armazenado possa ser chamado passando o nome do procedimento armazenado seguido por argumentos de parâmetros como uma instrução SQL, usar a coleção de Parameters do objeto DbCommand do ADO.NET permite definir mais explicitamente os parâmetros de procedimento armazenados e acessar parâmetros de saída e valores de retorno.

Observação

As instruções parametrizadas são executadas no servidor usando sp_executesql, que permite a reutilização do plano de consulta. Os cursores locais ou variáveis no lote sp_executesql não são visíveis para os lotes que chamam sp_executesql. As alterações no contexto de banco de dados duram somente até o final da instrução sp_executesql. Para saber mais, confira sp_executesql (Transact-SQL).

Ao usar parâmetros com um SqlCommand para executar um procedimento armazenado do SQL Server, os nomes dos parâmetros adicionados à coleção de Parameters devem coincidir com os nomes dos marcadores de parâmetros no procedimento armazenado. O Provedor de Dados do Microsoft SqlClient para SQL Server não oferece suporte ao espaço reservado de ponto de interrogação (?) para transmitir parâmetros a uma instrução SQL ou a um procedimento armazenado. Ele trata parâmetros no procedimento armazenado como parâmetros nomeados e procura marcadores de parâmetro compatíveis. Por exemplo, o procedimento armazenado CustOrderHist é definido usando um parâmetro chamado @CustomerID. Quando o código executar o procedimento armazenado, também deverá usar um parâmetro chamado @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Exemplo

Este exemplo demonstra como chamar um procedimento armazenado do SQL Server no banco de dados de exemplo Northwind. O nome do procedimento armazenado é dbo.SalesByCategory e tem um parâmetro de entrada chamado @CategoryName com um tipo de dados de nvarchar(15). O código cria um novo SqlConnection dentro de um bloco using para que a conexão seja descartada quando o procedimento terminar. Os objetos SqlCommand e SqlParameter são criados e suas propriedades são definidas. Um SqlDataReader executa o SqlCommand e retorna o conjunto de resultados do procedimento armazenado, exibindo a saída na janela do console.

Observação

Em vez de criar objetos SqlCommand e SqlParameter e depois definir as propriedades em instruções separadas, você poderá eleger usar um dos construtores sobrecarregados para definir várias propriedades em uma única instrução.

static void GetSalesByCategory(string connectionString,
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
            reader.Close();
        }
    }
}

Confira também