Delen via

Parameters en parametergegevenstypen configureren

  • Artikel

Opdrachtobjecten gebruiken parameters om waarden door te geven aan SQL-instructies of opgeslagen procedures, waarbij typecontrole en validatie wordt geboden. In tegenstelling tot opdrachttekst wordt parameterinvoer behandeld als een letterlijke waarde, niet als uitvoerbare code. Dit helpt u te beschermen tegen aanvallen met SQL-injectie, waarbij een aanvaller een opdracht invoegt waarmee de beveiliging op de server in een SQL-instructie wordt aangetast.

Geparameteriseerde opdrachten kunnen ook de prestaties van de uitvoering van query's verbeteren, omdat ze de databaseserver helpen de binnenkomende opdracht nauwkeurig te vinden met een juist queryplan in de cache. Zie De cache van het uitvoeringsplan opslaan en opnieuw gebruiken en parameters en uitvoeringsplan hergebruiken voor meer informatie. Naast de voordelen van beveiliging en prestaties bieden geparameteriseerde opdrachten een handige methode voor het ordenen van waarden die worden doorgegeven aan een gegevensbron.

Een DbParameter object kan worden gemaakt met behulp van de constructor of door het toe te voegen aan de DbParameterCollection methode Add van de DbParameterCollection verzameling. De Add methode wordt gebruikt als invoerconstructorargumenten of een bestaand parameterobject, afhankelijk van de gegevensprovider.

De eigenschap ParameterDirection opgeven

Wanneer u parameters toevoegt, moet u een ParameterDirection eigenschap opgeven voor andere parameters dan invoerparameters. In de volgende tabel ziet u de ParameterDirection waarden die u met de ParameterDirection opsomming kunt gebruiken.

De naam van lid Beschrijving
Input De parameter is een invoerparameter. Dit is de standaardinstelling.
InputOutput De parameter kan zowel invoer als uitvoer uitvoeren.
Output De parameter is een uitvoerparameter.
ReturnValue De parameter vertegenwoordigt een retourwaarde van een bewerking, zoals een opgeslagen procedure, ingebouwde functie of door de gebruiker gedefinieerde functie.

Werken met tijdelijke aanduidingen voor parameters

De syntaxis voor tijdelijke aanduidingen voor parameters is afhankelijk van de gegevensbron. De .NET Framework-gegevensproviders verwerken de naamgeving en het opgeven van parameters en tijdelijke aanduidingen voor parameters anders. Deze syntaxis is aangepast aan een specifieke gegevensbron, zoals beschreven in de volgende tabel.

Gegevensprovider Naamgevingsyntaxis van parameters
System.Data.SqlClient Maakt gebruik van benoemde parameters in de indelingsparameternaam@.
System.Data.OleDb Gebruikt positionele parametermarkeringen die worden aangegeven met een vraagteken (?).
System.Data.Odbc Gebruikt positionele parametermarkeringen die worden aangegeven met een vraagteken (?).
System.Data.OracleClient Gebruikt benoemde parameters in de notatie :parmname (of parmname).

Parametergegevenstypen opgeven

Het gegevenstype van een parameter is specifiek voor de .NET Framework-gegevensprovider. Als u het type opgeeft, wordt de waarde van de Parameter gegevensprovider geconverteerd naar het .NET Framework-gegevensprovidertype voordat u de waarde doorgeeft aan de gegevensbron. U kunt ook het type van een Parameter op een algemene manier opgeven door de DbType eigenschap van het Parameter object in te stellen op een bepaald DbTypeobject.

Het .NET Framework-gegevensprovidertype van een Parameter object wordt afgeleid van het .NET Framework-type van het Parameter Value object of van het DbType Parameter object. In de volgende tabel ziet u het uitgestelde Parameter type op basis van het object dat is doorgegeven als de Parameter waarde of de opgegeven DbTypewaarde.

