Configurazione dei parametri

  • Articolo

Si applica a: .NET Framework .NET Standard

Scarica ADO.NET

Gli oggetti comando usano i parametri per passare valori a istruzioni o stored procedure SQL, fornendo la verifica e la convalida dei tipi. A differenza del testo dei comandi, l'input dei parametri viene trattato come valore letterale, non come codice eseguibile. In questo modo è possibile difendersi da attacchi SQL injection, in cui l'autore di un attacco inserisce un comando che compromette la sicurezza del server in un'istruzione SQL.

I comandi con parametri possono anche migliorare le prestazioni di esecuzione delle query in quanto aiutano il server database a ottenere una corrispondenza accurata tra il comando in arrivo e un piano di query memorizzato nella cache appropriato. Per altre informazioni, vedere Memorizzazione nella cache e riutilizzo del piano di esecuzione e Parametri e riutilizzo del piano di esecuzione. Oltre ai vantaggi in termini di sicurezza e prestazioni, i comandi con parametri offrono un metodo pratico per organizzare i valori passati a un'origine dati.

Per creare un oggetto DbParameter , è possibile usare il relativo costruttore o aggiungerlo all'oggetto DbParameterCollection chiamando il metodo Add della raccolta DbParameterCollection . Il metodo Add accetta come input argomenti del costruttore o un oggetto parametro esistente, a seconda del provider di dati.

Specificare la proprietà ParameterDirection

Quando si aggiungono parametri, è necessario fornire una proprietà ParameterDirection per i parametri diversi da quelli di input. La tabella seguente illustra i valori ParameterDirection che è possibile usare con l'enumerazione ParameterDirection .

Nome del membro Descrizione
Input Il parametro è un parametro di input. Si tratta del valore predefinito.
InputOutput Il parametro può essere sia di input che di output.
Output Il parametro è un parametro di output.
ReturnValue Il parametro rappresenta un valore restituito da un'operazione quale una stored procedure, una funzione predefinita o una funzione definita dall'utente.

Usare i segnaposto dei parametri

La sintassi per i segnaposto dei parametri varia in base all'origine dati. Il provider di dati Microsoft SqlClient per SQL Server gestisce la denominazione e la specifica dei parametri e dei segnaposto dei parametri in modo diverso. Il provider di dati usa parametri denominati nel formato @parametername.

Specificare i tipi di dati per i parametri

Il tipo di dati di un parametro è specifico per il provider di dati Microsoft SqlClient per SQL Server. Se si specifica il tipo, il valore di Parameter viene convertito nel tipo per il provider di dati Microsoft SqlClient per SQL Server prima che sia passato all'origine dati. È inoltre possibile specificare il tipo di un oggetto Parameter in modo generico impostando la proprietà DbType dell'oggetto Parameter su un determinato oggetto DbType.

Il tipo per il provider di dati Microsoft SqlClient per SQL Server di un oggetto Value viene dedotto dal tipo .NET Framework di Parameter dell'oggetto Parameter oppure da DbType dell'oggetto Parameter. La tabella seguente illustra il tipo Parameter dedotto in base all'oggetto passato come valore di Parameter o all'oggetto DbTypespecificato.

Tipo .NET DbType SqlDbType
Boolean Boolean Bit
Byte Byte TinyInt
byte[] Binary VarBinary. La conversione implicita non riesce se le dimensioni della matrice di byte sono maggiori delle dimensioni di VarBinary, che è di 8000 byte. Per le matrici di byte maggiori di 8000 byte, impostare in modo esplicito SqlDbType.
Char L'inferenza di un oggetto SqlDbType da char non è supportata.
DateTime DateTime DateTime
DateTimeOffset DateTimeOffset DateTimeOffset in SQL Server 2008. L'inferenza di un oggetto SqlDbType da DateTimeOffset non è supportata nelle versioni di SQL Server precedenti a SQL Server 2008.
Decimal Decimal Decimal
Double Double Float
Single Single Real
Guid Guid UniqueIdentifier
Int16 Int16 SmallInt
Int32 Int32 Int
Int64 Int64 BigInt
Object Object Variant
String String NVarChar. La conversione implicita non riesce se la stringa ha una dimensione superiore a quella massima di NVarChar, che è di 4000 caratteri. Per le stringhe maggiori di 4000 caratteri, impostare in modo esplicito SqlDbType.
TimeSpan Time Time in SQL Server 2008. L'inferenza di un oggetto SqlDbType da TimeSpan non è supportata nelle versioni di SQL Server precedenti a SQL Server 2008.
UInt16 UInt16 L'inferenza di un oggetto SqlDbType da UInt16 non è supportata.
UInt32 UInt32 L'inferenza di un oggetto SqlDbType da UInt32 non è supportata.
UInt64 UInt64 L'inferenza di un oggetto SqlDbType da UInt64 non è supportata.
AnsiString VarChar
AnsiStringFixedLength Char
Currency Money
Date Date in SQL Server 2008. L'inferenza di un oggetto SqlDbType da Date non è supportata nelle versioni di SQL Server precedenti a SQL Server 2008.
SByte L'inferenza di un oggetto SqlDbType da SByte non è supportata.
StringFixedLength NChar
Time Time in SQL Server 2008. L'inferenza di un oggetto SqlDbType da Time non è supportata nelle versioni di SQL Server precedenti a SQL Server 2008.
VarNumeric L'inferenza di un oggetto SqlDbType da VarNumeric non è supportata.
Tipo di oggetto definito dall'utente (oggetto con SqlUserDefinedAggregateAttribute SqlClient restituisce sempre un oggetto SqlDbType.Udt se SqlUserDefinedTypeAttribute è presente. In caso contrario, Variant

