1,事件
SQLDatasource控件有执行Insert,Update,Delete动作的前后的事件,在这些事件中,
我们可以填写自己的代码。而且,在这些事件中,能利用out类型的参数,取得我们想要的数据。
2,参数
SQLDatasource控件的数据源中的各列,是调用的存储过程的入参。其出参须另行定义。可
以在执行调用存储过程的命令之前定义,然后在执行命令之后取得返回值。
下面的例子演示了给SQLDatasource数据控件定义输出参数,并在SQLDatasource执行Insert命
令后取得返回值:
<%@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">
//执行Insert之前
//定义输出参数(入参是数据源的各列)
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);//给数据控件添加参数
}
//执行Insert之后
//通过输出参数,取得执行结果
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 >
<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>
SQLDatasource控件有执行Insert,Update,Delete动作的前后的事件,在这些事件中,
我们可以填写自己的代码。而且,在这些事件中,能利用out类型的参数,取得我们想要的数据。
2,参数
SQLDatasource控件的数据源中的各列,是调用的存储过程的入参。其出参须另行定义。可
以在执行调用存储过程的命令之前定义,然后在执行命令之后取得返回值。
下面的例子演示了给SQLDatasource数据控件定义输出参数,并在SQLDatasource执行Insert命
令后取得返回值:
<%@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">
//执行Insert之前
//定义输出参数(入参是数据源的各列)
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);//给数据控件添加参数
}
//执行Insert之后
//通过输出参数,取得执行结果
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 >
<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>