Paraméterek és paraméter adattípusok konfigurálása

  • Cikk

A parancsobjektumok paraméterekkel adják át az értékeket az SQL-utasításoknak vagy a tárolt eljárásoknak, és típusellenőrzést és ellenőrzést biztosítanak. A parancsszövegtől eltérően a paraméterbemenet konstans értékként, nem végrehajtható kódként lesz kezelve. Ez segít az "SQL-injektálási" támadások elleni védelemben, amelyben a támadó beszúr egy parancsot, amely veszélyezteti a kiszolgáló biztonságát egy SQL-utasításba.

A paraméteres parancsok a lekérdezések végrehajtási teljesítményét is javíthatják, mivel segítenek az adatbázis-kiszolgálónak a bejövő parancsnak a megfelelő gyorsítótárazott lekérdezési tervvel való pontos egyeztetésében. További információ: Végrehajtási terv gyorsítótárazása, újrafelhasználása , paraméterek és végrehajtási terv újrafelhasználása. A biztonsági és teljesítménybeli előnyök mellett a paraméteres parancsok kényelmes módszert biztosítanak az adatforrásnak átadott értékek rendszerezésére.

Egy DbParameter objektum létrehozható a konstruktor használatával, vagy a DbParameterCollection gyűjtemény metódusának DbParameterCollection meghívásávalAdd. A Add metódus bemenetként konstruktorargumentumokat vagy egy meglévő paraméterobjektumot fogad az adatszolgáltatótól függően.

A ParameterDirection tulajdonság megadása

Paraméterek hozzáadásakor a bemeneti paramétereken kívül egy ParameterDirection tulajdonságot kell megadnia. Az alábbi táblázat az ParameterDirection enumerálással ParameterDirection használható értékeket mutatja be.

Tag neve Leírás
Input A paraméter egy bemeneti paraméter. Ez az alapértelmezett beállítás.
InputOutput A paraméter képes a bemenetre és a kimenetre is.
Output A paraméter egy kimeneti paraméter.
ReturnValue A paraméter egy művelet , például tárolt eljárás, beépített függvény vagy felhasználó által definiált függvény visszatérési értékét jelöli.

Paraméterhelyőrzők használata

A paraméterhelyőrzők szintaxisa az adatforrástól függ. A .NET-keretrendszer adatszolgáltatók eltérően kezelik a paraméterek és a paraméterek helyőrzőinek elnevezését és megadását. Ez a szintaxis egy adott adatforrásra van testre szabva, az alábbi táblázatban leírtak szerint.

Adatszolgáltató Paraméterelnevezési szintaxis
System.Data.SqlClient Elnevezett paramétereket használ a formátumparaméternévben@.
System.Data.OleDb Kérdőjel (?) által jelzett pozícióparaméter-jelölőket használ.
System.Data.Odbc Kérdőjel (?) által jelzett pozícióparaméter-jelölőket használ.
System.Data.OracleClient Elnevezett paramétereket használ a parmname (vagy parmname) formátumban:.

Paraméter adattípusának megadása

A paraméter adattípusa a .NET-keretrendszer adatszolgáltatóra jellemző. A típus megadása a .NET-keretrendszer adatszolgáltató típussá alakítja át az értéketParameter, mielőtt átadja az értéket az adatforrásnak. Az objektum típusát Parameter általános módon is megadhatja úgy, hogy az DbTypeParameter objektum tulajdonságát egy adott DbTypeértékre állítja.

Az objektum .NET-keretrendszer adatszolgáltatójának Parameter típusa az objektum .NET-keretrendszer típusából ValueParameter vagy az DbTypeParameter objektumból származik. Az alábbi táblázat az értékként vagy a megadottként ParameterDbTypeátadott objektum alapján kikövetkozott Parameter típust mutatja be.

