Konfiguruj parametry z Microsoft. Data.SqlClient

Parametry służą do przekazywania wartości do polecenia oddzielnie od jego tekstu Transact-SQL (T-SQL). Używaj SqlParameter obiektów zamiast łączyć lub interpolować wartości w instrukcjach SQL. Parametry pomagają chronić przed wstrzyknięciem kodu SQL i zachowują typ danych wartości w bazie danych.

Przykłady w tym artykule zakładają, że otwarte SqlConnection o nazwie connection jest ukierunkowane na przykładową bazę danych AdventureWorks.

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

Dodaj parametry wejściowe

Dodaj parametr dla każdego symbolu zastępczego wartości w tekście polecenia. Określ typ SQL Server parametru i przypisz jego wartość:

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

Używaj tej samej wielkości liter dla nazwy parametru w tekście poleceń oraz w SqlParameter. SQL Server dopasowuje nazwy zmiennych za pomocą sortacji serwera, więc niedopasowanie powoduje błąd „Must declare the scalar variable” na serwerze z sortacją rozróżniającą wielkość liter.

Praca z parametrami zastępczymi

Microsoft. Data.SqlClient używa nazwanych parametrów w formie @parameterName. Symbole zastępcze pozycyjnego znaku zapytania (?) nie są obsługiwane.

Symbole parametrów reprezentują wartości, a nie identyfikatory SQL czy składnię. Nie możesz użyć parametru do nazwy tabeli, nazwy kolumny, kierunku sortowania ani całego fragmentu SQL. Jeśli aplikacja wybiera identyfikator dynamicznie, zweryfikowaj go względem listy dozwolonych i dodaj do tekstu polecenia dopiero po weryfikacji. Kontynuuj przekazywanie wszystkich wartości danych jako parametrów.

Dla poleceń tekstowych, Microsoft. Data.SqlClient wysyła parametryzowane instrukcje do SQL Server przez sp_executesql. SQL Server może ponownie wykorzystać plan wykonania, gdy tekst instrukcji i definicje parametrów pozostają takie same.

Określanie typów danych parametrów

Ustaw SqlDbType to wyraźnie, gdy aplikacja zna docelowy typ SQL Server. Ustaw także metadane specyficzne dla typu, takie jak Size, Precision, i Scale gdy to dotyczy.

Ten przykład odpowiada kolumnom nvarchar(50) i datetime w 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);

Dopasowywanie metadanych parametrów do kolumny docelowej unika niejawnych konwersji i zapobiega zmianom długości ciągu lub skali dziesiętnej, które mogłyby zmienić definicję parametru.

Dla wartości dziesiętnych ustaw jawnie precyzję i skalę. Kolumna Weight w Production.Product używa 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;

Przekaż wartości NULL w bazie danych

Przydziel Value do wysłania bazy danych NULL. C# null nie reprezentuje typu SQL Server NULL dla zwykłych parametrów skalarnych:

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;

Używaj wywnioskowanych metadanych wybiórczo

Metoda AddWithValue wywnioskuje metadane parametrów na podstawie wartości w środowisku uruchomieniowym. Wnioskowanie jest wygodne, gdy wywnioskowane metadane są stabilne i odpowiadają typowi bazy danych. Może generować różne definicje parametrów, gdy zmieniają się długości ciągów tekstów, precyzja dziesiętna lub inne cechy wartości.

Preferuj Add z jawnym SqlDbType dla poleceń produkcyjnych, których schemat docelowy jest znany. Nie przekazuj C# null do AddWithValue; określ typ i przypisz DBNull.Value zamiast niego.

Użyj ponownie parametrów polecenia

Stwórz parametr raz i zmieniaj tylko jego Value, gdy uruchamiasz to samo polecenie z różnymi wartościami:

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

Nie dodawaj kolejnego parametru o tej samej nazwie przy każdym wykonaniu. Ponownie użyj istniejącego parametru lub wyczyść kolekcję przed jej odbudową.

