GridView高效分页+搜索的完整实现

前言：

        公司项目开发，上周的任务是做基础数据的管理。在Sharepoint2010里边内嵌asp.net的aspx页，遇到了各种各样奇葩的问题，因为之前对sharepoint只是有一些了解，但是没有设计到具体的编程工作，这一次算是初次接触吧。其中有一部分基础数据数据量很大，大致有十多万，因为是对基础数据的维护，所以还需要对数据进行列表展示，增删改查什么的，大家都知道Asp.net里边的GridView有自带的分页，但是，那个分页对于少量的数据还好，对于这种数十万的数据量而言，这种分页方式简直就是灾难。网上关于GridView高效分页的东西有很多，找了一个自己改了改。用着效果还不错，和大家分享一下。

        这是分页的效果图

         下边就讲一下具体的实现，首先声明，这个东西是不是我原创的，只是在此基础上进行了修饰和完善。希望能给各位有所启发。

          一、前台布局

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

< div >      < div  id = "main" >      < div  id = "search" >         < table > < tr > < td >      < asp:Label  ID = "lb"  runat = "server"  Text = "姓名" ></ asp:Label ></ td > < td >      < asp:TextBox  ID = "SearchName"  runat = "server" ></ asp:TextBox > </ td > < td >      < asp:Button  ID = "btnSearch"  runat = "server"  Text = "查询"  onclick = "PagerBtnCommand_OnClick"  CommandName = "search"  /> </ td > < td >      < asp:Button  ID = "btnReset"  runat = "server"  Text = "重置"  onclick = "btnReset_Click"  /> </ td > </ tr > </ table >      </ div >      < div  id = "gridView" >       < asp:GridView  ID = "UserGridView"  runat = "server"   AutoGenerateColumns = "false" >          < Columns >          < asp:TemplateField  HeaderText = "用户名" >          < ItemTemplate >              < asp:Label  ID = "UserName"  runat = "server"  Text='<%#Eval("username") %>'></ asp:Label >          </ ItemTemplate >          </ asp:TemplateField >          < asp:TemplateField  HeaderText = "单位名称" >          < ItemTemplate >              < asp:Label  ID = "DisplayName"  runat = "server"  Text='<%#Eval("displayname") %>'></ asp:Label >          </ ItemTemplate >          </ asp:TemplateField >          < asp:TemplateField  HeaderText = "组织编码" >          < ItemTemplate >              < asp:Label  ID = "OrgCode"  runat = "server"  Text='<%#Eval("orgcode") %>'></ asp:Label >          </ ItemTemplate >          </ asp:TemplateField >          < asp:TemplateField  HeaderText = "组织名称" >          < ItemTemplate >              < asp:Label  ID = "OrgName"  runat = "server"  Text='<%#Eval("orgname") %>'></ asp:Label >          </ ItemTemplate >          </ asp:TemplateField >          </ Columns >          </ asp:GridView >      </ div >      < div  id = "page" >      < table >       < tr >       < td >        < asp:Label  ID = "lbcurrentpage1"  runat = "server"  Text = "当前页:" ></ asp:Label >      < asp:Label  ID = "lbCurrentPage"  runat = "server"  Text = "" ></ asp:Label >      < asp:Label  ID = "lbFenGe"  runat = "server"  Text = "/" ></ asp:Label >      < asp:Label  ID = "lbPageCount"  runat = "server"  Text = "" ></ asp:Label >      </ td >       < td >       < asp:Label  ID = "recordscount"  runat = "server"  Text = "总条数:" ></ asp:Label >        < asp:Label  ID = "lbRecordCount"  runat = "server"  Text = "" ></ asp:Label >      </ td >      < td > < asp:Button  ID = "Fistpage"  runat = "server"  CommandName = ""  Text = "首页"  OnClick = "PagerBtnCommand_OnClick"  /> < asp:Button  ID = "Prevpage"  runat = "server"  CommandName = "prev"  Text = "上一页"          OnClick = "PagerBtnCommand_OnClick"  /> < asp:Button  ID = "Nextpage"  runat = "server"  CommandName = "next"  Text = "下一页"  OnClick = "PagerBtnCommand_OnClick"  /> < asp:Button  ID = "Lastpage"  runat = "server"  CommandName = "last"  Text = "尾页"         key = "last"  OnClick = "PagerBtnCommand_OnClick"  /> </ td >      < td >      < asp:Label  ID = "lbjumppage"  runat = "server"  Text = "跳转到第" ></ asp:Label >      < asp:TextBox  ID = "GotoPage"  runat = "server"  Width = "25px" ></ asp:TextBox >      < asp:Label  ID = "lbye"  runat = "server"  Text = "页" ></ asp:Label >        < asp:Button  ID = "Jump"  runat = "server"  Text = "跳转"  CommandName = "jump"  OnClick = "PagerBtnCommand_OnClick"  />      </ td > </ tr > </ table >      </ div >      </ div >      </ div >

