简化的通用分页代码封装

准备工作：

• 支持DISTINCT的通用分页存储过程（SQL2005）
• 幸运草的EntityBuilder<Entity>（见该文中1楼的回复）
• Enterprise Library

 

在以上基础上，写了个通用的分页辅助类：

/// <summary>
/// 数据库分页操作辅助类
/// </summary>
public sealed class PaginationHelper<Entity>
{
    /// <summary>
    /// 实例化
    /// </summary>
    /// <returns></returns>
    public static PaginationHelper<Entity> Instance()
    {
        return new PaginationHelper<Entity>();
    }

    /// <summary>
    /// 取当前页，使用存储过程p_CommonPagination
    /// </summary>
    /// <param name="Table"></param>
    /// <param name="Fields"></param>
    /// <param name="Where"></param>
    /// <param name="Order"></param>
    /// <param name="PageIndex"></param>
    /// <param name="PageSize"></param>
    /// <param name="UseDistinct"></param>
    /// <param name="Action">0表示返回查询结果和总数，1表示只返回查询结果，2表示只返回总数</param>
    /// <param name="TotalCount"></param>
    /// <returns>List&gt;Entity&lt;</returns>
    public List<Entity> GetCurrentPage4List(string Table, string Fields, string Where, string Order,
        int PageIndex, int PageSize, bool UseDistinct, int Action, out int TotalCount)
    {
        List<Entity> list = new List<Entity>();

        Database db = DatabaseFactory.CreateDatabase();
        DbCommand cmd = db.GetStoredProcCommand("p_CommonPagination");

        db.AddInParameter(cmd, "@Fields", DbType.String, Fields);
        db.AddInParameter(cmd, "@Table", DbType.String, Table);
        db.AddInParameter(cmd, "@Where", DbType.String, Where);
        db.AddInParameter(cmd, "@Order", DbType.String, Order);
        db.AddInParameter(cmd, "@PageIndex", DbType.Int32, PageIndex);
        db.AddInParameter(cmd, "@PageSize", DbType.Int32, PageSize);
        db.AddInParameter(cmd, "@UseDistinct", DbType.Boolean, UseDistinct);
        db.AddInParameter(cmd, "@Action", DbType.Int32, Action);
        db.AddOutParameter(cmd, "@TotalCount", DbType.Int32, 4);

        using (IDataReader dr = db.ExecuteReader(cmd))
        {
            while (dr.Read())
            {
                Entity entity = EntityBuilder<Entity>.CreateBuilder(dr).Build(dr);
                list.Add(entity);
            }
        }

        object result = db.GetParameterValue(cmd, "@TotalCount");
        if (result == null)
            TotalCount = 0;
        else
            int.TryParse(result.ToString(), out TotalCount);

        return list;
    }
}

 

调用方法，以使用CodeSmith的代码模板生成的DAL代码为例：

/// <summary>
/// 获取当页User
/// </summary>
/// <param name="where"></param>
/// <param name="orderBy"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns>List&gt;User&lt;</returns>
public static List<User> GetPagedUser(string where, string orderBy, int pageIndex, int pageSize, out int total)
{
    return PaginationHelper<User>.Instance()
        .GetCurrentPage4List("[dbo].[Ts_User]", "*", where, orderBy, pageIndex, pageSize, false, 0, out total);
}

 

这样，在实现具体的分页功能时，代码就很简洁了。