我介绍了原创企业级控件库之大数据据量分页控件，这个控件主要是通过存储过程进行数据分页，得到了大家的支持，也给出了许多宝贵的建议，在这儿先感谢各位。同时也让我更有信心进行以后的文章（企业级控件库系列）。
分页对于每个项目来说都有它存在的意义，想起在以前刚刚刚软件开发时，由于刚刚毕业，理论知识雄厚，但实际工作经验欠缺，记得几年前做开发时，数据量很大，要用分页，一开始真不知道如何是好，方法到知道，但速度与稳定性却没有经验。在这儿，我只是起到一个抛砖引玉的作用，以便引起我们在实际工作中要多思考，对同一件事多想几种解决方式，只有这样才能不断提高，同时要学会总结。
这篇我将给大家介绍：不用存储过程，直接用代码来实现数据分页，欢迎大家拍砖，觉得不错的可以推荐下。同时，你要用什么好的想法，也可以与我交流，谢谢。
本分页控件原理
分页的方法很多，有用存储过程的，有不用存储过程的，还有在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
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
[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
{
/// <summary>
/// 分页控件（使用代码实现，不用存储过程）
/// UcPageControlByCode
/// 修改纪录
///
/// 2010-01-06 胡勇 修改转到某页由原来的KeyPress方法改为KeyDown，让用户按回车键确认转页，以防止连续绑定两次。
/// 2011-01-06 胡勇 增加对分页控件的初始化代码:public DataTable InitializePageControl()。
/// 2011-01-05 胡勇 创建分页控件
/// 2011-04-02 胡勇 优化代码、减少不必要的私有变量，去掉多余的代码
///
/// <author>
/// <name>胡勇</name>
/// <QQ>80368704</QQ>
/// <Email>80368704@qq.com</Email>
/// </author>
/// </summary>
[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 自定义事件
/// <summary>
/// 单击分页按钮(第一页、上一页、下一页、最后页、跳页)时发生
/// </summary>
[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();
/// <summary>
/// 返回当前页码
/// </summary>
public int PageIndex
{
get
{
return pageIndex + 1;
}
}
/// <summary>
/// 得到或设置分页大小(默认为:50)
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("得到或设置分页大小(默认为:50)")]
public int PageSize
{
get
{
return _pageSize;
}
set
{
_pageSize = value;
}
}
/// <summary>
/// SQL语句的Where表达式
/// </summary>
[Browsable(false), Category("UcPageControlByCode"), Description("得到或设置SQL语句的Where表达式")]
public string SqlWhereStatement
{
get
{
return _sqlWhereStatement;
}
set
{
_sqlWhereStatement = value;
}
}
/// <summary>
/// 得到或设置SqlServer的连接字符串
/// </summary>
[Browsable(false), Category("UcPageControlByCode"), Description("得到或设置SqlServer的连接字符串")]
public string SqlConnString
{
get
{
return _sqlConnString;
}
set
{
_sqlConnString = value;
}
}
/// <summary>
/// 得到用户单击分页按钮后返回的DataTable
/// </summary>
[Browsable(false), Category("UcPageControlByCode"), Description("得到用户单击分页按钮后返回的DataTable")]
public DataTable PageTable
{
get
{
return _pageTable;
}
}
/// <summary>
/// 设置或得到与分页控件绑定的表名或视图名
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("设置或得到与分页控件绑定的表名或视图名")]
public string TableName
{
get
{
return _tableName;
}
set
{
_tableName = value;
}
}
/// <summary>
/// 设置或得到分页控件排序字段
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("设置或得到分页控件排序字段")]
public string OrderField
{
get
{
return _orderField;
}
set
{
_orderField = value;
}
}
/// <summary>
/// 设置或得到分页控件绑定数据表的主键
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("设置或得到分页控件绑定数据表的主键")]
public string PrimaryKey
{
get
{
return _primaryKey;
}
set
{
_primaryKey = value;
}
}
/// <summary>
/// 设置或得到分页控件绑定的字段列表(默认为:*)
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("设置或得到分页控件绑定的字段列表(默认为:*)")]
public string QueryFieldList
{
get
{
return _queryFieldList;
}
set
{
_queryFieldList = value;
}
}
#endregion
#region 构造函数
/// <summary>
/// 分页控件（使用代码实现，不用存储过程）
/// </summary>
public UcPageControlByCode()
{
InitializeComponent();
}
#endregion
#region 分页实现相关代码
#region void SetUcPageControlPars(string connStr, string whereStatement, string tbName, string orderField, string primaryKeyName, string fieldList):给UcPageControlByCode控件传递必需参数
/// <summary>
/// 给UcPageControlByCode控件传递必需参数
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="whereStatement">MsSql Where语句 </param>
/// <param name="tbName">数据表名或视力名</param>
/// <param name="orderField">排序字段</param>
/// <param name="primaryKeyName">主键值</param>
/// <param name="fieldList">字段列表(默认为:*)</param>
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
/// <summary>
/// 绑定UcPageControlByCode(并返回包含当前页的DataTable)
/// </summary>
/// <returns>DataTable</returns>
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
{
/// <summary>
/// 分页控件（使用代码实现，不用存储过程）
/// UcPageControlByCode
/// 修改纪录
///
/// 2010-01-06 胡勇 修改转到某页由原来的KeyPress方法改为KeyDown，让用户按回车键确认转页，以防止连续绑定两次。
/// 2011-01-06 胡勇 增加对分页控件的初始化代码:public DataTable InitializePageControl()。
/// 2011-01-05 胡勇 创建分页控件
/// 2011-04-02 胡勇 优化代码、减少不必要的私有变量，去掉多余的代码
///
/// <author>
/// <name>胡勇</name>
/// <QQ>80368704</QQ>
/// <Email>80368704@qq.com</Email>
/// </author>
/// </summary>
[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 自定义事件
/// <summary>
/// 单击分页按钮(第一页、上一页、下一页、最后页、跳页)时发生
/// </summary>
[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();
/// <summary>
/// 返回当前页码
/// </summary>
public int PageIndex
{
get
{
return pageIndex + 1;
}
}
/// <summary>
/// 得到或设置分页大小(默认为:50)
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("得到或设置分页大小(默认为:50)")]
public int PageSize
{
get
{
return _pageSize;
}
set
{
_pageSize = value;
}
}
/// <summary>
/// SQL语句的Where表达式
/// </summary>
[Browsable(false), Category("UcPageControlByCode"), Description("得到或设置SQL语句的Where表达式")]
public string SqlWhereStatement
{
get
{
return _sqlWhereStatement;
}
set
{
_sqlWhereStatement = value;
}
}
/// <summary>
/// 得到或设置SqlServer的连接字符串
/// </summary>
[Browsable(false), Category("UcPageControlByCode"), Description("得到或设置SqlServer的连接字符串")]
public string SqlConnString
{
get
{
return _sqlConnString;
}
set
{
_sqlConnString = value;
}
}
/// <summary>
/// 得到用户单击分页按钮后返回的DataTable
/// </summary>
[Browsable(false), Category("UcPageControlByCode"), Description("得到用户单击分页按钮后返回的DataTable")]
public DataTable PageTable
{
get
{
return _pageTable;
}
}
/// <summary>
/// 设置或得到与分页控件绑定的表名或视图名
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("设置或得到与分页控件绑定的表名或视图名")]
public string TableName
{
get
{
return _tableName;
}
set
{
_tableName = value;
}
}
/// <summary>
/// 设置或得到分页控件排序字段
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("设置或得到分页控件排序字段")]
public string OrderField
{
get
{
return _orderField;
}
set
{
_orderField = value;
}
}
/// <summary>
/// 设置或得到分页控件绑定数据表的主键
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("设置或得到分页控件绑定数据表的主键")]
public string PrimaryKey
{
get
{
return _primaryKey;
}
set
{
_primaryKey = value;
}
}
/// <summary>
/// 设置或得到分页控件绑定的字段列表(默认为:*)
/// </summary>
[Browsable(true), Category("UcPageControlByCode"), Description("设置或得到分页控件绑定的字段列表(默认为:*)")]
public string QueryFieldList
{
get
{
return _queryFieldList;
}
set
{
_queryFieldList = value;
}
}
#endregion
#region 构造函数
/// <summary>
/// 分页控件（使用代码实现，不用存储过程）
/// </summary>
public UcPageControlByCode()
{
InitializeComponent();
}
#endregion
#region 分页实现相关代码
#region void SetUcPageControlPars(string connStr, string whereStatement, string tbName, string orderField, string primaryKeyName, string fieldList):给UcPageControlByCode控件传递必需参数
/// <summary>
/// 给UcPageControlByCode控件传递必需参数
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="whereStatement">MsSql Where语句 </param>
/// <param name="tbName">数据表名或视力名</param>
/// <param name="orderField">排序字段</param>
/// <param name="primaryKeyName">主键值</param>
/// <param name="fieldList">字段列表(默认为:*)</param>
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
/// <summary>
/// 绑定UcPageControlByCode(并返回包含当前页的DataTable)
/// </summary>
/// <returns>DataTable</returns>
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 余页计算与总记录数
/// <summary>
/// 计算余页
/// </summary>
/// <returns></returns>
private int OverPage()
{
int returnValue = 0;
if (recordCount % PageSize != 0)
{
returnValue = 1;
}
return returnValue;
}
/// <summary>
/// 计算余页，防止SQL语句执行时溢出查询范围
/// </summary>
/// <returns></returns>
private int ModPage()
{
int returnValue = 0;
if (recordCount % PageSize == 0 && recordCount != 0)
{
returnValue = 1;
}
return returnValue;
}
/// <summary>
/// 计算总记录数
/// </summary>
/// <returns>记录总数</returns>
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
}
}
/// <summary>
/// 计算余页
/// </summary>
/// <returns></returns>
private int OverPage()
{
int returnValue = 0;
if (recordCount % PageSize != 0)
{
returnValue = 1;
}
return returnValue;
}
/// <summary>
/// 计算余页，防止SQL语句执行时溢出查询范围
/// </summary>
/// <returns></returns>
private int ModPage()
{
int returnValue = 0;
if (recordCount % PageSize == 0 && recordCount != 0)
{
returnValue = 1;
}
return returnValue;
}
/// <summary>
/// 计算总记录数
/// </summary>
/// <returns>记录总数</returns>
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.AppSettings["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<string, string> dicListQueryItems = new Dictionary<string, string>();
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 }
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.AppSettings["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<string, string> dicListQueryItems = new Dictionary<string, string>();
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 }
最后的效果如下：
本篇主要内容如下：
- 概述
- 本分页控件原理。
- 分页控件代码。
- 分页控件使用实例。
------------------------------------------------
cnBlobs:http://www.cnblogs.com/huyong/
CSDN：http://blog.csdn.net/chinahuyong