　　布局的效果如下：

  

二、后台的代码实现

     我会一点一点向大家讲解清楚，这个分页的原理

     Members：这里主要定义几个全局的变量，主要用来记录信息的数量、页的数量和当前页

?

#region Members          const  int  PAGESIZE = 10; //每页显示信息数量          int  PagesCount, RecordsCount; //记录总页数和信息总条数          int  CurrentPage, Pages, JumpPage; //当前页，信息总页数(用来控制按钮失效)，跳转页码          const  string  COUNT_SQL = "select count(*)  from p_user" ;          #endregion

Methods：　　

1、GetRecordsCount：该方法主要用来获取当前信息的总数，有一个sqlSearch参数，默认的为default,即初始化页面时，查询所有信息的总条数，当用户输入要搜索的用户名进行检索时，获取符合用户检索条件的信息的总条数

?

/// <summary>         /// 获取信息总数         /// </summary>         /// <param name="sqlSearch"></param>         /// <returns></returns>         public  static  int  GetRecordsCount( string  sqlRecordsCount)         {             string  sqlQuery;             if  (sqlRecordsCount == "default" )             {                 sqlQuery = COUNT_SQL;             }             else             {                 sqlQuery = sqlRecordsCount;             }             int  RecordCount = 0;             SqlCommand cmd = new  SqlCommand(sqlQuery, Conn());             RecordCount = Convert.ToInt32(cmd.ExecuteScalar());             cmd.Connection.Close();             return  RecordCount;         }

2、OverPage:该方法主要用来计算剩余页，当前设置的为每页显示10条数据，如何符合条件的数据有11条，则要显示2页

?

/// <summary>         /// 计算余页         /// </summary>         /// <returns></returns>         public  int  OverPage()         {             int  pages = 0;             if  (RecordsCount % PAGESIZE != 0)                 pages = 1;             else                 pages = 0;             return  pages;         }

3、ModPage：该方法也是用计算余页，主要用来防止SQL执行溢出

?

/// <summary>         /// 计算余页，防止SQL语句执行时溢出查询范围         /// </summary>         /// <returns></returns>         public  int  ModPage()         {             int  pages = 0;             if  (RecordsCount % PAGESIZE == 0 && RecordsCount != 0)                 pages = 1;             else                 pages = 0;             return  pages;         }

4、Conn：该方法用来创建数据连接对象，在使用的时候只需改成自己的数据库名即可　

?

/// <summary>          /// 数据连接对象          /// </summary>          /// <returns></returns>          public  static  SqlConnection Conn()          {              SqlConnection conn = new  SqlConnection( "data source=.;initial catalog=DB_GSL_ZCW;Integrated Security=true" );              conn.Open();              return  conn;          }

5、GridViewDataBind：该方法主要用来数据绑定，如果传入的参数为default则，默认的绑定所有的数据，否则，则绑定过滤过的数据

?

