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 .

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.

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.

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.

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.

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.

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