再上数据分页控件（不用存储过程）
本文共 14112 字，大约阅读时间需要 47 分钟。
本篇主要内容如下：
· 概述
· 本分页控件原理。
· 分页控件代码。
· 分页控件使用实例。
概述
在前几篇：我介绍了，这个控件主要是通过存储过程进行数据分页，得到了大家的支持，也给出了许多宝贵的建议，在这儿先感谢各位。同时也让我更有信心进行以后的文章（企业级控件库系列）。
分页对于每个项目来说都有它存在的意义，想起在以前刚刚刚软件开发时，由于刚刚毕业，理论知识雄厚，但实际工作经验欠缺，记得几年前做开发时，数据量很大，要用分页，一开始真不知道如何是好，方法到知道，但速度与稳定性却没有经验。在这儿，我只是起到一个抛砖引玉的作用，以便引起我们在实际工作中要多思考，对同一件事多想几种解决方式，只有这样才能不断提高，同时要学会总结。
这篇我将给大家介绍：不用存储过程，直接用代码来实现数据分页，欢迎大家拍砖，觉得不错的可以推荐下。同时，你要用什么好的想法，也可以与我交流，谢谢。
本分页控件原理
分页的方法很多，有用存储过程的，有不用存储过程的，还有在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 > 11 ( 12 SELECT ISNULL(MAX(id),0) FROM
(SELECT TOP 50 * ( 8-1 ) UniqueID FROM tbTestData ORDER BY UniqueID) 13 ) 14 ORDER BY UniqueID
原理就这么简单。
分页控件代码
（一）、实例数据库代码
创建实例数据库。
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 { /// <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 } }
分页控件使用实例
客户端使用代码如下：
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 }
最后的效果如下：
本文转自yonghu86 51CTO博客，原文链接：http://blog.51cto.com/yonghu/1321362，如需转载请自行联系原作者
你可能感兴趣的文章
安卓.9图片制作
查看>>
MySQL 高可用性keepalived+mysql双主
查看>>
Python环境安装及数据基本预处理-大数据ML样本集案例实战
查看>>
【详解】TiDB 2.0 GA is here !
查看>>
iOS开发-模拟网络环境
查看>>
Redux执行流程梳理
查看>>
iOS 指纹识别
查看>>
说说 Vue.js 组件
查看>>
iPhone 用USB连接SSH的时候一直报错
查看>>
关于Vuex的action传入多个参数的问题
查看>>
放弃jQuery, 使用原生js
查看>>
跨越适配&性能那道坎，企鹅电竞Android weex优化
查看>>
一文读懂鼠标滚轮事件（wheelEvent）
查看>>
腾讯云国内节点centos7.2安装k8sv1.12.3
查看>>
Python爬虫--- 1.5 爬虫实践： 获取百度贴吧内容
查看>>
解决Shell脚本$'\r': command not found问题
查看>>
ionic3使用百度地图
查看>>
JavaWEB开发11——JSP
查看>>
轻松搞定javascript中this的指向
查看>>
每天一个设计模式之单例模式
查看>>