C#分页面向对象封装-教学示范版--分页实现

2016-09-09 w738990729

 分页相关SQL常量:

view plaincopy to clipboardprint?using System; 
using System.Collections.Generic; 
using System.Text; 
using System.Data; 
 
 
namespace BookShopSqlServerDAL 

    public partial class BaseService 
    { 
        //总条数  
        private const String _SQL_ROWCOUNT = "SELECT COUNT(*) FROM {TABLE_NAME} as model WHERE {CONDITION}"; 
 
        //计算分页结果  
        private const String _SQL_PAGER = "SELECT TOP {PAGE_SIZE} * FROM {TABLE_NAME} as model  " + 
                                          " WHERE {PKEY} NOT IN " + 
                                          "(SELECT TOP {PASS_OUT} {PKEY} FROM {TABLE_NAME}  as model WHERE {CONDITION} {ORDER_LIST})" + 
                                          " AND {CONDITION} {ORDER_LIST}"; 
    } 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;


namespace BookShopSqlServerDAL
{
    public partial class BaseService
    {
        //总条数
        private const String _SQL_ROWCOUNT = "SELECT COUNT(*) FROM {TABLE_NAME} as model WHERE {CONDITION}";

        //计算分页结果
        private const String _SQL_PAGER = "SELECT TOP {PAGE_SIZE} * FROM {TABLE_NAME} as model  " +
                                          " WHERE {PKEY} NOT IN " +
                                          "(SELECT TOP {PASS_OUT} {PKEY} FROM {TABLE_NAME}  as model WHERE {CONDITION} {ORDER_LIST})" +
                                          " AND {CONDITION} {ORDER_LIST}";
    }
}
 

分页实现:

view plaincopy to clipboardprint?using System; 
using System.Collections.Generic; 
using System.Text; 
using System.Data; 
using BookShopModel; 
using System.Data.SqlClient; 
using BookShopSqlServerDAL; 
 
 
namespace BookShopSqlServerDAL 

    public partial class BaseService 
    { 
        /// <summary>  
        /// 分页方法  
        /// </summary>  
        /// <param name="pi"></param>  
        public virtual void Pager(PageInfo pi) 
        { 
            if (String.IsNullOrEmpty(pi.TableName) || 
                String.IsNullOrEmpty(pi.Pkey)) 
            { 
                return; 
            } 
 
            //表名特殊处理  
            if (pi.TableName.ToUpper().Contains("FROM "))//如果包含FROM说明是一个子查询  
            { 
                pi.TableName = " (" + pi.TableName + ") ";//如果是子查询则添加一对圆括号  
            } 
 
 
            #region 处理条件  
 
            StringBuilder _conditions = new StringBuilder();//where field1=@field1 and field2=@field2 and field3=@field  
            List<SqlParameter> parList = new List<SqlParameter>(); 
            PrepareCondition(_conditions, parList, pi.Conditions);             
 
            #endregion 
 
            #region 总条数  
             
            pi.RecordCount = 
                (int)SqlHelper.ExecuteScalar( 
                        _SQL_ROWCOUNT 
                            .Replace("{TABLE_NAME}", pi.TableName) 
                            .Replace("{CONDITION}", _conditions.ToString()), 
                        parList.ToArray() 
                    ); 
 
            #endregion 
 
            #region 总页数  
 
            pi.PageCount = 
                pi.RecordCount % pi.PageSize == 0 ? 
                pi.RecordCount / pi.PageSize : 
                pi.RecordCount / pi.PageSize + 1; 
 
            #endregion 
 
            #region 分页结果  
 
            String orderlist = PrepareOrder(pi.Orders); 
 
            pi.Result = 
                SqlHelper.ExecuteDataTable( 
                    _SQL_PAGER 
                        .Replace("{PAGE_SIZE}",pi.PageSize.ToString()) 
                        .Replace("{TABLE_NAME}",pi.TableName) 
                        .Replace("{PKEY}",pi.Pkey) 
                        .Replace("{PASS_OUT}",((pi.PageIndex-1)*pi.PageSize).ToString()) 
                        .Replace("{CONDITION}",_conditions.ToString()) 
                        .Replace("{ORDER_LIST}", String.IsNullOrEmpty(orderlist) ? " order by " + pi.Pkey : orderlist), 
 
                    parList.ToArray() 
                ); 
 
            #endregion  
 
 
        } 
 
 
        /// <summary>  
        /// 预处理条件  
        /// </summary>  
        /// <param name="conditionBuilder"></param>  
        /// <param name="parList"></param>  
        /// <param name="conditions"></param>  
        private void PrepareCondition(StringBuilder conditionBuilder, List<SqlParameter> parList,params Condition[] conditions) 
        { 
            conditionBuilder.Clear(); 
            conditionBuilder.Append(" 1=1 "); 
            parList.Clear(); 
 
            if (conditions != null && conditions.Length > 0) 
            { 
                foreach (Condition con in conditions) 
                { 
                    switch (con.Opt) 
                    { 
                        case Compare.EQ: 
                            conditionBuilder.Append(" and " + con.PropertyName + "=@" + con.PropertyName); 
                            break; 
                        case Compare.GT: 
                            conditionBuilder.Append(" and " + con.PropertyName + ">@" + con.PropertyName); 
                            break; 
                        case Compare.LT: 
                            conditionBuilder.Append(" and " + con.PropertyName + "<@" + con.PropertyName); 
                            break; 
                        case Compare.GE: 
                            conditionBuilder.Append(" and " + con.PropertyName + " >=@" + con.PropertyName); 
                            break; 
                        case Compare.LE: 
                            conditionBuilder.Append(" and " + con.PropertyName + " <=@" + con.PropertyName); 
                            break; 
                        case Compare.NE: 
                            conditionBuilder.Append(" and " + con.PropertyName + " <>@" + con.PropertyName); 
                            break; 
                        case Compare.LIKE: 
                            conditionBuilder.Append(" and " + con.PropertyName + " like @" + con.PropertyName); 
                            break; 
                        case Compare.BETWEEN: 
                            String par1 = "@" + con.PropertyName + "_1"; 
                            String par2 = "@" + con.PropertyName + "_2"; 
                            conditionBuilder.Append(" and " + con.PropertyName + " between " + par1 + " and " + par2); 
                            break; 
                        default: 
                            break; 
                    } 
 
                    if (con.Opt == Compare.LIKE) 
                    { 
                        parList.Add(new SqlParameter("@" + con.PropertyName, "%" + con.PropertyValue.ToString() + "%")); 
                    } 
                    else if (con.Opt == Compare.BETWEEN) 
                    { 
                        Object[] parValues = con.PropertyValue as Object[]; 
                        String par1 = "@" + con.PropertyName + "_1"; 
                        String par2 = "@" + con.PropertyName + "_2"; 
                        parList.Add(new SqlParameter(par1, parValues[0])); 
                        parList.Add(new SqlParameter(par2, parValues[1])); 
                    } 
                    else 
                    { 
                        parList.Add(new SqlParameter("@" + con.PropertyName, con.PropertyValue)); 
                    } 
                } 
            } 
        } 
 
        /// <summary>  
        /// 预处理排序  
        /// </summary>  
        /// <param name="conditions"></param>  
        /// <returns></returns>  
        private String PrepareOrder(NOrder[] orders) 
        { 
            if (orders == null || 
                orders.Length == 0) 
            { 
                return ""; 
            } 
 
            StringBuilder orderlist = new StringBuilder(" order by "); 
            foreach (NOrder norder in orders) 
            { 
                orderlist.Append(norder.PropertyName+" "+norder.Direct.ToString()+","); 
            } 
 
            orderlist.Remove(orderlist.Length - 1, 1); 
 
            return orderlist.ToString(); 
 
        } 
                 
    } 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using BookShopModel;
using System.Data.SqlClient;
using BookShopSqlServerDAL;


