SqlDataSource.Update 方法

定义

使用 UpdateCommand SQL 字符串和 UpdateParameters 集合中的所有参数执行更新操作。Performs an update operation using the UpdateCommand SQL string and any parameters that are in the UpdateParameters collection.

public:
 int Update();
public int Update ();
member this.Update : unit -> int
Public Function Update () As Integer

返回

一个值,该值表示基础数据库中被更新的行数。A value that represents the number of rows updated in the underlying database.

异常

SqlDataSource 无法与基础数据源建立连接。The SqlDataSource cannot establish a connection with the underlying data source.

示例

本部分包含两个代码示例。This section contains two code examples. 第一个代码示例演示如何使用SqlDataSource控件DropDownList在控件中显示数据, 并在单击 "提交" 按钮时更新数据。The first code example demonstrates how to use a SqlDataSource control to display data in a DropDownList control and update data when the Submit button is clicked. 第二个代码示例演示如何显示从DropDownList控件中的 Microsoft SQL Server 数据库检索的数据, 以及如何TextBox使用控件更新记录。The second code example demonstrates how to display data that is retrieved from a Microsoft SQL Server database in a DropDownList control and update the record using a TextBox control.

下面的代码示例演示如何使用SqlDataSource控件DropDownList在控件中显示数据, 并在单击 "提交" 按钮时更新数据。The following code example demonstrates how to use a SqlDataSource control to display data in a DropDownList control and update data when the Submit button is clicked. 使用参数化 SQL 语句设置ControlParameter UpdateParameters 属性,并将两个参数添加UpdateCommand到集合中。The UpdateCommand property is set with a parameterized SQL statement, and two ControlParameter parameters are added to the UpdateParameters collection. 单击 "提交" 按钮时, 将OnClick Update处理该事件以显式调用方法。When the Submit button is clicked, the OnClick event is handled to call the Update method explicitly.

<%@Page  Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
 private void On_Click(Object source, EventArgs e) {
    try {
        SqlDataSource1.Update();
    }
    catch (Exception except) {
        // Handle the Exception.
    }

    Label2.Text="The record was updated successfully!";
 }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees"
          UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID">
          <UpdateParameters>
              <asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/>
              <asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/>
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="LastName"
          DataValueField="EmployeeID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br />
      <asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user."
        AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />

    </form>
  </body>
</html>
<%@Page  Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

 Sub On_Click(ByVal source As Object, ByVal e As EventArgs)
    Try
        SqlDataSource1.Update()
    Catch except As Exception
        ' Handle the Exception.
    End Try

    Label2.Text="The record was updated successfully!"

 End Sub 'On_Click
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees"
          UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID">
          <UpdateParameters>
              <asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/>
              <asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/>
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="LastName"
          DataValueField="EmployeeID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br />
      <asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user."
        AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />
    </form>
  </body>
</html>

下面的代码示例演示如何显示从DropDownList控件中的 SQL Server 数据库检索的数据, 以及如何TextBox使用控件更新记录。The following code example demonstrates how to display data that is retrieved from a SQL Server database in a DropDownList control and update the record using a TextBox control. 该示例演示如何在DbTransaction SqlDataSource使用控件更新数据时使用对象添加事务上下文。The example shows how you can use a DbTransaction object to add transaction context when using the SqlDataSource control to update data.

<%@Page  Language="C#" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Data.SqlClient" %>
<%@Import Namespace="System.Diagnostics" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

 private void On_Click(Object source, EventArgs e) {    
    SqlDataSource1.Update();
 }

 private void OnSqlUpdating(Object source, SqlDataSourceCommandEventArgs e) {
    DbCommand command = e.Command;
    DbConnection cx  = command.Connection;    
    cx.Open();    
    DbTransaction tx = cx.BeginTransaction();
    command.Transaction = tx;
 }

 private void OnSqlUpdated(Object source, SqlDataSourceStatusEventArgs e) {
    DbCommand command = e.Command;
    DbTransaction tx = command.Transaction;
    
    // In this code example the OtherProcessSucceeded variable represents
    // the outcome of some other process that occurs whenever the data is 
    // updated, and must succeed for the data change to be committed. For 
    // simplicity, we set this value to true. 
    bool OtherProcessSucceeded = true;
    
    if (OtherProcessSucceeded) {
        tx.Commit();
        Label2.Text="The record was updated successfully!";
    }
    else {
        tx.Rollback();
        Label2.Text="The record was not updated.";
    }
 }

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees"
          UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID"
          OnUpdating="OnSqlUpdating"
          OnUpdated ="OnSqlUpdated">
          <UpdateParameters>
              <asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/>
              <asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/>
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="LastName"
          DataValueField="EmployeeID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br />
      <asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user."
        AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />

    </form>
  </body>
