SqlDataSource.InsertCommand SqlDataSource.InsertCommand SqlDataSource.InsertCommand SqlDataSource.InsertCommand Property

定义

获取或设置 SqlDataSource 控件将数据插入基础数据库所用的 SQL 字符串。Gets or sets the SQL string that the SqlDataSource control uses to insert data into the underlying database.

public:
 property System::String ^ InsertCommand { System::String ^ get(); void set(System::String ^ value); };
public string InsertCommand { get; set; }
member this.InsertCommand : string with get, set
Public Property InsertCommand As String

属性值

SqlDataSource 插入数据所用的 SQL 字符串。An SQL string that the SqlDataSource uses to insert data.

示例

本部分包含两个代码示例。This section contains two code examples. 第一个代码示例演示如何使用SqlDataSource控件和简单的 Web 窗体页将数据插入到数据库中。The first code example demonstrates how to insert data into a database using the SqlDataSource control and a simple Web Forms page. 第二个代码示例演示如何从 Microsoft SQL Server 检索数据并在GridView控件中显示数据, 以及如何DetailsView使用控件查看GridView控件中选定行的详细信息, 以及如何使用窗体来插入新记录。The second code example demonstrates how to retrieve data from Microsoft SQL Server and display it in a GridView control and how to use a DetailsView control to see details of a selected row into the GridView control and as a form to insert new records.

备注

这些示例演示如何使用声明性语法进行数据访问。These examples show how to use declarative syntax for data access. 有关如何使用代码而不是标记访问数据的信息, 请参阅在 Visual Studio 中访问数据For information about how to access data by using code instead of markup, see Accessing data in Visual Studio.

下面的代码示例演示如何使用SqlDataSource控件和简单的 Web 窗体页将数据插入到数据库中。The following code example demonstrates how to insert data into a database using the SqlDataSource control and a simple Web Forms page. 数据表中的当前数据显示在DropDownList控件中。The current data in the Data table is displayed in the DropDownList control. 您可以通过在TextBox控件中输入值, 然后单击 "插入" 按钮来添加新记录。You can add new records by entering values into the TextBox controls, and then clicking the Insert button. 单击 "插入" 按钮时, 会将指定的值插入到数据库中, 并DropDownList刷新控件。When the Insert button is clicked, the specified values are inserted into the database, and the DropDownList control is refreshed.

重要

此示例包括一个文本框, 该文本框接受用户输入 (这是一个潜在的安全威胁), 而将值插入到无验证的参数中, 这也是一个潜在的安全威胁。This example includes a text box that accepts user input, which is a potential security threat, and values are inserted into parameters without validation, which is also a potential security threat. Inserting使用事件在执行查询之前验证参数值。Use the Inserting event to validate parameter values before executing the query. 有关详细信息,请参阅脚本侵入概述For more information, see Script Exploits Overview.

<%@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 InsertShipper (object source, EventArgs e) {
  SqlDataSource1.Insert();
}
</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:dropdownlist
        id="DropDownList1"
        runat="server"
        datasourceid="SqlDataSource1"
        datatextfield="CompanyName"
        datavaluefield="ShipperID" />

<!-- Security Note: The SqlDataSource uses a FormParameter,
     Security Note: which does not perform validation of input from the client.
     Security Note: To validate the value of the FormParameter, handle the Inserting event. -->

      <asp:sqldatasource
        id="SqlDataSource1"
        runat="server"
        connectionstring="<%$ ConnectionStrings:MyNorthwind %>"
        selectcommand="SELECT CompanyName,ShipperID FROM Shippers"
        insertcommand="INSERT INTO Shippers (CompanyName,Phone) VALUES (@CoName,@Phone)">
          <insertparameters>
            <asp:formparameter name="CoName" formfield="CompanyNameBox" />
            <asp:formparameter name="Phone"  formfield="PhoneBox" />
          </insertparameters>
      </asp:sqldatasource>

      <br /><asp:textbox
           id="CompanyNameBox"
           runat="server" />

      <asp:RequiredFieldValidator
        id="RequiredFieldValidator1"
        runat="server"
        ControlToValidate="CompanyNameBox"
        Display="Static"
        ErrorMessage="Please enter a company name." />

      <br /><asp:textbox
           id="PhoneBox"
           runat="server" />

      <asp:RequiredFieldValidator
        id="RequiredFieldValidator2"
        runat="server"
        ControlToValidate="PhoneBox"
        Display="Static"
        ErrorMessage="Please enter a phone number." />

      <br /><asp:button
           id="Button1"
           runat="server"
           text="Insert New Shipper"
           onclick="InsertShipper" />

    </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">
Private Sub InsertShipper (ByVal Source As Object, ByVal e As EventArgs)
  SqlDataSource1.Insert()
