Modyfikowanie danych za pomocą procedur składowanych

Ustaw CommandType na CommandType.StoredProcedure, a CommandText na nazwę procedury kwalifikowaną schematem. Dodaj jeden parametr dla każdego parametru procedury, z odpowiednim typem, rozmiarem i kierunkiem.

Przykłady w tym artykule zakładają, że otwarte połączenie SqlConnection o nazwie connection jest skierowane do przykładowej bazy danych AdventureWorks.

Przykłady w języku C# wymagają using Microsoft.Data.SqlClient;. Przykłady, które używają SqlDbType lub CommandType również wymagają using System.Data;.

Wywołaj procedurę składowaną

AdventureWorks zawiera dbo.uspGetEmployeeManagers, który akceptuje jeden parametr wejściowy i zwraca zbiór wyników:

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

Użyj nazw parametrów zadeklarowanych przez procedurę. Kwalifikacja schematu unika niejednoznaczności rozstrzygania nazw oraz niepotrzebnych wariantów planu wykonania procedury.

Wykonaj procedurę modyfikacji danych

AdventureWorks zawiera również procedury modyfikujące dane. HumanResources.uspUpdateEmployeePersonalInfo Zastępuje wszystkie pola osobiste pracownika. Poniższy przykład wykonuje następujące kroki w ramach transakcji:

1. Odczytuje aktualne wartości pracownika.
2. Zmienia stan cywilny.
3. Odczytuje zmianę z powrotem.
4. Cofa transakcję, więc przykładowa baza danych pozostaje niezmieniona.

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

Procedura ustawia SET NOCOUNT ON, więc ExecuteNonQuery zwraca -1 zamiast liczby wierszy. Nie używaj wartości zwrotnej do potwierdzenia aktualizacji. Przeczytaj zamiast tego wiersz ponownie.

Sprawdź kontrakt procedury i skutki uboczne przed jej wykonaniem. Każde polecenie uruchamiane na połączeniu z oczekującą transakcją lokalną musi mieć ustawione Transaction. Upewnij się, że obsługa transakcji w procedurze obsługuje transakcję zewnętrzną. Procedura ta wykrywa błędy i rejestruje je za pomocą dbo.uspLogError zamiast przekazywać je do wywołującego.

Odczytaj zbiory wyników

Użyj ExecuteReader lub ExecuteReaderAsync, gdy procedura zwraca wiersze. Gdy zwraca więcej niż jeden zbiór wyników, wywołuj NextResult lub NextResultAsync, aż zwróci false.

Zwolnij czytnik przed odczytem parametrów wyjściowych, wejściowych/wyjściowych lub wartości zwrotnej. Te wartości nie są ostateczne, dopóki czytnik jest otwarty.

Skonfiguruj kierunki parametrów

Ustaw Direction dla każdego parametru, który nie jest tylko wejściowy:

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;

Ustaw Size dla parametrów wyjściowych o zmiennej długości oraz parametrów wejścia/wyjścia. Parametr wejścia/wyjścia również wymaga początkowego Value. Parametr zwracający otrzymuje liczbę całkowitą z instrukcji języka Transact-SQL (T-SQL) RETURN procedury. Nie otrzymuje zestawu wyników ani wartości wybranej przez procedurę.

Odczytuj Value każdego parametru dopiero po zakończeniu wykonania i zamknięciu czytnika:

command.ExecuteNonQuery();

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

Gdy procedura nie przypisuje parametru wyjściowego, jego wartość może być DBNull.Value.

Wybierz metodę wykonania

Użyj metody wykonania, która odpowiada wynikowi procedury:

ExecuteNonQuery zwraca liczbę wierszy, których dotyczy operacja, gdy są dostępne. Procedura używająca SET NOCOUNT ON może modyfikować wiersze i nadal zwracać -1. Używaj parametru wyjściowego lub wartości zwrotnej, gdy procedura definiuje taki parametr lub taką wartość jako warunek powodzenia.

Zarządzanie awariami

Umieść SqlException wokół wywołania wykonania. Procedura może samodzielnie zarządzać transakcjami i obsługą błędów, ale klient musi nadal zdecydować, czy operacja jest bezpieczna do ponownego wykonania i czy jej połączenie pozostaje użyteczne.

W przypadku błędów serwera i PRINT komunikatów zobacz Obsługa błędów i komunikatów.

Treści powiązane

• Konfigurowanie parametrów
• Wykonaj polecenie
• Parametry wartości tabeli
• Obsługa błędów i wiadomości