sql server 纯sql语句分页 存储过程 返回结果集及页总数等

经常要对数据库中的数据进行大量的查询，特别是业务后台，但是EF等提供的语句应用起来可能看起来复杂、臃肿，我们需要借助SQL语句、或者存储过程来取得结果

1、.net EF 定义参数

 using (YouEntites db = new YouEntites())
            {
                var pageIndex = new System.Data.SqlClient.SqlParameter
                {
                    ParameterName = "@pageIndex",
                    Value = pageNum
                };
                var pageSize = new System.Data.SqlClient.SqlParameter
                {
                    ParameterName = "@pageSize",
                    Value = rowPerPage
                };

                var userid = new System.Data.SqlClient.SqlParameter
                {
                    ParameterName = "@userid",
                    Value = userId
                };
                var totalRecord = new SqlParameter
                {
                    ParameterName = "@totalRecord",
                    Value = recordCount,
                    Direction = ParameterDirection.Output
                    //Direction = ParameterDirection.ReturnValue;
                };
                var TotalPage = new SqlParameter
                {
                    ParameterName = "@TotalPage",
                    Value = totalPage,
                    Direction = ParameterDirection.Output
                    //Direction = ParameterDirection.ReturnValue;
                };
                var start = new System.Data.SqlClient.SqlParameter
                {
                    ParameterName = "@start",
                    Value = startTime
                };
                var end = new System.Data.SqlClient.SqlParameter
                {
                    ParameterName = "@end",
                    Value = endTime
                };
                System.Data.SqlClient.SqlParameter[] parm = { userid, totalRecord, TotalPage, start, end, pageSize, pageIndex };
                try
                {
                    var results = db.Database.SqlQuery<Cost>("exec Query @userid,@totalRecord  Output, @TotalPage Output,@start,@end,@pageSize,@pageIndex", parm);
                    List<Cost> list = results.ToList();
                    recordCount = Convert.ToInt32(totalRecord.Value);
                    totalPage = Convert.ToInt32(TotalPage.Value);
                    return list;
                }
                catch (Exception ex)
                {
                    Console.Write(ex.Message);
                }

值得注意的是

 var TotalPage = new SqlParameter
                {
                    ParameterName = "@TotalPage",
                    Value = totalPage,
                    Direction = ParameterDirection.Output
                    //Direction = ParameterDirection.ReturnValue;
                };

此参数为引用参数，注意！相当于一个存储过程要传出多个变量（.NET EF code first有个缺陷，不能返回多个返回集，但是可以返回除一个返回查询集以外的多个引用参数；但是DB First ，EF自动生成的模型edmx的时候是可以取得多个查询集的）

2、存储过程：

CREATE PROCEDURE [dbo].[Query]
@userid int, @totalRecord int OUTPUT, @TotalPage int OUTPUT, @start datetime, @end datetime, @pageSize int, @pageIndex int
WITH EXEC AS CALLER
AS
begin
select @totalRecord=count(*) from Cost where Users_ids in(@userid) and BackTime between @start and @end and InSuccess=1 and PaySuccess=1;
--计算总页数
select @TotalPage=CEILING((@totalRecord+0.0)/@pageSize)
--处理页数超出范围情况
    if @pageIndex<=0
        Set @pageIndex = 1;

    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage;

     --处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int;

    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1

select * from( 
select * ,ROW_NUMBER() over(order by BackTime desc) as "No."  from cost where Users_ids in(@userid) --后接其它条件
)  as t  
where t."No." between @StartRecord and @EndRecord  
end
GO

注意分页的处理

3、关于分页的一些引述

常用的分页方式有：

select top 和select not in
select top 和 select max(列键)
select top和中间变量
利用Row_number() 此方法
利用临时表及Row_number
所非本人的测试，其针对较大量的数据其效果表现如下：

测试结果显示：select max >row_number>not in>临时表>中间变量

一般选择row_number方式。

参见：

http://q.cnblogs.com/q/56836/

http://www.cnblogs.com/lli0077/archive/2008/09/03/1282862.html

https://technet.microsoft.com/zh-cn/library/ms187004(v=sql.105).aspx