Configurazione di parametri e tipi di dati dei parametri

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.

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 dell'impostazione predefinita.
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.

Uso dei segnaposto dei parametri

La sintassi per i segnaposto dei parametri varia in base all'origine dati. I provider di dati .NET Framework gestiscono in modo diverso la denominazione e la specifica di parametri e segnaposto di parametri. Questa sintassi è personalizzata in base a un'origine dati specifica, come descritto nella tabella seguente.

Provider di dati	Sintassi di denominazione di parametri
System.Data.SqlClient	Usa parametri denominati nel formato @nomeparametro.
System.Data.OleDb	Usa marcatori dei parametri di posizione indicati da un punto interrogativo (?).
System.Data.Odbc	Usa marcatori dei parametri di posizione indicati da un punto interrogativo (?).
System.Data.OracleClient	Usa parametri denominati nel formato :parmname (o parmname).

Specifica dei tipi di dati per i parametri

Il tipo di dati di un parametro è specifico del provider di dati .NET Framework. Se si specifica il tipo, il valore dell'oggetto Parameter viene convertito nel tipo del provider di dati .NET Framework prima che il valore venga 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 di provider di dati .NET Framework di un oggetto Parameter viene dedotto dal tipo .NET Framework del Value dell'oggetto Parameter o dal 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 Framework	DbType	SqlDbType	OleDbType	OdbcType	OracleType
Boolean	Booleano	Bit	Booleano	Bit	Byte
Byte	Byte	TinyInt	UnsignedTinyInt	TinyInt	Byte
byte[]	Binario	VarBinary. La conversione implicita non riesce se la matrice di byte ha una dimensione superiore a quella massima di VarBinary, che è di 8000 byte.Per le matrici di byte maggiori di 8000 byte, impostare in modo esplicito SqlDbType.	VarBinary	Binario	Raw
Char		L'inferenza di un oggetto SqlDbType da char non è supportata.	Char	Char	Byte
DateTime	DataOra	DataOra	DBTimeStamp	DataOra	DataOra
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.			Data/Ora
Decimal	Decimale	Decimale	Decimale	Numerico	Numero
Double	Double	Float	Double	Double	Double
Single	Singola	Reale	Singola	Reale	Float
Guid	GUID	UniqueIdentifier	GUID	UniqueIdentifier	Raw
Int16	Int16	SmallInt	SmallInt	SmallInt	Int16
Int32	Int32	Int	Int	Int	Int32
Int64	Int64	BigInt	BigInt	BigInt	Numero
Object	Oggetto	Variante	Variante	L'inferenza di un oggetto OdbcType da Object non è supportata.	BLOB
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.	VarWChar	NVarChar	NVarChar
TimeSpan	Ora	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.	DBTime	Time	Data/Ora
UInt16	UInt16	L'inferenza di un oggetto SqlDbType da UInt16 non è supportata.	UnsignedSmallInt	Int	UInt16
UInt32	UInt32	L'inferenza di un oggetto SqlDbType da UInt32 non è supportata.	UnsignedInt	BigInt	UInt32
UInt64	UInt64	L'inferenza di un oggetto SqlDbType da UInt64 non è supportata.	UnsignedBigInt	Numerico	Numero
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringFixedLength	Char	Char	Char	Char
	Valuta	Moneta	Valuta	L'inferenza di un oggetto OdbcType da Currency non è supportata.	Numero
	Data	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.	DBDate	Data	Data/Ora
	SByte	L'inferenza di un oggetto SqlDbType da SByte non è supportata.	TinyInt	L'inferenza di un oggetto OdbcType da SByte non è supportata.	SByte
	StringFixedLength	NChar	WChar	NChar	NChar
	Ora	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.	DBTime	Time	Data/Ora
	VarNumeric	L'inferenza di un oggetto SqlDbType da VarNumeric non è supportata.	VarNumeric	L'inferenza di un oggetto OdbcType da VarNumeric non è supportata.	Numero
Tipo di oggetto definito dall'utente (oggetto con SqlUserDefinedAggregateAttribute	Object o String, a seconda del provider (SqlClient restituisce sempre Object, ODBC restituisce sempre String e il provider di dati gestito OleDb può vedere entrambi	SqlDbType.Udt se SqlUserDefinedTypeAttribute è in caso contrario, altrimenti Variant	OleDbType.VarWChar (se il valore è null) in caso contrario OleDbType.Variant.	OdbcType.NVarChar	non supportato

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. Per altre informazioni sui valori null di database, vedere Handling Null Values.

Derivare informazioni sui parametri

I parametri possono anche essere derivati da una stored procedure usando la classe DbCommandBuilder . Le classi SqlCommandBuilder e OleDbCommandBuilder forniscono un metodo statico, DeriveParameters, che popola automaticamente la raccolta di parametri di un oggetto comando con le informazioni provenienti da una stored procedure. Si noti che 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. Con il provider di dati .NET Framework per SQL Server non è possibile usare il punto interrogativo (?) come segnaposto per passare i 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(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

Uso di parametri con OleDbCommand o OdbcCommand

Quando si usano parametri con OleDbCommand o OdbcCommand, l'ordine dei parametri aggiunti alla raccolta Parameters deve corrispondere all'ordine dei parametri definiti nella stored procedure. Con il provider di dati .NET Framework per OLE DB e il provider di dati .NET Framework per ODBC, i parametri di una stored procedure vengono gestiti come segnaposto e i valori dei parametri vengono applicati nell'ordine. Inoltre, i parametri dei valori restituiti devono essere i primi parametri aggiunti alla raccolta Parameters .

Con il provider di dati .NET Framework per OLE DB e il provider di dati .NET Framework per ODBC non è possibile usare parametri denominati nel passaggio di parametri a un'istruzione SQL o a una stored procedure. In questo caso è necessario usare il punto interrogativo (?) come segnaposto, come nell'esempio seguente.

SELECT * FROM Customers WHERE CustomerID = ?

Di conseguenza, l'ordine in cui vengono aggiunti gli oggetti Parameter alla raccolta Parameters deve corrispondere esattamente alla posizione del segnaposto punto interrogativo (?) usato per il parametro.

Esempio di OleDb

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;

Esempio di Odbc

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;

Vedi anche

• Comandi e parametri
• Parametri DataAdapter
• Mapping dei tipi di dati in ADO.NET
• Panoramica di ADO.NET