c#中调用存储过程返回表的分页数据及总记录个数

方法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);
            }