在网站开发中进行分页数据查询

打了很多字然后浏览器崩了，没保存，心态大崩。。。

-------------------------------正文开始分界线--------------------------------

1，LINQ

LINQ在c#3.0版本被加入，即便是你不用EF作为数据源，LINQ照样是.NET程序员绕不开的语法糖。

在简单的查询中，我们可以用Linq中的SKIP/TAKE ，SKIP就是从数据源的第几条数据开始，TAKE就是查询的数据数量，所以在我们的日常的开发中，只要在linq语句的最后调用即可。

但是我们平时用的泛型LIST<T>并不是继承Iqueryable，所以进行扩展。后再使用会显得更加优雅。

-----------------------扩展list<T>的Iqueryable方法----------------------

   /// <summary>
    /// list扩展
    /// </summary>
    public static class ListExtensions
    {
        public static List<T> Where<T>(this List<T> list, Expression<Func<T,bool>> exp) {

           var rult = list.AsQueryable().Where<T>(exp).ToList();

            return rult;
        }

        public static List<T> Skip<T>(this List<T> list, int num)
        {

            var rult = list.AsQueryable().Skip<T>(num).ToList();

            return rult;
        }
        public static List<T> SkipWhile<T>(this List<T> list, Expression<Func<T, bool>> exp)
        {

            var rult = list.AsQueryable().SkipWhile<T>(exp).ToList();

            return rult;
        }

        public static List<T> Take<T>(this List<T> list, int num)
        {

            var rult = list.AsQueryable().Take<T>(num).ToList();

            return rult;
        }
        public static List<T> TakeWhile<T>(this List<T> list, Expression<Func<T, bool>> exp)
        {

            var rult = list.AsQueryable().TakeWhile<T>(exp).ToList();

            return rult;
        }

        public static List<T> OrderBy<T>(this List<T> list,string propName,bool isDesc =true) where T:class
        {
            if (!string.IsNullOrWhiteSpace(propName)) {
                var rult = isDesc? list.AsQueryable().OrderByDescending(propName).ToList() : list.AsQueryable().OrderBy(propName).ToList();
                return rult;
            }
            throw new Exception("It must has a property name for sort");

        
        }
    }

 --------------------对OrderBy<T>的扩展------------------------------

因为在Iqueryable接口中，orderby只接受lambda表达式，在我的方法中没办法使用，因为我们不知道泛型T内的属性，所以用以下扩展。

 public static class IqueryableExtensions
    {
        #region orderby 扩展


        public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> query, string propertyName)
        {
            return _OrderBy<T>(query, propertyName, false);
        }
        public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> query, string propertyName)
        {
            return _OrderBy<T>(query, propertyName, true);
        }

        static IOrderedQueryable<T> _OrderBy<T>(IQueryable<T> query, string propertyName, bool isDesc)
        {
            string methodname = (isDesc) ? "OrderByDescendingInternal" : "OrderByInternal";

            var memberProp = typeof(T).GetProperty(propertyName);

            var method = typeof(ListExtensions).GetMethod(methodname).MakeGenericMethod(typeof(T), memberProp.PropertyType);

            return (IOrderedQueryable<T>)method.Invoke(null, new object[] { query, memberProp });
        }

        public static IOrderedQueryable<T> OrderByInternal<T, TProp>(IQueryable<T> query, PropertyInfo memberProperty)
        {
            return query.OrderBy(_GetLamba<T, TProp>(memberProperty));
        }

        public static IOrderedQueryable<T> OrderByDescendingInternal<T, TProp>(IQueryable<T> query, PropertyInfo memberProperty)
        {
            return query.OrderByDescending(_GetLamba<T, TProp>(memberProperty));
        }

        static Expression<Func<T, TProp>> _GetLamba<T, TProp>(PropertyInfo memberProperty)
        {
            if (memberProperty.PropertyType != typeof(TProp)) throw new Exception();

            var thisArg = Expression.Parameter(typeof(T));
            var lamba = Expression.Lambda<Func<T, TProp>>(Expression.Property(thisArg, memberProperty), thisArg);

            return lamba;
        }


        #endregion
    }

 --------------------最后分页方法的实现--------------------------------

    /// <summary>
    /// list 分页
    /// </summary>
   public static class PaginationHelpr
    {
        public static List<T> Pagination<T>(this List<T> source, int start, int size) 
        {
            return source.Skip(start).Take(size);
        }
        public static List<T> Pagination<T>(this List<T> source,int start,int size,string sort,bool isDesc =false)where T:class
        {
            if (string.IsNullOrWhiteSpace(sort))
            {
                return source.Skip(start).Take(size);
            }
            else {
                return source.OrderBy(sort, isDesc).Skip(start).Take(size);
            } 
        }
    }

