.NET程式分頁(僅支持MSSQL2005及以上)

aspx

<asp:TextBox ID="txtKeywords" runat="server" CssClass="keyword" style="width:731%;margin-left:-20em;" placeholder="輸入姓名/手機號/身份證號碼進行搜索" />

<asp:LinkButton ID="lbtnSearch" runat="server" CssClass="btn-search" OnClick="lbtnSearch_Click">查詢</asp:LinkButton>

<%#rptList.Items.Count == 0 ? "<tr><td align=\"center\" colspan=\"14\">暫無記錄</td></tr>" : ""%>

<span>顯示</span><asp:TextBox ID="txtPageNum" runat="server" CssClass="pagenum" onkeydown="return checkNumber(event);"
                OnTextChanged="txtPageNum_TextChanged" AutoPostBack="True"></asp:TextBox><span>條/頁</span>

<div id="PageContent" runat="server" class="default"></div>

CodeBehind

命名空間

public partial class xxx : Web.UI.ManagePage
{

protected int totalCount;//總記錄數
protected int page;//當前頁面
protected int pageSize;//每頁數據大小
protected string keywords = string.Empty;//查詢條件

protected void Page_Load(object sender, EventArgs e)
 　　　{

　　　  this.keywords = Utils..GetQueryString("keywords");//獲取查詢條件
            this.pageSize = GetPageSize(10); //設置每頁數據大小

            if (!Page.IsPostBack)
            {

　　　　RptBind("根據keywords整合的查詢語句", "排序欄位 asc/desc");

　　　  }

　　　}

private void RptBind(string _strWhere, string _orderby)
        {
            this.page = Utils.GetQueryInt("page", 1);
            txtKeywords.Text = this.keywords;
            BLL.Business bll = new BLL.Business();
            this.rptList.DataSource = bll.GetList(this.pageSize, this.page, _strWhere, _orderby, out totalCount,out totalIncome);
            this.rptList.DataBind();
            lblTotalIncome.Text = Math.Round(totalIncome, 3).ToString();
            txtPageNum.Text = this.pageSize.ToString();
            string pageUrl = Utils.CombUrlTxt("xxx.aspx", "keywords={0}&page={1}", this.keywords, "__id__");
            PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8);
        }

private int GetPageSize(int _default_size)
        {
            int _pagesize;
            if (int.TryParse(Utils.GetCookie("detail_page_size", "NovelPage"), out _pagesize))
            {
                if (_pagesize > 0)
                {
                    return _pagesize;
                }
            }
            return _default_size;
        }

protected void lbtnSearch_Click(object sender, EventArgs e)
        {
            Response.Redirect(Utils.CombUrlTxt("xxx.aspx", "keywords={0}", txtKeywords.Text));
        }

protected void txtPageNum_TextChanged(object sender, EventArgs e)
        {
            int _pagesize;
            if (int.TryParse(txtPageNum.Text.Trim(), out _pagesize))
            {
                if (_pagesize > 0)
                {
                    Utils.WriteCookie("detail_page_size", "NovelPage", _pagesize.ToString(), 14400);
                }
            }
            Response.Redirect(Utils.CombUrlTxt("xxx.aspx", "keywords={0}", this.keywords));
        }

}

Utils

命名空間

