Konfigurace parametrů a datových typů parametrů

Článek
03/02/2024

Objekty příkazů používají parametry k předání hodnot příkazům SQL nebo uloženým procedurám, které poskytují kontrolu typů a ověřování. Na rozdíl od textu příkazu se vstup parametru považuje za literálovou hodnotu, ne jako spustitelný kód. To pomáhá chránit před útoky prostřednictvím injektáže SQL, ve kterých útočník vloží příkaz, který na serveru ohrožuje zabezpečení, do příkazu SQL.

Parametrizované příkazy můžou také zlepšit výkon provádění dotazů, protože pomáhají databázovému serveru přesně odpovídat příchozímu příkazu se správným plánem dotazů uloženým v mezipaměti. Další informace najdete v tématu Plán provádění Ukládání do mezipaměti a opakované použití a parametry a opětovné použití plánu provádění. Kromě výhod zabezpečení a výkonu poskytují parametrizované příkazy pohodlný způsob uspořádání hodnot předaných zdroji dat.

Objekt DbParameter lze vytvořit pomocí jeho konstruktoru nebo jeho přidáním do DbParameterCollection volání Add metody DbParameterCollection kolekce. Metoda Add bude v závislosti na zprostředkovateli dat brát jako vstupní argumenty konstruktoru nebo existující objekt parametru.

Zadání vlastnosti ParameterDirection

Při přidávání parametrů musíte zadat ParameterDirection vlastnost pro jiné parametry než vstupní parametry. Následující tabulka ukazuje ParameterDirection hodnoty, které můžete použít s výčtem ParameterDirection .

Název členu	Popis
Input	Parametr je vstupní parametr. Tato možnost je výchozí.
InputOutput	Parametr může provádět vstup i výstup.
Output	Parametr je výstupní parametr.
ReturnValue	Parametr představuje návratovou hodnotu z operace, jako je uložená procedura, integrovaná funkce nebo uživatelem definovaná funkce.

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

Syntaxe zástupných symbolů parametrů závisí na zdroji dat. Zprostředkovatelé dat rozhraní .NET Framework zpracovávají pojmenování a určují parametry a zástupné symboly parametrů odlišně. Tato syntaxe je přizpůsobená konkrétnímu zdroji dat, jak je popsáno v následující tabulce.

Poskytovatel dat	Syntaxe pojmenování parametrů
System.Data.SqlClient	Používá pojmenované parametry v názvu parametru formátu.`@`
System.Data.OleDb	Používá značky pozičních parametrů označené otazníkem (`?`).
System.Data.Odbc	Používá značky pozičních parametrů označené otazníkem (`?`).
System.Data.OracleClient	Používá pojmenované parametry ve formátu `:`parmname (nebo parmname).

Určení datových typů parametrů

Datový typ parametru je specifický pro zprostředkovatele dat rozhraní .NET Framework. Určení typu převede hodnotu Parameter na typ zprostředkovatele dat rozhraní .NET Framework před předáním hodnoty do zdroje dat. Typ objektu Parameter můžete také zadat obecným způsobem nastavením DbType vlastnosti objektu Parameter na určitý DbType.

Typ zprostředkovatele dat rozhraní .NET Framework objektu Parameter je odvozen z typu ParameterValue rozhraní .NET Framework objektu nebo z DbType objektuParameter. Následující tabulka ukazuje odvozený Parameter typ na základě objektu Parameter předaného jako hodnota nebo zadaný DbType.

