Nastavte parametry pomocí Microsoft.Data.SqlClient

Parametry předávají hodnoty příkazu samostatně od jeho textu jazyka Transact-SQL (T-SQL). Používejte SqlParameter objekty místo spojování nebo interpolace hodnot do SQL příkazů. Parametry pomáhají chránit před SQL injekcí a zachovávají databázový typ hodnoty.

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

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

Přidejte vstupní parametry

Přidejte parametr pro každý zástupný symbol hodnoty v textu příkazu. Zadejte typ SQL Server parametru a přiřadte jeho hodnotu:

const string sql = """
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
      AND ListPrice >= @minimumPrice
    ORDER BY ListPrice;
    """;

using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@subcategoryId", SqlDbType.Int).Value = 2;
command.Parameters.Add("@minimumPrice", SqlDbType.Money).Value = 1_000m;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)}: {reader.GetString(1)} ({reader.GetDecimal(2):C})");
}

Použijte stejnou velikost písmen pro název parametru v textu příkazu a v SqlParameter. SQL Server porovnává názvy proměnných pomocí kolace serveru, takže nesoulad selže s chybou „Je nutné deklarovat skalární proměnnou“ na serveru s kolací rozlišující velká a malá písmena.

Práce se zástupnými symboly parametrů

Microsoft.Data.SqlClient používá pojmenované parametry ve tvaru @parameterName. Zástupné symboly pozičního otazníku (?) nejsou podporovány.

Zástupné symboly parametrů představují hodnoty, nikoli SQL identifikátory nebo syntaxi. Nemůžete použít parametr pro název tabulky, název sloupce, směr třídění nebo celý SQL fragment. Pokud aplikace dynamicky vybere identifikátor, ověřte jej podle seznamu povolených a přidejte jej do textu příkazu až po ověření. Pokračujte v předávání všech datových hodnot jako parametrů.

Pro textové příkazy Microsoft.Data.SqlClient posílá parametrizované příkazy do serveru SQL Server přes sp_executesql. SQL Server může znovu použít plán vykonání, pokud text příkazu a definice parametrů zůstanou stejné.

Zadání datových typů parametrů

Nastavte SqlDbType explicitně, když aplikace zná cílový typ SQL Server. Nastavte také typově specifická metadata, jako jsou Size, Precision a Scale, pokud je to relevantní.

Tento příklad odpovídá hodnotám ve sloupcích nvarchar(50) a datetime v Person.Person:

const string sql = """
    SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE LastName = @lastName
      AND ModifiedDate >= @modifiedSince
    ORDER BY BusinessEntityID;
    """;

using var command = new SqlCommand(sql, connection);

SqlParameter lastName = command.Parameters.Add(
    "@lastName",
    SqlDbType.NVarChar,
    50);
lastName.Value = "O'Brien";

SqlParameter modifiedSince = command.Parameters.Add(
    "@modifiedSince",
    SqlDbType.DateTime);
modifiedSince.Value = new DateTime(2013, 1, 1);

Přizpůsobení metadat parametru cílovému sloupci zabraňuje implicitním převodům a brání tomu, aby změny délky řetězce nebo počtu desetinných míst měnily definici parametru.

Pro desetinné hodnoty nastavte přesnost a počet desetinných míst explicitně. Sloupec Weight v Production.Product používá decimal(8, 2):

const string sql = """
    SELECT ProductID, Name, Weight
    FROM Production.Product
    WHERE Weight >= @minimumWeight
    ORDER BY Weight;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter minimumWeight = command.Parameters.Add(
    "@minimumWeight",
    SqlDbType.Decimal);
minimumWeight.Precision = 8;
minimumWeight.Scale = 2;
minimumWeight.Value = 10.25m;

Předat hodnoty NULL databázi

Přiřaďte Value, aby se odeslala databáze NULL. C# null nereprezentuje parametr SQL Server NULL pro běžné skalární parametry:

const string sql = """
    SELECT SalesOrderID, OrderDate, SalesPersonID
    FROM Sales.SalesOrderHeader
    WHERE (@salesPersonId IS NULL OR SalesPersonID = @salesPersonId)
    ORDER BY SalesOrderID;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter salesPersonId = command.Parameters.Add(
    "@salesPersonId",
    SqlDbType.Int);

int? selectedSalesPersonId = null;
salesPersonId.Value = selectedSalesPersonId.HasValue
    ? selectedSalesPersonId.Value
    : DBNull.Value;

Selektivně používejte odvozená metadata

Metoda AddWithValue odvozuje metadata parametrů z hodnoty za běhu. Inference je praktická, když jsou odvozená metadata stabilní a odpovídají typu databáze. Může vytvářet různé definice parametrů, když se mění délka řetězce, desetinná přesnost nebo jiné hodnotové charakteristiky.

