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 Buforowanie i ponowne użycie planu wykonania 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.

Obiekt DbParameter można utworzyć przy użyciu konstruktora lub dodać go do DbParameterCollection, wywołując metodę Add kolekcji DbParameterCollection. Metoda Add przyjmuje albo argumenty konstruktora, albo istniejący obiekt parametru, zależnie 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 tabeli ParameterDirection przedstawiono wartości, które można użyć z wyliczeniem ParameterDirection.

Praca z zastępnikami 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.

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 właściwość obiektu DbType na określony Parameter.

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

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.

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

Używanie parametrów z komendą SqlCommand oraz procedurą składowaną

Procedury składowane oferują wiele zalet w aplikacjach bazujących na danych. Korzystając z procedur składowanych, operacje bazy danych można zamknąć za pomocą jednej komendy, co zapewnia optymalną wydajność i zwiększone bezpieczeństwo. 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.

W przypadku używania parametrów z SqlCommand do wykonania procedury składowanej w SQL Server, nazwy parametrów dodanych do kolekcji Parameters 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 procedura składowana CustOrderHist jest definiowana przy użyciu nazwanego parametru @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, a ich właściwości ustawiane. Obiekt SqlDataReader wykonuje SqlCommand i zwraca zestaw wyników z procedury składowanej, wyświetlając wynik w oknie konsoli.

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($"{reader[0]}: {reader[1]:C}");
                }
            }
            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 Dostawca danych .NET Framework dla ODBC traktują parametry w procedurze składowanej jako symbole zastępcze i stosują wartości parametrów w odpowiedniej kolejności. Ponadto parametry wartości zwracanej muszą być pierwszymi parametrami dodanymi do kolekcji Parameters .

Dostawca danych .NET Framework dla OLE DB i dostawca danych .NET Framework dla ODBC nie obsługują nazwanych parametrów do przekazywania wartości 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