Como configurar parâmetros e tipos de dados de parâmetro

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. Isso 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. Este é 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. Os provedores de dados .NET Framework processam a nomeação e a especificação de parâmetros e de espaços reservados de parâmetros de maneira diferente. Essa sintaxe é personalizada para uma fonte de dados específica, conforme descrito na tabela a seguir.

Provedor de dados	Sintaxe de nomeação de parâmetro
System.Data.SqlClient	Usa parâmetros nomeados no formato @parametername.
System.Data.OleDb	Usa os marcadores de parâmetros posicionais indicados por um ponto de interrogação (?).
System.Data.Odbc	Usa os marcadores de parâmetros posicionais indicados por um ponto de interrogação (?).
System.Data.OracleClient	Usa parâmetros nomeados no formato :parmname (ou parmname).

Especificar tipos de dados de parâmetros

O tipo de dados de um parâmetro é específico do provedor de dados .NET Framework. A especificação do tipo converte o valor do Parameter no tipo de provedor de dados .NET Framework antes de transmitir o valor para a 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 do provedor de dados .NET Framework de um objeto Parameter é inferido do tipo .NET Framework do Value do objeto Parameter ou do 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 de .NET Framework	DbType	SqlDbType	OleDbType	OdbcType	OracleType
Boolean	Booliano	bit	Booliano	bit	Byte
Byte	Byte	TinyInt	UnsignedTinyInt	TinyInt	Byte
byte[]	Binário	VarBinary. Essa conversão implícita falhará se a matriz de bytes for maior que o tamanho máximo de um VarBinary, que é 8.000 bytes. Para matrizes de bytes acima de 8.000 bytes, defina explicitamente o SqlDbType.	VarBinary	Binário	Raw
Char		Inferir um SqlDbType do char não tem suporte.	Char	Char	Byte
DateTime	Datetime	DateTime	DBTimeStamp	Datetime	DateTime
DateTimeOffset	DateTimeOffset	DateTimeOffset no SQL Server 2008. Inferir um SqlDbType de DateTimeOffset não tem suporte em versões do SQL Server anteriores ao SQL Server 2008.			Datetime
Decimal	Decimal	Decimal	Decimal	Numérico	Número
Double	Double	Float	Double	Double	Double
Single	Single	Real	Single	Real	Float
Guid	Guid	UniqueIdentifier	Guid	UniqueIdentifier	Raw
Int16	Int16	SmallInt	SmallInt	SmallInt	Int16
Int32	Int32	int	int	int	Int32
Int64	Int64	BigInt	BigInt	BigInt	Número
Object	Objeto	Variante	Variante	Inferir um OdbcType de objeto não tem suporte.	Blob
String	String	NVarChar. Essa conversão implícita falhará se a cadeia de caracteres for maior do que o tamanho máximo de um NVarChar, que é 4000 caracteres. Para cadeias de caracteres maiores que 4000 caracteres, defina explicitamente o SqlDbType.	VarWChar	NVarChar	NVarChar
TimeSpan	Hora	Hora no SQL Server 2008. Inferir um SqlDbType de TimeSpan não tem suporte em versões do SQL Server anteriores ao SQL Server 2008.	DBTime	Hora	Datetime
UInt16	UInt16	Inferir um SqlDbType do UInt16 não tem suporte.	UnsignedSmallInt	int	UInt16
UInt32	UInt32	Inferir um SqlDbType do UInt32 não tem suporte.	UnsignedInt	BigInt	UInt32
UInt64	UInt64	Inferir um SqlDbType do UInt64 não tem suporte.	UnsignedBigInt	Numérica	Número
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringFixedLength	Char	Char	Char	Char
	Moeda	Money	Moeda	Inferir um OdbcType de Currency não tem suporte.	Número
	Data	Data no SQL Server 2008. Inferir um SqlDbType de Date não tem suporte em versões do SQL Server anteriores ao SQL Server 2008.	DBDate	Data	Datetime
	SByte	Inferir um SqlDbType do SByte não tem suporte.	TinyInt	Inferir um OdbcType do SByte não tem suporte.	SByte
	StringFixedLength	NChar	WChar	NChar	NChar
	Hora	Hora no SQL Server 2008. Inferir um SqlDbType de Time não tem suporte em versões do SQL Server anteriores ao SQL Server 2008.	DBTime	Hora	Datetime
	VarNumeric	Inferir um SqlDbType do VarNumeric não tem suporte.	VarNumeric	Inferir um OdbcType do VarNumeric não tem suporte.	Número
