Modificare i dati con procedure archiviate

Impostate CommandType a CommandType.StoredProcedure e impostate CommandText al nome della procedura qualificata dallo schema. Aggiungi un parametro per ciascun parametro di procedura, con tipo, dimensione e direzione corrispondenti.

Gli esempi in questo articolo presuppongono che un elemento connection aperto denominato SqlConnection faccia riferimento al database di esempio AdventureWorks.

Gli esempi di C# richiedono using Microsoft.Data.SqlClient;. Esempi che usano SqlDbType o CommandType richiedono using System.Data;anche .

Chiamare una procedura memorizzata

AdventureWorks include dbo.uspGetEmployeeManagers, che accetta un parametro di input e restituisce un insieme di risultati:

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

Usa i nomi dei parametri dichiarati dalla procedura. La qualificazione dello schema evita ambiguità nella risoluzione dei nomi e varianti non necessarie del piano di esecuzione della procedura.

Esegui una procedura di modifica dei dati

AdventureWorks include anche procedure che modificano i dati. HumanResources.uspUpdateEmployeePersonalInfo sostituisce tutti i campi personali del dipendente. Il seguente esempio esegue questi passaggi all'interno di una transazione:

1. Legge i valori attuali del dipendente.
2. Cambia lo stato civile.
3. Legge nuovamente la modifica.
4. Annulla la transazione in modo che il database di esempio rimanga invariato.

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();

La procedura imposta SET NOCOUNT ON, quindi ExecuteNonQuery restituisce -1 invece di un conteggio di righe. Non usare il valore di ritorno per confermare l'aggiornamento. Leggi invece di nuovo la riga.

Verifica il contratto della procedura e gli effetti collaterali prima di eseguirla. Ogni comando che viene eseguito su una connessione con una transazione locale in sospeso deve impostare Transaction. Assicurarsi che la gestione delle transazioni della procedura supporti quella esterna. Questa procedura intercetta gli errori e li registra con dbo.uspLogError invece di riportarli al chiamante.

Leggere i set di risultati

Usa ExecuteReader o ExecuteReaderAsync quando una procedura restituisce righe. Quando restituisce più di un set di risultati, chiama NextResult o NextResultAsync finché non restituisce false.

Smaltire il lettore prima di leggere i parametri di output, di input/output o i valori restituiti. Questi valori non sono definitivi finché Reader è aperto.

Configura la direzione dei parametri

Imposta Direction per ogni parametro che non sia solo input:

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;

Imposta Size per parametri di uscita a lunghezza variabile e parametri di input/output. Un parametro input/output necessita anch'esso di un elemento iniziale Value. Un parametro con valore restituito riceve il valore intero dall'istruzione Transact-SQL (T-SQL) RETURN della procedura. Non riceve un set di risultati né un valore selezionato dalla procedura.

Leggi ogni parametro di Value solo dopo la fine dell'esecuzione e la chiusura di qualsiasi lettore:

command.ExecuteNonQuery();

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

Quando una procedura non assegna un parametro di output, il suo valore può essere DBNull.Value.

Scegli un metodo di esecuzione

Usa il metodo di esecuzione che corrisponde al risultato della procedura:

ExecuteNonQuery restituisce i conteggi delle righe interessate quando sono disponibili. Una procedura che usa SET NOCOUNT ON può modificare le righe e comunque restituire -1. Usa un parametro di output o un valore di ritorno quando la procedura lo definisce come suo contratto di successo.

Gestire i guasti

Intercettare SqlException attorno alla chiamata di esecuzione. Una procedura può gestire autonomamente la gestione delle transazioni e degli errori, ma il client deve comunque decidere se l'operazione è sicura da riprovare e se la sua connessione rimane utilizzabile.

Per errori e PRINT messaggi del server, vedi Gestire errori e messaggi.

Contenuti correlati

• Configurare i parametri
• Esegui un comando
• Parametri con valori di tabella
• Gestire errori e messaggi