Úprava dat pomocí uložených procedur

Nastavte CommandType na CommandType.StoredProcedure a CommandText na název procedury kvalifikovaný schématem. Přidejte jeden parametr pro každý parametr procedury s odpovídajícím typem, velikostí a směrem.

Příklady v tomto článku předpokládají, že je otevřené připojení SqlConnection s názvem connection, které cílí na ukázkovou databázi AdventureWorks.

Příklady v C# vyžadují using Microsoft.Data.SqlClient;. Příklady, které používají SqlDbType nebo CommandType také vyžadují using System.Data;.

Volání uložené procedury

AdventureWorks zahrnuje dbo.uspGetEmployeeManagers, který přijímá jeden vstupní parametr a vrací množinu výsledků:

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

Použijte názvy parametrů deklarované v proceduře. Kvalifikace schématu se vyhýbá nejednoznačnosti při řešení názvů a zbytečným variantám plánů procedur.

Spusťte postup pro úpravu dat

AdventureWorks také zahrnuje postupy, které mění data. HumanResources.uspUpdateEmployeePersonalInfo nahrazuje všechna osobní pole zaměstnance. Následující příklad provádí tyto kroky uvnitř transakce:

  1. Čte aktuální hodnoty zaměstnance.
  2. Mění to rodinný stav.
  3. Načte změněnou hodnotu.
  4. Vrátí transakci zpět, takže vzorová databáze zůstane beze změny.
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 nastavuje SET NOCOUNT ON, takže ExecuteNonQuery vrací -1 místo počtu řádků. Nepoužívejte hodnotu vrácení k potvrzení aktualizace. Přečti si místo toho řádek znovu.

Před provedením si ověřte kontrakt a vedlejší účinky procedury. Každý příkaz, který běží na spojení s čekající lokální transakcí, musí nastavit Transaction. Ujistěte se, že zpracování transakcí procedury podporuje vnější transakci. Tento postup zachytí chyby a zaznamená je pomocí dbo.uspLogError místo toho, aby je propagoval volajícímu.

Čtení sad výsledků

Použijte ExecuteReader nebo ExecuteReaderAsync, když procedura vrací řádky. Když vrátí více než jednu sadu výsledků, volejte NextResult nebo NextResultAsync, dokud nevrátí false.

Čtečku uvolněte před čtením z výstupních, vstupně-výstupních nebo návratových parametrů. Tyto hodnoty nejsou konečné, dokud je čtečka otevřená.

Nakonfigurujte směry parametrů

Nastavte hodnotu Direction pro každý parametr, který není pouze pro vstup:

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;

Nastavte Size pro výstup proměnné délky a vstupní/výstupní parametry. Vstupní/výstupní parametr také potřebuje počáteční Value. Parametr return-value přijímá celé číslo z příkazu jazyka Transact-SQL (T-SQL) procedury RETURN. Nepřijímá žádnou sadu výsledků ani hodnotu vybranou procedurou.

Čtěte hodnotu každého parametru Value až po dokončení provádění a uzavření všech čteček:

command.ExecuteNonQuery();

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

Když procedura nepřiřadí výstupní parametr, její hodnota může být DBNull.Value.

Vyberte metodu spuštění

Použijte metodu spuštění, která odpovídá výsledku procedury:

Metoda Použít, když
ExecuteReader Procedura vrací řádky nebo více sad výsledků.
ExecuteNonQuery Potřebujete výstupní parametry nebo vrácenou hodnotu, ale nepotřebujete výsledkové řádky. Klient spotřebuje všechny vrácené řádky před dokončením volání.
ExecuteScalar Potřebujete jen první sloupec prvního řádku.

ExecuteNonQuery vrací počty ovlivněných řádků, když jsou k dispozici. Procedura, která používá , SET NOCOUNT ON může upravovat řádky a přesto vrátit -1. Použijte výstupní parametr nebo vrácenou hodnotu, pokud procedura některý z nich definuje jako podmínku úspěchu.

Poznámka:

Když SqlDataAdapter používá uloženou proceduru k aktualizaci nebo mazání řádků, nepoužívejte SET NOCOUNT ON, pokud adaptér spoléhá na počet ovlivněných řádků pro optimistické řízení souběžnosti. Nulový počet může způsobit DBConcurrencyException.

Řešení selhání

Zachyťte SqlException kolem volání spuštění. Procedura může spravovat své transakce a zpracování chyb sama, ale klient musí stále rozhodnout, zda je bezpečné operaci zopakovat a zda její spojení zůstává použitelné.

Informace o chybách serveru a zprávách PRINT najdete v tématu Zvládání chyb a zpráv.

Související obsah

  • Poslední aktualizace v