Configurando parâmetros e tipos de dados de parâmetros

Os objetos de comando usam parâmetros para passar valores para instruções SQL ou procedimentos armazenados, fornecendo verificação e validação de tipo. Ao contrário do texto do comando, a entrada de parâmetros é tratada como um valor literal, não como código executável. Isso ajuda a proteger contra ataques de "injeção de SQL", nos quais um invasor insere um comando que compromete a segurança do servidor em uma instrução SQL.

Os comandos parametrizados também podem melhorar o desempenho da execução de consultas, pois ajudam o servidor de banco de dados a corresponder com precisão o comando de entrada com um plano de consulta em cache adequado. Para obter mais informações, consulte Cache e reutilização do plano de execução e Parâmetros e Reutilização do 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 DbParameter objeto pode ser criado usando seu construtor ou adicionando-o DbParameterCollection ao chamando o Add método da DbParameterCollection coleção. O Add método tomará como entrada argumentos do construtor ou um objeto de parâmetro existente, dependendo do provedor de dados.

Fornecendo a propriedade ParameterDirection

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

Nome do membro	Description
Input	O parâmetro é um parâmetro de entrada. Esta é a predefiniçã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, função interna ou função definida pelo usuário.

Trabalhando com espaços reservados para parâmetros

A sintaxe dos espaços reservados para parâmetros depende da fonte de dados. Os provedores de dados do .NET Framework lidam com nomenclatura e especificação de parâmetros e espaços reservados para parâmetros de forma diferente. Essa sintaxe é personalizada para uma fonte de dados específica, conforme descrito na tabela a seguir.

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

Especificando tipos de dados de parâmetros

O tipo de dados de um parâmetro é específico para o provedor de dados .NET Framework. Especificar o tipo converte o valor do para o tipo de provedor de dados do .NET Framework antes de passar o valor para a fonte de Parameter dados. Você também pode especificar o tipo de a Parameter de uma maneira genérica definindo a DbTypeParameter propriedade do objeto como um determinado DbType.

O tipo de provedor de dados do .NET Framework de um Parameter objeto é inferido do tipo .NET Framework do ParameterValue objeto ou do DbTypeParameter objeto. A tabela a seguir mostra o tipo inferido Parameter com base no objeto passado como o Parameter valor ou o especificado DbType.

Tipo do .NET Framework	DbType	SqlDbType	OleDbType	OdbcType	OracleType
Boolean	Boolean	Pouco	Boolean	Pouco	Byte
Byte	Byte	TinyInt	Não assinadoTinyInt	TinyInt	Byte
byte[]	Binário	VarBinary. Esta conversão implícita falhará se a matriz de bytes for maior do que o tamanho máximo de um VarBinary, que é de 8000 bytes. Para matrizes de bytes maiores que 8000 bytes, defina explicitamente o SqlDbType.	VarBinary	Binário	Raw
Char		Não há suporte para inferir um SqlDbType de char.	Char	Char	Byte
DateTime	DateTime	DateTime	DBTimeStamp	DateTime	DateTime
DateTimeOffset	DateTimeOffset	DateTimeOffset no SQL Server 2008. Não há suporte para inferir um SqlDbType de DateTimeOffset em versões do SQL Server anteriores ao SQL Server 2008.			DateTime
Decimal	Decimal	Decimal	Decimal	Numérico	Número
Double	Duplo	Float	Duplo	Duplo	Duplo
Single	Única	Real	Única	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	Object	Variante	Variante	Não há suporte para inferir um OdbcType de Object.	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 é de 4000 caracteres. Para cadeias de caracteres maiores que 4000 caracteres, defina explicitamente o SqlDbTypearquivo .	VarWChar	NVarChar	NVarChar
TimeSpan	Hora	Hora no SQL Server 2008. Não há suporte para inferir um SqlDbType a partir do TimeSpan em versões do SQL Server anteriores ao SQL Server 2008.	DBTime	Hora	DateTime
UInt16	UInt16	Não há suporte para inferir um SqlDbType de UInt16.	UnsignedSmallInt	Int	UInt16
UInt32	UInt32	Não há suporte para inferir um SqlDbType de UInt32.	UnsignedInt	BigInt	UInt32
UInt64	UInt64	Não há suporte para inferir um SqlDbType a partir de UInt64.	Não assinadoBigInt	Numérico	Número
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringFixedLength	Char	Char	Char	Char
	Moeda	Dinheiro	Moeda	Não há suporte para inferir um OdbcType de Currency .	Número
	Date	Data no SQL Server 2008. Não há suporte para inferir um SqlDbType de Date em versões do SQL Server anteriores ao SQL Server 2008.	DBDate	Date	DateTime
	SByte	Não há suporte para inferir um SqlDbType do SByte.	TinyInt	Não há suporte para inferir um OdbcType do SByte.	SByte
	StringFixedLength	NChar	WChar	NChar	NChar
	Hora	Hora no SQL Server 2008. Não há suporte para inferir um SqlDbType from Time em versões do SQL Server anteriores ao SQL Server 2008.	DBTime	Hora	DateTime
	VarNumeric	Não há suporte para inferir um SqlDbType de VarNumeric.	VarNumeric	Não há suporte para inferir um OdbcType de VarNumeric.	Número
