Udostępnij za pośrednictwem

Konfigurowanie parametrów i typów danych parametrów

  • Artykuł

Obiekty poleceń używają parametrów do przekazywania wartości do instrukcji SQL lub procedur składowanych, zapewniając sprawdzanie typów i walidację. W przeciwieństwie do tekstu polecenia dane wejściowe parametru są traktowane jako wartość literału, a nie jako kod wykonywalny. Pomaga to chronić przed atakami polegającymi na wstrzyknięciu kodu SQL, w których osoba atakująca wstawia polecenie, które narusza zabezpieczenia na serwerze w instrukcji SQL.

Polecenia sparametryzowane mogą również poprawić wydajność wykonywania zapytań, ponieważ pomagają one serwerowi bazy danych dokładnie dopasować przychodzące polecenie z odpowiednim planem zapytań w pamięci podręcznej. Aby uzyskać więcej informacji, zobacz Buforowanie planu wykonywania i ponowne używanie oraz parametry i ponowne użycie planu wykonania. Oprócz korzyści z zabezpieczeń i wydajności sparametryzowane polecenia zapewniają wygodną metodę organizowania wartości przekazywanych do źródła danych.

DbParameter Obiekt można utworzyć przy użyciu konstruktora lub dodać go do DbParameterCollection obiektu przez wywołanie Add metody kolekcjiDbParameterCollection. Metoda Add będzie przyjmować jako argumenty konstruktora wejściowego lub istniejący obiekt parametru w zależności od dostawcy danych.

Dostarczanie właściwości ParameterDirection

Podczas dodawania ParameterDirection parametrów należy podać właściwość parametrów innych niż parametry wejściowe. W poniższej ParameterDirection tabeli przedstawiono wartości, których można użyć z wyliczeniem ParameterDirection .

Nazwa elementu członkowskiego opis
Input Parametr jest parametrem wejściowym. Jest to opcja domyślna.
InputOutput Parametr może wykonywać zarówno dane wejściowe, jak i wyjściowe.
Output Parametr jest parametrem wyjściowym.
ReturnValue Parametr reprezentuje wartość zwracaną z operacji, takiej jak procedura składowana, wbudowana funkcja lub funkcja zdefiniowana przez użytkownika.

Praca z symbolami zastępczymi parametrów

Składnia symboli zastępczych parametrów zależy od źródła danych. Dostawcy danych programu .NET Framework obsługują nazewnictwo i określanie parametrów i symboli zastępczych parametrów w inny sposób. Ta składnia jest dostosowywana do określonego źródła danych zgodnie z opisem w poniższej tabeli.

Dostawca danych Składnia nazewnictwa parametrów
System.Data.SqlClient Używa nazwanych parametrów w formacie @nazwa_parametru.
System.Data.OleDb Używa znaczników parametrów pozycyjnych wskazywanych przez znak zapytania (?).
System.Data.Odbc Używa znaczników parametrów pozycyjnych wskazywanych przez znak zapytania (?).
System.Data.OracleClient Używa nazwanych parametrów w formacie :parmname (lub parmname).

Określanie typów danych parametrów

Typ danych parametru jest specyficzny dla dostawcy danych programu .NET Framework. Określenie typu konwertuje wartość Parameter elementu na typ dostawcy danych programu .NET Framework przed przekazaniem wartości do źródła danych. Można również określić typ obiektu Parameter w ogólny sposób, ustawiając DbType właściwość Parameter obiektu na określony DbTypeelement .

Typ Parameter dostawcy danych programu .NET Framework obiektu jest wnioskowany z typu Parameter Value .NET Framework obiektu lub DbType Parameter obiektu. W poniższej tabeli przedstawiono wywnioskowany Parameter typ na podstawie obiektu przekazanego Parameter jako wartość lub określony DbTypeelement .

