Utilizzo dei parametri con il controllo SqlDataSource

Articolo
10/22/2014

Aggiornamento: novembre 2007

Le istruzioni SQL e le stored procedure spesso includono parametri che vengono valutati in fase di esecuzione. Un'istruzione SQL scritta con parametri viene definita istruzione SQL con parametri.

Quando si utilizza il controllo SqlDataSource, è possibile specificare query e istruzioni SQL che utilizzano parametri. In questo modo gli scenari di associazione dati risulteranno più flessibili, grazie alla lettura e scrittura di informazioni del database in base ai valori valutati in fase di esecuzione. È possibile ottenere i valori dei parametri da diverse origini, compresi le variabili di applicazione ASP.NET, le identità utente e i valori selezionati dall'utente. È possibile utilizzare i parametri per fornire criteri di ricerca per il recupero dei dati, per fornire valori da inserire, aggiornare o eliminare in un archivio dati e per fornire valori per ordinare, spostare e filtrare.

Utilizzo dei parametri

Analogamente a tutti i controlli origine dati, il controllo SqlDataSource accetta i parametri di input in fase di esecuzione e li gestisce negli insiemi di parametri. A ciascuna operazione sui dati è correlato un insieme di parametri. Per le operazioni di selezione è possibile utilizzare l'insieme SelectParameters, per gli aggiornamenti l'insieme UpdateParameters e così via.

Per ciascun parametro è possibile specificare un nome, un tipo, una direzione e un valore predefinito. Per i parametri che ottengono valori da un oggetto specifico, ad esempio un controllo, una variabile di sessione o il profilo utente, è necessario impostare proprietà aggiuntive. Ad esempio, un oggetto ControlParameter richiede l'impostazione della proprietà ControlID per l'identificazione del controllo da cui accettare il valore di parametro e l'impostazione della proprietà PropertyName per la specifica della proprietà contenente il valore di parametro. Per ulteriori informazioni, vedere Utilizzo di parametri con controlli origine dati.

Il controllo SqlDataSource, inoltre, crea automaticamente parametri in base ai valori passati da un controllo con associazione a dati, ad esempio un controllo GridView o FormView, che supporta le operazioni automatiche di aggiornamento, inserimento ed eliminazione. Per ulteriori informazioni, vedere Come un controllo origine dati crea parametri per campi associati a dati.

Specifica dei parametri nei comandi

Quando si utilizza il controllo SqlDataSource, è possibile impostare le proprietà Command del controllo sulle istruzioni SQL con parametri oppure sul nome di una stored procedure. Se si specifica una stored procedure per un comando, è necessario indicare che il comando è del tipo StoredProcedure.

Nomi dei parametri

Il controllo SqlDataSource aggiunge il valore della proprietà ParameterPrefix all'inizio di tutti i nomi di parametri. Il prefisso predefinito è "@".

Se un controllo con associazione a dati, ad esempio GridView, è associato al controllo SqlDataSource, durante un'operazione di aggiornamento o eliminazione il controllo con associazione a dati passa al controllo SqlDataSource sia i valori di record correnti sia quelli originali. I valori correnti vengono passati nel dizionario Values. I valori originali vengono passati nel dizionario Keys oppure OldValues. Il contenuto di tali dizionari viene aggiunto all'insieme Parameters dell'oggetto DbCommand per una determinata operazione sui dati.

Nei comandi SQL del controllo SqlDataSource utilizzare una convenzione di denominazione per creare una corrispondenza tra i segnaposto dei parametri e i vecchi valori passati nel comando. Per stabilire il formato del nome del segnaposto, impostare la proprietà OldValuesParameterFormatString del controllo SqlDataSource. Impostare la proprietà OldValuesParameterFormatString su una stringa in cui "{0}" è un segnaposto per il nome del campo. Se, ad esempio, si imposta la proprietà OldValuesParameterFormatString su "old_{0}", i nomi dei parametri dei valori originali si risolveranno nel nome del campo con prefisso "@old_". Si consideri un'operazione di aggiornamento che riguarda un campo denominato LastModifiedDate. Il valore corrente relativo al campo viene passato nel dizionario Values, mentre il valore originale viene passato nel dizionario OldValues. Per passare il valore corrente viene creato un parametro denominato @LastModifiedDate, mentre per passare il valore originale viene creato un parametro denominato @old\_LastModifiedDate. È quindi possibile includere entrambi i parametri in un'istruzione SQL per distinguere il valore originale da quello corrente, come illustrato nell'esempio riportato di seguito:

UPDATE Table1 SET LastModifiedDate = @LastModifiedDate
  WHERE Key = @Key AND LastModifiedDate = @old_LastModifiedDate

La possibilità di separare il valore corrente da quello originale in un comando è essenziale quando si eseguono controlli di concorrenza ottimistica o si utilizza un'origine dati in cui è possibile modificare la chiave primaria.

Per ulteriori informazioni sui dizionari passati da un controllo con associazione a dati, vedere Come un controllo origine dati crea parametri per campi associati a dati.

Utilizzo dei parametri con il provider SqlClient

Per impostazione predefinita, il controllo SqlDataSource si serve del provider di dati System.Data.SqlClient per utilizzare SQL Server come origine dati. Il provider System.Data.SqlClient supporta parametri denominati come segnaposto, come illustrato nell'esempio riportato di seguito:

SELECT * FROM Employees WHERE LastName = @LastName 
  AND FirstName = @FirstName

Con i parametri denominati, l'ordine con cui questi sono specificati nell'insieme di parametri del comando non è importante. È invece necessario verificare che i nomi dei parametri utilizzati nel comando SQL corrispondano ai nomi dei parametri dell'insieme associato.

