Configurar parâmetros com Microsoft.Data.SqlClient

Os parâmetros enviam valores para um comando separadamente de seu texto Transact-SQL (T-SQL). Use objetos SqlParameter em vez de concatenar ou interpolar valores em instruções SQL. Os parâmetros ajudam a proteger contra injeção de SQL e preservam o tipo de dados do valor no banco de dados.

Os exemplos deste artigo assumem que um SqlConnection aberto chamado connection tem como alvo o banco de dados de exemplo AdventureWorks.

Os exemplos de C# requerem using Microsoft.Data.SqlClient;. Exemplos que usam SqlDbType também requerem using System.Data;.

Adicionar parâmetros de entrada

Adicione um parâmetro para cada placeholder de valor no texto do comando. Especifique o tipo de SQL Server do parâmetro e atribua seu valor:

const string sql = """
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
      AND ListPrice >= @minimumPrice
    ORDER BY ListPrice;
    """;

using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@subcategoryId", SqlDbType.Int).Value = 2;
command.Parameters.Add("@minimumPrice", SqlDbType.Money).Value = 1_000m;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)}: {reader.GetString(1)} ({reader.GetDecimal(2):C})");
}

Use a mesma capitalização para o nome de um parâmetro no texto do comando e no SqlParameter. O SQL Server compara os nomes das variáveis usando o agrupamento do servidor, então uma incompatibilidade falha com o erro "Deve declarar a variável escalar" em um servidor com agrupamento sensível a maiúsculas e minúsculas.

Trabalhar com espaços reservados de parâmetro

Microsoft.Data.SqlClient utiliza parâmetros nomeados na forma @parameterName. Pontos de interrogação posicional (?) não são suportados.

Os marcadores de parâmetros representam valores, não identificadores SQL ou sintaxe. Você não pode usar um parâmetro para nome de tabela, nome de coluna, direção de ordenação ou um fragmento SQL inteiro. Se uma aplicação selecionar um identificador dinamicamente, valide-o contra uma lista de permissão e adicione-o ao texto do comando somente após a validação. Continue a passar todos os valores de dados como parâmetros.

Para comandos de texto, a Microsoft.Data.SqlClient envia instruções parametrizadas para o SQL Server através de sp_executesql. O SQL Server pode reutilizar um plano de execução quando o texto da instrução e as definições de parâmetros permanecem os mesmos.

Especificar tipos de dados de parâmetro

Defina SqlDbType explicitamente quando a aplicação souber o tipo de SQL Server alvo. Também defina metadados específicos de tipo, como Size, Precision, e Scale quando aplicável.

Este exemplo corresponde às colunas nvarchar(50) e datetime em Person.Person:

const string sql = """
    SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE LastName = @lastName
      AND ModifiedDate >= @modifiedSince
    ORDER BY BusinessEntityID;
    """;

using var command = new SqlCommand(sql, connection);

SqlParameter lastName = command.Parameters.Add(
    "@lastName",
    SqlDbType.NVarChar,
    50);
lastName.Value = "O'Brien";

SqlParameter modifiedSince = command.Parameters.Add(
    "@modifiedSince",
    SqlDbType.DateTime);
modifiedSince.Value = new DateTime(2013, 1, 1);

A correspondência dos metadados dos parâmetros com a coluna de destino evita conversões implícitas e impede que mudanças no comprimento da string ou na escala decimal alterem a definição do parâmetro.

Para valores decimais, defina precisão e escala explicitamente. A coluna Weight em Production.Product usa decimal(8, 2):

const string sql = """
    SELECT ProductID, Name, Weight
    FROM Production.Product
    WHERE Weight >= @minimumWeight
    ORDER BY Weight;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter minimumWeight = command.Parameters.Add(
    "@minimumWeight",
    SqlDbType.Decimal);
minimumWeight.Precision = 8;
minimumWeight.Scale = 2;
minimumWeight.Value = 10.25m;

Passar valores NULL do banco de dados

Atribua Value para enviar um banco de dados NULL. C# null não representa um NULL do SQL Server para parâmetros escalares comuns:

const string sql = """
    SELECT SalesOrderID, OrderDate, SalesPersonID
    FROM Sales.SalesOrderHeader
    WHERE (@salesPersonId IS NULL OR SalesPersonID = @salesPersonId)
    ORDER BY SalesOrderID;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter salesPersonId = command.Parameters.Add(
    "@salesPersonId",
    SqlDbType.Int);

int? selectedSalesPersonId = null;
salesPersonId.Value = selectedSalesPersonId.HasValue
    ? selectedSalesPersonId.Value
    : DBNull.Value;

Use os metadados inferidos seletivamente