.NET Framework-type DbType SqlDbType OleDbType OdbcType OracleType
Boolean Booleaanse waarde Bit Booleaanse waarde Bit Byte
Byte Byte TinyInt UnsignedTinyInt TinyInt Byte
byte[] Binary VarBinary. Deze impliciete conversie mislukt als de bytematrix groter is dan de maximale grootte van een VarBinary, dat 8000 bytes is. Stel voor bytematrices die groter zijn dan 8000 bytes, expliciet de SqlDbType. VarBinary Binary Onbewerkt
Char Het uitstellen van een SqlDbType teken wordt niet ondersteund. Char Char Byte
DateTime DateTime DateTime DBTimeStamp DateTime DateTime
DateTimeOffset DateTimeOffset DateTimeOffset in SQL Server 2008. Het uitstellen van een SqlDbType DateTimeOffset wordt niet ondersteund in versies van SQL Server ouder dan SQL Server 2008. Datum en tijd
Decimal Decimal Decimal Decimal Numeriek Aantal
Double Dubbel Float Dubbel Dubbel Dubbel
Single Eén Real Eén Real Float
Guid Guid UniqueIdentifier Guid UniqueIdentifier Onbewerkt
Int16 Int16 SmallInt SmallInt SmallInt Int16
Int32 Int32 Int Int Int Int32
Int64 Int64 BigInt BigInt BigInt Aantal
Object Object Variant Variant Het uitstellen van een OdbcType van object wordt niet ondersteund. Blob
String String NVarChar. Deze impliciete conversie mislukt als de tekenreeks groter is dan de maximale grootte van een NVarChar, wat 4000 tekens is. Stel voor tekenreeksen die groter zijn dan 4000 tekens, expliciet de SqlDbType. VarWChar NVarChar NVarChar
TimeSpan Tijd Tijd in SQL Server 2008. Het uitstellen van een SqlDbType timespan wordt niet ondersteund in versies van SQL Server ouder dan SQL Server 2008. DBTime Tijd Datum en tijd
UInt16 UInt16 Het uitstellen van een SqlDbType UInt16 wordt niet ondersteund. UnsignedSmallInt Int UInt16
UInt32 UInt32 Het uitstellen van een SqlDbType UInt32 wordt niet ondersteund. UnsignedInt BigInt UInt32
UInt64 UInt64 Het uitstellen van een SqlDbType UInt64 wordt niet ondersteund. UnsignedBigInt Numeriek Aantal
AnsiString VarChar VarChar VarChar VarChar
AnsiStringFixedLength Char Char Char Char
Valuta Money Valuta Het uitstellen van een OdbcType van Currency wordt niet ondersteund. Aantal
Datum Datum in SQL Server 2008. Het uitstellen van een SqlDbType datum wordt niet ondersteund in versies van SQL Server ouder dan SQL Server 2008. DBDate Datum Datum en tijd
SByte Het uitstellen van een SqlDbType SByte wordt niet ondersteund. TinyInt Het uitstellen van een OdbcType SByte wordt niet ondersteund. SByte
StringFixedLength NChar WChar NChar NChar
Tijd Tijd in SQL Server 2008. Het uitstellen van een SqlDbType tijd wordt niet ondersteund in versies van SQL Server ouder dan SQL Server 2008. DBTime Tijd Datum en tijd
VarNumeric Het uitstellen van een SqlDbType varnumeric wordt niet ondersteund. VarNumeric Het uitstellen van een OdbcType varnumeric wordt niet ondersteund. Aantal
door de gebruiker gedefinieerd type (een object met SqlUserDefinedAggregateAttribute Object of tekenreeks, afhankelijk van de provider (SqlClient retourneert altijd een object, odbc retourneert altijd een tekenreeks en de beheerde OleDb-gegevensprovider kan een van beide zien SqlDbType.Udt als SqlUserDefinedTypeAttribute aanwezig is, anders variant OleDbType.VarWChar (als de waarde null is) anders OleDbType.Variant. OdbcType.NVarChar niet ondersteund

Notitie

Conversies van decimale naar andere typen beperken conversies die de decimale waarde afronden op de dichtstbijzijnde gehele waarde naar nul. Als het resultaat van de conversie niet kan worden weergegeven in het doeltype, wordt er een OverflowException gegenereerd.

Notitie

Wanneer u een null-parameterwaarde naar de server verzendt, moet u opgeven DBNull, niet null (Nothing in Visual Basic). De null-waarde in het systeem is een leeg object dat geen waarde heeft. DBNull wordt gebruikt om null-waarden weer te geven. Zie Null-waarden verwerken voor meer informatie over database-null-waarden.

Parametergegevens afleiden

Parameters kunnen ook worden afgeleid van een opgeslagen procedure met behulp van de DbCommandBuilder klasse. Zowel de als OleDbCommandBuilder de SqlCommandBuilder klassen bieden een statische methode, DeriveParameterswaarmee automatisch de parametersverzameling van een opdrachtobject wordt gevuld dat gebruikmaakt van parametergegevens uit een opgeslagen procedure. Houd er rekening mee dat DeriveParameters alle bestaande parametergegevens voor de opdracht worden overschreven.

Notitie

Het afleiden van parameterinformatie veroorzaakt een prestatiestraf omdat hiervoor een extra retour naar de gegevensbron nodig is om de informatie op te halen. Als parametergegevens bekend zijn tijdens het ontwerp, kunt u de prestaties van uw toepassing verbeteren door de parameters expliciet in te stellen.

Zie Opdrachten genereren met CommandBuilders voor meer informatie.

Parameters gebruiken met een SqlCommand en een opgeslagen procedure

Opgeslagen procedures bieden veel voordelen in gegevensgestuurde toepassingen. Met behulp van opgeslagen procedures kunnen databasebewerkingen worden ingekapseld in één opdracht, geoptimaliseerd voor de beste prestaties en verbeterd met extra beveiliging. Hoewel een opgeslagen procedure kan worden aangeroepen door de naam van de opgeslagen procedure door te geven, gevolgd door parameterargumenten als SQL-instructie, kunt u met behulp van de Parameters verzameling van het ADO.NET-object DbCommand expliciet opgeslagen procedureparameters definiëren en toegang krijgen tot uitvoerparameters en retourwaarden.

Notitie

Geparameteriseerde instructies worden uitgevoerd op de server met behulp van sp_executesql, waardoor queryplan opnieuw kan worden gebruikt. Lokale cursors of variabelen in de sp_executesql batch zijn niet zichtbaar voor de batch die aanroept sp_executesql. Wijzigingen in de databasecontext duren alleen tot het einde van de sp_executesql instructie. Zie sp_executesql (Transact-SQL) voor meer informatie.

Wanneer u parameters met een SqlCommand opgeslagen SQL Server-procedure gebruikt, moeten de namen van de parameters die aan de Parameters verzameling worden toegevoegd, overeenkomen met de namen van de parametermarkeringen in de opgeslagen procedure. De .NET Framework-gegevensprovider voor SQL Server biedt geen ondersteuning voor de tijdelijke aanduiding voor het vraagteken (?) voor het doorgeven van parameters aan een SQL-instructie of een opgeslagen procedure. Hiermee worden parameters in de opgeslagen procedure behandeld als benoemde parameters en wordt gezocht naar overeenkomende parametermarkeringen. De opgeslagen procedure wordt bijvoorbeeld gedefinieerd met behulp van een parameter met de CustOrderHist naam @CustomerID. Wanneer uw code de opgeslagen procedure uitvoert, moet deze ook een parameter met de naam @CustomerIDgebruiken.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Opmerking

In dit voorbeeld ziet u hoe u een opgeslagen SQL Server-procedure aanroept in de Northwind voorbeelddatabase. De naam van de opgeslagen procedure is dbo.SalesByCategory en heeft een invoerparameter met @CategoryName een gegevenstype nvarchar(15). De code maakt een nieuwe SqlConnection in een using-blok, zodat de verbinding wordt verwijderd wanneer de procedure wordt beëindigd. De SqlCommand objecten en SqlParameter de objecten worden gemaakt en hun eigenschappenset. Een SqlDataReader voert de SqlCommand en retourneert de resultatenset uit de opgeslagen procedure, waarin de uitvoer in het consolevenster wordt weergegeven.

Notitie

In plaats van objecten te maken SqlCommand en SqlParameter vervolgens eigenschappen in afzonderlijke instructies in te stellen, kunt u ervoor kiezen om een van de overbelaste constructors te gebruiken om meerdere eigenschappen in één instructie in te stellen.

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

Parameters gebruiken met een OleDbCommand of OdbcCommand

Wanneer u parameters gebruikt met een OleDbCommand of OdbcCommand, moet de volgorde van de parameters die aan de Parameters verzameling worden toegevoegd, overeenkomen met de volgorde van de parameters die zijn gedefinieerd in uw opgeslagen procedure. De .NET Framework-gegevensprovider voor OLE DB en .NET Framework-gegevensprovider voor ODBC behandelt parameters in een opgeslagen procedure als tijdelijke aanduidingen en past parameterwaarden in volgorde toe. Daarnaast moeten retourwaardeparameters de eerste parameters zijn die aan de Parameters verzameling worden toegevoegd.

De .NET Framework-gegevensprovider voor OLE DB en .NET Framework-gegevensprovider voor ODBC biedt geen ondersteuning voor benoemde parameters voor het doorgeven van parameters aan een SQL-instructie of een opgeslagen procedure. In dit geval moet u de tijdelijke aanduiding voor het vraagteken (?) gebruiken, zoals in het volgende voorbeeld.

SELECT * FROM Customers WHERE CustomerID = ?

Als gevolg hiervan moet de volgorde waarin Parameter objecten aan de Parameters verzameling worden toegevoegd, rechtstreeks overeenkomen met de positie van de ? tijdelijke aanduiding voor de parameter.

OleDb-voorbeeld

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;

Odbc-voorbeeld

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;

Zie ook