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. Questo è il 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.

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. Specificando il tipo converte il valore dell'oggetto Parameter nel tipo di provider di dati .NET Framework prima di passare il valore 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 Parameter oggetto viene dedotto dal tipo .NET Framework dell'oggetto ParameterValue o dall'oggetto DbTypeParameter . 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 Boolean bit Boolean bit Byte
Byte Byte TinyInt UnsignedTinyInt TinyInt Byte
byte[] Binary VarBinary. Questa conversione implicita avrà esito negativo se la matrice di byte è maggiore della dimensione massima di un VarBinary, ovvero 8000 byte. Per le matrici di byte superiori a 8000 byte, impostare in modo esplicito l'oggetto SqlDbType. VarBinary Binary Notifica non elaborata
Char L'inferenza di un oggetto SqlDbType da char non è supportata. Char Char Byte
DateTime Datetime Datetime DBTimeStamp 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. Datetime
Decimal Decimal Decimal Decimal Numeric Number
Double Double Float Double Double Double
Single Single Real Single Real Float
Guid Guid UniqueIdentifier Guid UniqueIdentifier Notifica non elaborata
Int16 Int16 SmallInt SmallInt SmallInt Int16
Int32 Int32 Int Int Int Int32
Int64 Int64 BigInt BigInt BigInt Number
Object Oggetto Variant Variant 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 Tempo 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 Ora Datetime
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 Numeric Number
AnsiString VarChar VarChar VarChar VarChar
AnsiStringFixedLength Char Char Char Char
Valuta Money Valuta L'inferenza di un oggetto OdbcType da Currency non è supportata. Number
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 Datetime
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
Tempo 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 Ora Datetime
VarNumeric L'inferenza di un oggetto SqlDbType da VarNumeric non è supportata. VarNumeric L'inferenza di un oggetto OdbcType da VarNumeric non è supportata. Number
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 un valore. DBNull viene usato per rappresentare i valori null. Per altre informazioni sui valori Null del database, vedere Gestione dei valori Null.

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

Utilizzo 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. Il provider di dati .NET Framework per OLE DB e il provider di dati .NET Framework per ODBC considerano i parametri in una stored procedure come segnaposto e applicano i valori dei parametri in ordine. Inoltre, i parametri dei valori restituiti devono essere i primi parametri aggiunti alla raccolta Parameters .

Il provider di dati .NET Framework per OLE DB e il provider di dati .NET Framework per ODBC non supportano parametri denominati per il 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