Konfigurera parametrar och parameterdatatyper

Artikel
03/05/2024

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 av körningsplan, återanvändning och parametrar och återanvändning av körningsplan. 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 konstruktorn eller genom att lägga till det i genom att DbParameterCollection anropa Add metoden för DbParameterCollection samlingen. Metoden Add tar som indata antingen konstruktorargument eller ett befintligt parameterobjekt, beroende på dataprovidern.

Ange 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.

Medlemsnamn	beskrivning
Input	Parametern är en indataparameter. Det här är standardinställningen.
InputOutput	Parametern kan utföra både indata och utdata.
Output	Parametern är en utdataparameter.
ReturnValue	Parametern representerar ett returvärde från en åtgärd, till exempel en lagrad procedur, inbyggd funktion eller användardefinierad funktion.

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.

Dataprovider	Parameternamnssyntax
System.Data.SqlClient	Använder namngivna parametrar i formatet `@`parameternamn.
System.Data.OleDb	Använder positionsparametermarkörer som anges av ett frågetecken (`?`).
System.Data.Odbc	Använder positionsparametermarkörer som anges av ett frågetecken (`?`).
System.Data.OracleClient	Använder namngivna parametrar i formatet `:`parmname (eller parmname).

Ange parameterdatatyper

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 Value Parameter objektet eller från DbType Parameter objektets. I följande tabell visas den härledda Parameter typen baserat på objektet som skickas som Parameter värdet eller den angivna DbType.

.NET Framework-typ	DbType	SqlDbType	OleDbType	OdbcType	OracleType
Boolean	Booleskt	Bit	Booleskt	Bit	Byte
Byte	Byte	TinyInt	UnsignedTinyInt	TinyInt	Byte
byte[]	Binära	VarBinary. Den här implicita konverteringen misslyckas om bytematrisen är större än den maximala storleken för en VarBinary, som är 8 000 byte. För bytematriser som är större än 8 000 byte anger du uttryckligen SqlDbType.	VarBinary	Binära	Raw
Char		Det går inte att härleda en SqlDbType från tecken.	Char	Char	Byte
DateTime	Datum/tid	Datum/tid	DBTimeStamp	Datum/tid	Datum/tid
DateTimeOffset	DateTimeOffset	DateTimeOffset i SQL Server 2008. Det går inte att härleda en SqlDbType från DateTimeOffset i versioner av SQL Server tidigare än SQL Server 2008.			Datum/tid
Decimal	Decimal	Decimal	Decimal	Numerisk	Antal
Double	Dubbel	Flyttal	Dubbel	Dubbel	Dubbel
Single	Enstaka	Real	Enstaka	Real	Flyttal
Guid	GUID	UniqueIdentifier	GUID	UniqueIdentifier	Raw
Int16	Int16	SmallInt	SmallInt	SmallInt	Int16
Int32	Int32	Int	Int	Int	Int32
Int64	Int64	BigInt	BigInt	BigInt	Antal
Object	Objekt	Variant	Variant	Det går inte att härleda en OdbcType från objekt.	Blob
String	String	NVarChar. Den här implicita konverteringen misslyckas om strängen är större än den maximala storleken för ett NVarChar, vilket är 4 000 tecken. För strängar som är större än 4 000 tecken anger du uttryckligen SqlDbType.	VarWChar	NVarChar	NVarChar
TimeSpan	Tid	Tid i SQL Server 2008. Det går inte att härleda en SqlDbType från TimeSpan i tidigare versioner av SQL Server än SQL Server 2008.	DBTime	Tid	Datum/tid
UInt16	UInt16	Det går inte att härleda en SqlDbType från UInt16.	UnsignedSmallInt	Int	UInt16
UInt32	UInt32	Det går inte att härleda en SqlDbType från UInt32.	OsigneradInt	BigInt	UInt32
UInt64	UInt64	Det går inte att härleda en SqlDbType från UInt64.	UnsignedBigInt	Numerisk	Antal
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringFixedLength	Char	Char	Char	Char
	Valuta	Money	Valuta	Det går inte att härleda en `OdbcType` från `Currency` .	Antal
	Datum	Datum i SQL Server 2008. Det går inte att härleda från SqlDbType Datum i versioner av SQL Server tidigare än SQL Server 2008.	DBDate	Date	Datum/tid
	SByte	Det går inte att härleda en SqlDbType från SByte.	TinyInt	Det går inte att härleda en `OdbcType` från SByte.	SByte
	StringFixedLength	NChar	WChar	NChar	NChar
	Tid	Tid i SQL Server 2008. Slutsatsdragning SqlDbType från tid stöds inte i versioner av SQL Server tidigare än SQL Server 2008.	DBTime	Tid	Datum/tid
	VarNumeric	Det går inte att härleda en SqlDbType från VarNumeric.	VarNumeric	Det går inte att härleda en `OdbcType` från VarNumeric.	Antal
användardefinierad typ (ett objekt med SqlUserDefinedAggregateAttribute	Objekt eller sträng, beroende på providern (SqlClient returnerar alltid ett objekt, Odbc returnerar alltid en sträng och OleDb-hanterad dataprovider kan se antingen	SqlDbType.Udt om SqlUserDefinedTypeAttribute finns, annars Variant	OleDbType.VarWChar (om värdet är null) annars OleDbType.Variant.	OdbcType.NVarChar	stöds inte

Kommentar

Konverteringar från decimal till andra typer begränsar konverteringar som avrundar decimalvärdet till närmaste heltalsvärde mot noll. Om resultatet av konverteringen inte kan representeras i måltypen genereras en OverflowException .

Kommentar

När du skickar ett null-parametervärde till servern måste du ange DBNull, inte null (Nothing i Visual Basic). Null-värdet i systemet är ett tomt objekt som inte har något värde. DBNull används för att representera null-värden. Mer information om null-databaser finns i Hantera nullvärden.

Härleda parameterinformation

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.

Kommentar

Att härleda parameterinformation medför en prestandaavgift eftersom det krävs ytterligare en tur- och returresa till datakällan för att hämta informationen. Om parameterinformationen är känd vid designtillfället kan du förbättra programmets prestanda genom att uttryckligen ange parametrarna.

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.

Kommentar

Parametriserade instruktioner körs på servern med hjälp sp_executesqlav , vilket möjliggör återanvändning av frågeplan. Lokala markörer eller variabler i batchen sp_executesql är inte synliga för batchen som anropar sp_executesql. Ändringar i databaskontexten varar endast till slutet av -instruktionen sp_executesql . Mer information finns i sp_executesql (Transact-SQL).

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.

Kommentar

I stället för att skapa SqlCommand och SqlParameter objekt och sedan ange egenskaper i separata instruktioner kan du i stället välja att använda en av de överlagrade konstruktorerna för att ange flera egenskaper i en enda instruktion.

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

Använda parametrar med en OleDbCommand eller 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;

Dela via

Konfigurera parametrar och parameterdatatyper

Ange egenskapen ParameterDirection

Arbeta med platshållare för parametrar

Ange parameterdatatyper

Härleda parameterinformation

Använda parametrar med en SqlCommand och en lagrad procedur

Exempel

Använda parametrar med en OleDbCommand eller OdbcCommand

OleDb-exempel

Odbc-exempel

Se även

Ytterligare resurser