Gegevens wijzigen met opgeslagen procedures

Stel CommandType in op CommandType.StoredProcedure en zet CommandText op de schemagekwalificeerde procedurenaam. Voeg één parameter toe voor elke procedureparameter, met bijpassende type, grootte en richting.

De voorbeelden in dit artikel gaan ervan uit dat een open SqlConnection met de naam connection de AdventureWorks-voorbeelddatabase als doel heeft.

De C#-voorbeelden vereisen using Microsoft.Data.SqlClient;. Voorbeelden die gebruikmaken van SqlDbType of CommandType, vereisen ook using System.Data;.

Een opgeslagen procedure aanroepen

AdventureWorks bevat dbo.uspGetEmployeeManagers, dat één invoerparameter accepteert en een resultaatset teruggeeft:

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

Gebruik de parameternamen die door de procedure zijn opgegeven. Schema-kwalificatie voorkomt naamresolutie-ambiguïteit en onnodige procedureplanvarianten.

Voer een data-modificatieprocedure uit

AdventureWorks bevat ook procedures die data wijzigen. HumanResources.uspUpdateEmployeePersonalInfo vervangt alle persoonlijke velden van een werknemer. Het volgende voorbeeld voert deze stappen uit binnen een transactie:

1. Leest de huidige waarden van de medewerker.
2. Verandert de burgerlijke staat.
3. Leest de wijziging terug.
4. Draait de transactie terug zodat de voorbeelddatabase ongewijzigd blijft.

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

De procedure stelt SET NOCOUNT ON in, dus ExecuteNonQuery geeft -1 terug in plaats van een rijtelling. Gebruik de retourwaarde niet om de update te bevestigen. Lees de regel in plaats daarvan terug.

Controleer het contract en de bijwerkingen van een procedure voordat je deze uitvoert. Elk commando dat op een verbinding draait met een lopende lokale transactie moet Transaction instellen. Zorg ervoor dat de transactieafhandeling van de procedure de buitenste transactie ondersteunt. Deze procedure vangt fouten op en registreert deze met dbo.uspLogError in plaats van ze door te geven aan de aanroeper.

Lees resultaatsets

Gebruik ExecuteReader of ExecuteReaderAsync wanneer een procedure rijen teruggeeft. Wanneer het meer dan één resultaatset teruggeeft, roep NextResult of NextResultAsync aan totdat het terugkeert false.

Geef de reader vrij voordat je output-, input/output- of retourwaardeparameters leest. Die waarden zijn niet definitief zolang de Reader open is.

Parameterrichtingen configureren

Stel Direction in voor elke parameter die niet alleen voor invoer is:

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;

Stel Size in voor uitvoer- en invoer-/uitvoerparameters met variabele lengte. Een input/outputparameter heeft ook een initiale Value nodig. Een return-value parameter ontvangt de integer die wordt geretourneerd door de Transact-SQL (T-SQL)-RETURN statement van de procedure. Het ontvangt geen resultaatset of een door de procedure geselecteerde waarde.

Lees de Value van elke parameter pas nadat de uitvoering is voltooid en elke lezer is gesloten:

command.ExecuteNonQuery();

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

Wanneer een procedure geen uitvoerparameter toewijst, kan de waarde ervan DBNull.Value zijn.

Kies een uitvoeringsmethode

Gebruik de uitvoeringsmethode die overeenkomt met het resultaat van de procedure:

ExecuteNonQuery geeft het aantal getroffen rijen terug zodra dit beschikbaar is. Een procedure die gebruikmaakt van SET NOCOUNT ON kan rijen wijzigen en toch -1 teruggeven. Gebruik een outputparameter of retourwaarde wanneer de procedure er één definieert als succescontract.

Fouten afhandelen

Vang SqlException rond de uitvoeringsaanroep. Een procedure kan zijn eigen transactie en foutafhandeling beheren, maar de client moet nog steeds beslissen of de operatie veilig is om opnieuw te proberen en of de verbinding bruikbaar blijft.

Voor serverfouten en PRINT -berichten, zie Fouten en berichten afhandelen.

Verwante onderwerpen

• Parameters configureren
• Voer een commando uit
• Parameters met tabelwaarde
• Afhandelen van fouten en berichten