波斯马BOSSMA Information Technology

关于asp.net的排序问题，我曾经写过一篇文章：GridView使用DataSource绑定数据源之排序问题的解决

本文重点解决大数据量情况的分页与排序问题，使用SQL语句（非存储过程）优化，大大提高了大数据量情况下的分页与排序效率。
1、前台页面程序

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
? Width="100%" AllowSorting="True" OnSorting="GridView1_Sorting" OnRowDataBound="GridView1_RowDataBound">
......(省略)
</asp:GridView>
<!--AspNetPager分页控件，需要引用，网上比较容易找到-->
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" AlwaysShow="True" OnPageChanged="AspNetPager1_PageChanged" ShowCustomInfoSection="Right" CustomInfoHTML="第<font color='red'><b>%currentPageIndex%</b></font>页&nbsp;&nbsp;共%PageCount%页&nbsp;&nbsp;每页%PageSize%条&nbsp;&nbsp;共%RecordCount%条"
?FirstPageText="首页" LastPageText="尾页" NextPageText="后页" PrevPageText="前页" CustomInfoTextAlign="Right">
</webdiyer:AspNetPager>

2、页面后台程序

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Order : System.Web.UI.Page
{
    const string vsKey = "searchCriteria"; //用于ViewState保存strWhere查询条件
    private Order orderList;  //自身

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            databinds();
        }
    }

    //数据绑定
    private void databinds()
    {
        //初始化分页控件，需要添加引用AspNetPager，网上比较容易找到
        initAspNetPager();
        //初始化SQL语句中的Where条件
        getStrWhere();
        //带有排序的数据绑定
        sortdatabinds(ViewState[vsKey].ToString(), false);

    }

    //带有排序的数据绑定
    private void sortdatabinds(string strWhere, bool isPageIndexChanging)
    {

        PagedDataSource pds = new PagedDataSource();
        //获取DataSet
        DataSet oitds = getorderList().setData(strWhere, isPageIndexChanging);
        //设置分页控件
        setAspNetPager(pds, oitds);

        GridView1.DataSource = oitds.Tables[0].DefaultView;
        GridView1.DataBind();
    }

    //返回当前页面的DataSet
    private DataSet setData(string strWhere, bool isPageIndexChanging)
    {
        //调用GetList方法，重要部分
        DataSet oitds = getOrderM().GetList(getPdsPage(), getPagerPageSize(), strWhere, GridViewSortExpression, returnSortDirection(isPageIndexChanging));
        return oitds;
    }

    //方便对象调用
    private static OrderM getOrderM()
    {
        OrderM oiap = new OrderM();
        return oiap;
    }

    //设置分页控件
   private void setAspNetPager(PagedDataSource pds, DataSet oitds)
    {
        DataView dv = oitds.Tables[0].DefaultView;
        //获取总的记录数 InfoCount方法
        AspNetPager1.RecordCount = getOrderM().InfoCount(ViewState[vsKey].ToString());

        pds.DataSource = dv;
        pds.AllowPaging = true;
        pds.CurrentPageIndex = AspNetPager1.CurrentPageIndex - 1;
        pds.PageSize = AspNetPager1.PageSize;
    }

    //初始化分页控件
    private void initAspNetPager()
    {
        ConfigHelper.initAspNetPager(AspNetPager1);
    }

    //获取数据集当前页
    private int getPdsPage()
    {
        return ConfigHelper.getPdsPage(AspNetPager1);
    }

    //获取每页记录数
    private int getPagerPageSize()
    {
        return ConfigHelper.getPagerPageSize(AspNetPager1);
    }

    //当前页对象
    private Order getorderList()
    {
        if (orderList == null)
        {
            orderList = this;
        }
        return orderList;
    }

    //返回查询条件
    private void getStrWhere()
    {
        string strWhere;
        ......(省略)
        //保存查询条件
      ViewState[vsKey] = strWhere;
    }

    //页更改处理
    protected void AspNetPager1_PageChanged(object src, EventArgs e)
    {
        int pageIndex = AspNetPager1.CurrentPageIndex - 1;

        sortdatabinds(ViewState[vsKey].ToString(), true);
    }

    //排序处理
    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        GridViewSortExpression = e.SortExpression;

        sortdatabinds(ViewState[vsKey].ToString(), false);
    }

    //获取排序方向
    private string returnSortDirection(bool isPageIndexChanging)
    {
        string sorderection = "";

        if (isPageIndexChanging)
        {
            sorderection = GridViewSortDirection;
        }

        else
        {
            sorderection = GetSortDirection();

        }
        return sorderection;
    }

    //排序方向
    private string GridViewSortDirection
    {

        get { return ViewState["SortDirection"] as string ?? "ASC"; }

        set { ViewState["SortDirection"] = value; }

    }

    //排序字段
    private string GridViewSortExpression
    {

        get { return ViewState["SortExpression"] as string ?? string.Empty; }

        set { ViewState["SortExpression"] = value; }

    }

    //新的排序方向
    private string GetSortDirection()
    {

        switch (GridViewSortDirection)
        {
            case "ASC":
                GridViewSortDirection = "DESC";
                break;
            case "DESC":
                GridViewSortDirection = "ASC";
                break;
        }

        return GridViewSortDirection;

    }
}

