关于使用有返回参数的存储过程

数据库在的存储过程为：

 

存储过程

1 USE [UserService]
2 GO
3   /* ***** Object: StoredProcedure [dbo].[UserService_Account_GetAllAccounts] Script Date: 11/10/2010 17:09:26 ***** */
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER PROC [dbo].[UserService_Account_GetAllAccounts]
9 @totalcount int output,
10 @totalpage int output,
11 @pageIndex int ,
12 @pageSize int
13 AS
14 declare @count int
15   set @count = 1
16
17 -- 表明
18 declare @tablename nvarchar( 100 )
19 set @tablename = ' User_Account '
20
21 -- 要查询的字段
22 declare @fieldlist nvarchar( 100 )
23 set @fieldlist = ' ID,Name,[Password],PwdUpdateTime,LastLoginTime,CreateTime '
24
25 -- 要查询条件
26 declare @Condition varchar( 50 )
27 set @Condition = ' 1=1 '
28 -- 排序条件
29 declare @Sort varchar( 50 )
30 set @Sort = ' ID Asc '
31 -- 执行分页存储过程
32 if (@count = 1 )
33 exec sp_UserService_ShowOnePage @tablename,@fieldlist,@Condition,@Sort,@pageIndex,@pageSize,@totalcount output,@totalpage output
34 set @count = @count + 1
35

Dal层代码为：

 

 

DAL层代码

1 /// <summary>
2   2 /// 获取所有帐户信息
3   3 /// </summary>
4   4 /// <param name="totalcount"> 记录总条数 </param>
5   5 /// <param name="totalpage"> 总页数 </param>
6   6 /// <param name="pageIndex"> 页面标签 </param>
7   7 /// <param name="pageSize"></param>
8   8 /// <returns></returns>
9   9 public List < AccountInfo > UserService_Account_GetAllAccounts( ref int totalcount, ref int totalpage, int pageIndex, int pageSize)
10   10 {
11   11 List < AccountInfo > listAccountInfo = new List < AccountInfo > ();
12   12
13   13 SqlParameter[] parmt = {
14 14 new SqlParameter( " @totalcount " ,SqlDbType.Int) ,
15 15 new SqlParameter( " @totalpage " , SqlDbType.Int),
16 16 new SqlParameter( " @pageIndex " ,SqlDbType.Int),
17 17 new SqlParameter( " @pageSize " , SqlDbType.Int)
18 18
19 19 };
20 20 parmt[ 0 ].Direction = ParameterDirection.Output;
21 21 parmt[ 1 ].Direction = ParameterDirection.Output;
22 22 parmt[ 2 ].Value = pageIndex;
23 23 parmt[ 3 ].Value = pageSize;
24 24
25 25 SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringUserService, CommandType.StoredProcedure, " UserService_Account_GetAllAccounts " , parmt);
26 26
27 27
28 28 while (dr.Read())
29 29 {
30 30 AccountInfo acInfo = new AccountInfo();
31 31 acInfo.ID = dr[ " ID " ].ToString();
32 32 acInfo.Name = dr[ " Name " ].ToString();
33 33 acInfo.Password = dr[ " Password " ].ToString();
34 34 acInfo.PwdUpdateTime = Convert.ToDateTime(dr[ " PwdUpdateTime " ]);
35 35 acInfo.LastLoginTime = Convert.ToDateTime(dr[ " LastLoginTime " ]);
36 36 acInfo.CreateTime = Convert.ToDateTime(dr[ " CreateTime " ]);
37 37 listAccountInfo.Add(acInfo);
38 38 }
39 39 dr.Close();
40 40 totalcount = Convert.ToInt32(parmt[ 0 ].Value);
41 41 totalpage = Convert.ToInt32(parmt[ 1 ].Value);
42 42
43 43 return listAccountInfo;
44 44 }

 

BLL层代码：

 

BLL层中公共使用：

  List<AccountInfo> acoList = new List<AccountInfo>();

      AccountDal acouDal = new AccountDal();

 

BLL层代码

/// <summary>
/// 获取帐户信息
/// </summary>
/// <param name="totalcount"> 总行数 </param>
/// <param name="totalpage"> 页数 </param>
/// <param name="pageIndex"> 页码 </param>
/// <param name="pageSize"> 每页显示记录数 </param>
/// <returns></returns>
public List < AccountInfo > UserService_Account_GetAllAccounts( ref int totalcount, ref int totalpage, int pageIndex, int pageSize)
{

acoList = acouDal.UserService_Account_GetAllAccounts( ref totalcount, ref totalpage, pageIndex, pageSize);
return acoList;
}

使用SQLHelper时注意

 

  //   cmd.Parameters.Clear();

他的清空作用 所以要注释掉 

 

页面代码：

页面实现代码

// 只是为了测试一下 没什么功能的

protected void test_Click( object sender, EventArgs e)
{
AccountBll ab = new AccountBll();

int totalcount = 0 ;
int totalpage = 0 ;
List < AccountInfo > accountList = ab.UserService_Account_GetAllAccounts( ref totalcount, ref totalpage, 0 , 10 );

TestText.Value = totalcount.ToString();
}

 

在DAL中记得一定要关闭读取的流  不然返回为Null ；当时找了好长时间才找出这个错

 

 

USE [UserService]
GO
/****** Object:  StoredProcedure [dbo].[UserService_Account_GetAllAccounts]    Script Date: 11/10/2010 17:09:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[UserService_Account_GetAllAccounts]
@totalcount  int output,
@totalpage int output,
@pageIndex int,
@pageSize int
AS
declare @count int
set @count=1

--表明
declare @tablename  nvarchar(100)
set @tablename='User_Account'

--要查询的字段
declare @fieldlist nvarchar(100)
set @fieldlist='ID,Name,[Password],PwdUpdateTime,LastLoginTime,CreateTime'

--要查询条件
declare @Condition varchar(50)
set @Condition='1=1'
--排序条件
declare @Sort varchar(50)
set @Sort='ID Asc'
--执行分页存储过程
if(@count=1)
exec sp_UserService_ShowOnePage  @tablename,@fieldlist,@Condition,@Sort,@pageIndex,@pageSize,@totalcount output,@totalpage output
set @count=@count+1

存储过程

1 USE [UserService]
2 GO
3 /* ***** Object: StoredProcedure [dbo].[UserService_Account_GetAllAccounts] Script Date: 11/10/2010 17:09:26 ***** */
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER PROC [dbo].[UserService_Account_GetAllAccounts]
9 @totalcount int output,
10 @totalpage int output,
11 @pageIndex int ,
12 @pageSize int
13 AS
14 declare @count int
15 set @count = 1
16
17 -- 表明
18 declare @tablename nvarchar( 100 )
19 set @tablename = ' User_Account '
20
21 -- 要查询的字段
22 declare @fieldlist nvarchar( 100 )
23 set @fieldlist = ' ID,Name,[Password],PwdUpdateTime,LastLoginTime,CreateTime '
24
25 -- 要查询条件
26 declare @Condition varchar( 50 )
27 set @Condition = ' 1=1 '
28 -- 排序条件
29 declare @Sort varchar( 50 )
30 set @Sort = ' ID Asc '
31 -- 执行分页存储过程
32 if (@count = 1 )
33 exec sp_UserService_ShowOnePage @tablename,@fieldlist,@Condition,@Sort,@pageIndex,@pageSize,@totalcount output,@totalpage output
34 set @count = @count + 1

 

转载于:https://www.cnblogs.com/zyuhong/archive/2010/11/10/1874072.html