Ustaw kierunek parametru

Właściwość Direction identyfikuje, jak polecenie wykorzystuje parametr. Ustaw ją na wartość ParameterDirection:

Direction Używanie
Input Wysyła wartość do instrukcji lub procedury składowanej. Jest to wartość domyślna.
Output Otrzymuje wartość z parametru wyjścia procedury przechowywanej.
InputOutput Wysyła wartość początkową i otrzymuje zaktualizowaną wartość.
ReturnValue Otrzymuje wartość całkowitą z instrukcji T-SQL RETURN .

Dla parametrów wyjściowych, wejściowych/wyjściowych i wartości zwrotnej, odczytuj Value dopiero po zakończeniu polecenia. Jeśli polecenie zwraca wiersze, zamknij lub usuń element SqlDataReader przed odczytaniem tych wartości.

Pełne przykłady procedur składowanych można znaleźć w artykule Zmodyfikuj dane za pomocą procedur składowanych.

Używanie parametrów w procedurze składowanej

Ustaw StoredProcedure i użyj nazw parametrów procedury. Ten przykład wywołuje procedurę dbo.uspGetEmployeeManagers zawartą w 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)}");
}

Nazwa parametru musi odpowiadać nazwie zadeklarowanej przez procedurę składowaną.

Konfiguruj parametry o wartościach tabelarycznych

Parametr o wartości tabelarycznej wysyła wiele wierszy do procedury składowanej lub parametryzowanej instrukcji. Aby go skonfigurować:

  1. Ustaw wartość SqlDbType.Structured.
  2. Ustaw TypeName na typ tabeli zdefiniowany przez użytkownika w bazie danych.
  3. Przypisz źródło wiersza: DataTable, DbDataReader lub IEnumerable<SqlDataRecord>.

A DataTable z zdefiniowanymi kolumnami i bez wierszy wysyła pustą tabelę. Pusty element IEnumerable<SqlDataRecord> powoduje ArgumentException; przypisz C# null, aby wysłać 0 wierszy dla tego formularza wejściowego. Nie przypisuj DBNull.Value do parametru o wartości tabelarycznej.

Aby uzyskać informacje o tworzeniu typów, mapowaniu wierszy i pełnych przykładach, zobacz parametry o wartościach tabelarycznych.

Konfiguruj wartości strumieniowania

Przypisz Stream, TextReader lub XmlReader do Value, aby stopniowo zapewniać dużą wartość. Do transferu z ograniczoną pamięcią użyj typu parametru (max), xml lub json. Microsoft.Data.SqlClient buforuje źródło przed wykonaniem dla typów innych niż znakowe(max) i binarne oraz starszych typów dużych obiektów.

Wartość musi odpowiadać typowi parametru:

Value Wartości kompatybilne SqlDbType
Stream Binary, VarBinary, czyli starszy typ Image
TextReader Char, NChar, NVarChar, VarChar, starsze typy Text i NText, Xml, lub Json
XmlReader Char, NChar, NVarChar, VarChar, starsze typy Text i NText, Xml, lub Json

Microsoft.Data.SqlClient wyrzuca ArgumentException, InvalidCastException lub SqlTypeException, gdy typ obiektu, SqlDbType lub deklarowany rozmiar są niekompatybilne. Pełne przykłady przesyłania i pobierania można znaleźć w artykule SqlClient streaming support.

Uzyskiwanie informacji o parametrach

DeriveParameterszapytuje SQL Server o metadane parametrów procedury przechowywanej i zastępuje istniejącą kolekcję parametrów polecenia:

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

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

DeriveParameters wymaga dodatkowego odwołania do bazy danych za każdym razem, gdy jest wywoływana. Generuj metadane podczas tworzenia, gdy jest to konieczne, a następnie definiuj parametry jawnie w ścieżkach wrażliwych na wydajność.

Treści powiązane

  • Ostatnia aktualizacja o