.NET-keretrendszer típusa DbType SqlDbType OleDbType OdbcType OracleType
Boolean Logikai Kicsit Logikai Kicsit Bájt
Byte Bájt TinyInt UnsignedTinyInt TinyInt Bájt
bájt[] Bináris VarBinary. Ez az implicit átalakítás sikertelen lesz, ha a bájttömb nagyobb, mint a VarBinary maximális mérete, amely 8000 bájt. A 8000 bájtnál nagyobb bájt méretű bájttömbök esetében explicit módon állítsa be a SqlDbType. VarBinary Bináris Nyers
Char A karakterből való következtetés SqlDbType nem támogatott. Char Char Bájt
DateTime Dátum/idő Dátum/idő DBTimeStamp Dátum/idő Dátum/idő
DateTimeOffset DateTimeOffset DateTimeOffset az SQL Server 2008-ban. A DateTimeOffsetből való következtetés SqlDbType nem támogatott az SQL Server 2008-nál korábbi verzióiban. Dátum/idő
Decimal Decimális Decimális Decimális Numerikus Szám
Double Dupla Lebegőpontos értékek Dupla Dupla Dupla
Single Egyszeres Valós Egyszeres Valós Lebegőpontos értékek
Guid GUID UniqueIdentifier GUID UniqueIdentifier Nyers
Int16 Int16 SmallInt SmallInt SmallInt Int16
Int32 Int32 Int Int Int Int32
Int64 Int64 BigInt BigInt BigInt Szám
Object Objektum Változat Változat Az OdbcType objektumból való következtetése nem támogatott. Blob
String Sztring NVarChar. Ez az implicit átalakítás sikertelen lesz, ha a sztring nagyobb, mint egy 4000 karakter hosszúságú NVarChar maximális mérete. A 4000 karakternél nagyobb sztringek esetében explicit módon állítsa be a SqlDbType. VarWChar NVarChar NVarChar
TimeSpan Idő Az SQL Server 2008-ban töltött idő. A TimeSpanból való következtetés SqlDbType nem támogatott az SQL Server 2008-nál korábbi verzióiban. DBTime Idő Dátum/idő
UInt16 UInt16 Az UInt16-ból való következtetés SqlDbType nem támogatott. UnsignedSmallInt Int UInt16
UInt32 UInt32 Az UInt32-ből való következtetés SqlDbType nem támogatott. UnsignedInt BigInt UInt32
UInt64 UInt64 Az UInt64-ből való következtetés SqlDbType nem támogatott. UnsignedBigInt Numerikus Szám
AnsiString Varchar Varchar Varchar Varchar
AnsiStringFixedLength Char Char Char Char
Pénznem Pénzt Pénznem A forrásból való Currency következtetés OdbcType nem támogatott. Szám
Dátum Dátum az SQL Server 2008-ban. Az SQL Server 2008-nál korábbi verziói nem támogatják a dátumtól való következtetést SqlDbType . DBDate Dátum Dátum/idő
SByte A SByte-ból való következtetés SqlDbType nem támogatott. TinyInt A SByte-ból való következtetés OdbcType nem támogatott. SByte
StringFixedLength NChar WChar NChar NChar
Idő Az SQL Server 2008-ban töltött idő. Az SQL Server 2008-nál korábbi verziói nem támogatják az időből való következtetést SqlDbType . DBTime Idő Dátum/idő
VarNumeric A VarNumeric-ből való következtetés SqlDbType nem támogatott. VarNumeric A VarNumericből való következtetés OdbcType nem támogatott. Szám
felhasználó által definiált típus (olyan objektum, amelynek SqlUserDefinedAggregateAttribute Objektum vagy sztring a szolgáltatótól függően (az SqlClient mindig egy objektumot ad vissza, az Odbc mindig egy sztringet ad vissza, és az OleDb által felügyelt adatszolgáltató láthatja a következőt: SqlDbType.Udt, ha SqlUserDefinedTypeAttribute jelen van, ellenkező esetben Variant OleDbType.VarWChar (ha az érték null) egyébként OleDbType.Variant. OdbcType.NVarChar nem támogatott

Feljegyzés

A decimálisból más típusba történő átalakítások olyan konverziók, amelyek a decimális értéket a legközelebbi egész számra kerekítik nullára. Ha az átalakítás eredménye nem ábrázolható a céltípusban, akkor a függvény egy OverflowException értéket ad.

Feljegyzés

Amikor null paraméterértéket küld a kiszolgálónak, meg kell adnia DBNulla (Visual Basicben nemnullNothing) értéket. A null érték a rendszerben egy üres objektum, amelynek nincs értéke. DBNull null értékeket jelöl. Az adatbázis null értékeivel kapcsolatos további információkért lásd : Null értékek kezelése.

Paraméteradatok származtatása

A paraméterek az osztály használatával DbCommandBuilder tárolt eljárásból is származtathatók. A mind az SqlCommandBuilderOleDbCommandBuilder osztályok statikus metódust biztosítanak, DeriveParametersamely automatikusan feltölti egy olyan parancsobjektum paramétergyűjteményét, amely egy tárolt eljárás paraméteradatait használja. Vegye figyelembe, hogy DeriveParameters felülírja a parancs meglévő paraméteradatait.

Feljegyzés

A paraméteradatok származtatása teljesítménybeli büntetést von maga után, mivel az adatok lekéréséhez további oda-visszaút szükséges az adatforráshoz. Ha a paraméterinformációk a tervezéskor ismertek, a paraméterek explicit beállításával javíthatja az alkalmazás teljesítményét.

További információ: Parancsok generálása commandbuilderekkel.

Paraméterek használata SqlCommanddel és tárolt eljárással

A tárolt eljárások számos előnnyel járnak az adatvezérelt alkalmazásokban. A tárolt eljárások használatával az adatbázis-műveletek egyetlen parancsba ágyazhatók, a legjobb teljesítményre optimalizálhatók, és további biztonsággal bővíthetők. Bár a tárolt eljárás hívható meg a tárolt eljárásnév és a paraméterargumentumok SQL-utasításként való átadásával, a ADO.NET DbCommand objektum gyűjteményének használatával Parameters pontosabban definiálhatja a tárolt eljárás paramétereit, és hozzáférhet a kimeneti paraméterekhez és visszaadhatja az értékeket.

Feljegyzés

A paraméteres utasítások végrehajtása a kiszolgálón sp_executesql, történik, amely lehetővé teszi a lekérdezésterv újbóli használatát. A köteg helyi sp_executesql kurzorai vagy változói nem láthatók a hívást sp_executesqlkezdeményező köteg számára. Az adatbázis-környezet változásai csak az utasítás végéig sp_executesql tartanak. További információ: sp_executesql (Transact-SQL).

Ha paramétereket SqlCommand használ egy SQL Server által tárolt eljárás végrehajtásához, a gyűjteményhez Parameters hozzáadott paraméterek nevének meg kell egyeznie a tárolt eljárás paraméterjelölőinek nevével. Az SQL Server .NET-keretrendszer adatszolgáltatója nem támogatja a paraméterek SQL-utasításnak vagy tárolt eljárásnak való továbbításához használt kérdőjel (?) helyőrzőt. A tárolt eljárás paramétereit nevesített paraméterekként kezeli, és megkeresi az egyező paraméterjelölőket. A tárolt eljárás például CustOrderHist egy névvel ellátott @CustomerIDparaméterrel van definiálva. Amikor a kód végrehajtja a tárolt eljárást, egy nevű @CustomerIDparamétert is használnia kell.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Példa

Ez a példa bemutatja, hogyan hívhat meg tárolt SQL Server-eljárást a Northwind mintaadatbázisban. A tárolt eljárás dbo.SalesByCategory neve, és van egy bemeneti paramétere @CategoryName , amelynek adattípusa nvarchar(15). A kód létrehoz egy újat SqlConnection egy használatblokkon belül, hogy a kapcsolat az eljárás végén megszűnjön. Létrejönnek SqlCommand az és SqlParameter az objektumok, és meg vannak adva a tulajdonságaik. Az A SqlDataReader végrehajtja és SqlCommand visszaadja az eredményhalmazt a tárolt eljárásból, és megjeleníti a kimenetet a konzolablakban.

Feljegyzés

Ahelyett, hogy külön utasításokban hoz SqlCommand létre és SqlParameter állít be tulajdonságokat, a túlterhelt konstruktorok egyikével több tulajdonságot is beállíthat egyetlen utasításban.

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

Paraméterek használata OleDbCommand vagy OdbcCommand használatával

Ha paramétereket használ egy OleDbCommand vagy OdbcCommand, a gyűjteményhez hozzáadott paraméterek sorrendjének Parameters meg kell egyeznie a tárolt eljárásban meghatározott paraméterek sorrendjével. Az OLE DB .NET-keretrendszer adatszolgáltatója és az ODBC .NET-keretrendszer adatszolgáltatója helyőrzőként kezeli a tárolt eljárás paramétereit, és sorrendben alkalmazza a paraméterértékeket. Emellett a visszaadott értékparamétereknek a gyűjteményhez hozzáadott első paramétereknek Parameters kell lenniük.

Az OLE DB .NET-keretrendszer adatszolgáltatója és .NET-keretrendszer ODBC-adatszolgáltatója nem támogatja a paraméterek SQL-utasításnak vagy tárolt eljárásnak való továbbításához használt elnevezett paramétereket. Ebben az esetben a kérdőjel (?) helyőrzőt kell használnia, ahogyan az alábbi példában is látható.

SELECT * FROM Customers WHERE CustomerID = ?

Ennek eredményeképpen az objektumok gyűjteményhez való Parameters hozzáadásának sorrendjének Parameter közvetlenül meg kell egyeznie a gyűjtemény helyével? paraméter helyőrzője.

Példa OleDb-ra

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élda odbc-ra

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;

Lásd még