欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-10-28 10:15:02
接上一篇《DataGridView使用自定义控件实现简单分页功能》,本篇使用BindingNavigator来实现简单分页功能。其实也只是借用了一个BindingNavigator空壳, 实现原理和代码与上一篇几乎一样,实现方法如下: 1、新建一个WinForm程序,命名为BindingNaviga ......

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

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

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

一个button(转到),bindingnavigator的样式如下:

DataGridView使用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、执行程序:

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

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