再上数据分页控件（不用存储过程）-阿里云开发者社区

再上数据分页控件（不用存储过程）

发布日期：2011年04月04日星期一作者:EricHu

本篇主要内容如下：

· 概述

· 本分页控件原理。

· 分页控件代码。

· 分页控件使用实例。

概述

在前几篇：我介绍了原创企业级控件库之大数据据量分页控件，这个控件主要是通过存储过程进行数据分页，得到了大家的支持，也给出了许多宝贵的建议，在这儿先感谢各位。同时也让我更有信心进行以后的文章（企业级控件库系列）。

分页对于每个项目来说都有它存在的意义，想起在以前刚刚刚软件开发时，由于刚刚毕业，理论知识雄厚，但实际工作经验欠缺，记得几年前做开发时，数据量很大，要用分页，一开始真不知道如何是好，方法到知道，但速度与稳定性却没有经验。在这儿，我只是起到一个抛砖引玉的作用，以便引起我们在实际工作中要多思考，对同一件事多想几种解决方式，只有这样才能不断提高，同时要学会总结。

这篇我将给大家介绍：不用存储过程，直接用代码来实现数据分页，欢迎大家拍砖，觉得不错的可以推荐下。同时，你要用什么好的想法，也可以与我交流，谢谢。

本分页控件原理

分页的方法很多，有用存储过程的，有不用存储过程的，还有在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
{
     /// <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 }

最后的效果如下：

原创作品，转贴请注明作者和出处，留此信息。

------------------------------------------------

cnBlobs:http://www.cnblogs.com/huyong/
CSDN：http://blog.csdn.net/chinahuyong

作者：EricHu（DB、C/S、B/S、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

Q Q：80368704 E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看 [置顶]索引贴——（不断更新中）

再上数据分页控件（不用存储过程）

热门文章

最新文章

相关电子书

相关实验场景