Typ rozhraní .NET Framework	Dbtype	Sqldbtype	Oledbtype	Odbctype	OracleType
Boolean	Logická hodnota	Bit	Logická hodnota	Bit	Byte
Byte	Byte	Tinyint	UnsignedTinyInt	Tinyint	Byte
byte[]	Binární	Varbinary. Tento implicitní převod selže, pokud je pole bajtů větší než maximální velikost varBinary, což je 8 000 bajtů. Pro bajtová pole větší než 8 000 bajtů explicitně nastavte SqlDbType.	Varbinary	Binární	Nezpracováno
Char		Odvození znaku SqlDbType ze znaku není podporováno.	Char	Char	Byte
DateTime	DateTime	DateTime	DBTimeStamp	DateTime	DateTime
DateTimeOffset	DateTimeOffset	DateTimeOffset v SQL Serveru 2008. Odvození SqlDbType z dateTimeOffset není podporováno ve verzích SQL Serveru starších než SQL Server 2008.			DateTime
Decimal	Desetinné	Desetinné číslo	Desetinné číslo	Číslo	Počet
Double	Hodnota s dvojitou přesností	Float	Hodnota s dvojitou přesností	Hodnota s dvojitou přesností	Hodnota s dvojitou přesností
Single	Jeden	Reálné	Jeden	Reálné	Float
Guid	Guid	Uniqueidentifier	Guid	Uniqueidentifier	Nezpracováno
Int16	Int16	Smallint	Smallint	Smallint	Int16
Int32	Int32	Int	Int	Int	Int32
Int64	Int64	BigInt	BigInt	BigInt	Počet
Object	Object	Varianta	Varianta	Odvození odbcType z objektu není podporováno.	Objekt blob
String	String	Nvarchar. Tento implicitní převod selže, pokud je řetězec větší než maximální velikost NVarChar, což je 4000 znaků. Pro řetězce větší než 4000 znaků explicitně nastavte SqlDbType.	VarWChar	Nvarchar	Nvarchar
TimeSpan	Čas	Čas v SYSTÉMU SQL Server 2008 Odvození SqlDbType z TimeSpan není podporováno ve verzích SQL Serveru starších než SQL Server 2008.	DBTime	Čas	DateTime
UInt16	UInt16	Odvození z SqlDbType UInt16 se nepodporuje.	UnsignedSmallInt	Int	UInt16
UInt32	UInt32	Odvození z SqlDbType UInt32 se nepodporuje.	UnsignedInt	BigInt	UInt32
UInt64	UInt64	Odvození z SqlDbType UInt64 se nepodporuje.	UnsignedBigInt	Číslo	Počet
	AnsiString	Varchar	Varchar	Varchar	Varchar
	AnsiStringFixedLength	Char	Char	Char	Char
	Měna	Peníze	Měna	Odvození odsud `OdbcTypeCurrency` není podporováno.	Počet
	Datum	Datum v SQL Serveru 2008 Odvození data SqlDbType od data není podporováno ve verzích SQL Serveru starších než SQL Server 2008.	DbDate	Date	DateTime
	SByte	Odvození SqlDbType z SByte se nepodporuje.	Tinyint	Odvození z `OdbcType` SByte se nepodporuje.	SByte
	StringFixedLength	Nchar	Wchar	Nchar	Nchar
	Čas	Čas v SYSTÉMU SQL Server 2008 Odvozování SqlDbType času není podporováno ve verzích SQL Serveru starších než SQL Server 2008.	DBTime	Čas	DateTime
	VarNumeric	Odvození SqlDbType z funkce VarNumeric není podporováno.	VarNumeric	Odvození z `OdbcType` funkce VarNumeric není podporováno.	Počet