O AddWithValue método infere metadados dos parâmetros a partir do valor de runtime. A inferência é conveniente quando os metadados inferidos são estáveis e correspondem ao tipo de banco de dados. Pode produzir diferentes definições de parâmetros quando comprimentos de string, precisão decimal ou outras características de valor mudam.

Prefira Add com um SqlDbType explícito para comandos de produção cujo esquema alvo é conhecido. Não passe C# null para AddWithValue; especifique o tipo e atribua DBNull.Value em vez disso.

Reutilizar parâmetros de comando

Crie um parâmetro uma vez e só mude seu valor Value quando você executa o mesmo comando com valores diferentes:

const string sql = """
    SELECT TOP (1) Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
    ORDER BY ListPrice DESC;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter subcategoryId = command.Parameters.Add(
    "@subcategoryId",
    SqlDbType.Int);

foreach (int id in new[] { 1, 2, 3 })
{
    subcategoryId.Value = id;

    using SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        Console.WriteLine($"{id}: {reader.GetString(0)}");
    }
}

Não adicione outro parâmetro com o mesmo nome a cada execução. Reutilize o parâmetro existente ou limpe a coleção antes de reconstruí-la.

Definir direção do parâmetro

A Direction propriedade identifica como um comando usa um parâmetro. Defina-o como um ParameterDirection valor:

Direction	Usar
Input	Envia um valor para uma instrução ou procedimento armazenado. Esse valor é o padrão.
Output	Recebe um valor de um parâmetro de saída de procedimento armazenado.
InputOutput	Envia um valor inicial e recebe o valor atualizado.
ReturnValue	Recebe o valor inteiro de uma instrução RETURN do T-SQL.

Para parâmetros de saída, entrada/saída e valor de retorno, leia Value somente após o término do comando. Se um comando devolver linhas, feche ou descarte o SqlDataReader antes de ler esses valores.

Para exemplos completos de procedimentos armazenados, veja Modificar dados com procedimentos armazenados.

Usar parâmetros com um procedimento armazenado

Defina StoredProcedure e use os nomes dos parâmetros do procedimento. Este exemplo chama o dbo.uspGetEmployeeManagers procedimento incluído no AdventureWorks:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(
    "@BusinessEntityID",
    SqlDbType.Int).Value = 6;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetString(2)} {reader.GetString(3)}");
}

O nome do parâmetro deve corresponder ao nome declarado pelo procedimento armazenado.

Configurar parâmetros com valor de tabela

Um parâmetro com valor de tabela envia várias linhas para um procedimento armazenado ou uma instrução parametrizada. Para configurar um deles:

1. Defina SqlDbType.Structured.
2. Defina TypeName como o tipo de tabela definido pelo usuário do banco de dados.
3. Atribua uma DataTable, DbDataReader ou IEnumerable<SqlDataRecord> origem da linha.

A DataTable com colunas definidas e sem linhas envia uma tabela vazia. Um IEnumerable<SqlDataRecord> vazio lança ArgumentException; atribua C# null para enviar zero linhas para esse formulário de entrada. Não atribua DBNull.Value a um parâmetro tabelado.

Para criação de tipos, mapeamento de linhas e exemplos completos, veja Parâmetros com valores de tabela.

Configurar valores de transmissão

Atribua um Stream, TextReader ou XmlReader a Value para aumentar gradualmente um valor em grande magnitude. Para transferência com memória limitada, use um tipo de parâmetro (max), xml ou json. Microsoft.Data.SqlClient armazena em buffer os dados de origem antes da execução para tipos de caractere e binários não (max), bem como para os tipos legados de objetos grandes.

O tipo de parâmetro deve corresponder ao valor:

Value	Valores compatíveis SqlDbType
Stream	Binary, VarBinary, ou o tipo legado Image
TextReader	Char, NChar, NVarChar, VarChar, os tipos legados Text e NText, Xml, ou Json
XmlReader	Char, NChar, NVarChar, VarChar, os tipos legados Text e NText, Xml, ou Json

Microsoft.Data.SqlClient lança ArgumentException, SqlTypeException ou SqlDbType quando o tipo de objeto, InvalidCastException, ou o tamanho declarado é incompatível. Para exemplos completos de upload e download, veja suporte a streaming do SqlClient.

Obter informações do parâmetro

DeriveParametersconsulta o SQL Server para obter os metadados de parâmetros de um procedimento armazenado e substitui a coleção de parâmetros existente do comando:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(command);
command.Parameters["@BusinessEntityID"].Value = 6;

DeriveParameters exige uma ida e volta extra ao banco de dados cada vez que é chamado. Extraia metadados durante o desenvolvimento quando necessário e, em seguida, defina parâmetros explicitamente em caminhos sensíveis ao desempenho.

Conteúdo relacionado

• Executar um comando
• Modificar dados com procedimentos armazenados
• Parâmetros com valor de tabela
• Suporte a streaming no SqlClient