程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> C#入門知識 >> C#分頁面向對象封裝-教學示范版--分頁實現

C#分頁面向對象封裝-教學示范版--分頁實現

編輯：C#入門知識

分頁相關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}\t\t{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}\t\t{1}\t\t{2}", row["ISBN"], row["pname"],row["cname"]);
            }
        }


    }
}