Configurazione di parametri e di tipi di dati dei parametri (ADO.NET)

Articolo
05/20/2011

Gli oggetti comando utilizzano 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 ulteriori informazioni, vedere Caching e riutilizzo del piano di esecuzione e Parametri e riutilizzo del piano di esecuzione nella documentazione online di SQL Server. 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 utilizzare 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.

Specifica della proprietà ParameterDirection

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

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

Utilizzo dei segnaposto di 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	Utilizza parametri denominati nel formato @nomeparametro.
System.Data.OleDb	Utilizza indicatori dei parametri di posizione indicati da un punto interrogativo (?).
System.Data.Odbc	Utilizza indicatori dei parametri di posizione indicati da un punto interrogativo (?).
System.Data.OracleClient	Utilizza parametri denominati nel formato :parmname (o parmname).

Specifica di un tipo 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 del provider di dati .NET Framework di un oggetto Parameter viene inferito dal tipo .NET Framework di Value dell'oggetto Parameter oppure da DbType dell'oggetto Parameter. Nella tabella seguente viene illustrato il tipo Parameter dedotto in base all'oggetto passato come valore di Parameter o all'oggetto DbType specificato.

Tipo .NET Framework	DbType	SqlDbType	OleDbType	OdbcType	OracleType
bool	Boolean	Bit	Boolean	Bit	Byte
byte	Byte	TinyInt	UnsignedTinyInt	TinyInt	Byte
byte[]	Binary	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	Binary	Raw
char		La deduzione di un oggetto SqlDbType da char non è supportata.	Char	Char	Byte
DateTime	DateTime	DateTime	DBTimeStamp	DateTime	DateTime
DateTimeOffset	DateTimeOffset	DateTimeOffset in SQL Server 2008. La deduzione di un oggetto SqlDbType da DateTimeOffset non è supportata nelle versioni di SQL Server precedenti a SQL Server 2008.			DateTime
Decimal	Decimal	Decimal	Decimal	Numeric	Number
double	Double	Float	Double	Double	Double
float	Single	Real	Single	Real	Float
Guid	Guid	UniqueIdentifier	Guid	UniqueIdentifier	Raw
Int16	Int16	SmallInt	SmallInt	SmallInt	Int16
Int32	Int32	Int	Int	Int	Int32
Int64	Int64	BigInt	BigInt	BigInt	Number
object	Object	Variant	Variant	La deduzione 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	Time	Time in SQL Server 2008. La deduzione di un oggetto SqlDbType da TimeSpan non è supportata nelle versioni di SQL Server precedenti a SQL Server 2008.	DBTime	Time	DateTime
UInt16	UInt16	La deduzione di un oggetto SqlDbType da UInt16 non è supportata.	UnsignedSmallInt	Int	UInt16
UInt32	UInt32	La deduzione di un oggetto SqlDbType da UInt32 non è supportata.	UnsignedInt	BigInt	UInt32
UInt64	UInt64	La deduzione di un oggetto SqlDbType da UInt64 non è supportata.	UnsignedBigInt	Numeric	Number
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringFixedLength	Char	Char	Char	Char
	Currency	Money	Currency	La deduzione di un oggetto OdbcType da Currency non è supportata.	Number
	Date	Date in SQL Server 2008. La deduzione di un oggetto SqlDbType da Date non è supportata nelle versioni di SQL Server precedenti a SQL Server 2008.	DBDate	Date	DateTime
	SByte	La deduzione di un oggetto SqlDbType da SByte non è supportata.	TinyInt	La deduzione di un oggetto OdbcType da SByte non è supportata.	SByte
	StringFixedLength	NChar	WChar	NChar	NChar
	Time	Time in SQL Server 2008. La deduzione di un oggetto SqlDbType da Time non è supportata nelle versioni di SQL Server precedenti a SQL Server 2008.	DBTime	Time	DateTime
	VarNumeric	La deduzione di un oggetto SqlDbType da VarNumeric non è supportata.	VarNumeric	L'inferenza di un oggetto OdbcType da VarNumeric non è supportata.	Number

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, invece di null (Nothing in Visual Basic).Il valore null nel sistema è un oggetto vuoto senza un valore.DBNull viene utilizzato per rappresentare i valori null.Per ulteriori informazioni sui valori null di database, vedere Gestione di valori null (ADO.NET).

Informazioni sui parametri

I parametri possono anche essere derivati da una stored procedure utilizzando 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 ulteriori informazioni, vedere Generazione di comandi con CommandBuilder (ADO.NET).

Utilizzo di parametri con SqlCommand e una stored procedure

Le stored procedure offrono numerosi vantaggi nelle applicazioni guidate dai dati. Utilizzando 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 semplicemente passandone il nome seguito dagli argomenti dei parametri come istruzione SQL, l'utilizzo 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 ulteriori informazioni, vedere la documentazione online di SQL Server.

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 ulteriori informazioni, vedere la documentazione online di SQL Server.

Quando si utilizzano parametri con SqlCommand per eseguire una stored procedure di SQL Server, i nomi dei parametri aggiunti alla raccolta Parameters devono corrispondere ai nomi degli indicatori dei parametri nella stored procedure. Il provider di dati .NET Framework per SQL Server non supporta il segnaposto punto interrogativo (?) per il passaggio di parametri a un'istruzione SQL o a una stored procedure. I parametri nella stored procedure vengono trattati come parametri denominati e vengono ricercati indicatori di parametri corrispondenti. Ad esempio, la stored procedure CustOrderHist è definita con un parametro denominato @CustomerID. Quando il codice esegue la stored procedure deve utilizzare 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 utilizzare uno dei costruttori di overload per impostare più proprietà in una singola istruzione.

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()
        Dim 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 Sub

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();
        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();
    }
}

Utilizzo di parametri con OleDbCommand o OdbcCommand

Quando si utilizzano 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 utilizzare parametri denominati nel passaggio di parametri a un'istruzione SQL o a una stored procedure. In questo caso è necessario utilizzare il segnaposto punto interrogativo (?) come illustrato 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 (?) utilizzato 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;

Vedere anche

Concetti

Parametri di DataAdapter (ADO.NET)

Altre risorse

Comandi e parametri (ADO.NET)

Mapping dei tipi di dati in ADO.NET

Condividi tramite