public class Utils
{

public static string GetQueryString(string strName)
        {
            return GetQueryString(strName, false);
        }

public static string GetQueryString(string strName, bool sqlSafeCheck)
        {
            if (HttpContext.Current.Request.QueryString[strName] == null)
                return "";

            if (sqlSafeCheck && !IsSafeSqlString(HttpContext.Current.Request.QueryString[strName]))
                return "unsafe string";

            return HttpContext.Current.Request.QueryString[strName];
        }

public static bool IsSafeSqlString(string str)
        {
            return !Regex.IsMatch(str, @"[-|;|,|\/|\(|\)|\[|\]|\}|\{|%|@|\*|!|\']");
        }

public static int GetQueryInt(string strName, int defValue)
        {
            return StrToInt(HttpContext.Current.Request.QueryString[strName], defValue);
        }

public static int StrToInt(string expression, int defValue)
        {
            if (string.IsNullOrEmpty(expression) || expression.Trim().Length >= 11 || !Regex.IsMatch(expression.Trim(), @"^([-]|[0-9])[0-9]*(\.\w*)?$"))
                return defValue;

            int rv;
            if (Int32.TryParse(expression, out rv))
                return rv;

            return Convert.ToInt32(StrToFloat(expression, defValue));
        }

public static float StrToFloat(string expression, float defValue)
        {
            if ((expression == null) || (expression.Length > 10))
                return defValue;

            float intValue = defValue;
            if (expression != null)
            {
                bool IsFloat = Regex.IsMatch(expression, @"^([-]|[0-9])[0-9]*(\.\w*)?$");
                if (IsFloat)
                    float.TryParse(expression, out intValue);
            }
            return intValue;
        }

public static string CombUrlTxt(string _url, string _keys, params string[] _values)
        {
            StringBuilder urlParams = new StringBuilder();
            try
            {
                string[] keyArr = _keys.Split(new char[] { '&' });
                for (int i = 0; i < keyArr.Length; i++)
                {
                    if (!string.IsNullOrEmpty(_values[i]) && _values[i] != "0")
                    {
                        _values[i] = UrlEncode(_values[i]);
                        urlParams.Append(string.Format(keyArr[i], _values) + "&");
                    }
                }
                if (!string.IsNullOrEmpty(urlParams.ToString()) && _url.IndexOf("?") == -1)
                    urlParams.Insert(0, "?");
            }
            catch
            {
                return _url;
            }
            return _url + DelLastChar(urlParams.ToString(), "&");
        }

public static string UrlEncode(string str)
        {
            if (string.IsNullOrEmpty(str))
            {
                return "";
            }
            str = str.Replace("'", "");
            return HttpContext.Current.Server.UrlEncode(str);
        }

//刪除最後結尾的指定字元後的字元

public static string DelLastChar(string str, string strchar)
        {
            if (string.IsNullOrEmpty(str))
                return "";
            if (str.LastIndexOf(strchar) >= 0 && str.LastIndexOf(strchar) == str.Length - 1)
            {
                return str.Substring(0, str.LastIndexOf(strchar));
            }
            return str;
        }

//返回分頁頁碼

public static string OutPageList(int pageSize, int pageIndex, int totalCount, string linkUrl, int centSize)
        {
            //計算頁數
            if (totalCount < 1 || pageSize < 1)
            {
                return "";
            }
            int pageCount = totalCount / pageSize;
            if (pageCount < 1)
            {
                return "";
            }
            if (totalCount % pageSize > 0)
            {
                pageCount += 1;
            }
            if (pageCount <= 1)
            {
                return "";
            }
            StringBuilder pageStr = new StringBuilder();
            string pageId = "__id__";
            string firstBtn = "<a href=\"" + ReplaceStr(linkUrl, pageId, (pageIndex-1).ToString()) + "\">上一頁»</a>";
            string lastBtn = "<a href=\"" + ReplaceStr(linkUrl, pageId, (pageIndex + 1).ToString()) + "\">下一頁»</a>";
            string firstStr = "<a href=\"" + ReplaceStr(linkUrl, pageId, "1") + "\">1</a>";
            string lastStr = "<a href=\"" + ReplaceStr(linkUrl, pageId, pageCount.ToString()) + "\">" + pageCount.ToString() + "</a>";

            if (pageIndex <= 1)
            {
                firstBtn = "<span class=\"disabled\">«上一頁</span>";
            }
            if (pageIndex >= pageCount)
            {
                lastBtn = "<span class=\"disabled\">下一頁»</span>";
            }
            if (pageIndex == 1)
            {
                firstStr = "<span class=\"current\">1</span>";
            }
            if (pageIndex == pageCount)
            {
                lastStr = "<span class=\"current\">" + pageCount.ToString() + "</span>";
            }
            int firstNum = pageIndex - (centSize / 2); //中間開始的頁碼
            if (pageIndex < centSize)
                firstNum = 2;
            int lastNum = pageIndex + centSize - ((centSize / 2) + 1); //中間結束的頁碼
            if (lastNum >= pageCount)
                lastNum = pageCount - 1;
            pageStr.Append("<span>共" + totalCount + "記錄</span>");
            pageStr.Append(firstBtn + firstStr);
            if (pageIndex >= centSize)
            {
                pageStr.Append("<span>...</span>\n");
            }
            for (int i = firstNum; i <= lastNum; i++)
            {
                if (i == pageIndex)
                {
                    pageStr.Append("<span class=\"current\">" + i + "</span>");
                }
                else
                {
                    pageStr.Append("<a href=\"" + ReplaceStr(linkUrl, pageId, i.ToString()) + "\">" + i + "</a>");
                }
            }
            if (pageCount - pageIndex > centSize - ((centSize / 2)))
            {
                pageStr.Append("<span>...</span>");
            }
            pageStr.Append(lastStr + lastBtn);
            return pageStr.ToString();
        }

public static string ReplaceStr(string originalStr, string oldStr, string newStr)
        {
            if (string.IsNullOrEmpty(oldStr))
            {
                return "";
            }
            return originalStr.Replace(oldStr, newStr);
        }

public static string GetCookie(string strName, string key)
        {
            if (HttpContext.Current.Request.Cookies != null && HttpContext.Current.Request.Cookies[strName] != null && HttpContext.Current.Request.Cookies[strName][key] != null)
                return UrlDecode(HttpContext.Current.Request.Cookies[strName][key].ToString());

            return "";
        }

public static string UrlDecode(string str)
        {
            if (string.IsNullOrEmpty(str))
            {
                return "";
            }
            return HttpContext.Current.Server.UrlDecode(str);
        }

public static void WriteCookie(string strName, string key, string strValue, int expires)
        {
            HttpCookie cookie = HttpContext.Current.Request.Cookies[strName];
            if (cookie == null)
            {
                cookie = new HttpCookie(strName);
            }
            cookie[key] = UrlEncode(strValue);
            cookie.Expires = DateTime.Now.AddMinutes(expires);
            HttpContext.Current.Response.AppendCookie(cookie);
        }

}

