asp.net 数据分页控件（不用存储过程)
本分页控件原理
分页的方法很多，有用存储过程的，有不用存储过程的，还有在c#中用datagridview的虚拟模式的，目的只有一个，对大数据量进行处理，让用户体验得到提高，软件速度得到提升。本分页控件主要是用了下面的sql语句，我相信你能看懂的，存储过程分页也是用类似的sql代码：
1 /*tablename :表名 如:tbtestdata
2 sqlwherestatement :sql where表达式 如:where表达式为空
3 primarykey :主键 如:uniqueid
4 pagesize :分页大小 如:50
5 pageindex :当前页 如:8
6 orderfield :排序字段 如:insetdatatime
7 */
8
9 select top 50 * from tbtestdata
10 where uniqueid not in
11 (
12 select top (50 * 8) uniqueid from tbtestdata order by insetdatatime desc
13 )
14 order by insetdatatime desc
原理就这么简单。
分页控件代码
（一）、实例数据库教程代码
创建实例数据库。
create table [tbtestdata](
[uniqueid] [bigint] not null,
[companyname] [varchar](200) null,
[companycode] [varchar](50) null,
[address] [varchar](500) null,
[owner] [varchar](100) null,
[memo] [varchar](2000) null,
[insetdatatime] [datetime] null,
constraint [pk_tbtestdata] primary key clustered
(
[uniqueid] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]
go
alter table [dbo].[tbtestdata] add constraint [df_tbtestdata_insetdatatime] default (getdate()) for [insetdatatime]
go
--生成实例数据
declare @intrownumber int;
select @intrownumber = 1;
while @intrownumber < 1000000
begin
insert into tbtestdata(uniqueid,companyname,companycode,address,owner,memo)
values(@intrownumber,'companyname' + cast(@intrownumber as varchar(2000)),
'companycode' + cast(@intrownumber as varchar(2000)),'address'+ cast(@intrownumber as varchar(2000)),
'owner' + cast(@intrownumber as varchar(2000)),'memo' + cast(@intrownumber as varchar(2000)));
select @intrownumber = @intrownumber + 1
end
（二）、分页控件代码。
namespace dotnet.controls
{
///
/// 分页控件（使用代码实现，不用存储过程）
/// ucpagecontrolbycode
/// 修改纪录
///
/// 2010-01-06 胡勇 修改转到某页由原来的keypress方法改为keydown，让用户按回车键确认转页，以防止连续绑定两次。
/// 2011-01-06 胡勇 增加对分页控件的初始化代码:public datatable initializepagecontrol()。
/// 2011-01-05 胡勇 创建分页控件
/// 2011-04-02 胡勇 优化代码、减少不必要的私有变量，去掉多余的代码
///
///
///
///
///
///
///
[toolboxitem(true)]
[defaultevent("oneventpageclicked")]
[toolboxbitmap(typeof(ucpagecontrolbycode), "images.ucpagecontrolbycodeicon.png")]
[description("分页控件（使用代码实现，不用存储过程）")]
public partial class ucpagecontrolbycode : usercontrol
{
#region 私有变量
int recordcount = 0; //记录数
int pagecount = 0; //总页数
int pageindex = 0; //当前页
#endregion
#region 自定义事件
///
/// 单击分页按钮(第一页、上一页、下一页、最后页、跳页)时发生
///
[category("ucpagecontrolbycode"), description("单击分页按钮时发生")]
public event eventhandler oneventpageclicked;
#endregion
#region 自定义属性
private int _pagesize = 50; //分页大小
private string _sqlwherestatement = string.empty; //mssql where语句
private string _sqlconnstring = string.empty; //mssql 数据库连接字符串
private string _tablename = string.empty; //表名
private string _orderfield = string.empty; //数据表的排序字段
private string _primarykey = string.empty; //数据表的主键
private string _queryfieldlist = "*"; //字段列表（默认为：*)
private datatable _pagetable = new datatable();
///
/// 返回当前页码
///
public int pageindex
{
get
{
return pageindex + 1;
}
}
///
/// 得到或设置分页大小(默认为:50)
///
[browsable(true), category("ucpagecontrolbycode"), description("得到或设置分页大小(默认为:50)")]
public int pagesize
{
get
{
return _pagesize;
}
set
{
_pagesize = value;
}
}
///
/// sql语句的where表达式
///
[browsable(false), category("ucpagecontrolbycode"), description("得到或设置sql语句的where表达式")]
public string sqlwherestatement
{
get
{
return _sqlwherestatement;
}
set
{
_sqlwherestatement = value;
}
}
///
/// 得到或设置sqlserver的连接字符串
///
[browsable(false), category("ucpagecontrolbycode"), description("得到或设置sqlserver的连接字符串")]
public string sqlconnstring
{
get
{
return _sqlconnstring;
}
set
{
_sqlconnstring = value;
}
}
///
/// 得到用户单击分页按钮后返回的datatable
///
[browsable(false), category("ucpagecontrolbycode"), description("得到用户单击分页按钮后返回的datatable")]
public datatable pagetable
{
get
{
return _pagetable;
}
}
///
/// 设置或得到与分页控件绑定的表名或视图名
///
[browsable(true), category("ucpagecontrolbycode"), description("设置或得到与分页控件绑定的表名或视图名")]
public string tablename
{
get
{
return _tablename;
}
set
{
_tablename = value;
}
}
///
/// 设置或得到分页控件排序字段
///
[browsable(true), category("ucpagecontrolbycode"), description("设置或得到分页控件排序字段")]
public string orderfield
{
get
{
return _orderfield;
}
set
{
_orderfield = value;
}
}
///
/// 设置或得到分页控件绑定数据表的主键
///
[browsable(true), category("ucpagecontrolbycode"), description("设置或得到分页控件绑定数据表的主键")]
public string primarykey
{
get
{
return _primarykey;
}
set
{
_primarykey = value;
}
}
///
/// 设置或得到分页控件绑定的字段列表(默认为:*)
///
[browsable(true), category("ucpagecontrolbycode"), description("设置或得到分页控件绑定的字段列表(默认为:*)")]
public string queryfieldlist
{
get
{
return _queryfieldlist;
}
set
{
_queryfieldlist = value;
}
}
#endregion
#region 构造函数
///
/// 分页控件（使用代码实现，不用存储过程）
///
public ucpagecontrolbycode()
{
initializecomponent();
}
#endregion
#region 分页实现相关代码
#region void setucpagecontrolpars(string connstr, string wherestatement, string tbname, string orderfield, string primarykeyname, string fieldlist):给ucpagecontrolbycode控件传递必需参数
///
/// 给ucpagecontrolbycode控件传递必需参数
///
/// 连接字符串
/// mssql where语句
/// 数据表名或视力名
/// 排序字段
/// 主键值
/// 字段列表(默认为:*)
public void setucpagecontrolpars(string connstr, string wherestatement, string tbname
, string orderfield, string primarykeyname, string fieldlist)
{
if (string.isnullorempty(connstr.trim()))
{
dialoghelper.showerrormsg("温馨提示：n无可用的数据库连接！");
return;
}
else
{
this.sqlconnstring = connstr;
}
this.sqlwherestatement = wherestatement;
this.tablename = tbname;
this.orderfield = orderfield;
this.primarykey = primarykeyname;
if (!string.isnullorempty(fieldlist.trim()))
{
this.queryfieldlist = fieldlist;
}
}
#endregion
#region datatable initializepagecontrol():初始化ucpagecontrolbycode
///
/// 绑定ucpagecontrolbycode(并返回包含当前页的datatable)
///
///
public datatable bindpagecontrol()
{
recordcount = gettotalrecordcount(); //获取总记录数
pagecount = recordcount / pagesize - modpage(); //保存总页数（减去modpage()函数防止sql语句执行时溢出查询范围，可以用存储过程分页算法来理解这句）
pageindex = 0; //保存一个为0的页面索引值到pageindex
lblpagecount.text = (recordcount / pagesize + overpage()).tostring();//显示lblpagecount、lblreccount的状态
lblreccount.text = recordcount.tostring();
if (recordcount <= pagesize)
{
txtgotopage.enabled = false;
}
else
{
txtgotopage.enabled = true;
}
return tdatabind();
}
#endregion
#region 余页计算与总记录数
///
/// 计算余页
///
///
private int overpage()
{
int returnvalue = 0;
if (recordcount % pagesize != 0)
{
returnvalue = 1;
}
return returnvalue;
}
///
/// 计算余页，防止sql语句执行时溢出查询范围
///
///
private int modpage()
{
int returnvalue = 0;
if (recordcount % pagesize == 0 && recordcount != 0)
{
returnvalue = 1;
}
return returnvalue;
}
///
/// 计算总记录数
///
///
private int gettotalrecordcount()
{
int returnvalue = 0;
string sqlstatement = "select count(1) as rowscount from " + tablename;
if (sqlwherestatement.trim().length > 0)
{
sqlstatement = "select count(1) as rowscount from " + tablename + " where " + sqlwherestatement;
}
sqldatareader dr = null;
try
{
dr = dbhelpersql.executereader(sqlstatement, sqlconnstring);
if (dr.read())
{
returnvalue = int32.parse(dr["rowscount"].tostring());
}
}
catch(exception ex)
{
dialoghelper.showerrormsg(ex.message);
}
finally
{
dr.close();
dr.dispose();
}
return returnvalue;
}
#endregion
#region datatable tdatabind():数据绑定
private datatable tdatabind()
{
stringbuilder sbsqlstatement = new stringbuilder();
bool isforward = pageindex + 1 > 1;
bool isbackward = (pageindex != pagecount);
btnfirstpage.enabled = isforward;
btnprevpage.enabled = isforward;
btnnextpage.enabled = isbackward;
btnlastpage.enabled = isbackward;
if (string.isnullorempty(sqlwherestatement.trim()))
{
sbsqlstatement.append("select top " + pagesize + " " + queryfieldlist + " from " + tablename + " where " + primarykey + " not in(select top ");
sbsqlstatement.append(pagesize * pageindex + " " + primarykey + " from " + tablename);
sbsqlstatement.append(" order by " + orderfield +" desc) order by " + orderfield + " desc");
}
else
{
sbsqlstatement.append("select top " + pagesize + " " + queryfieldlist + " from " + tablename + " where " + sqlwherestatement + " and " + primarykey + " not in(select top ");
sbsqlstatement.append(pagesize * pageindex + " " + primarykey + " from " + tablename + " where " + sqlwherestatement + " order by " + orderfield + " desc) order by " + orderfield + " desc");
}
_pagetable = dbhelpersql.query(sbsqlstatement.tostring(), sqlconnstring).tables[0];
lblcurrentpage.text = (pageindex + 1).tostring();
txtgotopage.text = (pageindex + 1).tostring();
return _pagetable;
}
#endregion
#region 按钮事件代码
private void btnfirstpage_click(object sender, eventargs e)
{
pageindex = 0;
_pagetable = tdatabind();
if (oneventpageclicked != null)
{
oneventpageclicked(this, null);
}
}
private void btnprevpage_click(object sender, eventargs e)
{
pageindex--;
_pagetable = tdatabind();
if (oneventpageclicked != null)
{
oneventpageclicked(this, null);
}
}
private void btnnextpage_click(object sender, eventargs e)
{
pageindex++;
_pagetable = tdatabind();
if (oneventpageclicked != null)
{
oneventpageclicked(this, null);
}
}
private void btnlastpage_click(object sender, eventargs e)
{
pageindex = pagecount;
_pagetable = tdatabind();
if (oneventpageclicked != null)
{
oneventpageclicked(this, null);
}
}
private void txtgotopage_keydown(object sender, keyeventargs e)
{
if (e.keycode == keys.enter)
{
try
{
if (int32.parse(txtgotopage.text) > (recordcount / pagesize + overpage()) || int32.parse(txtgotopage.text) <= 0)
{
dialoghelper.showwarningmsg("页码范围越界!");
txtgotopage.clear();
txtgotopage.focus();
}
else
{
pageindex = int32.parse(txtgotopage.text.tostring()) - 1;
_pagetable = tdatabind();
if (oneventpageclicked != null)
{
oneventpageclicked(this, null);
}
}
}
catch (exception ex) //捕获由用户输入不正确数据类型时造成的异常
{
dialoghelper.showwarningmsg(ex.message);
txtgotopage.clear();
txtgotopage.focus();
}
}
}
#endregion
#endregion
}
}
分页控件使用实例
客户端使用代码如下：
view code
1 using system;
2 using system.collections.generic;
3 using system.componentmodel;
4 using system.data;
5 using system.drawing;
6 using system.configuration;
7 using dotnet.controls;
8 using system.text;
9 using system.windows.forms;
10 using dotnet.common;
11 using dotnet.winform.utilities;
12
13 namespace dotnet.winform.example
14 {
15 public partial class frmucpagecontrolbycodetest : form
16 {
17 public frmucpagecontrolbycodetest()
18 {
19 initializecomponent();
20 }
21
22 private void frmucpagecontrolbycodetest_shown(object sender, eventargs e)
23 {
24 //初始化方法一
25 //ucpagecontrolbycode.sqlconnstring = configurationsettings.apps教程ettings["dbconnection"];
26 //ucpagecontrolbycode.sqlwherestatement = "1=1";
27 //ucpagecontrolbycode.tablename = "tbtestdata";
28 //ucpagecontrolbycode.orderfield = "uniqueid";
29 //ucpagecontrolbycode.primarykey = "uniqueid";
30 //ucpagecontrolbycode.queryfieldlist = "*";
31
32 //初始化方法二
33 ucpagecontrolbycode.setucpagecontrolpars(configurationsettings.appsettings["dbconnection"], "1=1", "tbtestdata",
34 "uniqueid", "uniqueid", "*");
35 datatable dttest = new datatable();
36 dttest = ucpagecontrolbycode.bindpagecontrol();
37 ucdatagridview.datasource = dttest;
38
39 //绑定查询项
40 dictionary
41 foreach (datacolumn dc in dttest.columns)
42 {
43 diclistqueryitems.add(dc.columnname, dc.datatype.tostring());
44 }
45 uccombinquery1.setqueryitems(diclistqueryitems);
46 }
47
48 private void ucdatagridview_databindingcomplete(object sender, datagridviewbindingcompleteeventargs e)
49 {
50 gbmain.text = "当前共:" + ucdatagridview.rows.count.tostring() + "条数据。";
51 }
52
53 private void ucpagecontrolbycode_oneventpageclicked(object sender, eventargs e)
54 {
55 ucdatagridview.datasource = null;
56 ucdatagridview.datasource = ucpagecontrolbycode.pagetable;
57 }
58
59 private void uccombinquery1_onqueryclicked(object sender, eventargs e)
60 {
61 try
62 {
63 splasher.show(typeof(frmsplash));
64 splasher.status = "正在查找数据，请稍等...";
65 system.threading.thread.sleep(450);
66 ucdatagridview.datasource = null;
67 ucpagecontrolbycode.sqlwherestatement = uccombinquery1.queryexpression; //指定查询表达式
68 ucdatagridview.datasource = ucpagecontrolbycode.bindpagecontrol(); //绑定datagridview
69 }
70 catch (exception ex)
71 {
72 ucpagecontrolbycode.sqlwherestatement = "1<>1";
73 ucdatagridview.datasource = ucpagecontrolbycode.bindpagecontrol(); //绑定datagridview
74 splasher.status = ex.message;
75 system.threading.thread.sleep(1000);
76 }
77 finally
78 {
79 system.threading.thread.sleep(100);
80 splasher.status = "查找完毕...";
81 splasher.close();
82 }
83 }
84 }
85 }
