Como um Controle da Fonte de Dados Cria Parâmetros para Campos Ligados a Dados

Artigo
10/15/2014

When you use a data-bound control such as a GridView, DetailsView, or FormView control with an ASP.NET data source control, the data-bound control can pass parameter names and values to the data source control based on the bound fields in the data-bound control.The data source control then includes the field names and values in the parameter collection for select or update operations.Para obter mais informações, consulte Usando parâmetros com o controle SqlDataSource e Usando Parâmetros com o Controle ObjectDataSource.

Dicionários Passados para Controles da Fonte de Dados

When a data-bound control requests an operation from the data source control, it passes one or more IDictionary collections containing parameter names and values for the requested data operation.Os valores dos pares nome/valor no dicionário são derivados dos controles filho.For example, in an update operation, the data-bound control reads parameter values from TextBox or CheckBox controls that are displayed in edit mode.The names for the name/value pairs are taken from the names of the fields bound to child controls and from the field names specified in the DataKeyNames property.Para uma operação de atualização ou exclusão, o controle ligado a dados também pode passar um dicionário contendo os valores originais do registro de dados.

Name/value pairs are passed using the following IDictionary collections:

Values collection Passed for an insert operation.Contém os pares nome/valor para um novo registro.Field names and values for the Values collection are taken from child controls in an InsertItemTemplate or from bound fields in a DetailsView control whose InsertVisible property is set to true.
Keys collection Passed for update and delete operations.Contém a chave primária ou chaves para o registro que está sendo atualizado ou excluído.If the key fields can be modified at the data source, the Keys collection also contains the original values of the key fields.Quando um controle ligado a dados é preenchido com dados do controle da fonte de dados, ele mantém os dados no estado da exibição.When an update or delete operation is requested, the Keys collection is populated with values stored earlier in view state.If the data-bound control's EnableViewState property is set to false, the Keys collection is not populated for the update or delete operation.
NewValues collection Passed for an update operation.Contém os pares nome/valor com os novos valores para o item atualizado, inclusive os novos valores para os campos de chave atualizáveis.Field names and values for the NewValues collection are taken from child controls in an EditItemTemplate or from bound fields in a DetailsView control whose ReadOnly property is set to false.
OldValues collection Passed for update or delete operations.Contém os valores originais para o registro de dados a ser usado para checagem de concorrência otimista.(For information on optimistic concurrency checking, see the ConflictDetection property of the data source control you are working with.) Values for key fields identified by the DataKeyNames property are not included in the OldValues collection.Key field names and values are included only in the Keys collection.Quando um controle ligado a dados é preenchido com dados do controle da fonte de dados, ele mantém os dados no estado da exibição.When an update or delete operation is requested, the OldValues collection is populated with values stored earlier in view state.If the data-bound control's EnableViewState property is set to false, the OldValues collection is not populated for the update or delete operation.

Você pode acessar todos os essas coleções usando os argumentos passados com o evento de controle ligado a dados para a operação solicitada.For example, in the GridView control's RowUpdating event, the GridViewUpdateEventArgs class provides access to the NewValues collection.

Nomes de parâmetro

The data source control creates parameters automatically for the values passed in the IDictionary collections.For an insert operation, the data source control populates its InsertParameters collection with values from the name/value pairs in the Values collection.For an update operation, the data source control populates its UpdateParameters collection with values from the name/value pairs in the Keys, NewValues, and OldValues collections.For a delete operation, the data source control populates its DeleteParameters collection with values from the name/value pairs in the Keys and OldValues collections.

The OldValues collection is not populated by default.It is populated only when the data-source control's ConflictDetection property is set to CompareAllValues.

