Ändern von Daten mit gespeicherten Prozeduren

Setze CommandType auf CommandType.StoredProcedure und setze CommandText auf den schema-qualifizierten Prozedurnamen. Füge für jeden Prozedurparameter einen Parameter hinzu, mit passendem Typ, Größe und Richtung.

Bei den Beispielen in diesem Artikel wird davon ausgegangen, dass eine offene SqlConnection mit dem Namen connection auf die AdventureWorks-Beispieldatenbank abzielt.

Die C#-Beispiele erfordern using Microsoft.Data.SqlClient;. Beispiele, die SqlDbType oder CommandType verwenden, erfordern auch using System.Data;.

Aufrufen einer gespeicherten Prozedur

AdventureWorks enthält dbo.uspGetEmployeeManagers, das einen Eingabeparameter akzeptiert und ein Ergebnisset zurückgibt:

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

Verwenden Sie die durch das Verfahren deklarierten Parameternamen. Die Schemaqualifizierung vermeidet Mehrdeutigkeiten bei der Namensauflösung und unnötige Prozedurplanvarianten.

Führen Sie eine Prozedur zur Datenänderung aus

AdventureWorks enthält außerdem Prozeduren, die Daten verändern. HumanResources.uspUpdateEmployeePersonalInfo ersetzt alle persönlichen Felder eines Mitarbeiters. Das folgende Beispiel führt diese Schritte innerhalb einer Transaktion aus:

  1. Liest die aktuellen Werte des Mitarbeiters.
  2. Ändert den Familienstand.
  3. Liest die Änderung zurück.
  4. Rollt die Transaktion zurück, sodass die Beispieldatenbank unverändert bleibt.
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();

Das Verfahren setzt SET NOCOUNT ON, also gibt ExecuteNonQuery-1 statt einer Zeilenzahl zurück. Verwenden Sie nicht den Rückgabewert zur Bestätigung des Updates. Lesen Sie stattdessen die Zeile zurück.

Überprüfen Sie den Vertrag und die Nebeneffekte einer Prozedur, bevor Sie sie ausführen. Jeder Befehl, der für eine Verbindung mit einer ausstehenden lokalen Transaktion ausgeführt wird, muss Transaction festlegen. Stellen Sie sicher, dass die Transaktionsabwicklung des Verfahrens die äußere Transaktion unterstützt. Dieses Verfahren fängt Fehler ab und protokolliert sie mit dbo.uspLogError , anstatt sie an den Aufrufer weiterzugeben.

Ergebnismengen lesen

Verwenden Sie ExecuteReader oder ExecuteReaderAsync, wenn ein Verfahren Zeilen zurückgibt. Wenn es mehr als eine Ergebnismenge zurückgibt, rufen Sie NextResult oder false auf, bis es NextResultAsync zurückgibt.

Entfernen Sie den Leser, bevor Sie Ausgabe-, Ein-/Ausgabe- oder Rückgabeparameter auslesen. Diese Werte sind nicht endgültig, solange der Reader geöffnet ist.

Parameterrichtungen konfigurieren

Legen Sie Direction für jeden Parameter fest, der kein reiner Eingabeparameter ist:

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;

Legen Sie Size für variable Ausgabe- und Ein-/Ausgabeparameter fest. Ein Ein-/Ausgabeparameter benötigt außerdem ein initiales Value. Ein Rückgabeparameter erhält die ganze Zahl aus der Transact-SQL (T-SQL)- RETURN Anweisung des Verfahrens. Er erhält keine Ergebnismenge oder einen von der Prozedur ausgewählten Wert.

Lesen Sie den Value jedes Parameters erst nach Abschluss der Ausführung und nachdem ein Leser geschlossen wurde:

command.ExecuteNonQuery();

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

Wenn ein Verfahren keinen Ausgabeparameter zuweist, kann sein Wert DBNull.Value sein.

Wählen Sie eine Ausführungsart

Verwenden Sie die Ausführungsmethode, die dem Ergebnis der Prozedur entspricht:

Methode Verwenden Sie, wenn
ExecuteReader Das Verfahren liefert Zeilen oder mehrere Ergebnismengen zurück.
ExecuteNonQuery Du brauchst Ausgabeparameter oder einen Rückgabewert, aber keine Ergebniszeilen. Der Client verarbeitet alle zurückgegebenen Zeilen, bevor der Aufruf abgeschlossen wird.
ExecuteScalar Du brauchst nur die erste Spalte der ersten Reihe.

ExecuteNonQuery gibt die Anzahl betroffener Zeilen zurück, sobald sie verfügbar sind. Ein Verfahren, das SET NOCOUNT ON verwendet, kann Zeilen ändern und trotzdem -1 zurückgeben. Verwenden Sie einen Ausgabeparameter oder Rückgabewert, wenn die Prozedur einen als Erfolgsbedingung definiert.

Hinweis

Wenn ein SqlDataAdapter eine gespeicherte Prozedur verwendet, um Zeilen zu aktualisieren oder zu löschen, verwenden Sie nicht SET NOCOUNT ON, wenn der Adapter auf die Anzahl der betroffenen Zeilen für optimistische Nebenläufigkeit angewiesen ist. Eine Anzahl von null kann DBConcurrencyException verursachen.

Fehler beheben

Erfassen Sie SqlException rund um den Ausführungsaufruf. Ein Verfahren kann seine eigene Transaktion und Fehlerbehandlung verwalten, aber der Client muss dennoch entscheiden, ob die Operation sicher ist, um sie erneut zu versuchen, und ob ihre Verbindung weiterhin nutzbar bleibt.

Für Serverfehler und PRINT Nachrichten siehe Fehler und Nachrichten handhaben.

Verwandte Inhalte

  • Zuletzt aktualisiert um