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

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 Plan wykonania Buforowanie i Ponowne używanie parametrów oraz ponowne użycie planu wykonania 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 ParameterValue .NET Framework obiektu lub DbTypeParameter 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	Bitowych	Wartość logiczna	Bitowych	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.	Unsignedint	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
	Varnumeric	Wnioskowanie z SqlDbType funkcji VarNumeric nie jest obsługiwane.	Varnumeric	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 sp_executesql, którego można ponownie użyć planu zapytania. 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ż

• Polecenia i parametry
• Parametry elementu DataAdapter
• Mapowanie typu danych w ADO.NET
• Omówienie ADO.NET