方法1:
本存储过程是用来分页显示sys.all_objects中的记录,调用本存储过程需要传递两个参数@pageSize是每页的记录数,@pageIndex的页码,存储过程返回两个datetable,第一个datatable是分页的datatable,第2个datatable是存储总记录个数。
if exists(select * from sys.all_objects where type='p' and name='usersp_p4') drop procedure usersp_p4 go create procedure usersp_p4 @pageSize int,@pageIndex int as declare @startRowIndex int,@endRowIndex int,@pageCount int,@rowCount int; set @rowCount=(select COUNT(*) from sys.all_objects); if @pageSize<=0 begin set @pageSize=10 end if @pageIndex<=0 begin set @pageIndex=1 end if @rowCount % @pageSize=0 begin set @pageCount=@rowCount/@pageSize; end else begin set @pageCount=@rowCount/@pageSize+1; end set @startRowIndex=(@pageIndex-1)*@pageSize+1; set @endRowIndex=@pageIndex*@pageSize; select * from (select (ROW_NUMBER() over (order by name)) rownumber, * from sys.all_objects) t where rownumber>=@startRowIndex and rownumber<=@endRowIndex; select @rowCount; go
在c#中调用该存储过程
string strCon = "data source=.;initial catalog=test;user id=sa;password=sa;"; using (SqlConnection conn = new SqlConnection(strCon)) { SqlCommand com = new SqlCommand("usersp_p4", conn); com.CommandType = CommandType.StoredProcedure; SqlParameter[] sqlparas={ new SqlParameter("@pageSize",SqlDbType.Int), new SqlParameter("@pageIndex",SqlDbType.Int) }; sqlparas[0].Value = 10; sqlparas[1].Value = 2; com.Parameters.AddRange(sqlparas); SqlDataAdapter oad = new SqlDataAdapter(com); DataSet ds = new DataSet(); oad.Fill(ds);//ds里面存着了两张表,一张表存储分页的datatable,另一页存储的是用来存储总页数的datatable,用来存储页数的datatable表格中只有一行一列 int recordCount = Convert.ToInt32( ds.Tables[1].Rows[0][0]); }
方法2:基本实现方式和上面是一样的,不同的是存储过程携带三个参数,第三个参数是输出参数,存储过程返回的是一个datatable而不是dataset
if exists(select * from sys.all_objects where type='p' and name='usersp_p5') drop procedure usersp_p5 go create procedure usersp_p5 @pageSize int,@pageIndex int,@rowCount int out as declare @startRowIndex int,@endRowIndex int,@pageCount int; set @rowCount=(select COUNT(*) from sys.all_objects); if @pageSize<=0 begin set @pageSize=10 end if @pageIndex<=0 begin set @pageIndex=1 end if @rowCount % @pageSize=0 begin set @pageCount=@rowCount/@pageSize; end else begin set @pageCount=@rowCount/@pageSize+1; end set @startRowIndex=(@pageIndex-1)*@pageSize+1; set @endRowIndex=@pageIndex*@pageSize; select * from (select (ROW_NUMBER() over (order by name)) rownumber, * from sys.all_objects) t where rownumber>=@startRowIndex and rownumber<=@endRowIndex; go
C#中调用,其中输出参数需要设置该参数的Direction属性为ParameterDirection.Output。执行完存储过程,再读取该参数的value属性就可以获取返回的参数值。
string strCon = "data source=.;initial catalog=test;user id=sa;password=sa;"; using (SqlConnection conn = new SqlConnection(strCon)) { SqlCommand com = new SqlCommand("usersp_p5", conn); com.CommandType = CommandType.StoredProcedure; SqlParameter[] sqlparas={ new SqlParameter("@pageSize",SqlDbType.Int), new SqlParameter("@pageIndex",SqlDbType.Int), new SqlParameter("@rowCount",SqlDbType.Int) }; sqlparas[0].Value = 10; sqlparas[1].Value = 2; sqlparas[2].Direction = ParameterDirection.Output;//这是通过传递输出参数得到记录数 com.Parameters.AddRange(sqlparas); SqlDataAdapter oad = new SqlDataAdapter(com); DataTable dt = new DataTable(); oad.Fill(dt);//结果只有一张表 int recordCount =Convert.ToInt32( sqlparas[2].Value); }