tipo definido pelo usuário (um objeto com SqlUserDefinedAggregateAttribute	Objeto ou cadeia de caracteres, dependendo do provedor (SqlClient sempre retorna um objeto, ODBC sempre retorna uma cadeia de caracteres e o provedor de dados gerenciados OleDb pode ver se	SqlDbType.Udt se SqlUserDefinedTypeAttribute está presente, caso contrário Variant	OleDbType.VarWChar (se o valor for nulo); caso contrário OleDbType.Variant.	OdbcType.NVarChar	sem suporte

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á gerado.

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. Para obter mais informações sobre valores nulos de banco de dados, confira Como lidar com valores nulos.

Derivar informações de parâmetro

Os parâmetros também podem ser derivados de um procedimento armazenado usando a classe DbCommandBuilder. As classes SqlCommandBuilder e OleDbCommandBuilder fornecem 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. Observe que DeriveParameters substitui 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. Ao usar 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 adicional. 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, o 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 .NET Framework para o SQL Server não dá suporte ao espaço reservado de ponto de interrogação (?) para transmitir parâmetros para uma instrução SQL ou 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(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new()
        {
            Connection = connection,
            CommandText = "SalesByCategory",
            CommandType = CommandType.StoredProcedure
        };

        // Add the input parameter and set its properties.
        SqlParameter parameter = new()
        {
            ParameterName = "@CategoryName",
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input,
            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();
        }
    }
}

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As 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 reader As SqlDataReader = command.ExecuteReader()

            If reader.HasRows Then
                Do While reader.Read()
                    Console.WriteLine("{0}: {1:C}", _
                      reader(0), reader(1))
                Loop
            Else
                Console.WriteLine("No rows returned.")
            End If
        End Using
    End Using
End Sub

Como usar parâmetros com um OleDbCommand ou um OdbcCommand

Ao usar parâmetros com um OleDbCommand ou OdbcCommand, a ordem dos parâmetros adicionados à coleção de Parameters deve coincidir com a ordem dos parâmetros definidos no procedimento armazenado. O Provedor de Dados .NET Framework para OLE DB e o Provedor de Dados .NET Framework para ODBC tratam os parâmetros em um procedimento armazenado como espaços reservados e aplicam os valores de parâmetro na ordem. Além disso, os parâmetros do valor de retorno devem ser os primeiros parâmetros adicionados à coleção de Parameters.

O Provedor de Dados .NET Framework para OLE DB e o Provedor de Dados .NET Framework para ODBC não dão suporte aos parâmetros nomeados para transmissão de parâmetros para uma instrução SQL ou um procedimento armazenado. Nesse caso, você deverá usar o espaço reservado de ponto de interrogação (?), como no exemplo a seguir.

SELECT * FROM Customers WHERE CustomerID = ?

Como resultado, a ordem na qual os objetos Parameter são adicionados à coleção de Parameters deve corresponder diretamente à posição do espaço reservado do ? para o parâmetro.

Exemplo de OleDb

Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

OleDbParameter parameter = command.Parameters.Add(
  "RETURN_VALUE", OleDbType.Integer);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add(
  "@InputParm", OleDbType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add(
  "@OutputParm", OleDbType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Exemplo de ODBC

Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OdbcCommand command = new OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;

OdbcParameter parameter = command.Parameters.Add( _
  "RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Confira também

• Comandos e parâmetros
• Parâmetros DataAdapter
• Mapeamentos de tipos de dados no ADO.NET
• Visão geral do ADO.NET