End Sub ' InsertShipper
</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:dropdownlist
        id="DropDownList1"
        runat="server"
        datasourceid="SqlDataSource1"
        datatextfield="CompanyName"
        datavaluefield="ShipperID" />

<!-- Security Note: The SqlDataSource uses a FormParameter,
     Security Note: which does not perform validation of input from the client.
     Security Note: To validate the value of the FormParameter, handle the Inserting event. -->

      <asp:sqldatasource
        id="SqlDataSource1"
        runat="server"
        connectionstring="<%$ ConnectionStrings:MyNorthwind %>"
        selectcommand="SELECT CompanyName,ShipperID FROM Shippers"
        insertcommand="INSERT INTO Shippers (CompanyName,Phone) VALUES (@CoName,@Phone)">
          <insertparameters>
            <asp:formparameter name="CoName" formfield="CompanyNameBox" />
            <asp:formparameter name="Phone"  formfield="PhoneBox" />
          </insertparameters>
      </asp:sqldatasource>

      <br /><asp:textbox
           id="CompanyNameBox"
           runat="server" />

      <asp:RequiredFieldValidator
        id="RequiredFieldValidator1"
        runat="server"
        ControlToValidate="CompanyNameBox"
        Display="Static"
        ErrorMessage="Please enter a company name." />

      <br /><asp:textbox
           id="PhoneBox"
           runat="server" />

      <asp:RequiredFieldValidator
        id="RequiredFieldValidator2"
        runat="server"
        ControlToValidate="PhoneBox"
        Display="Static"
        ErrorMessage="Please enter a phone number." />

      <br /><asp:button
           id="Button1"
           runat="server"
           text="Insert New Shipper"
           onclick="InsertShipper" />

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

下面的代码示例演示如何从 SQL Server 检索数据并在GridView控件中显示数据, 以及如何DetailsView使用控件查看GridView控件中选定行的详细信息, 以及如何使用窗体来插入新记录。The following code example demonstrates how to retrieve data from SQL Server and display it in a GridView control and how to use a DetailsView control to see details of a selected row in the GridView control and as a form to insert new records.

最初, 数据显示在GridView控件中, 所选行GridView还显示在DetailsView控件中。Initially, the data is displayed in the GridView control, and the selected row of the GridView is also displayed in the DetailsView control. FilterExpression DetailsView FilterParameters GridView和控件使用不同的数据源控件; 与关联的控件具有和属性, 这可确保的选定行为DetailsView GridView会.The GridView and DetailsView controls use different data source controls; the one that is associated with the DetailsView has the FilterExpression and FilterParameters properties, which ensures that the selected row of the GridView is displayed.

如果单击DetailsView控件的自动生成的插入按钮, 则DetailsView会显示一个不同的用户界面, 用于插入新记录。If you click the automatically generated Insert button of the DetailsView control, the DetailsView shows a different user interface, which is used to insert a new record. 该示例使用存储过程插入记录, 并返回插入行的主键。The example uses a stored procedure to insert records and returns the primary key of the inserted row. 如果插入记录, 将DetailsView使用Insert绑定列中的InsertParameters值自动填充集合, 并调用方法。If you insert a record, the DetailsView automatically populates the InsertParameters collection with values from the bound columns and calls the Insert method. DetailsView使用 ASP.NET 双向数据绑定语法时BoundField , 可以从任何对象推断TemplateField正确的参数, 并从对象中推断出参数。The DetailsView can infer the correct parameters from any BoundField object and a parameter for the TemplateField object when the ASP.NET two-way data-binding syntax is used. 在此示例中, 会在OnInserting事件处理程序中添加一个附加参数, 用于处理存储过程返回的主键。In this example, an additional parameter is added in the OnInserting event handler to handle the primary key that is returned by the stored procedure.

最后, 在DetailsView控件将数据插入到数据库OnInserted中后, 将调用事件处理程序来处理Inserted事件, 并显示插入行的主键的值以及DataBind GridView显式调用控件来刷新数据。Finally, after data is inserted into the database by the DetailsView control, the OnInserted event handler is called to handle the Inserted event, the value of the primary key of the inserted row is displayed and the DataBind method of the GridView control is called explicitly to refresh the data.

<%@Page  Language="C#" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Data.SqlClient" %>
<!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_Inserting(Object sender, SqlDataSourceCommandEventArgs e) {

    SqlParameter insertedKey = new SqlParameter("@PK_New", SqlDbType.Int);
    insertedKey.Direction    = ParameterDirection.Output;        
    e.Command.Parameters.Add(insertedKey);
 }

 private void On_Inserted(Object sender, SqlDataSourceStatusEventArgs e) {
    DbCommand command = e.Command;    
    
    // The label displays the primary key of the recently inserted row.
    Label1.Text = command.Parameters["@PK_New"].Value.ToString();
    
    // Force a refresh after the data is inserted.
    GridView1.DataBind();
 }