namespace BookShopSqlServerDAL
{
    public partial class BaseService
    {
        /// <summary>
        /// 分页方法
        /// </summary>
        /// <param name="pi"></param>
        public virtual void Pager(PageInfo pi)
        {
            if (String.IsNullOrEmpty(pi.TableName) ||
                String.IsNullOrEmpty(pi.Pkey))
            {
                return;
            }

            //表名特殊处理
            if (pi.TableName.ToUpper().Contains("FROM "))//如果包含FROM说明是一个子查询
            {
                pi.TableName = " (" + pi.TableName + ") ";//如果是子查询则添加一对圆括号
            }


            #region 处理条件

            StringBuilder _conditions = new StringBuilder();//where field1=@field1 and field2=@field2 and field3=@field
            List<SqlParameter> parList = new List<SqlParameter>();
            PrepareCondition(_conditions, parList, pi.Conditions);           

            #endregion

            #region 总条数
           
            pi.RecordCount =
                (int)SqlHelper.ExecuteScalar(
                        _SQL_ROWCOUNT
                            .Replace("{TABLE_NAME}", pi.TableName)
                            .Replace("{CONDITION}", _conditions.ToString()),
                        parList.ToArray()
                    );

            #endregion

            #region 总页数

            pi.PageCount =
                pi.RecordCount % pi.PageSize == 0 ?
                pi.RecordCount / pi.PageSize :
                pi.RecordCount / pi.PageSize + 1;

            #endregion

            #region 分页结果

            String orderlist = PrepareOrder(pi.Orders);

            pi.Result =
                SqlHelper.ExecuteDataTable(
                    _SQL_PAGER
                        .Replace("{PAGE_SIZE}",pi.PageSize.ToString())
                        .Replace("{TABLE_NAME}",pi.TableName)
                        .Replace("{PKEY}",pi.Pkey)
                        .Replace("{PASS_OUT}",((pi.PageIndex-1)*pi.PageSize).ToString())
                        .Replace("{CONDITION}",_conditions.ToString())
                        .Replace("{ORDER_LIST}", String.IsNullOrEmpty(orderlist) ? " order by " + pi.Pkey : orderlist),

                    parList.ToArray()
                );

            #endregion


        }


        /// <summary>
        /// 预处理条件
        /// </summary>
        /// <param name="conditionBuilder"></param>
        /// <param name="parList"></param>
        /// <param name="conditions"></param>
        private void PrepareCondition(StringBuilder conditionBuilder, List<SqlParameter> parList,params Condition[] conditions)
        {
            conditionBuilder.Clear();
            conditionBuilder.Append(" 1=1 ");
            parList.Clear();

            if (conditions != null && conditions.Length > 0)
            {
                foreach (Condition con in conditions)
                {
                    switch (con.Opt)
                    {
                        case Compare.EQ:
                            conditionBuilder.Append(" and " + con.PropertyName + "=@" + con.PropertyName);
                            break;
                        case Compare.GT:
                            conditionBuilder.Append(" and " + con.PropertyName + ">@" + con.PropertyName);
                            break;
                        case Compare.LT:
                            conditionBuilder.Append(" and " + con.PropertyName + "<@" + con.PropertyName);
                            break;
                        case Compare.GE:
                            conditionBuilder.Append(" and " + con.PropertyName + " >=@" + con.PropertyName);
                            break;
                        case Compare.LE:
                            conditionBuilder.Append(" and " + con.PropertyName + " <=@" + con.PropertyName);
                            break;
                        case Compare.NE:
                            conditionBuilder.Append(" and " + con.PropertyName + " <>@" + con.PropertyName);
                            break;
                        case Compare.LIKE:
                            conditionBuilder.Append(" and " + con.PropertyName + " like @" + con.PropertyName);
                            break;
                        case Compare.BETWEEN:
                            String par1 = "@" + con.PropertyName + "_1";
                            String par2 = "@" + con.PropertyName + "_2";
                            conditionBuilder.Append(" and " + con.PropertyName + " between " + par1 + " and " + par2);
                            break;
                        default:
                            break;
                    }

                    if (con.Opt == Compare.LIKE)
                    {
                        parList.Add(new SqlParameter("@" + con.PropertyName, "%" + con.PropertyValue.ToString() + "%"));
                    }
                    else if (con.Opt == Compare.BETWEEN)
                    {
                        Object[] parValues = con.PropertyValue as Object[];
                        String par1 = "@" + con.PropertyName + "_1";
                        String par2 = "@" + con.PropertyName + "_2";
                        parList.Add(new SqlParameter(par1, parValues[0]));
                        parList.Add(new SqlParameter(par2, parValues[1]));
                    }
                    else
                    {
                        parList.Add(new SqlParameter("@" + con.PropertyName, con.PropertyValue));
                    }
                }
            }
        }