Nell'esempio riportato di seguito viene illustrato l'utilizzo dei parametri denominati in un comando SQL per un controllo SqlDataSource che utilizza il provider System.Data.SqlClient.

<asp:sqlDataSource ID="EmployeeDetailsSqlDataSource" 
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

  InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                 SELECT @EmpID = SCOPE_IDENTITY()"
  UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName 
                   WHERE EmployeeID=@EmployeeID"
  DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

  ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
  OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
  RunAt="server">

  <SelectParameters>
    <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
  </SelectParameters>

  <InsertParameters>
    <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
  </InsertParameters>

</asp:sqlDataSource>

<asp:sqlDataSource ID="EmployeeDetailsSqlDataSource" 
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

  InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                 SELECT @EmpID = SCOPE_IDENTITY()"
  UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName 
                   WHERE EmployeeID=@EmployeeID"
  DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

  ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
  OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
  RunAt="server">

  <SelectParameters>
    <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
  </SelectParameters>

  <InsertParameters>
    <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
  </InsertParameters>

</asp:sqlDataSource>

Utilizzo dei parametri con i provider OleDb e Odbc

Se si esegue la connessione a un'origine dati OLE DB o ODBC, è possibile configurare il controllo SqlDataSource per utilizzare rispettivamente il provider System.Data.OleDb o System.Data.Odbc insieme all'origine dati. I provider System.Data.OleDb e System.Data.Odbc supportano solo parametri posizionali identificati dal carattere "?", come illustrato nell'esempio riportato di seguito:

SELECT * FROM Employees WHERE LastName = ? AND FirstName = ?

Quando si utilizzano i provider System.Data.OleDb e System.Data.Odbc con le istruzioni SQL con parametri, l'ordine con cui si specificano i segnaposto dei parametri deve corrispondere a quello dei parametri dell'insieme di parametri correlato. È possibile controllare l'ordine dei parametri specificandoli esplicitamente nell'insieme per l'operazione sui dati correlata, ad esempio l'insieme UpdateParameters per l'operazione UpdateCommand correlata. Quando si crea esplicitamente un insieme per i parametri che vengono creati automaticamente dai valori passati da un controllo con associazione a dati, i parametri creati esplicitamente sovrascrivono quelli generati automaticamente. Questo garantisce che i parametri vengano passati nell'ordine desiderato. Se si chiama una stored procedure che restituisce un valore, è necessario specificare un parametro con un valore Direction di ReturnValue come primo parametro nell'insieme di parametri di comando.

Nota:
Per impostazione predefinita, i parametri che si basano sui campi associati da un controllo con associazione a dati vengono aggiunti al comando dai dizionari dei parametri nell'ordine seguente: Values, Keys, OldValues. Per le operazioni di eliminazione viene utilizzato solo il dizionario Keys. Per le operazioni di inserimento viene utilizzato solo il dizionario Values. Per ulteriori informazioni sui dizionari passati da un controllo con associazione a dati, vedere Come un controllo origine dati crea parametri per campi associati a dati.

Per impostazione predefinita, i parametri che si basano sui campi associati da un controllo con associazione a dati vengono aggiunti al comando dai dizionari dei parametri nell'ordine seguente: Values, Keys, OldValues. Per le operazioni di eliminazione viene utilizzato solo il dizionario Keys. Per le operazioni di inserimento viene utilizzato solo il dizionario Values. Per ulteriori informazioni sui dizionari passati da un controllo con associazione a dati, vedere Come un controllo origine dati crea parametri per campi associati a dati.

Nell'esempio riportato di seguito viene illustrato come specificare parametri per un controllo SqlDataSource che utilizza il provider System.Data.OleDb. I parametri vengono specificati esplicitamente per garantire che l'ordine dei parametri dell'insieme corrisponda a quello dei segnaposto nelle istruzioni SQL.

<Fields>                  
  <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>                    
  <asp:BoundField DataField="FirstName"  HeaderText="First Name"/>
  <asp:BoundField DataField="LastName"   HeaderText="Last Name"/>                    
  <asp:TemplateField HeaderText="Birth Date">
    <ItemTemplate> 
      <asp:Label ID="BirthDateLabel" Runat="Server" 
                 Text='<%# Eval("BirthDate", "{0:d}") %>' />
    </ItemTemplate>
    <InsertItemTemplate>
      <asp:Calendar ID="InsertBirthDateCalendar" Runat="Server"
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </InsertItemTemplate>
    <EditItemTemplate>
      <asp:Calendar ID="EditBirthDateCalendar" Runat="Server"
                    VisibleDate='<%# Eval("BirthDate") %>'
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </EditItemTemplate>
  </asp:TemplateField>                    
</Fields>

<Fields>                  
  <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>                    
  <asp:BoundField DataField="FirstName"  HeaderText="First Name"/>
  <asp:BoundField DataField="LastName"   HeaderText="Last Name"/>                    
  <asp:TemplateField HeaderText="Birth Date">
    <ItemTemplate> 
      <asp:Label ID="BirthDateLabel" Runat="Server" 
                 Text='<%# Eval("BirthDate", "{0:d}") %>' />
    </ItemTemplate>
    <InsertItemTemplate>
      <asp:Calendar ID="InsertBirthDateCalendar" Runat="Server"
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </InsertItemTemplate>
    <EditItemTemplate>
      <asp:Calendar ID="EditBirthDateCalendar" Runat="Server"
                    VisibleDate='<%# Eval("BirthDate") %>'
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </EditItemTemplate>
  </asp:TemplateField>                    
</Fields>