Para uma operação de atualização ou excluir, por padrão somente parâmetros para atual limite valores são criados.Se você tiver acesso tanto corrente e original limite valores (por exemplo, para oferecer suporte a verificações de simultaneidade otimista), você pode ter o controle da fonte de dados cria parâmetros para valores corrente e originais.Para fazer isso, você deve estabelecer uma convenção de nomenclatura para parâmetros que irá conter valores originais.The format of the parameters for original values is determined by the OldValuesParameterFormatString property.Set the OldValuesParameterFormatString property to a string that includes "{0}" as a placeholder for the name of the field.Por exemplo, se você estiver usando o SqlDataSource controle, e se você conjunto o OldValuesParameterFormatStringpropriedade para "old_ 0", os nomes dos parâmetros de valor original resolverão o nome de campo prefixado com "@ old_". (The SqlDataSourcecontrole acrescenta um caractere "@" ao início de todos os nomes de parâmetro.) Consider an update operation that involves a field named LastModifiedDate.O valor corrente para o campo é passado no Values dicionário e o valor original para o campo é passado a OldValues dicionário. Um parâmetro chamado @LastModifiedDate é criado para passar o valor corrente e um parâmetro nomeado @old\_LastModifiedDate é criado para passar o valor original. Em seguida, você pode incluir ambos os parâmetros em um Instrução SQL para diferenciar entre os valores atuais e originais do campo, conforme mostrado no exemplo a seguir:

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

Não é necessário que acessar o nome/valor IDictionary coleções diretamente. You can simply include the automatically generated parameter names in your SQL statements (if your data source supports named parameters), or as the names of the parameters for the data methods in a business object you are accessing with an ObjectDataSource control.

Optionally, you can define Parameter objects in the data source control's UpdateParameters, InsertParameters, or DeleteParameters collections in order to customize the values passed by the data-bound control.You might create Parameter objects to strongly type the value or to specify a default value if null is passed.

The following code example shows a DetailsView control bound to a SqlDataSource control.The SqlDataSource control's InsertCommand, UpdateCommand, and DeleteCommand properties use the parameter names that are automatically generated by the SqlDataSource control.The parameter values are populated based on the Keys and NewValues dictionaries.The OldValues dictionary is not used because the ConflictDetection property is set to ConflictOptions.OverwriteChanges, the default.

<%@ Page language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script >

  Sub EmployeesDropDownList_OnSelectedIndexChanged(sender As Object, e As EventArgs)
    EmployeeDetailsView.DataBind()
  End Sub

  Sub EmployeeDetailsView_ItemUpdated(sender As Object, e As DetailsViewUpdatedEventArgs)
    EmployeesDropDownList.DataBind()
    EmployeesDropDownList.SelectedValue = e.Keys("EmployeeID").ToString()
    EmployeeDetailsView.DataBind()
  End Sub

  Sub EmployeeDetailsView_ItemDeleted(sender As Object, e As DetailsViewDeletedEventArgs)
    EmployeesDropDownList.DataBind()
  End Sub

  Sub EmployeeDetailsSqlDataSource_OnInserted(sender As Object, e As SqlDataSourceStatusEventArgs)
    Dim command As System.Data.Common.DbCommand = e.Command  
    EmployeesDropDownList.DataBind()
    EmployeesDropDownList.SelectedValue = _
      command.Parameters("@EmpID").Value.ToString()
    EmployeeDetailsView.DataBind()
  End Sub

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head >
    <title>Northwind Employees</title>