-------------------测试一下代码-------------------------------------------

    class Program
    {
        static void Main(string[] args)
        {
            var list = new List<person>();

            for(int i=0;i<5000;i++) {
                list.Add(new person() { age = i, id = i, name="jon" });
            }

            var datas = list.Pagination(1,20,"id",true);
            foreach (var i in datas)
            {
                Console.WriteLine($"{i.id}");
            }

            Console.Read();
        }
    }
    public class person {
        public int id  {get;set;}

        public string name { get; set; }

        public int age { get; set; }
    }

2，SQL

 在存储过程中实现分页

当然我需要为这个查询条件准备好，表名称，where条件，orderby排序，当然有心的同学还可以自己扩展上更多的骚操作，Groupby啊之类的拓展。但是谨防被SQL注入啊。

--------------------分页代码----------------------------------------------

USE [V8_Materiel]
GO

/****** Object:  StoredProcedure [dbo].[proc_pageList]    Script Date: 2017/6/24 9:52:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_pageList]
(
@tableName NVARCHAR(500),
@where NVARCHAR(MAX),
@orderBy NVARCHAR(200),
@pageIndex INT,--页索引
@pageSize INT--每页显示数
)
AS 
BEGIN 
SET  NOCOUNT  ON ;
BEGIN TRY 
DECLARE @sql nvarchar(1000)

SELECT  @sql='SELECT * INTO #tempTable_0 FROM  '+@tableName+' WHERE  1=1 AND ';

IF ISNULL(@where,'')<>''
BEGIN
SELECT @sql =@sql + @where
END
ELSE
BEGIN
SELECT @sql =@sql +'1=1'
END  

IF ISNULL(@orderBy,'')=''
BEGIN
SELECT @orderBy = 'id' 
END
 
SELECT @sql =@sql+'

SELECT  * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY @orderBy ) AS rowNum,t.*  FROM #tempTable_0 t
) t
WHERE t.rowNum between ((@pageIndex-1)*@pageSize)  AND ((@pageIndex-1)*@pageSize) +@pageSize
 
SELECT COUNT(*) recordCount FROM #tempTable_0

';
PRINT @sql
EXECUTE sp_executesql @sql,
@parm =N'
@orderBy NVARCHAR(200),
@pageIndex INT,
@pageSize INT',
@orderBy=@orderBy,
@pageIndex=@pageIndex,
@pageSize=@pageSize


END TRY
BEGIN CATCH

SELECT ERROR_NUMBER() AS error_number ,
ERROR_MESSAGE() AS error_message,
ERROR_STATE() AS error_state,
ERROR_SEVERITY() AS error_severity
END CATCH

SET  NOCOUNT  OFF ;
END 
GO

----------------- 存储过程的使用----

USE [xxx]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[proc_pageList]
        @tableName = N'T_Apply',
        @where = N'Code=''MSQ1612140006''',
        @orderBy = NULL,
        @pageIndex = 1,
        @pageSize = 100

SELECT    'Return Value' = @return_value

GO

 

-------------------------------------------------------------------------------------------------------------------------

 

USE [xxx]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[proc_pageList]
        @tableName = N'T_Apply',
        @where = N'id>5',
        @orderBy = NULL,
        @pageIndex = 1,
        @pageSize = 100

SELECT    'Return Value' = @return_value

GO

 

 

-----------------------------------

 

总结就到这里了~~~~