Utilizar parámetros con el control SqlDataSource

Artículo
10/22/2014

Actualización: noviembre 2007

Las instrucciones y procedimientos almacenados SQL incluyen a menudo parámetros que se evalúan en tiempo de ejecución. Una instrucción SQL escrita con parámetros se denomina instrucción SQL parametrizada.

Cuando utiliza el control SqlDataSource, puede especificar las consultas e instrucciones SQL que utilizan parámetros. Esto ayuda a que los escenarios de enlace de datos sean más flexibles al leer y escribir información de base de datos basada en valores que se evalúan en tiempo de ejecución. Puede obtener valores de parámetros desde distintos orígenes, incluso variables de aplicación ASP.NET, identidades de usuario y valores seleccionados por el usuario. Puede utilizar parámetros para proporcionar criterios de búsqueda para recuperación de datos, para proporcionar valores que se deben insertar, actualizar o eliminar en un almacén de datos, y para proporcionar valores para ordenación, paginación y filtrado.

Utilizar parámetros

Como todos los controles de origen de datos, el control SqlDataSource acepta parámetros de entrada en tiempo de ejecución y los administra en colecciones de parámetros. Cada operación de datos tiene una colección de parámetros relacionada. Para las operaciones de selección, puede utilizar la colección SelectParameters y para las actualizaciones, la colección UpdateParameters, etc.

Puede especificar un nombre, tipo, dirección y valor predeterminado para cada parámetro. Los parámetros que reciben valores de un objeto concreto, como un control, variable de sesión o el perfil de usuario, exigen que se establezcan propiedades adicionales. Por ejemplo, ControlParameter requiere establecer ControlID para identificar el control del que se va a tomar el valor del parámetro y establecer la propiedad PropertyName para especificar la propiedad que contiene el valor del parámetro. Para obtener más información, vea Utilizar parámetros con controles de origen de datos.

El control SqlDataSource crea también automáticamente parámetros basados en valores pasados por un control enlazado a datos (como un control GridView o FormView) que admite operaciones automáticas de actualización, inserción y eliminación. Para obtener más información, vea Cómo un control de origen de datos crea parámetros para campos enlazados a datos.

Especificar parámetros en comandos

Cuando utilice el control SqlDataSource, puede establecer las propiedades del comando del control en instrucciones SQL parametrizadas o en el nombre de un procedimiento almacenado. Si especifica un procedimiento almacenado para un comando, debe especificar que el tipo del comando es StoredProcedure.

Nombres de parámetros

El control SqlDataSource agrega el valor de la propiedad ParameterPrefix al comienzo de todos los nombres de parámetros. (El prefijo predeterminado es "@".)

Si un control enlazado a datos como un control GridView está enlazado al control SqlDataSource, durante una operación de actualización o eliminación el control enlazado a datos pasa los valores de registro actuales y originales al control SqlDataSource. Los valores actuales se pasan en el diccionario Values. Los valores originales se pasan en los diccionarios Keys o OldValues. El contenido de estos diccionarios se anexa a la colección Parameters del objeto DbCommand subyacente para una operación de datos determinada.

En los comandos SQL del control SqlDataSource, se utiliza una convención de nomenclatura para que los marcadores de posición de los parámetros coincidan con los valores anteriores pasados al comando. Establezca el formato del nombre del marcador de posición estableciendo la propiedad OldValuesParameterFormatString del control SqlDataSource. Establezca la propiedad OldValuesParameterFormatString en una cadena que incluya "{0} " como marcador de posición para el nombre del campo. Por ejemplo, si establece la propiedad OldValuesParameterFormatString en "old_{0}", los nombres de los parámetros de los valores originales se resolverán en el nombre de campo precedido por "@old_". Considere una operación de actualización que implique un campo denominado LastModifiedDate. El valor actual para el campo se pasa en el diccionario Values y el valor original para el campo, en el diccionario OldValues. Se crean un parámetro denominado @LastModifiedDate para pasar el valor actual y un parámetro denominado @old\_LastModifiedDate para pasar el valor original. Puede incluir ambos parámetros en una instrucción SQL para diferenciarlos entre los valores actuales y originales para el campo, tal como se muestra en el ejemplo siguiente:

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

La capacidad de separar los valores actuales y originales en un comando es fundamental para realizar comprobaciones de concurrencia optimista o trabajar con un origen de datos en el que se puede modificar la clave principal.

Para obtener más información sobre los diccionarios pasados por un control enlazado a datos, vea Cómo un control de origen de datos crea parámetros para campos enlazados a datos.

Utilizar parámetros con el proveedor SqlClient

De manera predeterminada, el control SqlDataSource utiliza el proveedor de datos System.Data.SqlClient para trabajar con SQL Server como origen de datos. El proveedor System.Data.SqlClient admite parámetros con nombre como marcadores de posición, tal como se muestra en el ejemplo siguiente:

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

Con parámetros con nombre, el orden en el que se especifican los parámetros en la colección de parámetros del comando no es importante. No obstante, debe asegurarse de que los nombres de parámetros que utiliza en el comando SQL corresponden a los nombres de los parámetros de la colección asociada.

El ejemplo siguiente muestra cómo utilizar los parámetros con nombre en un comando SQL para un control SqlDataSource que utiliza el proveedor 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>

Utilizar parámetros con los proveedores OleDb y Odbc

Si se conecta a un origen de datos OLE DB o ODBC, puede configurar el control SqlDataSource para que utilice el proveedor System.Data.OleDb o System.Data.Odbc, respectivamente, y que trabaje con el origen de datos. Los proveedores System.Data.OleDb y System.Data.Odbc admiten sólo parámetros posicionales identificados por el carácter "? ", tal como se muestra en el ejemplo siguiente:

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

Cuando utiliza los proveedores System.Data.OleDb y System.Data.Odbc con instrucciones SQL parametrizadas, el orden en el que especifica los marcadores de posición de los parámetros debe coincidir con el orden de los parámetros en la colección de parámetros relacionados. Puede controlar el orden de los parámetros especificándolos explícitamente en la colección para la operación de datos relacionados, como una colección UpdateParameters para el control UpdateCommand relacionado. Cuando crea explícitamente una colección de parámetros para parámetros que se crean automáticamente a partir de valores pasados por un control enlazado a datos, los parámetros creados explícitamente sobrescriben todos los parámetros que se generan automáticamente. De esta manera garantiza que los parámetros se pasan en el orden que desea.

Nota:
De forma predeterminada, los parámetros basados en campos enlazados de un control enlazado a datos se agregan al comando de los diccionarios de parámetros en el orden siguiente: Values, Keys, OldValues. Para operaciones de eliminación, sólo se utiliza el diccionario Keys. Para operaciones de inserción, sólo se utiliza el diccionario Values. Para obtener más información sobre los diccionarios pasados por un control enlazado a datos, vea Cómo un control de origen de datos crea parámetros para campos enlazados a datos.

De forma predeterminada, los parámetros basados en campos enlazados de un control enlazado a datos se agregan al comando de los diccionarios de parámetros en el orden siguiente: Values, Keys, OldValues. Para operaciones de eliminación, sólo se utiliza el diccionario Keys. Para operaciones de inserción, sólo se utiliza el diccionario Values. Para obtener más información sobre los diccionarios pasados por un control enlazado a datos, vea Cómo un control de origen de datos crea parámetros para campos enlazados a datos.

El ejemplo siguiente muestra cómo especificar parámetros para un control SqlDataSource que utiliza el proveedor System.Data.OleDb. Los parámetros se especifican explícitamente para garantizar que el orden de los parámetros en la colección coincida con el orden de los marcadores de posición en las instrucciones 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>