</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:GridView
        id="GridView1"
        runat="server"
        AutoGenerateColumns="False"
        DataKeyNames="EmployeeID"        
        DataSourceID="SqlDataSource1">
        <columns>          
          <asp:BoundField HeaderText="First Name" DataField="FirstName" />
          <asp:BoundField HeaderText="Last Name" DataField="LastName" />
          <asp:BoundField HeaderText="Title" DataField="Title" />
          <asp:ButtonField ButtonType="Link" CommandName="Select" Text="Details..." />
        </columns>
      </asp:GridView>

      <asp:SqlDataSource
        id="SqlDataSource1"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyNorthwind %>"
        SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees">
      </asp:SqlDataSource>

      <hr />

      <asp:DetailsView
        id="DetailsView1"
        runat="server"
        DataSourceID="SqlDataSource2"
        AutoGenerateRows="False"
        AutoGenerateInsertButton="True">
        <fields>
          <asp:BoundField HeaderText="First Name" DataField="FirstName" ReadOnly="False"/>
          <asp:BoundField HeaderText="Last Name" DataField="LastName" ReadOnly="False"/>
          <asp:TemplateField HeaderText="Title">
            <ItemTemplate>
              <asp:DropDownList
                id="TitleDropDownList"
                runat="server"
                selectedvalue="<%# Bind('Title') %>" >
                <asp:ListItem Selected="True">Sales Representative</asp:ListItem>
                <asp:ListItem>Sales Manager</asp:ListItem>
                <asp:ListItem>Vice President, Sales</asp:ListItem>
              </asp:DropDownList>
            </ItemTemplate>
          </asp:TemplateField>
          <asp:BoundField HeaderText="Notes" DataField="Notes" ReadOnly="False"/>
        </fields>
      </asp:DetailsView>


      <asp:SqlDataSource
        id="SqlDataSource2"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
        SelectCommand="SELECT * FROM Employees"
        InsertCommandType = "StoredProcedure"
        InsertCommand="sp_insertemployee"        
        OnInserting="On_Inserting"
        OnInserted ="On_Inserted"
        FilterExpression="EmployeeID={0}">
        <FilterParameters>
          <asp:ControlParameter Name="EmployeeID" ControlId="GridView1" PropertyName="SelectedValue" />
        </FilterParameters>
      </asp:SqlDataSource>

<!-- 
     -- An example sp_insertemployee stored procedure that returns
     -- the primary key of the row that was inserted in an OUT parameter.
     CREATE PROCEDURE sp_insertemployee 
        @FirstName nvarchar(10), 
        @LastName nvarchar(20) , 
        @Title nvarchar(30), 
        @Notes nvarchar(200), 
        @PK_New int OUTPUT
      AS
        INSERT INTO Employees(FirstName,LastName,Title,Notes)VALUES (@FirstName,@LastName,@Title,@Notes)
        SELECT @PK_New = @@IDENTITY
        RETURN (1)    
      GO
-->      

      <asp:Label 
        id="Label1"
        runat="server" />
      
    </form>
  </body>
</html>
<%@Page  Language="VB" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Data.SqlClient" %>
<!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_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)

    Dim insertedKey As SqlParameter  
    insertedKey = New SqlParameter("@PK_New", SqlDbType.Int)
    insertedKey.Direction    = ParameterDirection.Output     
    
    e.Command.Parameters.Add(insertedKey)

 End Sub 'On_Inserting

 Sub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
    Dim command As DbCommand 
    command = e.Command
    
    ' The label displays the primary key of the recently inserted row.
    Label1.Text = command.Parameters("@PK_New").Value.ToString()    
    
    ' Explicitly call DataBind to refresh the data
    ' and show the newly inserted row.
    GridView1.DataBind()
 End Sub 'On_Inserted

