GridView高效分页和搜索功能的实现代码

前言：

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

        这是分页的效果图

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

          一、前台布局

  <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，所以就只有自己动手实现了。

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