Modificar dados com procedimentos armazenados

Defina CommandType para CommandType.StoredProcedure e defina CommandText para o nome do procedimento qualificado pelo esquema. Adicione um parâmetro para cada parâmetro de procedimento, com tipo, tamanho e direção correspondentes.

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

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

Chamar um procedimento armazenado

AdventureWorks inclui dbo.uspGetEmployeeManagers, que aceita um parâmetro de entrada e retorna um conjunto de resultados:

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)}");
}

Use os nomes dos parâmetros declarados pelo procedimento. A qualificação de esquema evita ambiguidade na resolução de nomes e variantes desnecessárias de planos de procedimento.

Execute um procedimento de modificação de dados

O AdventureWorks também inclui procedimentos que modificam dados. HumanResources.uspUpdateEmployeePersonalInfo substitui todos os campos pessoais do funcionário. O exemplo a seguir executa estes passos dentro de uma transação:

  1. Lê os valores atuais do funcionário.
  2. Muda o estado civil.
  3. Lê novamente a alteração.
  4. Reverte a transação para que o banco de dados de exemplo permaneça inalterado.
const int businessEntityId = 1;
using SqlTransaction transaction = connection.BeginTransaction();

string nationalIdNumber;
DateTime birthDate;
string maritalStatus;
string gender;

using (var select = new SqlCommand(
    """
    SELECT NationalIDNumber, BirthDate, MaritalStatus, Gender
    FROM HumanResources.Employee
    WHERE BusinessEntityID = @BusinessEntityID;
    """,
    connection,
    transaction))
{
    select.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;

    using SqlDataReader reader = select.ExecuteReader();
    if (!reader.Read())
    {
        throw new InvalidOperationException(
            $"Employee {businessEntityId} wasn't found.");
    }

    nationalIdNumber = reader.GetString(0);
    birthDate = reader.GetDateTime(1);
    maritalStatus = reader.GetString(2);
    gender = reader.GetString(3);
}

string newMaritalStatus = maritalStatus == "M" ? "S" : "M";

using (var command = new SqlCommand(
    "HumanResources.uspUpdateEmployeePersonalInfo",
    connection,
    transaction))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;
    command.Parameters.Add(
        "@NationalIDNumber",
        SqlDbType.NVarChar,
        15).Value = nationalIdNumber;
    command.Parameters.Add(
        "@BirthDate",
        SqlDbType.DateTime).Value = birthDate;
    command.Parameters.Add(
        "@MaritalStatus",
        SqlDbType.NChar,
        1).Value = newMaritalStatus;
    command.Parameters.Add(
        "@Gender",
        SqlDbType.NChar,
        1).Value = gender;

    command.ExecuteNonQuery();
}

using (var verify = new SqlCommand(
    """
    SELECT MaritalStatus
    FROM HumanResources.Employee
    WHERE BusinessEntityID = @BusinessEntityID;
    """,
    connection,
    transaction))
{
    verify.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;
    Console.WriteLine(
        $"Marital status: {maritalStatus} -> {verify.ExecuteScalar()}");
}

transaction.Rollback();

O procedimento define SET NOCOUNT ON, então ExecuteNonQuery retorna -1 em vez de uma contagem de linhas. Não use o valor de retorno para confirmar a atualização. Leia a linha novamente.

Verifique o contrato e os efeitos colaterais de um procedimento antes de executá-lo. Todo comando que roda em uma conexão com uma transação local pendente deve definir Transaction. Garanta que o tratamento das transações do procedimento suporte a transação pai. Esse procedimento detecta erros e os registra com dbo.uspLogError em vez de levantá-los para o chamador.

Ler conjuntos de resultados

Use ExecuteReader ou ExecuteReaderAsync quando um procedimento retorna linhas. Quando retorna mais de um conjunto de resultados, chame NextResult ou NextResultAsync até que retorne false.

Descarte o leitor antes de ler os parâmetros de saída, de entrada/saída ou de valor de retorno. Esses valores não são finais enquanto o visualizador está aberto.

Configurar direção dos parâmetros

Defina Direction para todo parâmetro que não seja apenas de entrada:

SqlParameter output = command.Parameters.Add(
    "@outputValue",
    SqlDbType.NVarChar,
    50);
output.Direction = ParameterDirection.Output;

SqlParameter inputOutput = command.Parameters.Add(
    "@counter",
    SqlDbType.Int);
inputOutput.Direction = ParameterDirection.InputOutput;
inputOutput.Value = 1;

SqlParameter returnValue = command.Parameters.Add(
    "@returnValue",
    SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;

Defina Size para parâmetros de saída e de entrada/saída de comprimento variável. Um parâmetro de entrada/saída também precisa de um valor inicial Value. Um parâmetro de valor de retorno recebe o inteiro da instrução Transact-SQL (T-SQL) do procedimento RETURN. Ele não recebe um conjunto de resultados nem um valor selecionado pelo procedimento.

Leia o Value de cada parâmetro somente após a execução terminar e qualquer leitor for fechado:

command.ExecuteNonQuery();

Console.WriteLine(output.Value);
Console.WriteLine(inputOutput.Value);
Console.WriteLine(returnValue.Value);

Quando um procedimento não atribui um parâmetro de saída, seu valor pode ser DBNull.Value.

Escolha um método de execução

Use o método de execução que corresponda ao resultado do procedimento:

Método Usar quando
ExecuteReader O procedimento retorna linhas ou múltiplos conjuntos de resultados.
ExecuteNonQuery Você precisa de parâmetros de saída ou de um valor de retorno, mas não precisa de linhas de resultado. O cliente consome quaisquer linhas retornadas antes que a chamada seja concluída.
ExecuteScalar Você precisa apenas da primeira coluna da primeira linha.

ExecuteNonQuery retorna as contagens de linhas afetadas quando estão disponíveis. Um procedimento que usa SET NOCOUNT ON pode modificar linhas e ainda assim devolver -1. Use um parâmetro de saída ou valor de retorno quando o procedimento definir um como seu contrato de sucesso.

Observação

Quando a SqlDataAdapter usa um procedimento armazenado para atualizar ou excluir linhas, não use SET NOCOUNT ON se o adaptador depende da contagem de linhas afetadas para concorrência otimista. Uma contagem zero pode causar DBConcurrencyException.

Gerenciar falhas

Capture SqlException ao redor da chamada de execução. Um procedimento pode gerenciar sua própria transação e tratamento de erros, mas o cliente ainda deve decidir se a operação é segura para tentar novamente e se sua conexão permanece utilizável.

Para erros e mensagens do servidor PRINT, veja Tratamento de erros e mensagens.

Conteúdo relacionado

  • Última atualização em