</html>
<%@Page  Language="VB" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Diagnostics" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

 Sub On_Click(ByVal source As Object, ByVal e As EventArgs)
        SqlDataSource1.Update()
 End Sub 'On_Click

 Sub On_Sql_Updating(ByVal source As Object, ByVal e As SqlDataSourceCommandEventArgs)
     Dim command as DbCommand
     Dim connection as DbConnection
     Dim transaction as DbTransaction
     
     command    = e.Command
     connection = command.Connection     
     connection.Open()     
     transaction = connection.BeginTransaction()
     command.Transaction = transaction
 
 End Sub 'On_Sql_Updating
 
 Sub On_Sql_Updated(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs)
 
    Dim command As DbCommand
    Dim transaction As DbTransaction
    
    command = e.Command
    transaction = command.Transaction
    
    ' In this code example the OtherProcessSucceeded variable represents
    ' the outcome of some other process that occurs whenever the data is 
    ' updated, and must succeed for the data change to be committed. For 
    ' simplicity, we set this value to true. 
    Dim OtherProcessSucceeded as Boolean = True
    
    If (OtherProcessSucceeded) Then
        transaction.Commit()
        Label2.Text="The record was updated successfully!"
    Else    
        transaction.Rollback()
        Label2.Text="The record was not updated."
    End If
 End Sub ' On_Sql_Updated
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees"
          UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID"
          OnUpdating="On_Sql_Updating"
          OnUpdated ="On_Sql_Updated">
          <UpdateParameters>
              <asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/>
              <asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/>
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="LastName"
          DataValueField="EmployeeID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br />
      <asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user."
        AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />

    </form>
  </body>
</html>

注解

如果数据已更改GridView, 则、 DetailsViewFormView控件会自动调用方法。UpdateThe Update method is automatically called by the GridView, DetailsView, and FormView controls during postback if the data has been changed. 对于在其他控件中已更改的数据, 可以UpdateLoad事件期间的回发中显式调用方法。For data that has been changed in other controls, the Update method can be explicitly called on postback during the Load event.

在执行OnUpdating操作之前, 将调用方法来引发Updating事件。 UpdateBefore the Update operation is performed, the OnUpdating method is called to raise the Updating event. 你可以处理此事件以检查参数的值并在Update操作之前执行任何预处理。You can handle this event to examine the values of the parameters and to perform any preprocessing before an Update operation.

操作完成后OnUpdated , 调用方法来引发Updated事件。 UpdateAfter the Update operation completes, the OnUpdated method is called to raise the Updated event. 可以处理此事件以检查任何返回值和错误代码, 并执行任何后续处理。You can handle this event to examine any return values and error codes and to perform any post-processing.

方法委托SqlDataSourceView Update给与SqlDataSource控件相关联的对象的方法。 UpdateThe Update method delegates to the Update method of the SqlDataSourceView object that is associated with the SqlDataSource control. 若要执行更新操作, 请SqlDataSourceView使用UpdateCommand文本DbCommandDbCommand任何关联UpdateParameters的属性生成对象, 然后对基础数据库执行对象。To perform an update operation, the SqlDataSourceView builds a DbCommand object using the UpdateCommand text and any associated UpdateParameters properties, and then executes the DbCommand object against the underlying database.

重要

无需验证即可将值插入到参数中, 这是一个潜在的安全威胁。Values are inserted into parameters without validation, which is a potential security threat. Updating使用事件在执行查询之前验证参数值。Use the Updating event to validate parameter values before executing the query. 有关详细信息,请参阅脚本侵入概述For more information, see Script Exploits Overview.

适用于

另请参阅