Konfigurace parametrů a datových typů parametrů

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 Ukládání plánu prová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 přidáním do DbParameterCollection zavoláním metody kolekce Add. 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 hodnoty ParameterDirection, 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 ve formátu @názevparametru.
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 v rozhraní .NET Framework objektu Parameter je odvozen z typu objektu Value v rozhraní .NET Framework, nebo z Parameter objektu DbType. Následující tabulka ukazuje odvozený Parameter typ na základě objektu, který je předán jako hodnota Parameter nebo který je specifikován jako DbType.

Typ rozhraní .NET Framework	DbType	SqlDbType	OleDbType	OdbcType	OracleType
Boolean	Boolovská logika	Bit	Boolovská logika	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.	Znak	Znak	byte
DateTime	Datum a čas	Datum a čas	DBTimeStamp	Datum a čas	Datum a čas
DateTimeOffset	Posun data a času	DateTimeOffset v SQL Serveru 2008. Odvození SqlDbType z dateTimeOffset není podporováno ve verzích SQL Serveru starších než SQL Server 2008.			Datum a čas
Decimal	Desítkové	Desítkové	Desítkové	Číselný	Číslo
Double	Hodnota s dvojitou přesností	Plovat	Hodnota s dvojitou přesností	Hodnota s dvojitou přesností	Hodnota s dvojitou přesností
Single	Jeden	Reálné	Jeden	Reálné	Plovat
Guid	Průvodce	Jedinečný identifikátor	Průvodce	Jedinečný identifikátor	Nezpracováno
Int16	Int16	SmallInt	SmallInt	SmallInt	Int16
Int32	Int32	int	int	int	Int32
Int64	Int64	BigInt	BigInt	BigInt	Číslo
Object	Objekt	Varianta	Varianta	Odvození odbcType z objektu není podporováno.	Bublina
String	řetězec	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	Datum a čas
UInt16	UInt16	Odvození SqlDbType z UInt16 není podporováno.	NepodepsanéMaléCeléČíslo	int	UInt16
UInt32	UInt32	Odvozování SqlDbType z UInt32 není podporováno.	Bez znaménka int	BigInt	UInt32
UInt64	UInt64	Odvození SqlDbType z UInt64 není podporováno.	UnsignedBigInt	Číselný	Číslo
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringPevenáDélka	Znak	Znak	Znak	Znak
	Měna	Peníze	Měna	Odvození OdbcType z Currency není podporováno.	Číslo
	Datum	Datum v SQL Serveru 2008 Odvozování SqlDbType z datového typu Date není podporováno ve verzích SQL Serveru starších než SQL Server 2008.	DbDatum	Datum	Datum a čas
	SByte	Odvození SqlDbType z SByte se nepodporuje.	TinyInt	Odvozování OdbcType ze SByte není podporováno.	SByte
	ŘetězecPevnáDélka	NChar	WChar	NChar	NChar
	Čas	Čas v SYSTÉMU SQL Server 2008 Odvození SqlDbType z času není podporováno ve verzích SQL Serveru před rokem 2008.	DBTime	Čas	Datum a čas
	VarNumeric	Odvození SqlDbType z VarNumeric není podporováno.	VarNumeric	Odvozování OdbcType z VarNumeric není podporováno.	Číslo
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 je SqlUserDefinedTypeAttribute přítomen, 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 nelze v cílovém typu reprezentovat, vyvolá se výjimka OverflowException.

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 má vliv na výkon, protože vyžaduje další komunikaci se zdrojem dat k načtení těchto informací. 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 pomocí 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 trvají pouze do konce sp_executesql příkazu. Další informace najdete v tématu sp_executesql (Transact-SQL).

Při použití parametrů ke spuštění uložené procedury SQL Serveru musí názvy parametrů přidaných do kolekce SqlCommand odpovídat názvům značek parametrů v uložené proceduře. Rozhraní Zprostředkovatel dat .NET Framework pro SQL Server nepodporuje použití zástupného symbolu otazníku (?) pro předávání parametrů do příkazu SQL nebo uložené procedury. 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 provede SqlCommand uloženou proceduru a vrátí sadu výsledků, zobrazující výstup v okně konzoly.

Poznámka:

Místo vytváření objektů SqlCommand a SqlParameter a jejich následného nastavení v samostatných příkazech můžete použít jeden z přetížených konstruktorů k nastavení více vlastností v jediném 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($"{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

Použití parametrů s příkazem 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. Zprostředkovatel dat .NET Framework pro OLE DB a ODBC zacházejí s parametry v uložené proceduře jako zástupné znaky a používají 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;

Viz také

• Příkazy a parametry
• Parametry adaptéru dat
• Mapování datového typu v ADO.NET
• Přehled ADO.NET