uživatelem definovaný typ (objekt s SqlUserDefinedAggregateAttribute	Objekt nebo řetězec v závislosti na zprostředkovateli (SqlClient vždy vrací objekt, Odbc vždy vrací řetězec a zprostředkovatel dat spravovaný OleDb může zobrazit buď	SqlDbType.Udt, pokud SqlUserDefinedTypeAttribute je k dispozici, jinak Variant	OleDbType.VarWChar (pokud hodnota je null), jinak OleDbType.Variant.	OdbcType.NVarChar	nepodporováno

Poznámka:

Převody z desítkového na jiné typy jsou zužující převody, které zaokrouhlují desetinnou hodnotu na nejbližší celočíselnou hodnotu směrem k nule. Pokud výsledek převodu není v cílovém typu reprezentovatelný, OverflowException vyvolá se chyba.

Poznámka:

Při odesílání hodnoty parametru null na server, musíte zadat DBNull, ne null (Nothing v jazyce Visual Basic). Hodnota null v systému je prázdný objekt, který nemá žádnou hodnotu. DBNull slouží k reprezentaci hodnot null. Další informace o hodnotách null databáze naleznete v tématu Zpracování hodnot null.

Odvození informací o parametrech

Parametry mohou být také odvozeny z uložené procedury pomocí DbCommandBuilder třídy. SqlCommandBuilder Obě třídy OleDbCommandBuilder poskytují statickou metodu, DeriveParameterskterá automaticky naplní kolekci parametrů objektu příkazu, který používá informace o parametrech z uložené procedury. Všimněte si, že DeriveParameters přepíše všechny existující informace o parametrech příkazu.

Poznámka:

Odvození informací o parametrech způsobuje snížení výkonu, protože k načtení informací vyžaduje další odezvu zdroje dat. Pokud jsou informace o parametrech známé v době návrhu, můžete zvýšit výkon aplikace nastavením parametrů explicitně.

Další informace naleznete v tématu Generování příkazů pomocí CommandBuilders.

Použití parametrů s sqlCommand a uloženou procedurou

Uložené procedury nabízejí mnoho výhod v aplikacích řízených daty. Pomocí uložených procedur lze databázové operace zapouzdřovat jedním příkazem, optimalizovat tak, aby byly co nejvýkonné a vylepšeny o další zabezpečení. I když uloženou proceduru lze volat předáním názvu uložené procedury následované argumenty parametrů jako příkazu SQL, pomocí Parameters kolekce objektu ADO.NET DbCommand umožňuje explicitnější definování parametrů uložené procedury a přístup k výstupním parametrům a návratovým hodnotám.

Poznámka:

Parametrizované příkazy se spouští na serveru, sp_executesql, což umožňuje opakované použití plánu dotazů. Místní kurzory nebo proměnné v sp_executesql dávce nejsou viditelné pro dávku, která volá sp_executesql. Změny v kontextu databáze trvaly pouze na konci sp_executesql příkazu. Další informace najdete v tématu sp_executesql (Transact-SQL).

Při použití parametrů se SqlCommand spuštěním uložené procedury SQL Serveru musí názvy parametrů přidaných do Parameters kolekce odpovídat názvům značek parametrů v uložené proceduře. Rozhraní .NET Framework Zprostředkovatel dat pro SQL Server nepodporuje zástupný symbol otazníku (?) pro předávání parametrů příkazu SQL nebo uložené proceduře. Zpracovává parametry v uložené proceduře jako pojmenované parametry a hledá odpovídající značky parametrů. Například uložená procedura CustOrderHist je definována pomocí parametru s názvem @CustomerID. Když váš kód spustí uloženou proceduru, musí také použít parametr s názvem @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Příklad

Tento příklad ukazuje, jak volat uloženou proceduru SQL Serveru v Northwind ukázkové databázi. Název uložené procedury je dbo.SalesByCategory a má vstupní parametr pojmenovaný @CategoryName s datovým typem nvarchar(15). Kód vytvoří nový SqlConnection uvnitř bloku using, aby bylo připojení uvolněno, když procedura skončí. Objekty SqlCommand a SqlParameter objekty jsou vytvořeny a jejich vlastnosti jsou nastaveny. A SqlDataReader spustí SqlCommand sadu výsledků uložené procedury a zobrazí výstup v okně konzoly.

Poznámka:

Místo vytváření SqlCommand a objektů a SqlParameter následného nastavení vlastností v samostatných příkazech se místo toho můžete rozhodnout použít jeden z přetížených konstruktorů k nastavení více vlastností v jednom příkazu.

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

Použití parametrů s OleDbCommand nebo OdbcCommand

Při použití parametrů s parametrem OleDbCommand nebo OdbcCommandmusí pořadí parametrů přidaných do Parameters kolekce odpovídat pořadí parametrů definovaných v uložené proceduře. Rozhraní .NET Framework Zprostředkovatel dat pro OLE DB a .NET Framework Zprostředkovatel dat pro rozhraní ODBC zacházet s parametry v uložené proceduře jako zástupné symboly a používat hodnoty parametrů v pořadí. Kromě toho musí být parametry návratové hodnoty prvními parametry přidanými do Parameters kolekce.

Rozhraní .NET Framework Zprostředkovatel dat pro OLE DB a rozhraní .NET Framework Zprostředkovatel dat pro rozhraní ODBC nepodporují pojmenované parametry pro předávání parametrů do příkazu SQL nebo uložené procedury. V tomto případě musíte použít zástupný symbol otazníku (?), jak je znázorněno v následujícím příkladu.

SELECT * FROM Customers WHERE CustomerID = ?

V důsledku toho musí pořadí, ve kterém Parameter jsou objekty přidány do Parameters kolekce, přímo odpovídat pozici ? zástupný symbol pro parametr.

Příklad 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;

Příklad 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;

Konfigurace parametrů a datových typů parametrů

Zadání vlastnosti ParameterDirection

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

Určení datových typů parametrů

Odvození informací o parametrech

Použití parametrů s sqlCommand a uloženou procedurou

Příklad

Použití parametrů s OleDbCommand nebo OdbcCommand

Příklad OleDb

Příklad odbc

Viz také

Další materiály