Typ programu .NET Framework DbType SqlDbType OleDbType OdbcType OracleType
Boolean Wartość logiczna Bit Wartość logiczna Bit Byte
Byte Byte TinyInt NiepodpisaneTinyInt TinyInt Byte
byte[] Plik binarny VarBinary. Ta niejawna konwersja zakończy się niepowodzeniem, jeśli tablica bajtów jest większa niż maksymalny rozmiar elementu VarBinary, który wynosi 8000 bajtów. W przypadku tablic bajtów większych niż 8000 bajtów jawnie ustaw wartość SqlDbType. VarBinary Plik binarny Nieprzetworzone
Char Wnioskowanie wartości SqlDbType z znaku nie jest obsługiwane. Char Char Byte
DateTime DateTime DateTime DbTimeStamp DateTime DateTime
DateTimeOffset DateTimeOffset DateTimeOffset w programie SQL Server 2008. Wnioskowanie SqlDbType elementu z elementu DateTimeOffset nie jest obsługiwane w wersjach programu SQL Server starszych niż SQL Server 2008. DateTime
Decimal Dziesiętne Dziesiętne Dziesiętne Liczbowe Liczba
Double Liczba rzeczywista Liczba zmiennoprzecinkowa Liczba rzeczywista Liczba rzeczywista Liczba rzeczywista
Single Pojedynczy Rzeczywista Pojedynczy Rzeczywista Liczba zmiennoprzecinkowa
Guid Identyfikator GUID UniqueIdentifier Identyfikator GUID UniqueIdentifier Nieprzetworzone
Int16 Int16 SmallInt SmallInt SmallInt Int16
Int32 Int32 Int Int Int Int32
Int64 Int64 BigInt BigInt BigInt Liczba
Object Objekt Wariant Wariant Wnioskowanie odbcType z obiektu nie jest obsługiwane. Obiekt blob
String String NVarChar. Ta niejawna konwersja zakończy się niepowodzeniem, jeśli ciąg jest większy niż maksymalny rozmiar NVarChar, czyli 4000 znaków. W przypadku ciągów większych niż 4000 znaków jawnie ustaw wartość SqlDbType. VarWChar NVarChar NVarChar
TimeSpan Czas Czas w programie SQL Server 2008. Wnioskowanie SqlDbType funkcji TimeSpan nie jest obsługiwane w wersjach programu SQL Server starszych niż SQL Server 2008. DBTime Czas DateTime
UInt16 UInt16 Wnioskowanie elementu SqlDbType z UInt16 nie jest obsługiwane. UnsignedSmallInt Int UInt16
UInt32 UInt32 Wnioskowanie z SqlDbType funkcji UInt32 nie jest obsługiwane. NiepodpisaneInt BigInt UInt32
UInt64 UInt64 Wnioskowanie elementu z interfejsu SqlDbType UInt64 nie jest obsługiwane. NiepodpisaneBigInt Liczbowe Liczba
AnsiString VarChar VarChar VarChar VarChar
AnsiStringFixedLength Char Char Char Char
Waluta Money Waluta Wnioskowanie elementu OdbcType from Currency nie jest obsługiwane. Liczba
Data Data w programie SQL Server 2008. Wnioskowanie SqlDbType od daty nie jest obsługiwane w wersjach programu SQL Server starszych niż SQL Server 2008. DbDate Date DateTime
SByte Wnioskowanie z bajtu SqlDbType SByte nie jest obsługiwane. TinyInt Wnioskowanie z bajtu OdbcType SByte nie jest obsługiwane. SByte
StringFixedLength NChar WChar NChar NChar
Czas Czas w programie SQL Server 2008. Wnioskowanie funkcji SqlDbType time nie jest obsługiwane w wersjach programu SQL Server starszych niż SQL Server 2008. DBTime Czas DateTime
Wariancja wariancyjna Wnioskowanie z SqlDbType funkcji VarNumeric nie jest obsługiwane. Wariancja wariancyjna Wnioskowanie z OdbcType funkcji VarNumeric nie jest obsługiwane. Liczba
typ zdefiniowany przez użytkownika (obiekt z SqlUserDefinedAggregateAttribute Obiekt lub ciąg, w zależności od dostawcy (sqlClient zawsze zwraca obiekt, Odbc zawsze zwraca ciąg, a zarządzany dostawca danych OleDb może zobaczyć albo SqlDbType.Udt, jeśli SqlUserDefinedTypeAttribute jest obecny, w przeciwnym razie wariant OleDbType.VarWChar (jeśli wartość ma wartość null) w przeciwnym razie OleDbType.Variant. OdbcType.NVarChar nieobsługiwane

Uwaga

Konwersje z liczby dziesiętnej do innych typów są zawężające konwersje, które zaokrąglają wartość dziesiętną do najbliższej wartości całkowitej w kierunku zera. Jeśli wynik konwersji nie może być reprezentowany w typie docelowym, OverflowException jest zgłaszany.

Uwaga

Po wysłaniu wartości parametru null do serwera należy określić DBNullwartość , a nie null (Nothing w Visual Basic). Wartość null w systemie jest pustym obiektem, który nie ma wartości. DBNull służy do reprezentowania wartości null. Aby uzyskać więcej informacji na temat wartości null bazy danych, zobacz Obsługa wartości null.

Wyprowadzanie informacji o parametrach

Parametry mogą również pochodzić z procedury składowanej przy użyciu DbCommandBuilder klasy . Zarówno klasy , jak SqlCommandBuilder i OleDbCommandBuilder zapewniają metodę statyczną , DeriveParametersktóra automatycznie wypełnia kolekcję parametrów obiektu polecenia, który używa informacji o parametrach z procedury składowanej. Należy pamiętać, że DeriveParameters zastępuje wszystkie istniejące informacje o parametrach polecenia.

Uwaga

Wyprowadzanie informacji o parametrach wiąże się z karą za wydajność, ponieważ wymaga dodatkowej rundy w źródle danych w celu pobrania informacji. Jeśli informacje o parametrach są znane w czasie projektowania, możesz poprawić wydajność aplikacji, ustawiając jawnie parametry.

Aby uzyskać więcej informacji, zobacz Generowanie poleceń za pomocą poleceń CommandBuilders.

Używanie parametrów z poleceniem SqlCommand i procedurą składowaną

Procedury składowane oferują wiele zalet w aplikacjach opartych na danych. Korzystając z procedur składowanych, operacje bazy danych można hermetyzować w jednym poleceniu, zoptymalizowane pod kątem najlepszej wydajności i ulepszone z dodatkowymi zabezpieczeniami. Chociaż procedurę składowaną można wywołać, przekazując nazwę procedury składowanej, a następnie argumenty parametrów jako instrukcję SQL, przy użyciu Parameters kolekcji obiektu ADO.NET DbCommand umożliwia bardziej jawne zdefiniowanie parametrów procedury składowanej oraz uzyskiwanie dostępu do parametrów wyjściowych i zwracanych wartości.

Uwaga

Instrukcje sparametryzowane są wykonywane na serwerze przy użyciu polecenia sp_executesql, co umożliwia ponowne użycie planu zapytań. Lokalne kursory lub zmienne w partii nie są widoczne dla sp_executesql partii, która wywołuje metodę sp_executesql. Zmiany w kontekście bazy danych trwają tylko na końcu instrukcji sp_executesql . Aby uzyskać więcej informacji, zobacz sp_executesql (Transact-SQL).

W przypadku używania parametrów z parametrami SqlCommand do wykonania procedury składowanej programu SQL Server nazwy parametrów dodanych do Parameters kolekcji muszą być zgodne z nazwami znaczników parametrów w procedurze składowanej. Dostawca danych programu .NET Framework dla programu SQL Server nie obsługuje symbolu zastępczego znaku zapytania (?) do przekazywania parametrów do instrukcji SQL lub procedury składowanej. Traktuje parametry w procedurze składowanej jako nazwane parametry i wyszukuje pasujące znaczniki parametrów. Na przykład CustOrderHist procedura składowana jest definiowana przy użyciu parametru o nazwie @CustomerID. Gdy kod wykonuje procedurę składowaną, musi również użyć parametru o nazwie @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Przykład

W tym przykładzie pokazano, jak wywołać procedurę składowaną programu SQL Server w przykładowej Northwind bazie danych. Nazwa procedury składowanej to dbo.SalesByCategory i ma parametr wejściowy o nazwie @CategoryName z typem nvarchar(15)danych . Kod tworzy nowy SqlConnection wewnątrz bloku using, aby połączenie zostało usunięte po zakończeniu procedury. Obiekty SqlCommand i SqlParameter są tworzone i ich zestaw właściwości. Obiekt SqlDataReader wykonuje SqlCommand element i zwraca zestaw wyników z procedury składowanej, wyświetlając dane wyjściowe w oknie konsoli.

Uwaga

Zamiast tworzyć SqlCommand obiekty i SqlParameter , a następnie ustawiać właściwości w osobnych instrukcjach, można zamiast tego wybrać użycie jednego z przeciążonych konstruktorów w celu ustawienia wielu właściwości w jednej instrukcji.

static void GetSalesByCategory(string connectionString,
    string categoryName)
{
    using (SqlConnection connection = new(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new()
        {
            Connection = connection,
            CommandText = "SalesByCategory",
            CommandType = CommandType.StoredProcedure
        };

        // Add the input parameter and set its properties.
        SqlParameter parameter = new()
        {
            ParameterName = "@CategoryName",
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input,
            Value = categoryName
        };

        // Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
            reader.Close();
        }
    }
}

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Using reader As SqlDataReader = command.ExecuteReader()

            If reader.HasRows Then
                Do While reader.Read()
                    Console.WriteLine("{0}: {1:C}", _
                      reader(0), reader(1))
                Loop
            Else
                Console.WriteLine("No rows returned.")
            End If
        End Using
    End Using
End Sub

Używanie parametrów z poleceniem OleDbCommand lub OdbcCommand

W przypadku używania parametrów z elementem OleDbCommand lub OdbcCommandkolejność parametrów dodanych do Parameters kolekcji musi być zgodna z kolejnością parametrów zdefiniowanych w procedurze składowanej. Dostawca danych .NET Framework dla ole DB i .NET Framework Dostawca danych dla parametrów odBC traktują parametry w procedurze składowanej jako symbole zastępcze i stosują wartości parametrów w kolejności. Ponadto parametry wartości zwracanej muszą być pierwszymi parametrami dodanymi do kolekcji Parameters .

Dostawca danych .NET Framework dla ole DB i .NET Framework Dostawca danych dla ODBC nie obsługują nazwanych parametrów przekazywania parametrów do instrukcji SQL lub procedury składowanej. W tym przypadku należy użyć symbolu zastępczego znaku zapytania (?), jak w poniższym przykładzie.

SELECT * FROM Customers WHERE CustomerID = ?

W związku z tym kolejność Parameter dodawania obiektów do Parameters kolekcji musi bezpośrednio odpowiadać pozycji obiektu ? symbol zastępczy parametru .

Przykład oledb

Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

OleDbParameter parameter = command.Parameters.Add(
  "RETURN_VALUE", OleDbType.Integer);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add(
  "@InputParm", OleDbType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add(
  "@OutputParm", OleDbType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Przykład odbc

Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OdbcCommand command = new OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;

OdbcParameter parameter = command.Parameters.Add( _
  "RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Zobacz też