业务逻辑>数据层>SqlDataSource>sql的输入输出参数:
输入参数包括,控件参数,窗体参数,用户设置文件参数,查询字符串参数,会话参数,Cookie参数,后台代码声明参数。
输出参数包括,存储过程声明的输出参数,sql语句声明的输出参数。
参数类型。
业务逻辑>数据层>SqlDataSource>sql的输入输出参数>存储过程 输出参数:
改编自《ASP.NET3.5 开发范例精讲精析 基于c#》
首先是表结构
存储过程的代码
1
ALTER
PROCEDURE
dbo.Demo10simple
2 /*
3 (
4 @parameter1 int = 5,
5 @parameter2 datatype OUTPUT
6 )
7 */
8 @average money output,
9 @maximum money output,
10 @minimum money output
11 AS
12 /* SET NOCOUNT ON */
13 select @average = avg (当前薪资), @maximum = max (当前薪资), @minimum = min (当前薪资)
14 from 章立民研究室
15 where 部门 = ' 生产制造部 ' ;
16
17 select 员工号码,姓名,地址,当前薪资,部门
18 from 章立民研究室
19 where 部门 = ' 生产制造部 ' ;
20
21 -- 存储过程的返回值
22 RETURN @@rowcount ;
23
2 /*
3 (
4 @parameter1 int = 5,
5 @parameter2 datatype OUTPUT
6 )
7 */
8 @average money output,
9 @maximum money output,
10 @minimum money output
11 AS
12 /* SET NOCOUNT ON */
13 select @average = avg (当前薪资), @maximum = max (当前薪资), @minimum = min (当前薪资)
14 from 章立民研究室
15 where 部门 = ' 生产制造部 ' ;
16
17 select 员工号码,姓名,地址,当前薪资,部门
18 from 章立民研究室
19 where 部门 = ' 生产制造部 ' ;
20
21 -- 存储过程的返回值
22 RETURN @@rowcount ;
23
你看,@average,@maximum,@minimum 声明为输出参数,然后把sql函数对字段的操作结果赋予这些输出参数。
这里还有个特殊的输出参数,就是存储过程的返回值。
网页前台代码:
1
<%
@ Page Language
=
"
C#
"
AutoEventWireup
=
"
true
"
CodeFile
=
"
Demo10simple.aspx.cs
"
Inherits
=
"
Demo10simple
"
%>
2
3 <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
4
5 < html xmlns ="http://www.w3.org/1999/xhtml" >
6 < head runat ="server" >
7 < title > 存储过程的输出参数与返回值 </ title >
8 </ head >
9 < body >
10 < form id ="form1" runat ="server" >
11 < div >
12
13 < asp:GridView ID ="GridView1" runat ="server" DataSourceID ="SqlDataSource1" >
14 </ asp:GridView >
15 < asp:SqlDataSource ID ="SqlDataSource1" runat ="server"
16 ConnectionString ="<%$ ConnectionStrings:ChtNorthwind %>"
17 SelectCommand ="Demo10simple" SelectCommandType ="StoredProcedure"
18 onselected ="SqlDataSource1_Selected" >
19
20 < SelectParameters >
21 < asp:Parameter Direction ="Output" Name ="average" Type ="Decimal" />
22 < asp:Parameter Direction ="Output" Name ="maximum" Type ="Decimal" />
23 < asp:Parameter Direction ="Output" Name ="minimum" Type ="Decimal" />
24 < asp:Parameter Direction ="ReturnValue" Name ="ret" Type ="Int32" />
25 </ SelectParameters >
26
27 </ asp:SqlDataSource >
28
29 < br />
30 < asp:TextBox ID ="TextBox1" runat ="server" Width ="380px" ></ asp:TextBox >
31 < br />
32 < asp:TextBox ID ="TextBox2" runat ="server" Width ="380px" ></ asp:TextBox >
33 < br />
34 < asp:TextBox ID ="TextBox3" runat ="server" Width ="380px" ></ asp:TextBox >
35 < br />
36 < asp:TextBox ID ="TextBox4" runat ="server" Width ="380px" ></ asp:TextBox >
37 </ div >
38 </ form >
39 </ body >
40 </ html >
41
2
3 <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
4
5 < html xmlns ="http://www.w3.org/1999/xhtml" >
6 < head runat ="server" >
7 < title > 存储过程的输出参数与返回值 </ title >
8 </ head >
9 < body >
10 < form id ="form1" runat ="server" >
11 < div >
12
13 < asp:GridView ID ="GridView1" runat ="server" DataSourceID ="SqlDataSource1" >
14 </ asp:GridView >
15 < asp:SqlDataSource ID ="SqlDataSource1" runat ="server"
16 ConnectionString ="<%$ ConnectionStrings:ChtNorthwind %>"
17 SelectCommand ="Demo10simple" SelectCommandType ="StoredProcedure"
18 onselected ="SqlDataSource1_Selected" >
19
20 < SelectParameters >
21 < asp:Parameter Direction ="Output" Name ="average" Type ="Decimal" />
22 < asp:Parameter Direction ="Output" Name ="maximum" Type ="Decimal" />
23 < asp:Parameter Direction ="Output" Name ="minimum" Type ="Decimal" />
24 < asp:Parameter Direction ="ReturnValue" Name ="ret" Type ="Int32" />
25 </ SelectParameters >
26
27 </ asp:SqlDataSource >
28
29 < br />
30 < asp:TextBox ID ="TextBox1" runat ="server" Width ="380px" ></ asp:TextBox >
31 < br />
32 < asp:TextBox ID ="TextBox2" runat ="server" Width ="380px" ></ asp:TextBox >
33 < br />
34 < asp:TextBox ID ="TextBox3" runat ="server" Width ="380px" ></ asp:TextBox >
35 < br />
36 < asp:TextBox ID ="TextBox4" runat ="server" Width ="380px" ></ asp:TextBox >
37 </ div >
38 </ form >
39 </ body >
40 </ html >
41
SqlDataSource 的查询方法设置成存储过程,声明几个参数为输出参数,名字对应存储过程的输出参数,并且设置好类型。
后台代码:
1
using
System;
2 using System.Collections;
3 using System.Configuration;
4 using System.Data;
5 using System.Linq;
6 using System.Web;
7 using System.Web.Security;
8 using System.Web.UI;
9 using System.Web.UI.HtmlControls;
10 using System.Web.UI.WebControls;
11 using System.Web.UI.WebControls.WebParts;
12 using System.Xml.Linq;
13 using System.Data.SqlClient;
14
15 public partial class Demo10simple : System.Web.UI.Page
16 {
17 protected void Page_Load( object sender, EventArgs e)
18 {
19
20 }
21 protected void SqlDataSource1_Selected( object sender, SqlDataSourceStatusEventArgs e)
22 {
23 IDbCommand cmd = e.Command;
24
25 SqlParameter Return_Param;
26 SqlParameter Average_Param;
27 SqlParameter Maximum_Param;
28 SqlParameter Minimum_Param;
29
30 Return_Param = (SqlParameter)cmd.Parameters[ " @ret " ];
31 Average_Param = (SqlParameter)cmd.Parameters[ " @average " ];
32 Maximum_Param = (SqlParameter)cmd.Parameters[ " @maximum " ];
33 Minimum_Param = (SqlParameter)cmd.Parameters[ " @minimum " ];
34
35 this .TextBox1.Text = " 输出参数名称: " + Average_Param.ParameterName + " ,参数值(平均薪资): " + Average_Param.Value.ToString();
36 this .TextBox2.Text = " 输出参数名称: " + Maximum_Param.ParameterName + " ,参数值(最高薪资): " + Maximum_Param.Value.ToString();
37 this .TextBox3.Text = " 输出参数名称: " + Minimum_Param.ParameterName + " ,参数值(最低薪资): " + Minimum_Param.Value.ToString();
38 this .TextBox4.Text = " 存储过程返回值参数名称: " + Return_Param.ParameterName + " ,参数值(人数): " + Return_Param.Value.ToString();
39 }
40 }
41
2 using System.Collections;
3 using System.Configuration;
4 using System.Data;
5 using System.Linq;
6 using System.Web;
7 using System.Web.Security;
8 using System.Web.UI;
9 using System.Web.UI.HtmlControls;
10 using System.Web.UI.WebControls;
11 using System.Web.UI.WebControls.WebParts;
12 using System.Xml.Linq;
13 using System.Data.SqlClient;
14
15 public partial class Demo10simple : System.Web.UI.Page
16 {
17 protected void Page_Load( object sender, EventArgs e)
18 {
19
20 }
21 protected void SqlDataSource1_Selected( object sender, SqlDataSourceStatusEventArgs e)
22 {
23 IDbCommand cmd = e.Command;
24
25 SqlParameter Return_Param;
26 SqlParameter Average_Param;
27 SqlParameter Maximum_Param;
28 SqlParameter Minimum_Param;
29
30 Return_Param = (SqlParameter)cmd.Parameters[ " @ret " ];
31 Average_Param = (SqlParameter)cmd.Parameters[ " @average " ];
32 Maximum_Param = (SqlParameter)cmd.Parameters[ " @maximum " ];
33 Minimum_Param = (SqlParameter)cmd.Parameters[ " @minimum " ];
34
35 this .TextBox1.Text = " 输出参数名称: " + Average_Param.ParameterName + " ,参数值(平均薪资): " + Average_Param.Value.ToString();
36 this .TextBox2.Text = " 输出参数名称: " + Maximum_Param.ParameterName + " ,参数值(最高薪资): " + Maximum_Param.Value.ToString();
37 this .TextBox3.Text = " 输出参数名称: " + Minimum_Param.ParameterName + " ,参数值(最低薪资): " + Minimum_Param.Value.ToString();
38 this .TextBox4.Text = " 存储过程返回值参数名称: " + Return_Param.ParameterName + " ,参数值(人数): " + Return_Param.Value.ToString();
39 }
40 }
41
在SqlDataSource的selected事件中,来获取输出参数的值,并赋值给页面上其它控件的属性。
输出参数还是很用,有些直接在数据库里的运算结果,比如最大,最小,平均,可以输出到逻辑代码里,然后调用。