</head>
<body>
    <form id="form1" >

      <h3>Northwind Employees</h3>

        <table cellspacing="10">

          <tr>
            <td valign="top">
              <asp:DropDownList ID="EmployeesDropDownList" 
                DataSourceID="EmployeesSqlDataSource" 
                DataValueField="EmployeeID" 
                DataTextField="FullName"
                AutoPostBack="True"
                OnSelectedIndexChanged="EmployeesDropDownList_OnSelectedIndexChanged"
                RunAt="Server" />            
            </td>

            <td valign="top">                
              <asp:DetailsView ID="EmployeeDetailsView"
                DataSourceID="EmployeeDetailsSqlDataSource"
                AutoGenerateRows="false"
                AutoGenerateInsertbutton="true"
                AutoGenerateEditbutton="true"
                AutoGenerateDeletebutton="true"
                DataKeyNames="EmployeeID"     
                Gridlines="Both"
                OnItemUpdated="EmployeeDetailsView_ItemUpdated"
                OnItemDeleted="EmployeeDetailsView_ItemDeleted"      
                RunAt="server">

                <HeaderStyle backcolor="Navy"
                  forecolor="White"/>

                <RowStyle backcolor="White"/>

                <AlternatingRowStyle backcolor="LightGray"/>

                <EditRowStyle backcolor="LightCyan"/>

                <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:BoundField DataField="Address"    HeaderText="Address"/>                    
                  <asp:BoundField DataField="City"       HeaderText="City"/>                        
                  <asp:BoundField DataField="Region"     HeaderText="Region"/>
                  <asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>                    
                </Fields>                    
              </asp:DetailsView>
            </td>                
          </tr>            
        </table>

        <asp:SqlDataSource ID="EmployeesSqlDataSource"  
          SelectCommand="SELECT EmployeeID, LastName + ', ' + FirstName AS FullName FROM Employees" 
          Connectionstring="<%$ ConnectionStrings:NorthwindConnection %>" 
          RunAt="server">
        </asp:SqlDataSource>


        <asp:SqlDataSource ID="EmployeeDetailsSqlDataSource" 
          SelectCommand="SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode
                         FROM Employees WHERE EmployeeID = @EmpID"

          InsertCommand="INSERT INTO Employees(LastName, FirstName, Address, City, Region, PostalCode)
                         VALUES (@LastName, @FirstName, @Address, @City, @Region, @PostalCode); 
                         SELECT @EmpID = SCOPE_IDENTITY()"

          UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName, Address=@Address,
                           City=@City, Region=@Region, PostalCode=@PostalCode
                         WHERE EmployeeID=@EmployeeID"

          DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

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

          <SelectParameters>
            <asp:ControlParameter ControlID="EmployeesDropDownList" PropertyName="SelectedValue"
                                  Name="EmpID" Type="Int32" DefaultValue="0" />
          </SelectParameters>

          <InsertParameters>
            <asp:Parameter Name="LastName"   Type="String" />
            <asp:Parameter Name="FirstName"  Type="String" />
            <asp:Parameter Name="Address"    Type="String" />
            <asp:Parameter Name="City"       Type="String" />
            <asp:Parameter Name="Region"     Type="String" />
            <asp:Parameter Name="PostalCode" Type="String" />
            <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
          </InsertParameters>

          <UpdateParameters>
            <asp:Parameter Name="LastName"   Type="String" />
            <asp:Parameter Name="FirstName"  Type="String" />
            <asp:Parameter Name="Address"    Type="String" />
            <asp:Parameter Name="City"       Type="String" />
            <asp:Parameter Name="Region"     Type="String" />
            <asp:Parameter Name="PostalCode" Type="String" />
            <asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
          </UpdateParameters>

          <DeleteParameters>
            <asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
          </DeleteParameters>

        </asp:SqlDataSource>
      </form>
  </body>
</html>

<%@ Page language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script >

  void EmployeesDropDownList_OnSelectedIndexChanged(Object sender, EventArgs e)
  {
    EmployeeDetailsView.DataBind();
  }

  void EmployeeDetailsView_ItemUpdated(Object sender, DetailsViewUpdatedEventArgs e)
  {
    EmployeesDropDownList.DataBind();
    EmployeesDropDownList.SelectedValue = e.Keys["EmployeeID"].ToString();
    EmployeeDetailsView.DataBind();
  }

  void EmployeeDetailsView_ItemDeleted(Object sender, DetailsViewDeletedEventArgs e)
  {
    EmployeesDropDownList.DataBind();
  }

  void EmployeeDetailsSqlDataSource_OnInserted(Object sender, SqlDataSourceStatusEventArgs e)
  {
    System.Data.Common.DbCommand command = e.Command;   
    EmployeesDropDownList.DataBind();
    EmployeesDropDownList.SelectedValue = 
      command.Parameters["@EmpID"].Value.ToString();
    EmployeeDetailsView.DataBind();
  }

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head >
    <title>Northwind Employees</title>
