在上篇：我介绍了原创企业级控件库之组合查询控件，这篇我将给大家介绍：企业级控件库之大数据量分页控件。
摘要
说到分页，大家采用的方法各有千秋，分页在一个中大型软件项目中对数据的快速呈现起到很关键的作用，试想一个数据量上几十万或者几百万的数据表，要是没有分页功能会是一个什么样的效果。总的说来，大家采用的分页方法大同小异，但到底那种方法才是最佳的呢，各有各的看法，让数据说话最有效。今天我给大家分享一个WinForm下大数据量分页控件（当然分页思想也可用于WebForm）。虽然不能说是最佳的，但在我的几个实际项目中，用的都是它，效果不错，可放心使用。
成就别人、等于成就自己。我没什么要求，欢迎大家多多支持与评论，觉得不错的，记得点击文章左下角的”关注博客”，就这么简单。同时，你要用什么好的想法，也可以与我交流，谢谢。
分页控件运行效果如下图：
用到的分页存储过程:
代码
1
--
=============================================
2 -- Author: EricHu QQ:80368704 WebSite:http://www.cnblogs.com/huyong/
3 -- Create date: 2008-10-25
4 -- Description: 千万数量级分页存储过程
5 -- Modify Date: 2010-10-26
6 -- =============================================
7
8 SET ANSI_NULLS ON
9 GO
10 SET QUOTED_IDENTIFIER ON
11 GO
12
13 ALTER PROCEDURE [ dbo ] . [ uspDividePage ]
14 /*
15 ***************************************************************
16 ** 千万数量级分页存储过程**
17 ***************************************************************
18 参数说明:
19 1.Tables :表名或视图名
20 2.PrimaryKey :主关键字
21 3.Sort :排序语句，不带Order By 比如：UserId Desc,CreateDate Asc
22 4.CurrentPage :当前页码
23 5.PageSize :分页尺寸
24 6.Fields :字段列表(默认为:*)
25 7.Filter :过滤语句，不带Where
26 8.Group :Group语句,不带Group By
27 ************************************************************** */
28 (
29 @Tables varchar ( 2000 ),
30 @PrimaryKey varchar ( 500 ),
31 @Sort varchar ( 500 ) = NULL ,
32 @CurrentPage int = 1 ,
33 @PageSize int = 10 ,
34 @Fields varchar ( 2000 ) = ' * ' ,
35 @Filter varchar ( 1000 ) = NULL ,
36 @Group varchar ( 1000 ) = NULL
37 )
38 AS
39 /* 默认排序 */
40 IF @Sort IS NULL OR @Sort = ''
41 SET @Sort = @PrimaryKey
42
43 DECLARE @SortTable varchar ( 1000 )
44 DECLARE @SortName varchar ( 1000 )
45 DECLARE @strSortColumn varchar ( 1000 )
46 DECLARE @operator char ( 2 )
47 DECLARE @type varchar ( 1000 )
48 DECLARE @prec int
49
50 /* 设定排序语句. */
51 IF CHARINDEX ( ' DESC ' , @Sort ) > 0
52 BEGIN
53 SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
54 SET @operator = ' <= '
55 END
56 ELSE
57 BEGIN
58 IF CHARINDEX ( ' ASC ' , @Sort ) = 0
59 SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
60 SET @operator = ' >= '
61 END
62
63 IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
64 BEGIN
65 SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
66 SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
67 END
68 ELSE
69 BEGIN
70 SET @SortTable = @Tables
71 SET @SortName = @strSortColumn
72 END
73
74 SELECT @type = t.name, @prec = c.prec
75 FROM sysobjects o
76 JOIN syscolumns c on o.id = c.id
77 JOIN systypes t on c.xusertype = t.xusertype
78 WHERE o.name = @SortTable AND c.name = @SortName
79
80 IF CHARINDEX ( ' char ' , @type ) > 0
81 SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
82
83 DECLARE @strPageSize varchar ( 500 )
84 DECLARE @strStartRow varchar ( 500 )
85 DECLARE @strFilter varchar ( 1000 )
86 DECLARE @strSimpleFilter varchar ( 1000 )
87 DECLARE @strGroup varchar ( 1000 )
88 /* 默认当前页 */
89 IF @CurrentPage < 1
90 SET @CurrentPage = 1
91
92 /* 设置分页参数. */
93 SET @strPageSize = CAST ( @PageSize AS varchar ( 500 ))
94 SET @strStartRow = CAST ((( @CurrentPage - 1 ) * @PageSize + 1 ) AS varchar ( 500 ))
95
96 /* 筛选以及分组语句. */
97 IF @Filter IS NOT NULL AND @Filter != ''
98 BEGIN
99 SET @strFilter = ' WHERE ' + @Filter + ' '
100 SET @strSimpleFilter = ' AND ' + @Filter + ' '
101 END
102 ELSE
103 BEGIN
104 SET @strSimpleFilter = ''
105 SET @strFilter = ''
106 END
107
108 IF @Group IS NOT NULL AND @Group != ''
109 SET @strGroup = ' GROUP BY ' + @Group + ' '
110 ELSE
111 SET @strGroup = ''
112
113 /* 执行查询语句 */
114 EXEC (
115 '
116 DECLARE @SortColumn ' + @type + '
117 SET ROWCOUNT ' + @strStartRow + '
118 SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
119 SET ROWCOUNT ' + @strPageSize + '
120 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
121 ' )
122 GO
123
124 SET ANSI_NULLS OFF
125 GO
126 SET QUOTED_IDENTIFIER OFF
127 GO
128
2 -- Author: EricHu QQ:80368704 WebSite:http://www.cnblogs.com/huyong/
3 -- Create date: 2008-10-25
4 -- Description: 千万数量级分页存储过程
5 -- Modify Date: 2010-10-26
6 -- =============================================
7
8 SET ANSI_NULLS ON
9 GO
10 SET QUOTED_IDENTIFIER ON
11 GO
12
13 ALTER PROCEDURE [ dbo ] . [ uspDividePage ]
14 /*
15 ***************************************************************
16 ** 千万数量级分页存储过程**
17 ***************************************************************
18 参数说明:
19 1.Tables :表名或视图名
20 2.PrimaryKey :主关键字
21 3.Sort :排序语句，不带Order By 比如：UserId Desc,CreateDate Asc
22 4.CurrentPage :当前页码
23 5.PageSize :分页尺寸
24 6.Fields :字段列表(默认为:*)
25 7.Filter :过滤语句，不带Where
26 8.Group :Group语句,不带Group By
27 ************************************************************** */
28 (
29 @Tables varchar ( 2000 ),
30 @PrimaryKey varchar ( 500 ),
31 @Sort varchar ( 500 ) = NULL ,
32 @CurrentPage int = 1 ,
33 @PageSize int = 10 ,
34 @Fields varchar ( 2000 ) = ' * ' ,
35 @Filter varchar ( 1000 ) = NULL ,
36 @Group varchar ( 1000 ) = NULL
37 )
38 AS
39 /* 默认排序 */
40 IF @Sort IS NULL OR @Sort = ''
41 SET @Sort = @PrimaryKey
42
43 DECLARE @SortTable varchar ( 1000 )
44 DECLARE @SortName varchar ( 1000 )
45 DECLARE @strSortColumn varchar ( 1000 )
46 DECLARE @operator char ( 2 )
47 DECLARE @type varchar ( 1000 )
48 DECLARE @prec int
49
50 /* 设定排序语句. */
51 IF CHARINDEX ( ' DESC ' , @Sort ) > 0
52 BEGIN
53 SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
54 SET @operator = ' <= '
55 END
56 ELSE
57 BEGIN
58 IF CHARINDEX ( ' ASC ' , @Sort ) = 0
59 SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
60 SET @operator = ' >= '
61 END
62
63 IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
64 BEGIN
65 SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
66 SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
67 END
68 ELSE
69 BEGIN
70 SET @SortTable = @Tables
71 SET @SortName = @strSortColumn
72 END
73
74 SELECT @type = t.name, @prec = c.prec
75 FROM sysobjects o
76 JOIN syscolumns c on o.id = c.id
77 JOIN systypes t on c.xusertype = t.xusertype
78 WHERE o.name = @SortTable AND c.name = @SortName
79
80 IF CHARINDEX ( ' char ' , @type ) > 0
81 SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
82
83 DECLARE @strPageSize varchar ( 500 )
84 DECLARE @strStartRow varchar ( 500 )
85 DECLARE @strFilter varchar ( 1000 )
86 DECLARE @strSimpleFilter varchar ( 1000 )
87 DECLARE @strGroup varchar ( 1000 )
88 /* 默认当前页 */
89 IF @CurrentPage < 1
90 SET @CurrentPage = 1
91
92 /* 设置分页参数. */
93 SET @strPageSize = CAST ( @PageSize AS varchar ( 500 ))
94 SET @strStartRow = CAST ((( @CurrentPage - 1 ) * @PageSize + 1 ) AS varchar ( 500 ))
95
96 /* 筛选以及分组语句. */
97 IF @Filter IS NOT NULL AND @Filter != ''
98 BEGIN
99 SET @strFilter = ' WHERE ' + @Filter + ' '
100 SET @strSimpleFilter = ' AND ' + @Filter + ' '
101 END
102 ELSE
103 BEGIN
104 SET @strSimpleFilter = ''
105 SET @strFilter = ''
106 END
107
108 IF @Group IS NOT NULL AND @Group != ''
109 SET @strGroup = ' GROUP BY ' + @Group + ' '
110 ELSE
111 SET @strGroup = ''
112
113 /* 执行查询语句 */
114 EXEC (
115 '
116 DECLARE @SortColumn ' + @type + '
117 SET ROWCOUNT ' + @strStartRow + '
118 SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
119 SET ROWCOUNT ' + @strPageSize + '
120 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
121 ' )
122 GO
123
124 SET ANSI_NULLS OFF
125 GO
126 SET QUOTED_IDENTIFIER OFF
127 GO
128
本控件类图
本控件UcpageControl类详细信息
本控件PageData类详细信息
本控件核心代码
一、数据源提供类PageData,主要负责与存储过程进行交互。
数据源提供(PageData)
1
#region
数据源提供(PageData)
2 /// <summary>
3 /// 数据源提供
4 /// </summary>
5 public class PageData
6 {
7 DataSet ds = null ;
8 private int _PageSize = 50 ; // 分页大小
9 private int _PageIndex = 1 ; // 当前页
10 private int _PageCount = 0 ; // 总页数
11 private int _TotalCount = 0 ; // 总记录数
12 private string _QueryFieldName = " * " ; // 表字段FieldStr
13 private bool _isQueryTotalCounts = true ; // 是否查询总的记录条数
14 private string _TableName = string .Empty; // 表名
15 private string _OrderStr = string .Empty; // 排序_SortStr
16 private string _QueryCondition = string .Empty; // 查询的条件 RowFilter
17 private string _PrimaryKey = string .Empty; // 主键
18
19 /// <summary>
20 /// 是否查询总的记录条数
21 /// </summary>
22 public bool IsQueryTotalCounts
23 {
24 get { return _isQueryTotalCounts; }
25 set { _isQueryTotalCounts = value; }
26 }
27
28 /// <summary>
29 /// 分页大小(每页显示多少条数据)
30 /// </summary>
31 public int PageSize
32 {
33 get
34 {
35 return _PageSize;
36
37 }
38 set
39 {
40 _PageSize = value;
41 }
42 }
43
44 /// <summary>
45 /// 当前页
46 /// </summary>
47 public int PageIndex
48 {
49 get
50 {
51 return _PageIndex;
52 }
53 set
54 {
55 _PageIndex = value;
56 }
57 }
58
59 /// <summary>
60 /// 总页数
61 /// </summary>
62 public int PageCount
63 {
64 get
65 {
66 return _PageCount;
67 }
68 }
69
70 /// <summary>
71 /// 总记录数
72 /// </summary>
73 public int TotalCount
74 {
75 get
76 {
77 return _TotalCount;
78 }
79 }
80
81 /// <summary>
82 /// 表名或视图名
83 /// </summary>
84 public string TableName
85 {
86 get
87 {
88 return _TableName;
89 }
90 set
91 {
92 _TableName = value;
93 }
94 }
95
96 /// <summary>
97 /// 表字段FieldStr
98 /// </summary>
99 public string QueryFieldName
100 {
101 get
102 {
103 return _QueryFieldName;
104 }
105 set
106 {
107 _QueryFieldName = value;
108 }
109 }
110
111 /// <summary>
112 /// 排序字段
113 /// </summary>
114 public string OrderStr
115 {
116 get
117 {
118 return _OrderStr;
119 }
120 set
121 {
122 _OrderStr = value;
123 }
124 }
125
126 /// <summary>
127 /// 查询条件
128 /// </summary>
129 public string QueryCondition
130 {
131 get
132 {
133 return _QueryCondition;
134 }
135 set
136 {
137 _QueryCondition = value;
138 }
139 }
140
141 /// <summary>
142 /// 主键
143 /// </summary>
144 public string PrimaryKey
145 {
146 get
147 {
148 return _PrimaryKey;
149 }
150 set
151 {
152 _PrimaryKey = value;
153 }
154 }
155
156 /// <summary>
157 /// 得到分页数据
158 /// </summary>
159 /// <param name="connectionstring"> 连接字符串 </param>
160 /// <returns> DataSet </returns>
161 public DataSet QueryDataTable( string connectionstring)
162 {
163 SqlParameter[] parameters = {
164 new SqlParameter( " @Tables " , SqlDbType.VarChar, 255 ),
165 new SqlParameter( " @PrimaryKey " , SqlDbType.VarChar , 255 ),
166 new SqlParameter( " @Sort " , SqlDbType.VarChar , 255 ),
167 new SqlParameter( " @CurrentPage " , SqlDbType.Int ),
168 new SqlParameter( " @PageSize " , SqlDbType.Int ),
169 new SqlParameter( " @Fields " , SqlDbType.VarChar, 255 ),
170 new SqlParameter( " @Filter " , SqlDbType.VarChar, 1000 ),
171 new SqlParameter( " @Group " , SqlDbType.VarChar, 1000 )
172 };
173 parameters[ 0 ].Value = _TableName;
174 parameters[ 1 ].Value = _PrimaryKey;
175 parameters[ 2 ].Value = _OrderStr;
176 parameters[ 3 ].Value = PageIndex;
177 parameters[ 4 ].Value = PageSize;
178 parameters[ 5 ].Value = _QueryFieldName;
179 parameters[ 6 ].Value = _QueryCondition;
180 parameters[ 7 ].Value = string .Empty;
181 ds = null ;
182 ds = new DataSet();
183 ds = DbHelperSQL.RunProcedure(connectionstring, " uspDividePage " , parameters, " tbPageData " );
184
185 if (_isQueryTotalCounts)
186 {
187 _TotalCount = GetTotalCount(connectionstring);
188 }
189
190 if (_TotalCount == 0 )
191 {
192 _PageIndex = 0 ;
193 _PageCount = 0 ;
194 }
195 else
196 {
197 _PageCount = _TotalCount % _PageSize == 0 ? _TotalCount / _PageSize : _TotalCount / _PageSize + 1 ;
198
199 if (_PageIndex > _PageCount)
200 {
201 _PageIndex = _PageCount;
202 parameters[ 4 ].Value = _PageSize;
203 ds = QueryDataTable(connectionstring);
204 }
205 }
206
207 return ds;
208 }
209
210 /// <summary>
211 /// 得到总的记录数
212 /// </summary>
213 /// <param name="connectionstring"> 连接字符串 </param>
214 /// <returns> 总的记录数 </returns>
215 public int GetTotalCount( string connectionstring)
216 {
217 string strSql = " select count(1) from " + _TableName;
218
219 if (_QueryCondition != string .Empty)
220 {
221 strSql += " where " + _QueryCondition;
222 }
223
224 return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString(), connectionstring));
225 }
226 }
227 #endregion
2 /// <summary>
3 /// 数据源提供
4 /// </summary>
5 public class PageData
6 {
7 DataSet ds = null ;
8 private int _PageSize = 50 ; // 分页大小
9 private int _PageIndex = 1 ; // 当前页
10 private int _PageCount = 0 ; // 总页数
11 private int _TotalCount = 0 ; // 总记录数
12 private string _QueryFieldName = " * " ; // 表字段FieldStr
13 private bool _isQueryTotalCounts = true ; // 是否查询总的记录条数
14 private string _TableName = string .Empty; // 表名
15 private string _OrderStr = string .Empty; // 排序_SortStr
16 private string _QueryCondition = string .Empty; // 查询的条件 RowFilter
17 private string _PrimaryKey = string .Empty; // 主键
18
19 /// <summary>
20 /// 是否查询总的记录条数
21 /// </summary>
22 public bool IsQueryTotalCounts
23 {
24 get { return _isQueryTotalCounts; }
25 set { _isQueryTotalCounts = value; }
26 }
27
28 /// <summary>
29 /// 分页大小(每页显示多少条数据)
30 /// </summary>
31 public int PageSize
32 {
33 get
34 {
35 return _PageSize;
36
37 }
38 set
39 {
40 _PageSize = value;
41 }
42 }
43
44 /// <summary>
45 /// 当前页
46 /// </summary>
47 public int PageIndex
48 {
49 get
50 {
51 return _PageIndex;
52 }
53 set
54 {
55 _PageIndex = value;
56 }
57 }
58
59 /// <summary>
60 /// 总页数
61 /// </summary>
62 public int PageCount
63 {
64 get
65 {
66 return _PageCount;
67 }
68 }
69
70 /// <summary>
71 /// 总记录数
72 /// </summary>
73 public int TotalCount
74 {
75 get
76 {
77 return _TotalCount;
78 }
79 }
80
81 /// <summary>
82 /// 表名或视图名
83 /// </summary>
84 public string TableName
85 {
86 get
87 {
88 return _TableName;
89 }
90 set
91 {
92 _TableName = value;
93 }
94 }
95
96 /// <summary>
97 /// 表字段FieldStr
98 /// </summary>
99 public string QueryFieldName
100 {
101 get
102 {
103 return _QueryFieldName;
104 }
105 set
106 {
107 _QueryFieldName = value;
108 }
109 }
110
111 /// <summary>
112 /// 排序字段
113 /// </summary>
114 public string OrderStr
115 {
116 get
117 {
118 return _OrderStr;
119 }
120 set
121 {
122 _OrderStr = value;
123 }
124 }
125
126 /// <summary>
127 /// 查询条件
128 /// </summary>
129 public string QueryCondition
130 {
131 get
132 {
133 return _QueryCondition;
134 }
135 set
136 {
137 _QueryCondition = value;
138 }
139 }
140
141 /// <summary>
142 /// 主键
143 /// </summary>
144 public string PrimaryKey
145 {
146 get
147 {
148 return _PrimaryKey;
149 }
150 set
151 {
152 _PrimaryKey = value;
153 }
154 }
155
156 /// <summary>
157 /// 得到分页数据
158 /// </summary>
159 /// <param name="connectionstring"> 连接字符串 </param>
160 /// <returns> DataSet </returns>
161 public DataSet QueryDataTable( string connectionstring)
162 {
163 SqlParameter[] parameters = {
164 new SqlParameter( " @Tables " , SqlDbType.VarChar, 255 ),
165 new SqlParameter( " @PrimaryKey " , SqlDbType.VarChar , 255 ),
166 new SqlParameter( " @Sort " , SqlDbType.VarChar , 255 ),
167 new SqlParameter( " @CurrentPage " , SqlDbType.Int ),
168 new SqlParameter( " @PageSize " , SqlDbType.Int ),
169 new SqlParameter( " @Fields " , SqlDbType.VarChar, 255 ),
170 new SqlParameter( " @Filter " , SqlDbType.VarChar, 1000 ),
171 new SqlParameter( " @Group " , SqlDbType.VarChar, 1000 )
172 };
173 parameters[ 0 ].Value = _TableName;
174 parameters[ 1 ].Value = _PrimaryKey;
175 parameters[ 2 ].Value = _OrderStr;
176 parameters[ 3 ].Value = PageIndex;
177 parameters[ 4 ].Value = PageSize;
178 parameters[ 5 ].Value = _QueryFieldName;
179 parameters[ 6 ].Value = _QueryCondition;
180 parameters[ 7 ].Value = string .Empty;
181 ds = null ;
182 ds = new DataSet();
183 ds = DbHelperSQL.RunProcedure(connectionstring, " uspDividePage " , parameters, " tbPageData " );
184
185 if (_isQueryTotalCounts)
186 {
187 _TotalCount = GetTotalCount(connectionstring);
188 }
189
190 if (_TotalCount == 0 )
191 {
192 _PageIndex = 0 ;
193 _PageCount = 0 ;
194 }
195 else
196 {
197 _PageCount = _TotalCount % _PageSize == 0 ? _TotalCount / _PageSize : _TotalCount / _PageSize + 1 ;
198
199 if (_PageIndex > _PageCount)
200 {
201 _PageIndex = _PageCount;
202 parameters[ 4 ].Value = _PageSize;
203 ds = QueryDataTable(connectionstring);
204 }
205 }
206
207 return ds;
208 }
209
210 /// <summary>
211 /// 得到总的记录数
212 /// </summary>
213 /// <param name="connectionstring"> 连接字符串 </param>
214 /// <returns> 总的记录数 </returns>
215 public int GetTotalCount( string connectionstring)
216 {
217 string strSql = " select count(1) from " + _TableName;
218
219 if (_QueryCondition != string .Empty)
220 {
221 strSql += " where " + _QueryCondition;
222 }
223
224 return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString(), connectionstring));
225 }
226 }
227 #endregion
窗体调用方法
一、设置窗体调用公共方法。
代码
#region
绑定DataGridView
/// <summary>
/// 绑定DataGridView
/// </summary>
/// <param name="sTb"> 表名 </param>
/// <param name="sPk"> 主键 </param>
/// <param name="sOrderField"> 排序字段 </param>
/// <param name="sWhere"> 查询条件 </param>
/// <param name="sQueryFieldName"> 字段列表 </param>
/// <returns> 总记录数 </returns>
private int dgvBind( string sTb, string sPk, string sOrderField, string sWhere, string sQueryFieldName)
{
pageData = null ;
dtPub = null ;
pageData = new PageData();
dtPub = new DataTable();
pageData.TableName = sTb;
pageData.PrimaryKey = sPk;
pageData.OrderStr = sOrderField;
pageData.PageIndex = this .ucPageControlTest.PageCurrent;
pageData.PageSize = 200 ;
pageData.QueryCondition = sWhere;
pageData.QueryFieldName = sQueryFieldName;
dtPub = pageData.QueryDataTable(ConfigurationSettings.AppSettings[ " DbConnection " ]).Tables[ " tbPageData " ];
this .ucPageControlTest.bindingSource.DataSource = dtPub;
this .ucPageControlTest.bindingNavigator.BindingSource = ucPageControlTest.bindingSource;
dgvUcPageControlTest.DataSource = null ;
dgvUcPageControlTest.DataSource = this .ucPageControlTest.bindingSource;
if (dgvUcPageControlTest.Rows.Count > 0 )
{
dgvUcPageControlTest[ 4 , ucPageControlTest.bindingSource.Position].Selected = true ;
}
return pageData.TotalCount;
}
#endregion
/// <summary>
/// 绑定DataGridView
/// </summary>
/// <param name="sTb"> 表名 </param>
/// <param name="sPk"> 主键 </param>
/// <param name="sOrderField"> 排序字段 </param>
/// <param name="sWhere"> 查询条件 </param>
/// <param name="sQueryFieldName"> 字段列表 </param>
/// <returns> 总记录数 </returns>
private int dgvBind( string sTb, string sPk, string sOrderField, string sWhere, string sQueryFieldName)
{
pageData = null ;
dtPub = null ;
pageData = new PageData();
dtPub = new DataTable();
pageData.TableName = sTb;
pageData.PrimaryKey = sPk;
pageData.OrderStr = sOrderField;
pageData.PageIndex = this .ucPageControlTest.PageCurrent;
pageData.PageSize = 200 ;
pageData.QueryCondition = sWhere;
pageData.QueryFieldName = sQueryFieldName;
dtPub = pageData.QueryDataTable(ConfigurationSettings.AppSettings[ " DbConnection " ]).Tables[ " tbPageData " ];
this .ucPageControlTest.bindingSource.DataSource = dtPub;
this .ucPageControlTest.bindingNavigator.BindingSource = ucPageControlTest.bindingSource;
dgvUcPageControlTest.DataSource = null ;
dgvUcPageControlTest.DataSource = this .ucPageControlTest.bindingSource;
if (dgvUcPageControlTest.Rows.Count > 0 )
{
dgvUcPageControlTest[ 4 , ucPageControlTest.bindingSource.Position].Selected = true ;
}
return pageData.TotalCount;
}
#endregion
二、在控件的EventPaging事件代码中调用即可。
return
dgvBind("tbTestData", "UniqueID", "UniqueID", sQueryWhere, "
*
");
三、SqlServer测试数据代码如下：
代码
--
=============================================
-- Author: EricHu QQ:80368704 WebSite:http://www.cnblogs.com/huyong/
-- Create date: 2010-12-18
-- Description: 原创企业级控件库之大数据量分页控件---测试数据
-- Modify Date: 2010-12-18
-- =============================================
/* 一、创建数据库dbTest */
CREATE DATABASE dbTest
go
/* 二、创建数据表 */
USE [ dbTest ]
GO
CREATE TABLE [ dbo ] . [ tbTestData ] (
[ UniqueID ] [ bigint ] IDENTITY ( 20000 , 1 ) NOT NULL ,
[ CompanyName ] [ varchar ] ( 200 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ CompanyCode ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Address ] [ varchar ] ( 500 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Owner ] [ varchar ] ( 100 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Memo ] [ varchar ] ( 2000 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ InsetDataTime ] [ datetime ] NULL CONSTRAINT [ DF_tbTestData_InsetDataTime ] DEFAULT ( getdate ()),
CONSTRAINT [ PK_tbTestData ] PRIMARY KEY CLUSTERED
(
[ UniqueID ] ASC
) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
/* 三、增加测试数据 */
declare @count bigint
select @count = 1
while @count <= 5000000
begin
insert into tbTestData
values ( ' Company ' + cast ( @count as varchar ), ' CompanyCode ' + cast ( @count as varchar )
, ' Address ' + cast ( @count as varchar ), ' Owner ' + cast ( @count as varchar )
, ' Memo ' + cast ( @count as varchar ), getdate ())
select @count = @count + 1
end
-- Author: EricHu QQ:80368704 WebSite:http://www.cnblogs.com/huyong/
-- Create date: 2010-12-18
-- Description: 原创企业级控件库之大数据量分页控件---测试数据
-- Modify Date: 2010-12-18
-- =============================================
/* 一、创建数据库dbTest */
CREATE DATABASE dbTest
go
/* 二、创建数据表 */
USE [ dbTest ]
GO
CREATE TABLE [ dbo ] . [ tbTestData ] (
[ UniqueID ] [ bigint ] IDENTITY ( 20000 , 1 ) NOT NULL ,
[ CompanyName ] [ varchar ] ( 200 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ CompanyCode ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Address ] [ varchar ] ( 500 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Owner ] [ varchar ] ( 100 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Memo ] [ varchar ] ( 2000 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ InsetDataTime ] [ datetime ] NULL CONSTRAINT [ DF_tbTestData_InsetDataTime ] DEFAULT ( getdate ()),
CONSTRAINT [ PK_tbTestData ] PRIMARY KEY CLUSTERED
(
[ UniqueID ] ASC
) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
/* 三、增加测试数据 */
declare @count bigint
select @count = 1
while @count <= 5000000
begin
insert into tbTestData
values ( ' Company ' + cast ( @count as varchar ), ' CompanyCode ' + cast ( @count as varchar )
, ' Address ' + cast ( @count as varchar ), ' Owner ' + cast ( @count as varchar )
, ' Memo ' + cast ( @count as varchar ), getdate ())
select @count = @count + 1
end
下面给出本控件完整代码
原创企业级控件库之大数据量分页控件
1
#region
版权信息
2 /* ---------------------------------------------------------------------*
3 // Copyright (C) 2010 http://www.cnblogs.com/huyong
4 // 版权所有。
5 // 项目 名称：《Winform通用控件库》
6 // 文 件 名： UcPageControl.cs
7 // 类 全 名： DotNet.Controls.UcPageControl
8 // 描 述: 分页控件
9 // 创建 时间： 2010-06-05
10 // 创建人信息： [**** 姓名:胡勇 QQ:80368704 E-Mail:80368704@qq.com *****]
11 *---------------------------------------------------------------------- */
12 #endregion
13
14 using System;
15 using System.Collections.Generic;
16 using System.ComponentModel;
17 using System.Drawing;
18 using System.Data;
19 using System.Data.SqlClient;
20 using System.Windows.Forms;
21 using DotNet.Common;
22 using DotNet.DBUtility;
23
24 namespace DotNet.Controls
25 {
26 #region 委托申明
27 /// <summary>
28 /// 申明委托
29 /// </summary>
30 /// <param name="e"></param>
31 /// <returns></returns>
32 public delegate int EventPagingHandler(EventPagingArg e);
33 #endregion
34
35 #region 分页控件
36 /// <summary>
37 /// 分页控件
38 ///
39 /// 修改纪录(此分页控件经过多次修改，已趋于完美，可放心使用。)
40 /// 2010-12-06 胡勇 对上一条、下一条、首条、末条数据导航的隐藏，因为控件本身已做了处理。
41 /// 2010-12-05 胡勇 对分页控件代码做了相应优化
42 /// 2010-06-05 胡勇 创建分页控件
43 ///
44 /// <author>
45 /// <name> 胡勇 </name>
46 /// <QQ> 80368704 </QQ>
47 /// <Email> 80368704@qq.com </Email>
48 /// </author>
49 /// </summary>
50 [ToolboxItem( true )]
51 [DefaultEvent( " EventPaging " )]
52 [ToolboxBitmap( typeof (UcPageControl), " DotNet.Controls.Images.UcPageControl.bmp " )]
53 [Description( " 分页控件 " )]
54 public partial class UcPageControl : UserControl
55 {
56 #region 申明事件
57 /// <summary>
58 /// 单击移动到当前页上一末记录时发生
59 /// </summary>
60 [Category( " 数据分页 " ), Description( " 单击移动到当前页上一末记录时发生。 " ),Browsable( false )]
61 public event EventHandler OnBindingNavigatorMovePreviousItemClick;
62
63 /// <summary>
64 /// 单击移动到当前页第一条记录时发生
65 /// </summary>
66 [Category( " 数据分页 " ), Description( " 单击移动到当前页第一条记录时发生。 " ), Browsable( false )]
67 public event EventHandler OnBindingNavigatorMoveFirstItemClick;
68
69 /// <summary>
70 /// 单击移动到当前页下一条记录时发生
71 /// </summary>
72 [Category( " 数据分页 " ), Description( " 单击移动到当前页下一条记录时发生。 " ), Browsable( false )]
73 public event EventHandler OnBindingNavigatorMoveNextItemClick;
74
75 /// <summary>
76 /// 单击移动到当前页最后一条记录时发生
77 /// </summary>
78 [Category( " 数据分页 " ), Description( " 单击移动到当前页最后一条记录时发生。 " ), Browsable( false )]
79 public event EventHandler OnBindingNavigatorMoveLastItemClick;
80
81 /// <summary>
82 /// 单击各分页按钮(上一页、下一页、第一页、最后一页和转到某页)时发生
83 /// </summary>
84 [Category( " 数据分页 " ), Description( " 分页时发生。 " )]
85 public event EventPagingHandler EventPaging;
86 #endregion
87
88 #region 构造函数
89 public UcPageControl()
90 {
91 InitializeComponent();
92 }
93 #endregion
94
95 #region 属性
96
97 private int _pageSize = 50 ; // 每页显示记录数
98 private int _nMax = 0 ; // 总记录数
99 private int _pageCount = 0 ; // 页数=总记录数/每页显示记录数
100 private int _pageCurrent = 0 ; // 当前页号
101
102 /// <summary>
103 /// 每页显示记录数
104 /// </summary>
105 [Category( " 数据分页 " ), Description( " 每页显示记录数。 " ), Browsable( false )]
106 public int PageSize
107 {
108 get
109 {
110 return _pageSize;
111 }
112 set
113 {
114 _pageSize = value;
115 GetPageCount(); // 页数
116 }
117 }
118
119 /// <summary>
120 /// 记录总数
121 /// </summary>
122 [Category( " 数据分页 " ), Description( " 记录总数。 " ),Browsable( false )]
123 public int NMax
124 {
125 get
126 {
127 return _nMax;
128 }
129 set
130 {
131 _nMax = value;
132 GetPageCount();
133 }
134 }
135
136 /// <summary>
137 /// 页数
138 /// </summary>
139 [Category( " 数据分页 " ), Description( " 页数。 " ), Browsable( false )]
140 public int PageCount
141 {
142 get
143 {
144 return _pageCount;
145 }
146 set
147 {
148 _pageCount = value;
149 }
150 }
151
152 /// <summary>
153 /// 当前页号
154 /// </summary>
155 [Category( " 数据分页 " ), Description( " 当前页号。 " ), Browsable( false )]
156 public int PageCurrent
157 {
158 get
159 {
160 return _pageCurrent;
161 }
162 set
163 {
164 _pageCurrent = value;
165 }
166 }
167 #endregion
168
169 #region 方法
170 [Category( " 数据分页 " ), Description( " bindingNavigator。 " ), Browsable( false )]
171 public BindingNavigator ToolBar
172 {
173 get
174 {
175 return this .bindingNavigator;
176 }
177 }
178
179 /// <summary>
180 /// 得到总页数
181 /// </summary>
182 private void GetPageCount()
183 {
184 if ( this .NMax > 0 )
185 {
186 this .PageCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble( this .NMax) / Convert.ToDouble( this .PageSize)));
187 }
188 else
189 {
190 this .PageCount = 0 ;
191 }
192 }
193
194 /// <summary>
195 /// 绑定分页控件(关键代码)
196 /// </summary>
197 public void Bind()
198 {
199 if ( this .EventPaging != null )
200 {
201 this .NMax = this .EventPaging( new EventPagingArg( this .PageCurrent));
202 }
203
204 if ( this .PageCurrent > this .PageCount)
205 {
206 this .PageCurrent = this .PageCount;
207 }
208 if ( this .PageCount == 1 )
209 {
210 this .PageCurrent = 1 ;
211 }
212 lblPageCount.Text = this .PageCount.ToString();
213 this .lblMaxPage.Text = " 共 " + this .NMax.ToString() + " 条记录 " ;
214 this .txtCurrentPage.Text = this .PageCurrent.ToString();
215
216 if ( this .PageCurrent == 1 )
217 {
218 this .btnPrev.Enabled = false ;
219 this .btnFirst.Enabled = false ;
220 }
221 else
222 {
223 btnPrev.Enabled = true ;
224 btnFirst.Enabled = true ;
225 }
226
227 if ( this .PageCurrent == this .PageCount)
228 {
229 this .btnLast.Enabled = false ;
230 this .btnNext.Enabled = false ;
231 }
232 else
233 {
234 btnLast.Enabled = true ;
235 btnNext.Enabled = true ;
236 }
237
238 if ( this .NMax == 0 )
239 {
240 btnNext.Enabled = false ;
241 btnLast.Enabled = false ;
242 btnFirst.Enabled = false ;
243 btnPrev.Enabled = false ;
244 }
245 }
246
247 #endregion
248
249 #region 按钮事件
250 private void btnFirst_Click( object sender, EventArgs e)
251 {
252 PageCurrent = 1 ;
253 this .Bind();
254 }
255
256 private void btnPrev_Click( object sender, EventArgs e)
257 {
258 PageCurrent -= 1 ;
259 if (PageCurrent <= 0 )
260 {
261 PageCurrent = 1 ;
262 }
263 this .Bind();
264 }
265
266 private void btnNext_Click( object sender, EventArgs e)
267 {
268 this .PageCurrent += 1 ;
269 if (PageCurrent > PageCount)
270 {
271 PageCurrent = PageCount;
272 }
273 this .Bind();
274 }
275
276 private void btnLast_Click( object sender, EventArgs e)
277 {
278 PageCurrent = PageCount;
279 this .Bind();
280 }
281
282 private void btnGo_Click( object sender, EventArgs e)
283 {
284 if ( this .txtCurrentPage.Text != null && txtCurrentPage.Text != "" )
285 {
286 if (Int32.TryParse(txtCurrentPage.Text, out _pageCurrent))
287 {
288 this .Bind();
289 }
290 else
291 {
292 DialogHelper.ShowErrorMsg( " 输入数字格式错误！ " );
293 }
294 }
295 }
296
297 private void txtCurrentPage_KeyDown( object sender, KeyEventArgs e)
298 {
299 if (e.KeyCode == Keys.Enter)
300 {
301 this .Bind();
302 }
303 }
304
305 private void bindingNavigatorMovePreviousItem_Click( object sender, EventArgs e)
306 {
307 if (OnBindingNavigatorMovePreviousItemClick != null )
308 {
309 OnBindingNavigatorMovePreviousItemClick( this , null );
310 }
311 }
312
313 private void bindingNavigatorMoveFirstItem_Click( object sender, EventArgs e)
314 {
315 if (OnBindingNavigatorMoveFirstItemClick != null )
316 {
317 OnBindingNavigatorMoveFirstItemClick( this , null );
318 }
319 }
320
321 private void bindingNavigatorMoveNextItem_Click( object sender, EventArgs e)
322 {
323 if (OnBindingNavigatorMoveNextItemClick != null )
324 {
325 OnBindingNavigatorMoveNextItemClick( this , null );
326 }
327 }
328
329 private void bindingNavigatorMoveLastItem_Click( object sender, EventArgs e)
330 {
331 if (OnBindingNavigatorMoveLastItemClick != null )
332 {
333 OnBindingNavigatorMoveLastItemClick( this , null );
334 }
335 }
336 #endregion
337 }
338 #endregion
339
340 #region 自定义事件数据基类
341 /// <summary>
342 /// 自定义事件数据基类
343 /// </summary>
344 public class EventPagingArg : EventArgs
345 {
346 private int _intPageIndex;
347 public EventPagingArg( int PageIndex)
348 {
349 _intPageIndex = PageIndex;
350 }
351 }
352 #endregion
353
354 #region 数据源提供(PageData)
355 /// <summary>
356 /// 数据源提供
357 /// </summary>
358 public class PageData
359 {
360 DataSet ds = null ;
361 private int _PageSize = 50 ; // 分页大小
362 private int _PageIndex = 1 ; // 当前页
363 private int _PageCount = 0 ; // 总页数
364 private int _TotalCount = 0 ; // 总记录数
365 private string _QueryFieldName = " * " ; // 表字段FieldStr
366 private bool _isQueryTotalCounts = true ; // 是否查询总的记录条数
367 private string _TableName = string .Empty; // 表名
368 private string _OrderStr = string .Empty; // 排序_SortStr
369 private string _QueryCondition = string .Empty; // 查询的条件 RowFilter
370 private string _PrimaryKey = string .Empty; // 主键
371
372 /// <summary>
373 /// 是否查询总的记录条数
374 /// </summary>
375 public bool IsQueryTotalCounts
376 {
377 get { return _isQueryTotalCounts; }
378 set { _isQueryTotalCounts = value; }
379 }
380
381 /// <summary>
382 /// 分页大小(每页显示多少条数据)
383 /// </summary>
384 public int PageSize
385 {
386 get
387 {
388 return _PageSize;
389
390 }
391 set
392 {
393 _PageSize = value;
394 }
395 }
396
397 /// <summary>
398 /// 当前页
399 /// </summary>
400 public int PageIndex
401 {
402 get
403 {
404 return _PageIndex;
405 }
406 set
407 {
408 _PageIndex = value;
409 }
410 }
411
412 /// <summary>
413 /// 总页数
414 /// </summary>
415 public int PageCount
416 {
417 get
418 {
419 return _PageCount;
420 }
421 }
422
423 /// <summary>
424 /// 总记录数
425 /// </summary>
426 public int TotalCount
427 {
428 get
429 {
430 return _TotalCount;
431 }
432 }
433
434 /// <summary>
435 /// 表名或视图名
436 /// </summary>
437 public string TableName
438 {
439 get
440 {
441 return _TableName;
442 }
443 set
444 {
445 _TableName = value;
446 }
447 }
448
449 /// <summary>
450 /// 表字段FieldStr
451 /// </summary>
452 public string QueryFieldName
453 {
454 get
455 {
456 return _QueryFieldName;
457 }
458 set
459 {
460 _QueryFieldName = value;
461 }
462 }
463
464 /// <summary>
465 /// 排序字段
466 /// </summary>
467 public string OrderStr
468 {
469 get
470 {
471 return _OrderStr;
472 }
473 set
474 {
475 _OrderStr = value;
476 }
477 }
478
479 /// <summary>
480 /// 查询条件
481 /// </summary>
482 public string QueryCondition
483 {
484 get
485 {
486 return _QueryCondition;
487 }
488 set
489 {
490 _QueryCondition = value;
491 }
492 }
493
494 /// <summary>
495 /// 主键
496 /// </summary>
497 public string PrimaryKey
498 {
499 get
500 {
501 return _PrimaryKey;
502 }
503 set
504 {
505 _PrimaryKey = value;
506 }
507 }
508
509 /// <summary>
510 /// 得到分页数据
511 /// </summary>
512 /// <param name="connectionstring"> 连接字符串 </param>
513 /// <returns> DataSet </returns>
514 public DataSet QueryDataTable( string connectionstring)
515 {
516 SqlParameter[] parameters = {
517 new SqlParameter( " @Tables " , SqlDbType.VarChar, 255 ),
518 new SqlParameter( " @PrimaryKey " , SqlDbType.VarChar , 255 ),
519 new SqlParameter( " @Sort " , SqlDbType.VarChar , 255 ),
520 new SqlParameter( " @CurrentPage " , SqlDbType.Int ),
521 new SqlParameter( " @PageSize " , SqlDbType.Int ),
522 new SqlParameter( " @Fields " , SqlDbType.VarChar, 255 ),
523 new SqlParameter( " @Filter " , SqlDbType.VarChar, 1000 ),
524 new SqlParameter( " @Group " , SqlDbType.VarChar, 1000 )
525 };
526 parameters[ 0 ].Value = _TableName;
527 parameters[ 1 ].Value = _PrimaryKey;
528 parameters[ 2 ].Value = _OrderStr;
529 parameters[ 3 ].Value = PageIndex;
530 parameters[ 4 ].Value = PageSize;
531 parameters[ 5 ].Value = _QueryFieldName;
532 parameters[ 6 ].Value = _QueryCondition;
533 parameters[ 7 ].Value = string .Empty;
534 ds = null ;
535 ds = new DataSet();
536 ds = DbHelperSQL.RunProcedure(connectionstring, " uspDividePage " , parameters, " tbPageData " );
537
538 if (_isQueryTotalCounts)
539 {
540 _TotalCount = GetTotalCount(connectionstring);
541 }
542
543 if (_TotalCount == 0 )
544 {
545 _PageIndex = 0 ;
546 _PageCount = 0 ;
547 }
548 else
549 {
550 _PageCount = _TotalCount % _PageSize == 0 ? _TotalCount / _PageSize : _TotalCount / _PageSize + 1 ;
551
552 if (_PageIndex > _PageCount)
553 {
554 _PageIndex = _PageCount;
555 parameters[ 4 ].Value = _PageSize;
556 ds = QueryDataTable(connectionstring);
557 }
558 }
559
560 return ds;
561 }
562
563 /// <summary>
564 /// 得到总的记录数
565 /// </summary>
566 /// <param name="connectionstring"> 连接字符串 </param>
567 /// <returns> 总的记录数 </returns>
568 public int GetTotalCount( string connectionstring)
569 {
570 string strSql = " select count(1) from " + _TableName;
571
572 if (_QueryCondition != string .Empty)
573 {
574 strSql += " where " + _QueryCondition;
575 }
576
577 return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString(), connectionstring));
578 }
579 }
580 #endregion
581 }
582
2 /* ---------------------------------------------------------------------*
3 // Copyright (C) 2010 http://www.cnblogs.com/huyong
4 // 版权所有。
5 // 项目 名称：《Winform通用控件库》
6 // 文 件 名： UcPageControl.cs
7 // 类 全 名： DotNet.Controls.UcPageControl
8 // 描 述: 分页控件
9 // 创建 时间： 2010-06-05
10 // 创建人信息： [**** 姓名:胡勇 QQ:80368704 E-Mail:80368704@qq.com *****]
11 *---------------------------------------------------------------------- */
12 #endregion
13
14 using System;
15 using System.Collections.Generic;
16 using System.ComponentModel;
17 using System.Drawing;
18 using System.Data;
19 using System.Data.SqlClient;
20 using System.Windows.Forms;
21 using DotNet.Common;
22 using DotNet.DBUtility;
23
24 namespace DotNet.Controls
25 {
26 #region 委托申明
27 /// <summary>
28 /// 申明委托
29 /// </summary>
30 /// <param name="e"></param>
31 /// <returns></returns>
32 public delegate int EventPagingHandler(EventPagingArg e);
33 #endregion
34
35 #region 分页控件
36 /// <summary>
37 /// 分页控件
38 ///
39 /// 修改纪录(此分页控件经过多次修改，已趋于完美，可放心使用。)
40 /// 2010-12-06 胡勇 对上一条、下一条、首条、末条数据导航的隐藏，因为控件本身已做了处理。
41 /// 2010-12-05 胡勇 对分页控件代码做了相应优化
42 /// 2010-06-05 胡勇 创建分页控件
43 ///
44 /// <author>
45 /// <name> 胡勇 </name>
46 /// <QQ> 80368704 </QQ>
47 /// <Email> 80368704@qq.com </Email>
48 /// </author>
49 /// </summary>
50 [ToolboxItem( true )]
51 [DefaultEvent( " EventPaging " )]
52 [ToolboxBitmap( typeof (UcPageControl), " DotNet.Controls.Images.UcPageControl.bmp " )]
53 [Description( " 分页控件 " )]
54 public partial class UcPageControl : UserControl
55 {
56 #region 申明事件
57 /// <summary>
58 /// 单击移动到当前页上一末记录时发生
59 /// </summary>
60 [Category( " 数据分页 " ), Description( " 单击移动到当前页上一末记录时发生。 " ),Browsable( false )]
61 public event EventHandler OnBindingNavigatorMovePreviousItemClick;
62
63 /// <summary>
64 /// 单击移动到当前页第一条记录时发生
65 /// </summary>
66 [Category( " 数据分页 " ), Description( " 单击移动到当前页第一条记录时发生。 " ), Browsable( false )]
67 public event EventHandler OnBindingNavigatorMoveFirstItemClick;
68
69 /// <summary>
70 /// 单击移动到当前页下一条记录时发生
71 /// </summary>
72 [Category( " 数据分页 " ), Description( " 单击移动到当前页下一条记录时发生。 " ), Browsable( false )]
73 public event EventHandler OnBindingNavigatorMoveNextItemClick;
74
75 /// <summary>
76 /// 单击移动到当前页最后一条记录时发生
77 /// </summary>
78 [Category( " 数据分页 " ), Description( " 单击移动到当前页最后一条记录时发生。 " ), Browsable( false )]
79 public event EventHandler OnBindingNavigatorMoveLastItemClick;
80
81 /// <summary>
82 /// 单击各分页按钮(上一页、下一页、第一页、最后一页和转到某页)时发生
83 /// </summary>
84 [Category( " 数据分页 " ), Description( " 分页时发生。 " )]
85 public event EventPagingHandler EventPaging;
86 #endregion
87
88 #region 构造函数
89 public UcPageControl()
90 {
91 InitializeComponent();
92 }
93 #endregion
94
95 #region 属性
96
97 private int _pageSize = 50 ; // 每页显示记录数
98 private int _nMax = 0 ; // 总记录数
99 private int _pageCount = 0 ; // 页数=总记录数/每页显示记录数
100 private int _pageCurrent = 0 ; // 当前页号
101
102 /// <summary>
103 /// 每页显示记录数
104 /// </summary>
105 [Category( " 数据分页 " ), Description( " 每页显示记录数。 " ), Browsable( false )]
106 public int PageSize
107 {
108 get
109 {
110 return _pageSize;
111 }
112 set
113 {
114 _pageSize = value;
115 GetPageCount(); // 页数
116 }
117 }
118
119 /// <summary>
120 /// 记录总数
121 /// </summary>
122 [Category( " 数据分页 " ), Description( " 记录总数。 " ),Browsable( false )]
123 public int NMax
124 {
125 get
126 {
127 return _nMax;
128 }
129 set
130 {
131 _nMax = value;
132 GetPageCount();
133 }
134 }
135
136 /// <summary>
137 /// 页数
138 /// </summary>
139 [Category( " 数据分页 " ), Description( " 页数。 " ), Browsable( false )]
140 public int PageCount
141 {
142 get
143 {
144 return _pageCount;
145 }
146 set
147 {
148 _pageCount = value;
149 }
150 }
151
152 /// <summary>
153 /// 当前页号
154 /// </summary>
155 [Category( " 数据分页 " ), Description( " 当前页号。 " ), Browsable( false )]
156 public int PageCurrent
157 {
158 get
159 {
160 return _pageCurrent;
161 }
162 set
163 {
164 _pageCurrent = value;
165 }
166 }
167 #endregion
168
169 #region 方法
170 [Category( " 数据分页 " ), Description( " bindingNavigator。 " ), Browsable( false )]
171 public BindingNavigator ToolBar
172 {
173 get
174 {
175 return this .bindingNavigator;
176 }
177 }
178
179 /// <summary>
180 /// 得到总页数
181 /// </summary>
182 private void GetPageCount()
183 {
184 if ( this .NMax > 0 )
185 {
186 this .PageCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble( this .NMax) / Convert.ToDouble( this .PageSize)));
187 }
188 else
189 {
190 this .PageCount = 0 ;
191 }
192 }
193
194 /// <summary>
195 /// 绑定分页控件(关键代码)
196 /// </summary>
197 public void Bind()
198 {
199 if ( this .EventPaging != null )
200 {
201 this .NMax = this .EventPaging( new EventPagingArg( this .PageCurrent));
202 }
203
204 if ( this .PageCurrent > this .PageCount)
205 {
206 this .PageCurrent = this .PageCount;
207 }
208 if ( this .PageCount == 1 )
209 {
210 this .PageCurrent = 1 ;
211 }
212 lblPageCount.Text = this .PageCount.ToString();
213 this .lblMaxPage.Text = " 共 " + this .NMax.ToString() + " 条记录 " ;
214 this .txtCurrentPage.Text = this .PageCurrent.ToString();
215
216 if ( this .PageCurrent == 1 )
217 {
218 this .btnPrev.Enabled = false ;
219 this .btnFirst.Enabled = false ;
220 }
221 else
222 {
223 btnPrev.Enabled = true ;
224 btnFirst.Enabled = true ;
225 }
226
227 if ( this .PageCurrent == this .PageCount)
228 {
229 this .btnLast.Enabled = false ;
230 this .btnNext.Enabled = false ;
231 }
232 else
233 {
234 btnLast.Enabled = true ;
235 btnNext.Enabled = true ;
236 }
237
238 if ( this .NMax == 0 )
239 {
240 btnNext.Enabled = false ;
241 btnLast.Enabled = false ;
242 btnFirst.Enabled = false ;
243 btnPrev.Enabled = false ;
244 }
245 }
246
247 #endregion
248
249 #region 按钮事件
250 private void btnFirst_Click( object sender, EventArgs e)
251 {
252 PageCurrent = 1 ;
253 this .Bind();
254 }
255
256 private void btnPrev_Click( object sender, EventArgs e)
257 {
258 PageCurrent -= 1 ;
259 if (PageCurrent <= 0 )
260 {
261 PageCurrent = 1 ;
262 }
263 this .Bind();
264 }
265
266 private void btnNext_Click( object sender, EventArgs e)
267 {
268 this .PageCurrent += 1 ;
269 if (PageCurrent > PageCount)
270 {
271 PageCurrent = PageCount;
272 }
273 this .Bind();
274 }
275
276 private void btnLast_Click( object sender, EventArgs e)
277 {
278 PageCurrent = PageCount;
279 this .Bind();
280 }
281
282 private void btnGo_Click( object sender, EventArgs e)
283 {
284 if ( this .txtCurrentPage.Text != null && txtCurrentPage.Text != "" )
285 {
286 if (Int32.TryParse(txtCurrentPage.Text, out _pageCurrent))
287 {
288 this .Bind();
289 }
290 else
291 {
292 DialogHelper.ShowErrorMsg( " 输入数字格式错误！ " );
293 }
294 }
295 }
296
297 private void txtCurrentPage_KeyDown( object sender, KeyEventArgs e)
298 {
299 if (e.KeyCode == Keys.Enter)
300 {
301 this .Bind();
302 }
303 }
304
305 private void bindingNavigatorMovePreviousItem_Click( object sender, EventArgs e)
306 {
307 if (OnBindingNavigatorMovePreviousItemClick != null )
308 {
309 OnBindingNavigatorMovePreviousItemClick( this , null );
310 }
311 }
312
313 private void bindingNavigatorMoveFirstItem_Click( object sender, EventArgs e)
314 {
315 if (OnBindingNavigatorMoveFirstItemClick != null )
316 {
317 OnBindingNavigatorMoveFirstItemClick( this , null );
318 }
319 }
320
321 private void bindingNavigatorMoveNextItem_Click( object sender, EventArgs e)
322 {
323 if (OnBindingNavigatorMoveNextItemClick != null )
324 {
325 OnBindingNavigatorMoveNextItemClick( this , null );
326 }
327 }
328
329 private void bindingNavigatorMoveLastItem_Click( object sender, EventArgs e)
330 {
331 if (OnBindingNavigatorMoveLastItemClick != null )
332 {
333 OnBindingNavigatorMoveLastItemClick( this , null );
334 }
335 }
336 #endregion
337 }
338 #endregion
339
340 #region 自定义事件数据基类
341 /// <summary>
342 /// 自定义事件数据基类
343 /// </summary>
344 public class EventPagingArg : EventArgs
345 {
346 private int _intPageIndex;
347 public EventPagingArg( int PageIndex)
348 {
349 _intPageIndex = PageIndex;
350 }
351 }
352 #endregion
353
354 #region 数据源提供(PageData)
355 /// <summary>
356 /// 数据源提供
357 /// </summary>
358 public class PageData
359 {
360 DataSet ds = null ;
361 private int _PageSize = 50 ; // 分页大小
362 private int _PageIndex = 1 ; // 当前页
363 private int _PageCount = 0 ; // 总页数
364 private int _TotalCount = 0 ; // 总记录数
365 private string _QueryFieldName = " * " ; // 表字段FieldStr
366 private bool _isQueryTotalCounts = true ; // 是否查询总的记录条数
367 private string _TableName = string .Empty; // 表名
368 private string _OrderStr = string .Empty; // 排序_SortStr
369 private string _QueryCondition = string .Empty; // 查询的条件 RowFilter
370 private string _PrimaryKey = string .Empty; // 主键
371
372 /// <summary>
373 /// 是否查询总的记录条数
374 /// </summary>
375 public bool IsQueryTotalCounts
376 {
377 get { return _isQueryTotalCounts; }
378 set { _isQueryTotalCounts = value; }
379 }
380
381 /// <summary>
382 /// 分页大小(每页显示多少条数据)
383 /// </summary>
384 public int PageSize
385 {
386 get
387 {
388 return _PageSize;
389
390 }
391 set
392 {
393 _PageSize = value;
394 }
395 }
396
397 /// <summary>
398 /// 当前页
399 /// </summary>
400 public int PageIndex
401 {
402 get
403 {
404 return _PageIndex;
405 }
406 set
407 {
408 _PageIndex = value;
409 }
410 }
411
412 /// <summary>
413 /// 总页数
414 /// </summary>
415 public int PageCount
416 {
417 get
418 {
419 return _PageCount;
420 }
421 }
422
423 /// <summary>
424 /// 总记录数
425 /// </summary>
426 public int TotalCount
427 {
428 get
429 {
430 return _TotalCount;
431 }
432 }
433
434 /// <summary>
435 /// 表名或视图名
436 /// </summary>
437 public string TableName
438 {
439 get
440 {
441 return _TableName;
442 }
443 set
444 {
445 _TableName = value;
446 }
447 }
448
449 /// <summary>
450 /// 表字段FieldStr
451 /// </summary>
452 public string QueryFieldName
453 {
454 get
455 {
456 return _QueryFieldName;
457 }
458 set
459 {
460 _QueryFieldName = value;
461 }
462 }
463
464 /// <summary>
465 /// 排序字段
466 /// </summary>
467 public string OrderStr
468 {
469 get
470 {
471 return _OrderStr;
472 }
473 set
474 {
475 _OrderStr = value;
476 }
477 }
478
479 /// <summary>
480 /// 查询条件
481 /// </summary>
482 public string QueryCondition
483 {
484 get
485 {
486 return _QueryCondition;
487 }
488 set
489 {
490 _QueryCondition = value;
491 }
492 }
493
494 /// <summary>
495 /// 主键
496 /// </summary>
497 public string PrimaryKey
498 {
499 get
500 {
501 return _PrimaryKey;
502 }
503 set
504 {
505 _PrimaryKey = value;
506 }
507 }
508
509 /// <summary>
510 /// 得到分页数据
511 /// </summary>
512 /// <param name="connectionstring"> 连接字符串 </param>
513 /// <returns> DataSet </returns>
514 public DataSet QueryDataTable( string connectionstring)
515 {
516 SqlParameter[] parameters = {
517 new SqlParameter( " @Tables " , SqlDbType.VarChar, 255 ),
518 new SqlParameter( " @PrimaryKey " , SqlDbType.VarChar , 255 ),
519 new SqlParameter( " @Sort " , SqlDbType.VarChar , 255 ),
520 new SqlParameter( " @CurrentPage " , SqlDbType.Int ),
521 new SqlParameter( " @PageSize " , SqlDbType.Int ),
522 new SqlParameter( " @Fields " , SqlDbType.VarChar, 255 ),
523 new SqlParameter( " @Filter " , SqlDbType.VarChar, 1000 ),
524 new SqlParameter( " @Group " , SqlDbType.VarChar, 1000 )
525 };
526 parameters[ 0 ].Value = _TableName;
527 parameters[ 1 ].Value = _PrimaryKey;
528 parameters[ 2 ].Value = _OrderStr;
529 parameters[ 3 ].Value = PageIndex;
530 parameters[ 4 ].Value = PageSize;
531 parameters[ 5 ].Value = _QueryFieldName;
532 parameters[ 6 ].Value = _QueryCondition;
533 parameters[ 7 ].Value = string .Empty;
534 ds = null ;
535 ds = new DataSet();
536 ds = DbHelperSQL.RunProcedure(connectionstring, " uspDividePage " , parameters, " tbPageData " );
537
538 if (_isQueryTotalCounts)
539 {
540 _TotalCount = GetTotalCount(connectionstring);
541 }
542
543 if (_TotalCount == 0 )
544 {
545 _PageIndex = 0 ;
546 _PageCount = 0 ;
547 }
548 else
549 {
550 _PageCount = _TotalCount % _PageSize == 0 ? _TotalCount / _PageSize : _TotalCount / _PageSize + 1 ;
551
552 if (_PageIndex > _PageCount)
553 {
554 _PageIndex = _PageCount;
555 parameters[ 4 ].Value = _PageSize;
556 ds = QueryDataTable(connectionstring);
557 }
558 }
559
560 return ds;
561 }
562
563 /// <summary>
564 /// 得到总的记录数
565 /// </summary>
566 /// <param name="connectionstring"> 连接字符串 </param>
567 /// <returns> 总的记录数 </returns>
568 public int GetTotalCount( string connectionstring)
569 {
570 string strSql = " select count(1) from " + _TableName;
571
572 if (_QueryCondition != string .Empty)
573 {
574 strSql += " where " + _QueryCondition;
575 }
576
577 return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString(), connectionstring));
578 }
579 }
580 #endregion
581 }
582
本文转自yonghu86 51CTO博客，原文链接：http://blog.51cto.com/yonghu/1321376，如需转载请自行联系原作者