Bussiness

命名空間

 public class Business
    {
        private readonly DAL.xxx xxxDal;

public Business()
        {
            xxxDal = new DAL.xxx();

　　}

public List<Model.xxx> GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount,out decimal totalIncome)
        {
            return xxxDal.GetList(pageSize, pageIndex, strWhere, filedOrder, out recordCount,out totalIncome);
        }

}

DAL

命名空間

public partial class xxx
{

public List<Model.xxx> GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount,out decimal totalIncome)
        {
            totalIncome = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select a.oid,ocreatetime,a.oaccname,a.oacctel,(select ucardnum from AppUser where uid=a.ouid) as ucardnum,c.pname,
                            otype,ISNULL(b.corealmoney,0) as corealmoney,ISNULL(b.cooid,0) as cooid,
                            omoney,ISNULL(coprate,0) coprate,ISNULL(codrawfee,0) codrawfee,ISNULL(codowndrawfee,0) codowndrawfee,ISNULL(d.uname,'無') as uname,
                             ISNULL(b.couserrate,0) as couserrate,
                            ROW_NUMBER() over(order by ocreatetime desc) r from xxx a left join
                            (select cooid,coprate,comoney,corealmoney,codowndrawfee,codrawfee,couupperid,couserrate from xxx where costatus=1)b
                            on a.oid=b.cooid join xxx c on a.opid=c.pid
                            left join xxx d on b.couupperid=d.[uid] where a.ostatus=1 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            using (var context = DataBaseConnection.GetSdkBaseConnection())
            {
                recordCount = context.ExecuteScalar<int>(PagingHelper.CreateCountingSql(strSql.ToString()));
                if (recordCount > 0)
                {
                    StringBuilder strSql2 = new StringBuilder();
                    strSql2.Append(@"select SUM(corealmoney) from xxx a left join
                                    (select cooid,coprate,corealmoney,codowndrawfee,comoney,codrawfee,couupperid,couserrate from xxx where costatus=1)b
                                    on a.oid=b.cooid join xxx c on a.opid=c.pid
                                    left join xxx d on b.couupperid=d.[uid] where a.ostatus=1 ");
                    if (strWhere.Trim() != "")
                    {
                        strSql2.Append(strWhere);
                    }
                }
                return context.Query<Model.xxx>(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)).ToList();
            }
        }

}

PagingHelper

命名空間

// ROW_NUMBER高效率分頁(僅支持MSSQL2005及以上)

public static class PagingHelper
 {

// 獲取分頁SQL語句，預設row_number為關健字，所有表不允許使用該欄位名

public static string CreatePagingSql(int _recordCount, int _pageSize, int _pageIndex, string _safeSql, string _orderField)
        {
            //計算總頁數
            _pageSize = _pageSize == 0 ? _recordCount : _pageSize;
            int pageCount = (_recordCount + _pageSize - 1) / _pageSize;

            //檢查當前頁數
            if (_pageIndex < 1)
            {
                _pageIndex = 1;
            }
            else if (_pageIndex > pageCount)
            {
                _pageIndex = pageCount;
            }
            //拼接SQL字元串，加上ROW_NUMBER函數進行分頁
            StringBuilder newSafeSql = new StringBuilder();
            newSafeSql.AppendFormat("SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,", _orderField);
            newSafeSql.Append(_safeSql.Substring(_safeSql.ToUpper().IndexOf("SELECT") + 6));

            //拼接成最終的SQL語句
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT * FROM (");
            sbSql.Append(newSafeSql.ToString());
            sbSql.Append(") AS T");
            sbSql.AppendFormat(" WHERE row_number between {0} and {1}", ((_pageIndex - 1) * _pageSize) + 1, _pageIndex * _pageSize);

            return sbSql.ToString();
        }
        // 獲取記錄總數SQL語句
        public static string CreateCountingSql(string _safeSql)
        {
            return string.Format(" SELECT COUNT(1) AS RecordCount FROM ({0}) AS T ", _safeSql);
        }

}