Konfigurera parametrar och parameterdatatyper

Kommandoobjekt använder parametrar för att skicka värden till SQL-instruktioner eller lagrade procedurer, vilket ger typkontroll och validering. Till skillnad från kommandotext behandlas parameterindata som ett literalvärde, inte som körbar kod. Detta skyddar mot "SQL-inmatningsattacker", där en angripare infogar ett kommando som äventyrar säkerheten på servern i en SQL-instruktion.

Parametriserade kommandon kan också förbättra frågekörningsprestanda, eftersom de hjälper databasservern att korrekt matcha det inkommande kommandot med en korrekt cachelagrad frågeplan. Mer information finns i Cachelagring och återanvändning av körningsplan och Parametrar och återanvändning av körningsplaner. Utöver säkerhets- och prestandafördelarna ger parametriserade kommandon en praktisk metod för att organisera värden som skickas till en datakälla.

Ett DbParameter-objekt kan skapas med hjälp av sin konstruktor eller genom att lägga till det i DbParameterCollection-samlingen genom att anropa Add-metoden för DbParameterCollection-samlingen. Metoden Add tar som indata antingen konstruktorargument eller ett befintligt parameterobjekt, beroende på dataprovidern.

Förse egenskapen ParameterDirection

När du lägger till parametrar måste du ange en ParameterDirection egenskap för andra parametrar än indataparametrar. I följande tabell visas de ParameterDirection värden som du kan använda med ParameterDirection uppräkningen.

Arbeta med platshållare för parametrar

Syntaxen för parameterplatshållare beror på datakällan. .NET Framework-dataprovidrar hanterar namngivning och anger parametrar och parameterplatshållare på olika sätt. Den här syntaxen anpassas till en specifik datakälla enligt beskrivningen i följande tabell.

Ange datatyper för parametrar

Datatypen för en parameter är specifik för .NET Framework-dataprovidern. Om du anger typen konverteras värdet Parameter för till .NET Framework-dataprovidertypen innan värdet skickas till datakällan. Du kan också ange typen av en Parameter på ett allmänt sätt genom att ange DbType egenskapen för Parameter objektet till en viss DbType.

.NET Framework-dataprovidertypen för ett Parameter objekt härleds från .NET Framework-typen för ValueParameter objektet eller från DbTypeParameter objektets. I följande tabell visas den härledda Parameter typen baserat på objektet som skickas som Parameter värdet eller den angivna DbType.

Härleda information om parametrar

Parametrar kan också härledas från en lagrad procedur med hjälp av DbCommandBuilder klassen . Både klasserna SqlCommandBuilder och OleDbCommandBuilder tillhandahåller en statisk metod, DeriveParameters, som automatiskt fyller i parametersamlingen för ett kommandoobjekt som använder parameterinformation från en lagrad procedur. Observera att DeriveParameters skriver över all befintlig parameterinformation för kommandot.

Mer information finns i Generera kommandon med CommandBuilders.

Använda parametrar med en SqlCommand och en lagrad procedur

Lagrade procedurer erbjuder många fördelar i datadrivna program. Genom att använda lagrade procedurer kan databasåtgärder kapslas in i ett enda kommando, optimeras för bästa prestanda och förbättras med ytterligare säkerhet. Även om en lagrad procedur kan anropas genom att skicka namnet på den lagrade proceduren följt av parameterargument som en SQL-instruktion, kan du använda Parameters samlingen av ADO.NET-objektet DbCommand för att mer explicit definiera lagrade procedureparametrar och få åtkomst till utdataparametrar och returvärden.

När du använder parametrar med en SqlCommand för att köra en lagrad SQL Server-procedur måste namnen på de parametrar som läggs till i Parameters samlingen matcha namnen på parametermarkörerna i den lagrade proceduren. .NET Framework-dataprovidern för SQL Server stöder inte platshållaren för frågetecken (?) för att skicka parametrar till en SQL-instruktion eller en lagrad procedur. Den behandlar parametrar i den lagrade proceduren som namngivna parametrar och söker efter matchande parametermarkörer. Den lagrade proceduren definieras till exempel CustOrderHist med hjälp av en parameter med namnet @CustomerID. När koden kör den lagrade proceduren måste den också använda en parameter med namnet @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Exempel

Det här exemplet visar hur du anropar en lagrad SQL Server-procedur i exempeldatabasen Northwind . Namnet på den lagrade proceduren är dbo.SalesByCategory och har en indataparameter med namnet @CategoryName med datatypen nvarchar(15). Koden skapar en ny SqlConnection i ett användningsblock så att anslutningen tas bort när proceduren avslutas. Objekten SqlCommand och SqlParameter skapas och deras egenskaper anges. A SqlDataReader kör SqlCommand och returnerar resultatuppsättningen från den lagrade proceduren och visar utdata i konsolfönstret.

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

Att använda parametrar med en OleDbCommand eller en OdbcCommand

När du använder parametrar med en OleDbCommand eller OdbcCommandmåste ordningen på de parametrar som läggs till i Parameters samlingen matcha ordningen på de parametrar som definierats i den lagrade proceduren. .NET Framework-dataprovidern för OLE DB och .NET Framework Data Provider för ODBC behandlar parametrar i en lagrad procedur som platshållare och tillämpar parametervärden i ordning. Dessutom måste returnerade värdeparametrar vara de första parametrarna som läggs till i Parameters samlingen.

.NET Framework-dataprovidern för OLE DB och .NET Framework Data Provider för ODBC stöder inte namngivna parametrar för att skicka parametrar till en SQL-instruktion eller en lagrad procedur. I det här fallet måste du använda platshållaren för frågetecknet (?), som i följande exempel.

SELECT * FROM Customers WHERE CustomerID = ?

Därför måste den ordning i vilken Parameter objekt läggs till Parameters i samlingen direkt motsvara positionen för ? platshållare för parametern.

OleDb-exempel

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-exempel

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;

Se även

• Kommandon och parametrar
• DataAdapter-parameterar
• Datatypmappningar i ADO.NET
• översikt över ADO.NET