Het configureren van parameters en parametergegevenstypen

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 door zijn constructor te gebruiken of door het toe te voegen aan de DbParameterCollection door de Add methode van de DbParameterCollection collectie aan te roepen. De Add-methode accepteert als invoer ofwel constructorargumenten 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.

Werken met plaatsaanduidingen 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.

Parametergegevenstypen opgeven

Het gegevenstype van een parameter is specifiek voor de .NET Framework-gegevensprovider. Door het type op te geven, wordt de waarde van de Parameter naar het .NET Framework-gegevensprovidertype geconverteerd voordat de waarde aan de gegevensbron wordt doorgegeven. 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 ValueParameter object of van het DbTypeParameter 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.

Parametergegevens afleiden

Parameters kunnen ook worden afgeleid van een opgeslagen procedure met behulp van de DbCommandBuilder klasse. Zowel de als SqlCommandBuilder de OleDbCommandBuilder 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.

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.

Wanneer u parameters gebruikt met een SqlCommand om een opgeslagen procedure in SQL Server uit te voeren, moeten de namen van de parameters die aan de Parameters collectie worden toegevoegd, overeenkomen met de namen van de parameterverwijzingen in de opgeslagen procedure. De .NET Framework-gegevensprovider voor SQL Server biedt geen ondersteuning voor het vraagteken als tijdelijke aanduiding 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 die CustOrderHist wordt genoemd. Wanneer uw code de opgeslagen procedure uitvoert, moet deze ook een parameter met de naam @CustomerIDgebruiken.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Voorbeeld

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 het heeft een invoerparameter met de naam @CategoryName en een gegevenstype van 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 en SqlParameter objecten worden gemaakt en hun eigenschappen worden ingesteld. Een SqlDataReader voert de SqlCommand uit en retourneert de resultatenset uit de opgeslagen procedure, die de uitvoer in het consolevenster weergeeft.

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

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 vraagtekenplaceholder (?) 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

• Opdrachten en parameters
• DataAdapter-parameters
• Toewijzingen van gegevenstypen in ADO.NET
• overzicht van ADO.NET