/// <summary>          /// GridView数据绑定，根据传入参数的不同，进行不同方式的查询，          /// </summary>          /// <param name="sqlSearch"></param>          private  void  GridViewDataBind( string  sqlSearch)          {              CurrentPage = ( int )ViewState[ "PageIndex" ];              //从ViewState中读取页码值保存到CurrentPage变量中进行按钮失效运算              Pages = ( int )ViewState[ "PagesCount" ];              //从ViewState中读取总页参数进行按钮失效运算              //判断四个按钮（首页、上一页、下一页、尾页）状态              if  (CurrentPage + 1 > 1) //当前页是否为首页              {                  Fistpage.Enabled = true ;                  Prevpage.Enabled = true ;              }              else              {                  Fistpage.Enabled = false ;                  Prevpage.Enabled = false ;              }              if  (CurrentPage == Pages) //当前页是否为尾页              {                  Nextpage.Enabled = false ;                  Lastpage.Enabled = false ;              }              else              {                  Nextpage.Enabled = true ;                  Lastpage.Enabled = true ;              }              DataSet ds = new  DataSet();              string  sqlResult;              //根据传入参数sqlSearch进行判断，如果为default则为默认的分页查询，否则为添加了过滤条件的分页查询              if  (sqlSearch == "default" )              {                  sqlResult = "Select Top "  + PAGESIZE + "user_serialid,username,displayname,orgcode,orgname from p_user where user_serialid not in(select top "  + PAGESIZE * CurrentPage + " user_serialid from p_user order by user_serialid asc) order by user_serialid asc" ;              }              else              {                  sqlResult = sqlSearch;              }              SqlDataAdapter sqlAdapter = new  SqlDataAdapter(sqlResult, Conn());              sqlAdapter.Fill(ds, "Result" );              UserGridView.DataSource = ds.Tables[ "Result" ].DefaultView;              UserGridView.DataBind();              //显示Label控件lbCurrentPaget和文本框控件GotoPage状态              lbCurrentPage.Text = (CurrentPage + 1).ToString();              GotoPage.Text = (CurrentPage + 1).ToString();              sqlAdapter.Dispose();          }

6、Page_Load：页面加载函数，主要是在首次进入页面时，进行初始化，默认的获取所有的信息

?