tipo definido pelo usuário (um objeto com SqlUserDefinedAggregateAttribute	Object ou String, dependendo do provedor (SqlClient sempre retorna um Object, Odbc sempre retorna um String, e o provedor de dados gerenciado OleDb pode ver	SqlDbType.Udt se SqlUserDefinedTypeAttribute estiver presente, caso contrário, Variant	OleDbType.VarWChar (se o valor for nulo), caso contrário, OleDbType.Variant.	OdbcType.NVarChar	não suportado

Nota

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

Nota

Quando você envia um valor de parâmetro nulo para o servidor, você deve especificar DBNull, não null (Nothing no Visual Basic). O valor nulo no sistema é um objeto vazio que não tem valor. DBNull é usado para representar valores nulos. Para obter mais informações sobre nulos de banco de dados, consulte Manipulando valores nulos.

Derivando informações de parâmetros

Os parâmetros também podem ser derivados de um procedimento armazenado usando a DbCommandBuilder classe. SqlCommandBuilder As classes 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âmetros de um procedimento armazenado. Observe que DeriveParameters substitui qualquer informação de parâmetro existente para o comando.

Nota

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

Para obter mais informações, consulte Gerando comandos com CommandBuilders.

Usando parâmetros com um SqlCommand e um procedimento armazenado

Os procedimentos armazenados oferecem muitas vantagens em aplicativos controlados por dados. Usando 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âmetro como uma instrução SQL, o Parameters uso da coleção do objeto ADO.NET DbCommand permite definir mais explicitamente os parâmetros do procedimento armazenado e acessar parâmetros de saída e valores de retorno.

Nota

As instruções parametrizadas são executadas no servidor usando sp_executesql, o que permite a reutilização do plano de consulta. Cursores locais ou variáveis no sp_executesql lote não são visíveis para o lote que chama sp_executesql. As alterações no contexto do banco de dados duram apenas até o final da sp_executesql instrução. Para obter mais informações, consulte sp_executesql (Transact-SQL).

Ao usar parâmetros com a SqlCommand para executar um procedimento armazenado do SQL Server, os nomes dos parâmetros adicionados à coleção devem corresponder aos Parameters nomes dos marcadores de parâmetro no procedimento armazenado. O Provedor de Dados do .NET Framework para SQL Server não oferece suporte ao espaço reservado de ponto de interrogação (?) para passar parâmetros para uma instrução SQL ou um procedimento armazenado. Ele trata os parâmetros no procedimento armazenado como parâmetros nomeados e procura marcadores de parâmetros correspondentes. Por exemplo, o CustOrderHist procedimento armazenado é definido usando um parâmetro chamado @CustomerID. Quando o código executa o procedimento armazenado, ele também deve usar um parâmetro chamado @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Exemplo

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

Nota

Em vez de criar SqlCommand e SqlParameter objetos e, em seguida, definir propriedades em instruções separadas, você pode optar por 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

Usando parâmetros com um OleDbCommand ou OdbcCommand

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

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

SELECT * FROM Customers WHERE CustomerID = ?

Como resultado, a ordem em que Parameter os objetos são adicionados à Parameters coleção deve corresponder diretamente à posição 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;

Consulte também

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