        /// <summary>
        /// 预处理排序
        /// </summary>
        /// <param name="conditions"></param>
        /// <returns></returns>
        private String PrepareOrder(NOrder[] orders)
        {
            if (orders == null ||
                orders.Length == 0)
            {
                return "";
            }

            StringBuilder orderlist = new StringBuilder(" order by ");
            foreach (NOrder norder in orders)
            {
                orderlist.Append(norder.PropertyName+" "+norder.Direct.ToString()+",");
            }

            orderlist.Remove(orderlist.Length - 1, 1);

            return orderlist.ToString();

        }
               
    }
}
 

 

测试代码:

view plaincopy to clipboardprint?using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text; 
using BookShopModel; 
using System.Data; 
using BookShopSqlServerDAL; 
 
namespace BookShopConsole 

    class Program 
    { 
    //对书籍类别编号为15的书的信息进行分页,并显示第2页[单表分页]  
        //static void Main(string[] args)  
        //{  
        //    BaseService bookSvr = new BaseService();  
 
        //    PageInfo pi = new PageInfo();  
 
        //    pi.TableName = "Books";              
        //    pi.Pkey = "Id";  
        //    pi.PageIndex = 2;  
        //    pi.Conditions = new Condition[] { new Condition("CategoryId", Compare.EQ,15) };  
 
 
        //    bookSvr.Pager(pi);  
 
        //    Console.WriteLine("共{0}条 {1}页 当前第{2}页", pi.RecordCount, pi.PageCount, pi.PageIndex);  
 
        //    Console.WriteLine("--------------------结果集---------------------------");  
        //    foreach (DataRow row in pi.Result.Rows)  
        //    {  
        //        Console.WriteLine("{0} {1}", row["ISBN"], row["Title"]);  
        //    }  
        //}  
 
    //对书籍类别编号为15的书的信息进行分页,并显示第2页,分页结果要取得书籍信息、出版社信息、类别信息[多表联合查询分页]  
        static void Main(string[] args) 
        { 
            BookShopSqlServerDAL.BookService bookSvr = new BookShopSqlServerDAL.BookService(); 
            PageInfo pi = new PageInfo(); 
            pi.TableName = "SELECT b.*,p.id as pid,p.name as pname,c.id as cid,c.name as cname FROM BOOKS as b INNER JOIN publishers as p ON b.PublisherId=p.Id INNER JOIN Categories as c ON b.CategoryId=c.Id"; 
            pi.PageIndex = 2; 
            pi.Pkey = "Id"; 
            pi.Conditions = new Condition[] { new Condition("CategoryId", Compare.EQ, 15) }; 
 
            bookSvr.Pager(pi); 
 
            Console.WriteLine("共{0}条 {1}页 当前第{2}页", pi.RecordCount, pi.PageCount, pi.PageIndex); 
 
            Console.WriteLine("--------------------结果集---------------------------"); 
            foreach (DataRow row in pi.Result.Rows) 
            { 
                Console.WriteLine("{0} {1} {2}", row["ISBN"], row["pname"],row["cname"]); 
            } 
        } 
 
         
    } 

作者“jiangtongcn的专栏”