SqlDataSource.InsertCommand 属性
定义
获取或设置 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. GridView和 DetailsView 控件使用不同的数据源控件; 与关联的控件 DetailsView 具有 FilterExpression 和 FilterParameters 属性,这确保显示了的选定行 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 InsertParameters 使用绑定列中的值自动填充集合,并调用 Insert 方法。If you insert a record, the DetailsView automatically populates the InsertParameters collection with values from the bound columns and calls the Insert method. DetailsView BoundField TemplateField 当使用 ASP.NET 双向数据绑定语法时,可以从任何对象推断正确的参数,并从对象中推断出参数。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>
注解
InsertCommand表示 SQL 查询或存储过程的名称,并由 Insert 方法使用。The 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.Odbc 或,则 System.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.
此属性委托给 InsertCommand SqlDataSourceView 与控件关联的的属性 SqlDataSource 。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.