Nota

Le conversioni da Decimal in altri tipi sono conversioni di restrizione che arrotondano il valore Decimal al valore integer più vicino che tende allo zero. Se non è possibile rappresentare il risultato della conversione nel tipo di destinazione, verrà generata un'eccezione OverflowException.

Nota

Quando si invia un valore di parametro Null al server, è necessario specificare DBNull anziché null (Nothing in Visual Basic). Il valore Null nel sistema è un oggetto vuoto senza alcun valore. DBNull viene utilizzato per rappresentare i valori Null.

Derivare le informazioni sui parametri

I parametri possono anche essere derivati da una stored procedure usando la classe DbCommandBuilder . La classe SqlCommandBuilder offre un metodo statico, DeriveParameters, che popola automaticamente la raccolta di parametri di un oggetto comando che usa le informazioni sui parametri di una stored procedure. DeriveParameters sovrascrive qualsiasi informazione esistente sui parametri per il comando.

Nota

La derivazione di informazioni sui parametri implica una riduzione delle prestazioni, in quando richiede un round trip aggiuntivo con l'origine dati per recuperare le informazioni. Se le informazioni sui parametri sono note in fase di progettazione, è possibile migliorare le prestazioni dell'applicazione impostando i parametri in modo esplicito.

Per altre informazioni, vedere Generazione dei comandi con CommandBuilders.

Uso di parametri con SqlCommand e di una stored procedure

Le stored procedure offrono numerosi vantaggi nelle applicazioni guidate dai dati. Usando le stored procedure, le operazioni nel database possono essere incapsulate in un unico comando, ottimizzate per migliorare le prestazioni e rese più sicure con funzioni di sicurezza aggiuntive. Anche se è possibile chiamare una stored procedure passandone il nome seguito dagli argomenti dei parametri come istruzione SQL, l'uso della raccolta Parameters dell'oggetto DbCommand di ADO.NET consente di definire in modo più esplicito i parametri delle stored procedure e di accedere ai parametri di output e ai valori restituiti.

Nota

Le istruzioni con parametri vengono eseguite sul server tramite sp_executesql, , che consente il riutilizzo del piano di query. I cursori o le variabili locali del batch sp_executesql non sono visibili per il batch che chiama sp_executesql. Le modifiche apportate al contesto del database durano solo fino al termine dell'esecuzione dell'istruzione sp_executesql . Per altre informazioni, vedere sp_executesql (Transact-SQL).

Quando si usano parametri con SqlCommand per eseguire una stored procedure di SQL Server, i nomi dei parametri aggiunti alla raccolta Parameters devono corrispondere ai nomi dei marcatori dei parametri nella stored procedure. Il provider di dati Microsoft SqlClient per SQL Server non supporta il segnaposto punto interrogativo (?) per il passaggio dei parametri a un'istruzione SQL o a una stored procedure. I parametri nella stored procedure vengono trattati come parametri denominati e vengono ricercati marcatori di parametri corrispondenti. Ad esempio, la stored procedure CustOrderHist è definita con un parametro denominato @CustomerID. Quando il codice esegue la stored procedure deve usare anche un parametro denominato @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Esempio

In questo esempio viene illustrato come chiamare una stored procedure di SQL Server nel database di esempio Northwind . Il nome della stored procedure è dbo.SalesByCategory e accetta un parametro di input denominato @CategoryName con un tipo di dati nvarchar(15). Nel codice viene creato un nuovo oggetto SqlConnection all'interno di un blocco using, in modo che la connessione venga eliminata al termine della procedura. Vengono creati gli oggetti SqlCommand e SqlParameter e vengono impostate le relative proprietà. Un oggetto SqlDataReader esegue SqlCommand e restituisce il set di risultati dalla stored procedure, visualizzando l'output nella finestra della console.

Nota

Anziché creare oggetti SqlCommand e SqlParameter e impostare quindi le proprietà in istruzioni distinte, è possibile scegliere di usare uno dei costruttori di overload per impostare più proprietà in una singola istruzione.

static void GetSalesByCategory(string connectionString,
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = 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 (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();
        }
    }
}

Vedi anche