protected  void  Page_Load( object  sender, EventArgs e)          {              if  (!IsPostBack) //首次进行该页时，页面初始化              {                  RecordsCount = GetRecordsCount( "default" ); //默认信息总数                  PagesCount = RecordsCount / PAGESIZE + OverPage(); //默认的页总数                  ViewState[ "PagesCount" ] = RecordsCount / PAGESIZE - ModPage(); //保存末页索引，比页总数小1                  ViewState[ "PageIndex" ] = 0; //保存页面初始索引从0开始                  ViewState[ "JumpPages" ] = PagesCount;                  //保存页总数，跳页时判断用户输入数是否超出页码范围                  //显示lbPageCount、lbRecordCount的状态                  lbPageCount.Text = PagesCount.ToString();                  lbRecordCount.Text = RecordsCount.ToString();                  //判断跳页文本框失效                  if  (RecordsCount <= 10)                  {                      GotoPage.Enabled = false ;                  }                      GridViewDataBind( "default" ); //调用数据绑定函数TDataBind()进行数据绑定运算              }          }

7、PagerBtnCommand_OnClick：该方法主要用来处理设计视图页的“首页”、“下一页”，“上一页”，“尾页”，“查询”按钮的Click事件，主要通过不同按钮的CommandName属性来分别处理，需要在前台为每一个按钮相应的CommandName属性赋值，如果用户点击的是“查询”按钮，这个时候需要对查询的Sql语句进行重写，加入过滤条件，即用户输入的查询的条件

?

/// <summary>          /// 页面按钮Click处理          /// </summary>          /// <param name="sender"></param>          /// <param name="e"></param>          protected  void  PagerBtnCommand_OnClick( object  sender, EventArgs e)          {              CurrentPage = ( int )ViewState[ "PageIndex" ];              //从ViewState中读取页码值保存到CurrentPage变量中进行参数运算              Pages = ( int )ViewState[ "PagesCount" ]; //从ViewState中读取总页参数运算              Button btn = sender as  Button;              string  sqlResult= "default" ;              if  (btn != null )              {                  string  cmd = btn.CommandName;                  switch  (cmd) //根据不同的CommandName做出不同的处理                  {                      case  "next" :                          CurrentPage++;                          break ;                      case  "prev" :                          CurrentPage--;                          break ;                      case  "last" :                          CurrentPage = Pages;                          break ;                      case  "search" :                          if  (! string .IsNullOrEmpty(SearchName.Text))                          {                              RecordsCount = GetRecordsCount( "select count(*) from p_user where username like '"  + SearchName.Text + "%'" ); //获取过滤后的总记录数                              PagesCount = RecordsCount / PAGESIZE + OverPage(); //该变量为页总数                              ViewState[ "PagesCount" ] = RecordsCount / PAGESIZE - ModPage(); //该变量为末页索引，比页总数小1                              ViewState[ "PageIndex" ] = 0; //保存一个为0的页面索引值到ViewState，页面索引从0开始                              ViewState[ "JumpPages" ] = PagesCount;                              //保存PageCount到ViewState，跳页时判断用户输入数是否超出页码范围                              //显示lbPageCount、lbRecordCount的状态                              lbPageCount.Text = PagesCount.ToString();                              lbRecordCount.Text = RecordsCount.ToString();                              //判断跳页文本框失效                              if  (RecordsCount <= 10)                                  GotoPage.Enabled = false ;                  sqlResult = "Select Top "  + PAGESIZE + "user_serialid,username,displayname,orgcode,orgname from p_user where user_serialid not in(select top "  + PAGESIZE * CurrentPage + " user_serialid from p_user order by user_serialid asc) and username like '"  + SearchName.Text + "%' order by user_serialid asc" ;                          }                          else                          {                              Response.Write( "请输入您所要查找的用户姓名！" );                          }                          break ;                      case  "jump" :                         JumpPage = ( int )ViewState[ "JumpPages" ];                        //从ViewState中读取可用页数值保存到JumpPage变量中                        //判断用户输入值是否超过可用页数范围值                        if (Int32.Parse(GotoPage.Text) > JumpPage ||      Int32.Parse(GotoPage.Text) <= 0) Response.Write( "<script>alert('页码范围越界！')</script>" );                        else                        {                            int  InputPage = Int32.Parse(GotoPage.Text.ToString()) - 1;                            //转换用户输入值保存在int型InputPage变量中                            ViewState[ "PageIndex" ] = InputPage;                            CurrentPage = InputPage;                           //写入InputPage值到ViewState["PageIndex"]中                            sqlResult = "Select Top "  + PAGESIZE + "user_serialid,username,displayname,orgcode,orgname from p_user where user_serialid not in(select top "  + PAGESIZE * CurrentPage + " user_serialid from p_user order by user_serialid asc) and username like '"  + SearchName.Text + "%' order by user_serialid asc" ;                        }                          break ;                      default :                          CurrentPage = 0;                          break ;                  }                  ViewState[ "PageIndex" ] = CurrentPage;                  //将运算后的CurrentPage变量再次保存至ViewState                  GridViewDataBind(sqlResult); //调用数据绑定函数TDataBind()              }          }

8、btn_Reset_Click:该方法主要用来进行重置，用户完成一次查询之后，需要重置，才能进行下一次的查询操作

?

protected  void  btnReset_Click( object  sender, EventArgs e)       (           RecordsCount = GetRecordsCount( "default" ); //默认信息总数           PagesCount = RecordsCount / PAGESIZE + OverPage(); //默认的页总数           ViewState[ "PagesCount" ] = RecordsCount / PAGESIZE - ModPage(); //保存末页索引，比页总数小1           ViewState[ "PageIndex" ] = 0; //保存页面初始索引从0开始           ViewState[ "JumpPages" ] = PagesCount;           //保存页总数，跳页时判断用户输入数是否超出页码范围           //显示lbPageCount、lbRecordCount的状态           lbPageCount.Text = PagesCount.ToString();           lbRecordCount.Text = RecordsCount.ToString();           //判断跳页文本框失效           if  (RecordsCount <= 10)           {               GotoPage.Enabled = false ;           }               GridViewDataBind( "default" ); //调用数据绑定函数TDataBind()进行数据绑定运算       }

 

这里的高效分页方法主要用的是select top 10 Id ，Name from tb where Id not in (select top 10*N from tb order by Id asc) order by Id asc

示例中的N代表的是页数，之前原子里也有很多关于高效分页的讨论，这里就不再多说了，我个人觉得这个分页语句效果还不错，当然除此之外还有row_number（）函数分页、select Max() 分页等等方法，之前也有总结过，有兴趣的朋友可以看一下我之前写的ListView和Repeater高效分页这篇文章，里边讲述的也很详细，只要你一步一步的照着去操练应该问题不大的。

这里需要解释一下的是为什么没有有数据绑定控件直接进行绑定，因为在sharepoint2010项目里边它支持的数据源控件只有XMLDataSource，所以就只有自己动手实现了。

好了今天就到这里了，希望能给大家带来些帮助吧！还请多多指教！