Preferujte Add s explicitním SqlDbType pro produkční příkazy, jejichž cílové schéma je známo. Nepředávejte C# null do proměnné AddWithValue; zadejte typ a místo něj přiřaďte DBNull.Value.

Znovu použít parametry příkazu

Vytvořte parametr jednou a změňte pouze jeho Value, když spustíte stejný příkaz s různými hodnotami:

const string sql = """
    SELECT TOP (1) Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
    ORDER BY ListPrice DESC;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter subcategoryId = command.Parameters.Add(
    "@subcategoryId",
    SqlDbType.Int);

foreach (int id in new[] { 1, 2, 3 })
{
    subcategoryId.Value = id;

    using SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        Console.WriteLine($"{id}: {reader.GetString(0)}");
    }
}

Nepřidávejte další parametr se stejným názvem při každém provedení. Znovu použijte existující parametr nebo vymažte kolekci před jejím znovuvytvořením.

Nastavte směr předávání parametru

Direction vlastnost určuje, jak příkaz používá parametr. Nastavte ji na ParameterDirection hodnotu:

Direction Použít
Input Posílá hodnotu do příkazu SQL nebo uložené procedury. Tato hodnota je výchozí.
Output Přijímá hodnotu z výstupního parametru uložené procedury.
InputOutput Odešle počáteční hodnotu a přijme aktualizovanou hodnotu.
ReturnValue Přijímá celočíselnou hodnotu z příkazu T-SQL RETURN .

Pro výstupní, vstupní/výstupní a parametry návratové hodnoty čtěte Value až po dokončení příkazu. Pokud příkaz vrací řádky, před načtením těchto hodnot uzavřete nebo zlikvidujte SqlDataReader.

Pro kompletní příklady uložených procedur viz Úprava dat pomocí uložených procedur.

Použijte parametry s uloženou procedurou

Nastavte StoredProcedure a použijte názvy parametrů procedury. Tento příklad volá postup dbo.uspGetEmployeeManagers zahrnutý v AdventureWorks:

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

Název parametru musí odpovídat názvu deklarovanému ve specifikaci uložené procedury.

Nakonfigurujte parametry s hodnotou tabulky

Parametr typu tabulka odesílá více řádků do uložené procedury nebo parametrizovaného příkazu. Chcete-li jeden nakonfigurovat:

  1. Nastavte SqlDbType.Structured.
  2. Nastavte TypeName na uživatelsky definovaný typ tabulky v databázi.
  3. Přiřaďte zdroj řádků DataTable, DbDataReader nebo IEnumerable<SqlDataRecord>.

DataTable s definovanými sloupci a bez řádků odesílá prázdnou tabulku. Prázdné pole IEnumerable<SqlDataRecord> vyvolá ArgumentException; přiřaďte C# null, aby se pro daný vstupní formulář odeslaly nulové řádky. Nepřiřazujte DBNull.Value k parametru s tabulkovou hodnotou.

Pro tvorbu typů, mapování řádků a kompletní příklady viz Parametry s hodnotou tabulky.

Nastavení hodnot streamování

Přiřaďte Stream, TextReader nebo XmlReader k Value, a postupně tak poskytněte velkou hodnotu. Pro přenos s omezenou pamětí použijte typ parametru (max), xml nebo json. Microsoft. Data.SqlClient před vykonáním bufferuje zdroj pro(max) jiné než znakové typy a binární soubory a pro starší velké objektové typy.

Typ parametru musí odpovídat hodnotě:

Value Kompatibilní SqlDbType hodnoty
Stream Binary, VarBinary nebo starší typ Image
TextReader Char, NChar, NVarChar, VarChar, starší typy Text a NText, Xml, nebo Json
XmlReader Char, NChar, NVarChar, VarChar, starší typy Text a NText, Xml, nebo Json

Microsoft.Data.SqlClient vyvolá ArgumentException, InvalidCastException nebo SqlTypeException, když je typ objektu, SqlDbType nebo deklarovaná velikost nekompatibilní. Pro kompletní příklady nahrávání a stahování viz podpora streamování SqlClient.

Odvození informací o parametrech

DeriveParametersdotazuje SQL Server na metadata parametrů uložené procedury a nahrazuje stávající kolekci parametrů příkazu:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(command);
command.Parameters["@BusinessEntityID"].Value = 6;

DeriveParameters vyžaduje pokaždé, když je volán, další komunikaci s databází. Během vývoje odvozujte metadata, když je to nutné, a poté explicitně definujte parametry v cestách citlivých na výkon.

Související obsah

  • Poslední aktualizace v