</head>
<body>
    <form id="form1" >

      <h3>Northwind Employees</h3>

        <table cellspacing="10">

          <tr>
            <td valign="top">
              <asp:DropDownList ID="EmployeesDropDownList" 
                DataSourceID="EmployeesSqlDataSource" 
                DataValueField="EmployeeID" 
                DataTextField="FullName"
                AutoPostBack="True"
                OnSelectedIndexChanged="EmployeesDropDownList_OnSelectedIndexChanged"
                RunAt="Server" />            
            </td>

            <td valign="top">                
              <asp:DetailsView ID="EmployeeDetailsView"
                DataSourceID="EmployeeDetailsSqlDataSource"
                AutoGenerateRows="false"
                AutoGenerateInsertbutton="true"
                AutoGenerateEditbutton="true"
                AutoGenerateDeletebutton="true"
                DataKeyNames="EmployeeID"     
                Gridlines="Both"
                OnItemUpdated="EmployeeDetailsView_ItemUpdated"
                OnItemDeleted="EmployeeDetailsView_ItemDeleted"      
                RunAt="server">

                <HeaderStyle backcolor="Navy"
                  forecolor="White"/>

                <RowStyle backcolor="White"/>

                <AlternatingRowStyle backcolor="LightGray"/>

                <EditRowStyle backcolor="LightCyan"/>

                <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:BoundField DataField="Address"    HeaderText="Address"/>                    
                  <asp:BoundField DataField="City"       HeaderText="City"/>                        
                  <asp:BoundField DataField="Region"     HeaderText="Region"/>
                  <asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>                    
                </Fields>                    
              </asp:DetailsView>
            </td>                
          </tr>            
        </table>

        <asp:SqlDataSource ID="EmployeesSqlDataSource"  
          SelectCommand="SELECT EmployeeID, LastName + ', ' + FirstName AS FullName FROM Employees" 
          Connectionstring="<%$ ConnectionStrings:NorthwindConnection %>" 
          RunAt="server">
        </asp:SqlDataSource>


        <asp:SqlDataSource ID="EmployeeDetailsSqlDataSource" 
          SelectCommand="SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode
                         FROM Employees WHERE EmployeeID = @EmpID"

          InsertCommand="INSERT INTO Employees(LastName, FirstName, Address, City, Region, PostalCode)
                         VALUES (@LastName, @FirstName, @Address, @City, @Region, @PostalCode); 
                         SELECT @EmpID = SCOPE_IDENTITY()"

          UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName, Address=@Address,
                           City=@City, Region=@Region, PostalCode=@PostalCode
                         WHERE EmployeeID=@EmployeeID"

          DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

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

          <SelectParameters>
            <asp:ControlParameter ControlID="EmployeesDropDownList" PropertyName="SelectedValue"
                                  Name="EmpID" Type="Int32" DefaultValue="0" />
          </SelectParameters>

          <InsertParameters>
            <asp:Parameter Name="LastName"   Type="String" />
            <asp:Parameter Name="FirstName"  Type="String" />
            <asp:Parameter Name="Address"    Type="String" />
            <asp:Parameter Name="City"       Type="String" />
            <asp:Parameter Name="Region"     Type="String" />
            <asp:Parameter Name="PostalCode" Type="String" />
            <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
          </InsertParameters>

          <UpdateParameters>
            <asp:Parameter Name="LastName"   Type="String" />
            <asp:Parameter Name="FirstName"  Type="String" />
            <asp:Parameter Name="Address"    Type="String" />
            <asp:Parameter Name="City"       Type="String" />
            <asp:Parameter Name="Region"     Type="String" />
            <asp:Parameter Name="PostalCode" Type="String" />
            <asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
          </UpdateParameters>

          <DeleteParameters>
            <asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
          </DeleteParameters>

        </asp:SqlDataSource>
      </form>
  </body>
</html>

Consulte também

Conceitos

Usando parâmetros com controles de fonte de dados

Usando parâmetros com o controle SqlDataSource

Usando Parâmetros com o Controle ObjectDataSource

Visão geral sobre controles fonte de dados

Partilhar via

Como um Controle da Fonte de Dados Cria Parâmetros para Campos Ligados a Dados

Dicionários Passados para Controles da Fonte de Dados

Nomes de parâmetro

Consulte também

Conceitos

Recursos adicionais