</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:GridView
        id="GridView1"
        runat="server"
        AutoGenerateColumns="False"
        DataKeyNames="EmployeeID"        
        DataSourceID="SqlDataSource1">
        <columns>          
          <asp:BoundField HeaderText="First Name" DataField="FirstName" />
          <asp:BoundField HeaderText="Last Name" DataField="LastName" />
          <asp:BoundField HeaderText="Title" DataField="Title" />
          <asp:ButtonField ButtonType="Link" CommandName="Select" Text="Details..." />
        </columns>
      </asp:GridView>

      <asp:SqlDataSource
        id="SqlDataSource1"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyNorthwind %>"
        SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees">
      </asp:SqlDataSource>

      <hr />

      <asp:DetailsView
        id="DetailsView1"
        runat="server"
        DataSourceID="SqlDataSource2"
        AutoGenerateRows="False"
        AutoGenerateInsertButton="True">
        <fields>
          <asp:BoundField HeaderText="First Name" DataField="FirstName" ReadOnly="False"/>
          <asp:BoundField HeaderText="Last Name" DataField="LastName" ReadOnly="False"/>
          <asp:TemplateField HeaderText="Title">
            <ItemTemplate>
              <asp:DropDownList
                id="TitleDropDownList"
                runat="server"
                selectedvalue="<%# Bind('Title') %>" >
                <asp:ListItem Selected="True">Sales Representative</asp:ListItem>
                <asp:ListItem>Sales Manager</asp:ListItem>
                <asp:ListItem>Vice President, Sales</asp:ListItem>
              </asp:DropDownList>
            </ItemTemplate>
          </asp:TemplateField>
          <asp:BoundField HeaderText="Notes" DataField="Notes" ReadOnly="False"/>
        </fields>
      </asp:DetailsView>


      <asp:SqlDataSource
        id="SqlDataSource2"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
        SelectCommand="SELECT * FROM Employees"
        InsertCommandType = "StoredProcedure"
        InsertCommand="sp_insertemployee"        
        OnInserting="On_Inserting"
        OnInserted ="On_Inserted"
        FilterExpression="EmployeeID={0}">
        <FilterParameters>
          <asp:ControlParameter Name="EmployeeID" ControlId="GridView1" PropertyName="SelectedValue" />
        </FilterParameters>
      </asp:SqlDataSource>

<!-- 
     -- An example sp_insertemployee stored procedure that returns
     -- the primary key of the row that was inserted in an OUT parameter.
     CREATE PROCEDURE sp_insertemployee 
        @FirstName nvarchar(10), 
        @LastName nvarchar(20) , 
        @Title nvarchar(30), 
        @Notes nvarchar(200), 
        @PK_New int OUTPUT
      AS
        INSERT INTO Employees(FirstName,LastName,Title,Notes)VALUES (@FirstName,@LastName,@Title,@Notes)
        SELECT @PK_New = @@IDENTITY
        RETURN (1)    
      GO
-->      

      <asp:Label 
        id="Label1"
        runat="server" />
      
    </form>
  </body>
</html>

注解

表示 SQL 查询或存储过程的名称, 并Insert由方法使用。 InsertCommandThe InsertCommand represents either an SQL query or the name of a stored procedure, and is used by the Insert method.

由于不同的数据库产品使用不同类型的 SQL,因此 SQL 字符串的语法取决于当前正在使用的 ADO.NET 提供程序,该提供程序由 ProviderName 属性标识。Because different database products use different varieties of SQL, the syntax of the SQL string depends on the current ADO.NET provider being used, which is identified by the ProviderName property. 如果 SQL 字符串是一个参数化查询或命令,则参数的占位符还取决于所使用的 ADO.NET 提供程序。If the SQL string is a parameterized query or command, the placeholder of the parameter also depends on the ADO.NET provider being used. 例如, 如果提供程序是System.Data.SqlClient, 它是SqlDataSource类的默认提供程序, 则参数的占位符为'@parameterName'For example, if the provider is the System.Data.SqlClient, which is the default provider for the SqlDataSource class, the placeholder of the parameter is '@parameterName'. 但是, 如果提供程序设置为System.Data.OdbcSystem.Data.OleDb, 则参数的占位符为'?'However, if the provider is set to the System.Data.Odbc or System.Data.OleDb, the placeholder of the parameter is '?'. 有关参数化 SQL 查询和命令的详细信息, 请参阅将参数与 SqlDataSource 控件一起使用For more information about parameterized SQL queries and commands, see Using Parameters with the SqlDataSource Control.

如果InsertCommand数据源支持存储过程, 则可以是 SQL 字符串或存储过程的名称。The InsertCommand can be an SQL string or the name of a stored procedure, if the data source supports stored procedures.

此属性委托给InsertCommandSqlDataSource控件关联的SqlDataSourceView的属性。This property delegates to the InsertCommand property of the SqlDataSourceView that is associated with the SqlDataSource control.

重要

出于安全目的, InsertCommand未存储属性是视图状态。For security purposes, the InsertCommand property is not stored is view state. 由于可以对客户端上的视图状态的内容进行解码, 因此在视图状态中存储有关数据库结构的敏感信息可能会导致信息泄露漏洞。Because it is possible to decode the contents of view state on the client, storing sensitive information about the database structure in view state could result in an information disclosure vulnerability.

重要

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

适用于

另请参阅