接上一篇《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、执行程序：
好了，分享就到此结束了，希望对有此需要的人有一些帮助。
黄山市民网：https://www.huangshanshimin.com/
