DataGridView使用BindingNavigator实现简单分页功能

    接上一篇《datagridview使用自定义控件实现简单分页功能》，本篇使用bindingnavigator来实现简单分页功能。其实也只是借用了一个bindingnavigator空壳，

实现原理和代码与上一篇几乎一样，实现方法如下：

　1、新建一个winform程序，命名为bindingnavigatormain，并拖入一个datagridview控件及一个bindingnavigator控件。在bindingnavigator右下角弹窗中添加

一个button(转到)，bindingnavigator的样式如下：

    2、bindingnavigatormain的代码如下：

        private int pagesize;   //每页显示记录数
        private int pageindex;  //页序号
        private int totalcount; //总记录数
        private int pagecount;  //总页数

        public bindingnavigatormain()
        {
            initializecomponent();
        }

        private void bindingnavigatormain_load(object sender, eventargs e)
        {
            pagesize = 20;
            pageindex = 0;
            setpage();
        }

        //设置页
        private void setpage()
        {
            //总记录数
            totalcount = 0;
            bindpage(pagesize, pageindex + 1, out totalcount);

            //总页数
            if (totalcount % pagesize == 0)
                pagecount = totalcount / pagesize;
            else
                pagecount = totalcount / pagesize + 1;

            //当前页及总页数
            txtcurrentpage.text = (pageindex + 1).tostring();
            lbltotalpage.text = "共 " + pagecount.tostring() + " 页";

            //bindingnavigator数据源不进行bindingsource赋值，但恢复控件可用性。
            bindingnavigatormovefirstitem.enabled = true;
            bindingnavigatormovepreviousitem.enabled = true;
            txtcurrentpage.enabled = true;
            lbltotalpage.enabled = true;
            bindingnavigatormovenextitem.enabled = true;
            bindingnavigatormovelastitem.enabled = true;
        }

        /// <summary>
        /// 绑定页
        /// </summary>
        /// <param name="pagesize">每页显示记录数</param>
        /// <param name="pageindex">页序号</param>
        /// <param name="totalcount">总记录数</param>
        private void bindpage(int pagesize, int pageindex, out int totalcount)
        {
            sqlconnection conn = null;
            sqlcommand cmd = null;
            totalcount = 0;

            #region 连接数据库测试
            try
            {
                //数据库连接
                conn = new sqlconnection("server=.;database=db_test;uid=sa;pwd=********;");
                conn.open();

                //sqlcommand
                cmd = new sqlcommand();
                cmd.connection = conn;
                cmd.commandtext = "pagetest";
                cmd.commandtype = commandtype.storedprocedure;

                sqlparameter[] param =
                {
                    new sqlparameter("@pagesize",sqldbtype.int),
                    new sqlparameter("@pageindex",sqldbtype.int),
                    new sqlparameter("@totalcount",sqldbtype.int)
                };
                param[0].value = pagesize;
                param[1].value = pageindex;
                param[2].direction = parameterdirection.output;
                cmd.parameters.addrange(param);

                //datatable
                datatable dt = new datatable("mf_mo");
                dt.columns.add(new datacolumn("mo_no", typeof(string)));
                dt.columns.add(new datacolumn("mrp_no", typeof(string)));
                dt.columns.add(new datacolumn("qty", typeof(decimal)));
                dt.columns.add(new datacolumn("bil_no", typeof(string)));

                #region 方法一：sqldatareader
                sqldatareader dr = cmd.executereader();
                dt.load(dr, loadoption.preservechanges);
                dr.close();
                totalcount = (int)param[2].value;
                datagridview1.datasource = dt;
                #endregion

                #region #方法二：sqldataadapter
                //sqldataadapter da = new sqldataadapter();
                //da.selectcommand = cmd;
                //dt.beginloaddata();
                //da.fill(dt);
                //dt.endloaddata();
                //totalcount = (int)param[2].value;
                //datagridview1.datasource = dt;
                #endregion
            }
            catch (exception ex)
            {
                messagebox.show(ex.message, "提示", messageboxbuttons.ok, messageboxicon.information);
            }
            finally
            {
                conn.close();
                cmd.dispose();
            }
            #endregion
        }

        /// <summary>
        /// 首页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bindingnavigatormovefirstitem_click(object sender, eventargs e)
        {
            pageindex = 0;
            setpage();
        }

        /// <summary>
        /// 上一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bindingnavigatormovepreviousitem_click(object sender, eventargs e)
        {
            pageindex--;
            if (pageindex < 0)
            {
                pageindex = 0;
            }
            setpage();
        }

        /// <summary>
        /// 下一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bindingnavigatormovenextitem_click(object sender, eventargs e)
        {
            pageindex++;
            if (pageindex > pagecount - 1)
            {
                pageindex = pagecount - 1;
            }
            setpage();
        }

        /// <summary>
        /// 末页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bindingnavigatormovelastitem_click(object sender, eventargs e)
        {
            pageindex = pagecount - 1;
            setpage();
        }

        /// <summary>
        /// 只能按0-9、delete、enter、backspace键
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void txtcurrentpage_keypress(object sender, keypresseventargs e)
        {
            if ((e.keychar >= 48 && e.keychar <= 57) || e.keychar == 8 || e.keychar == 13 || e.keychar == 127)
            {
                e.handled = false;
                if (e.keychar == 13)
                {
                    go();
                }
            }
            else
            {
                e.handled = true;
            }
        }

        /// <summary>
        /// 指定页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btngo_click(object sender, eventargs e)
        {
            go();
        }

        private void go()
        {
            if (string.isnullorempty(txtcurrentpage.text))
            {
                messagebox.show("指定页不能为空。", "提示", messageboxbuttons.ok, messageboxicon.information);
                txtcurrentpage.focus();
                return;
            }

            if (int.parse(txtcurrentpage.text) > pagecount)
            {
                messagebox.show("指定页已超过总页数。", "提示", messageboxbuttons.ok, messageboxicon.information);
                txtcurrentpage.focus();
                return;
            }

            pageindex = int.parse(txtcurrentpage.text) - 1;
            setpage();
        }

    3、sql server创建存储过程pagetest：

create procedure [dbo].[pagetest]
    @pagesize int,
    @pageindex int,
    @totalcount int output
as
begin
    --总记录数
    select @totalcount=count(1) from mf_mo
    
    --记录返回(使用动态sql绕开参数嗅探问题，效率大幅度提升。)
    declare @sql nvarchar(1000)
    set @sql=
        'select top ('+convert(varchar(32),@pagesize)+') mo_no,mrp_no,qty,bil_no '+
        'from mf_mo a '+
        'where not exists (select 1 from (select top ('+convert(varchar(32),(@pageindex-1)*@pagesize)+') mo_no from mf_mo order by mo_no) b where a.mo_no=b.mo_no) '+
        'order by mo_no'
    exec (@sql)
end

    4、执行程序：

    好了，分享就到此结束了，希望对有此需要的人有一些帮助。