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

数据库在的存储过程为：

 

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

Dal层代码为：

 

 

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

 

BLL层代码：

 

BLL层中公共使用：

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

      AccountDal acouDal = new AccountDal();

 

  /// <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

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