3、ConfigHelper.cs（分页控件部分）

         /// 
         /// 设置分页控件
      /// 
        public static void initAspNetPager(AspNetPager AspNetPager1)
        {
            AspNetPager1.CurrentPageIndex = 1;
            AspNetPager1.PageSize = getGlobalPageSize();
        }
        public static int getCurrPagerPage(AspNetPager AspNetPager1)
        {
            int currPagerPage = AspNetPager1.CurrentPageIndex;
            return currPagerPage;
        }
        public static int getPdsPage(AspNetPager AspNetPager1)
        {
            return (getCurrPagerPage(AspNetPager1) - 1);
        }

        public static int getPagerPageSize(AspNetPager AspNetPager1)
        {
            return AspNetPager1.PageSize;
        }

        //从配置文件（web.config）读取每页显示记录数
        public static int  getGlobalPageSize(){
            try
            {
                return Int32.Parse(GetConfigString("pagesize"));
            }
            catch
            {
                return 12;
            }
        }

4、OrderM.cs(GetList部分)

         /// 
         /// 获得数据列表
      /// 
        public DataSet GetList(int currPage, int pageSize, string strWhere, string sortExpression, string sorDirection)
        {
            return SubPageSelect.getSubPageSelect().GetList(currPage, pageSize, strWhere, sortExpression, sorDirection, "vsv_member", "member_id");
        }

5、SubPageSelect.cs
构造分页排序的SQL语句，并返回查询结果

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Bossma.DBUtility;//通用数据访问，需要替换成自己的

public class SubPageSelect
{

        private static SubPageSelect subPageSelect;

        public static SubPageSelect getSubPageSelect()
        {
            if (subPageSelect == null)
            {
                subPageSelect = new SubPageSelect();
            }
            return subPageSelect;
        }

        /// 
        /// 获得数据列表
      /// 
        public DataSet GetList(int currPage, int pageSize, string strWhere, string sortExpression, string sorDirection, string tableName,string defaultStr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top " + pageSize + " * ");
            strSql.Append(" FROM " + tableName + " ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + getStrWhere(currPage, pageSize, strWhere, sortExpression, sorDirection, tableName, defaultStr));
            }
                //DbHelperSQL为通用数据访问类，需要替换成自己的
                return DbHelperSQL.Query(strSql.ToString());
        }

        /// 
        /// 获取查询条件
     /// 
        ///

        /// string
        private string getStrWhere(int currPage, int pageSize, string strWhere, string sortExpression, string sorDirection, string tableName, string defaultStr)
        {
            string newWhere = "";

            if (sortExpression != string.Empty)
            {
            //当前排序方式
                newWhere = strWhere + " and " + defaultStr + " not in (select top " + (currPage * pageSize) + " " + defaultStr + " from " + tableName + " where " + strWhere + " order by " + sortExpression + " " + sorDirection + ") order by " + sortExpression + " " + sorDirection;

            }
            else
            {
            //默认排序方式
                newWhere = strWhere + " and " + defaultStr + " not in (select top " + (currPage * pageSize) + " " + defaultStr + " from " + tableName + " where " + strWhere + " order by " + defaultStr + " desc) order by " + defaultStr + " desc";

            }
            return newWhere;
        }
}

关键字: asp.net SQL分页排序 大数据量