欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

原创企业级控件库之大数据量分页控件

程序员文章站 2022-04-10 23:39:51
...

原创企业级控件库之大数据量分页控件

发布日期:2010年12月18日星期六作者:EricHu

  在上篇:我介绍了原创企业级控件库之组合查询控件,这篇我将给大家介绍:企业级控件库之大数据量分页控件。

  摘要

  说到分页,大家采用的方法各有千秋,分页在一个中大型软件项目中对数据的快速呈现起到很关键的作用,试想一个数据量上几十万或者几百万的数据表,要是没有分页功能会是一个什么样的效果。总的说来,大家采用的分页方法大同小异,但到底那种方法才是最佳的呢,各有各的看法,让数据说话最有效。今天我给大家分享一个WinForm下大数据量分页控件(当然分页思想也可用于WebForm)。虽然不能说是最佳的,但在我的几个实际项目中,用的都是它,效果不错,可放心使用。

  成就别人、等于成就自己。我没什么要求,欢迎大家多多支持与评论,觉得不错的,记得点击文章左下角的”关注博客”,就这么简单。同时,你要用什么好的想法,也可以与我交流,谢谢。

  分页控件运行效果如下图:

  

原创企业级控件库之大数据量分页控件

用到的分页存储过程:  

/****** 对象:  StoredProcedure [dbo].[uspDividePage]    脚本日期: 12/18/2010 22:28:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE    PROCEDURE [dbo].[uspDividePage]
  /*
 ***************************************************************
 ** 千万数量级分页存储过程 **
 ***************************************************************
 参数说明:
 1.Tables	   :表名或视图名
 2.PrimaryKey  :主关键字
 3.Sort		   :排序语句,不带Order By 比如:UserId Desc,CreateDate Asc
 4.CurrentPage :当前页码
 5.PageSize	   :分页尺寸
 6.Fields      :字段列表(默认为:*)
 7.Filter      :过滤语句,不带Where 
 8.Group	   :Group语句,不带Group By
 ***************************************************************/
 (
 @Tables varchar(2000),
 @PrimaryKey varchar(500),
 @Sort varchar(500) = NULL,
 @CurrentPage int = 1,
 @PageSize int = 10,
 @Fields varchar(2000) = '*',
 @Filter varchar(1000) = NULL,
 @Group varchar(1000) = NULL
 )
 AS
/*默认排序*/
 IF @Sort IS NULL OR @Sort = ''
	 SET @Sort = @PrimaryKey

 DECLARE @SortTable varchar(1000)
 DECLARE @SortName varchar(1000)
 DECLARE @strSortColumn varchar(1000)
 DECLARE @operator char(2)
 DECLARE @type varchar(1000)
 DECLARE @prec int

  /*设定排序语句.*/
 IF CHARINDEX('DESC',@Sort)>0
	 BEGIN
	 SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
	 SET @operator = '<='
	 END
 ELSE
	 BEGIN
	 IF CHARINDEX('ASC', @Sort) = 0
	 SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
	 SET @operator = '>='
	 END

 IF CHARINDEX('.', @strSortColumn) > 0
	 BEGIN
	 SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
	 SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
	 END
 ELSE
	 BEGIN
	 SET @SortTable = @Tables
	 SET @SortName = @strSortColumn
	 END

 SELECT @type=t.name, @prec=c.prec
 FROM sysobjects o 
 JOIN syscolumns c on o.id=c.id
 JOIN systypes t on c.xusertype=t.xusertype
 WHERE o.name = @SortTable AND c.name = @SortName

 IF CHARINDEX('char', @type) > 0
	 SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

 DECLARE @strPageSize varchar(500)
 DECLARE @strStartRow varchar(500)
 DECLARE @strFilter varchar(1000)
 DECLARE @strSimpleFilter varchar(1000)
 DECLARE @strGroup varchar(1000)
  /*默认当前页*/
 IF @CurrentPage < 1
	 SET @CurrentPage = 1

  /*设置分页参数.*/
 SET @strPageSize = CAST(@PageSize AS varchar(500))
 SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(500))

  /*筛选以及分组语句.*/
 IF @Filter IS NOT NULL AND @Filter != ''
	 BEGIN
	 SET @strFilter = ' WHERE ' + @Filter + ' '
	 SET @strSimpleFilter = ' AND ' + @Filter + ' '
	 END
 ELSE
	 BEGIN
	 SET @strSimpleFilter = ''
	 SET @strFilter = ''
	 END

 IF @Group IS NOT NULL AND @Group != ''
	 SET @strGroup = ' GROUP BY ' + @Group + ' '
 ELSE
	 SET @strGroup = ''

 /*执行查询语句*/
 EXEC(
	 '
	 DECLARE @SortColumn ' + @type + '
	 SET ROWCOUNT ' + @strStartRow + '
	 SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
	 SET ROWCOUNT ' + @strPageSize + '
	 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
	 ')


  本控件类图  

原创企业级控件库之大数据量分页控件

  本控件UcpageControl类详细信息

原创企业级控件库之大数据量分页控件


  本控件PageData类详细信息

原创企业级控件库之大数据量分页控件


  本控件核心代码

   一、数据源提供类PageData,主要负责与存储过程进行交互。

原创企业级控件库之大数据量分页控件数据源提供(PageData)

  

数据源提供类PageData,主要负责与存储过程进行交互。
#region 数据源提供(PageData)
    /// <summary>
    /// 数据源提供
    /// </summary>
    public class PageData
    {
        DataSet ds                         = null;
        private int    _PageSize           = 50;           //分页大小
        private int    _PageIndex          = 1;            //当前页
        private int    _PageCount          = 0;            //总页数
        private int    _TotalCount         = 0;            //总记录数
        private string _QueryFieldName     = "*";          //表字段FieldStr
        private bool   _isQueryTotalCounts = true;         //是否查询总的记录条数
        private string _TableName          = string.Empty; //表名       
        private string _OrderStr           = string.Empty; //排序_SortStr
        private string _QueryCondition     = string.Empty; //查询的条件RowFilter
        private string _PrimaryKey         = string.Empty; //主键

        /// <summary>
        /// 是否查询总的记录条数
        /// </summary>
        public bool IsQueryTotalCounts
        {
            get { return _isQueryTotalCounts; }
            set { _isQueryTotalCounts = value; }
        }

        /// <summary>
        /// 分页大小(每页显示多少条数据)
        /// </summary>
        public int PageSize
        {
            get
            {
                return _PageSize;

            }
            set
            {
                _PageSize = value;
            }
        }

        /// <summary>
        /// 当前页
        /// </summary>
        public int PageIndex
        {
            get
            {
                return _PageIndex;
            }
            set
            {
                _PageIndex = value;
            }
        }

        /// <summary>
        /// 总页数
        /// </summary>
        public int PageCount
        {
            get
            {
                return _PageCount;
            }
        }

        /// <summary>
        /// 总记录数
        /// </summary>
        public int TotalCount
        {
            get
            {
                return _TotalCount;
            }
        }

        /// <summary>
        /// 表名或视图名
        /// </summary>
        public string TableName
        {
            get
            {
                return _TableName;
            }
            set
            {
                _TableName = value;
            }
        }

        /// <summary>
        /// 表字段FieldStr
        /// </summary>
        public string QueryFieldName
        {
            get
            {
                return _QueryFieldName;
            }
            set
            {
                _QueryFieldName = value;
            }
        }

        /// <summary>
        /// 排序字段
        /// </summary>
        public string OrderStr
        {
            get
            {
                return _OrderStr;
            }
            set
            {
                _OrderStr = value;
            }
        }

        /// <summary>
        /// 查询条件
        /// </summary>
        public string QueryCondition
        {
            get
            {
                return _QueryCondition;
            }
            set
            {
                _QueryCondition = value;
            }
        }

        /// <summary>
        /// 主键
        /// </summary>
        public string PrimaryKey
        {
            get 
            {
                return _PrimaryKey;
            }
            set 
            {
                _PrimaryKey = value;
            }
        }

        /// <summary>
        /// 得到分页数据
        /// </summary>
        /// <param name="connectionstring">连接字符串</param>
        /// <returns>DataSet</returns>
        public DataSet QueryDataTable(string connectionstring)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Tables",      SqlDbType.VarChar,  255),
				    new SqlParameter("@PrimaryKey" , SqlDbType.VarChar , 255),	
                    new SqlParameter("@Sort",        SqlDbType.VarChar , 255),
                    new SqlParameter("@CurrentPage", SqlDbType.Int          ),
					new SqlParameter("@PageSize",    SqlDbType.Int          ),									
                    new SqlParameter("@Fields",      SqlDbType.VarChar,  255),
					new SqlParameter("@Filter",      SqlDbType.VarChar,  1000),
                    new SqlParameter("@Group" ,      SqlDbType.VarChar,  1000)
					};
            parameters[0].Value = _TableName;
            parameters[1].Value = _PrimaryKey;
            parameters[2].Value = _OrderStr;
            parameters[3].Value = PageIndex;
            parameters[4].Value = PageSize;
            parameters[5].Value =_QueryFieldName;
            parameters[6].Value = _QueryCondition;
            parameters[7].Value = string.Empty;
            ds = null;
            ds = new DataSet();
            ds = DbHelperSQL.RunProcedure(connectionstring, "uspDividePage", parameters, "tbPageData");

            if (_isQueryTotalCounts)
            {
                _TotalCount = GetTotalCount(connectionstring);
            }

            if (_TotalCount == 0)
            {
                _PageIndex = 0;
                _PageCount = 0;
            }
            else
            {
                _PageCount = _TotalCount % _PageSize == 0 ? _TotalCount / _PageSize : _TotalCount / _PageSize + 1;

                if (_PageIndex > _PageCount)
                {
                    _PageIndex = _PageCount;
                    parameters[4].Value = _PageSize;
                    ds = QueryDataTable(connectionstring);
                }
            }

            return ds;
        }

        /// <summary>
        /// 得到总的记录数
        /// </summary>
        /// <param name="connectionstring">连接字符串</param>
        /// <returns>总的记录数</returns>
        public int GetTotalCount(string connectionstring)
        {
            string strSql = " select count(1) from "+_TableName;

            if (_QueryCondition != string.Empty)
            {
                strSql += " where " + _QueryCondition;
            }

            return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString(), connectionstring));
        }
    }
    #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


二、在控件的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


下面给出本控件完整代码

原创企业级控件库之大数据量分页控件 
 #region  版权信息
 /*---------------------------------------------------------------------*
 // Copyright (C) 2010 http://www.cnblogs.com/huyong
 // 版权所有。 
 // 项目  名称:《Winform通用控件库》
 // 文  件  名: UcPageControl.cs
 // 类  全  名: DotNet.Controls.UcPageControl 
 // 描      述:  分页控件
 // 创建  时间: 2010-06-05
 // 创建人信息: [**** 姓名:胡勇 QQ:80368704 E-Mail:aaa@qq.com *****]
 *----------------------------------------------------------------------*/
 #endregion
 
 using System;
 using System.Collections.Generic;
 using System.ComponentModel;
 using System.Drawing;
 using System.Data;
 using System.Data.SqlClient;
 using System.Windows.Forms;
 using DotNet.Common;
 using DotNet.DBUtility;
 
 namespace DotNet.Controls
 {
     #region 委托申明
     /// <summary>
     /// 申明委托
     /// </summary>
     /// <param name="e"></param>
     /// <returns></returns>
     public delegate int EventPagingHandler(EventPagingArg e);
     #endregion
 
     #region 分页控件
     /// <summary>
     /// 分页控件
     /// 
     /// 修改纪录(此分页控件经过多次修改,已趋于完美,可放心使用。)
     ///     2010-12-06 胡勇 对上一条、下一条、首条、末条数据导航的隐藏,因为控件本身已做了处理。
     ///     2010-12-05 胡勇 对分页控件代码做了相应优化
     ///     2010-06-05 胡勇 创建分页控件
     ///     
     /// <author>
     ///     <name>胡勇</name>
     ///     <QQ>80368704</QQ>
     ///     <Email>aaa@qq.com</Email>
     /// </author>
     /// </summary>
     [ToolboxItem(true)]
     [DefaultEvent("EventPaging")]
     [ToolboxBitmap(typeof(UcPageControl), "DotNet.Controls.Images.UcPageControl.bmp")]
     [Description("分页控件")]
     public partial class UcPageControl : UserControl
     {
         #region 申明事件
         /// <summary>
         /// 单击移动到当前页上一末记录时发生
         /// </summary>
         [Category("数据分页"), Description("单击移动到当前页上一末记录时发生。"),Browsable(false)]
         public event EventHandler OnBindingNavigatorMovePreviousItemClick;
 
         /// <summary>
         /// 单击移动到当前页第一条记录时发生
         /// </summary>
         [Category("数据分页"), Description("单击移动到当前页第一条记录时发生。"), Browsable(false)]
         public event EventHandler OnBindingNavigatorMoveFirstItemClick;
 
         /// <summary>
         /// 单击移动到当前页下一条记录时发生
         /// </summary>
         [Category("数据分页"), Description("单击移动到当前页下一条记录时发生。"), Browsable(false)]
         public event EventHandler OnBindingNavigatorMoveNextItemClick;
 
         /// <summary>
         /// 单击移动到当前页最后一条记录时发生
         /// </summary>
         [Category("数据分页"), Description("单击移动到当前页最后一条记录时发生。"), Browsable(false)]
         public event EventHandler OnBindingNavigatorMoveLastItemClick;
 
         /// <summary>
         /// 单击各分页按钮(上一页、下一页、第一页、最后一页和转到某页)时发生
         /// </summary>
         [Category("数据分页"), Description("分页时发生。")]
         public event EventPagingHandler EventPaging;
         #endregion 
 
         #region 构造函数
         public UcPageControl()
         {
             InitializeComponent();
         }
         #endregion
 
         #region 属性
 
         private int _pageSize    = 50;  //每页显示记录数
         private int _nMax        = 0;   //总记录数
         private int _pageCount   = 0;   //页数=总记录数/每页显示记录数
         private int _pageCurrent = 0;   //当前页号
 
         /// <summary>
         /// 每页显示记录数
         /// </summary>
         [Category("数据分页"), Description("每页显示记录数。"), Browsable(false)]
         public int PageSize
         {
             get 
             { 
                 return _pageSize;
             }
             set
             {
                 _pageSize = value;
                 GetPageCount();//页数
             }
         }              
       
         /// <summary>
         /// 记录总数
         /// </summary>
         [Category("数据分页"), Description("记录总数。"),Browsable(false)]
         public int NMax
         {
             get 
             { 
                 return _nMax; 
             }
             set
             {
                 _nMax = value;
                 GetPageCount();
             }
         }       
 
         /// <summary>
         /// 页数
         /// </summary>
         [Category("数据分页"), Description("页数。"), Browsable(false)]
         public int PageCount
         {
             get 
             { 
                 return _pageCount;
             }
             set 
             { 
                 _pageCount = value; 
             }
         }       
 
         /// <summary>
         /// 当前页号
         /// </summary>
         [Category("数据分页"), Description("当前页号。"), Browsable(false)]
         public int PageCurrent
         {
             get 
             {
                 return _pageCurrent;
             }
             set 
             {
                 _pageCurrent = value; 
             }
         }
         #endregion
 
         #region 方法
         [Category("数据分页"), Description("bindingNavigator。"), Browsable(false)]
         public BindingNavigator ToolBar
         {
             get 
             { 
                 return this.bindingNavigator;
             }
         }
 
         /// <summary>
         /// 得到总页数
         /// </summary>
         private void GetPageCount()
         {
             if (this.NMax > 0)
             {
                 this.PageCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(this.NMax) / Convert.ToDouble(this.PageSize)));
             }
             else
             {
                 this.PageCount = 0;
             }
         }
 
         /// <summary>
         /// 绑定分页控件(关键代码)
         /// </summary>
         public void Bind()
         {
             if (this.EventPaging != null)
             {
                 this.NMax = this.EventPaging(new EventPagingArg(this.PageCurrent));
             }
 
             if (this.PageCurrent > this.PageCount)
             {
                 this.PageCurrent = this.PageCount;
             }
             if (this.PageCount == 1)
             {
                 this.PageCurrent = 1;
             }
             lblPageCount.Text = this.PageCount.ToString();
             this.lblMaxPage.Text = "共"+this.NMax.ToString()+"条记录";
             this.txtCurrentPage.Text = this.PageCurrent.ToString();
 
             if (this.PageCurrent == 1)
             {
                 this.btnPrev.Enabled = false;
                 this.btnFirst.Enabled = false;
             }
             else
             {
                 btnPrev.Enabled = true;
                 btnFirst.Enabled = true;
             }
 
             if (this.PageCurrent == this.PageCount)
             {
                 this.btnLast.Enabled = false;
                 this.btnNext.Enabled = false;
             }
             else
             {
                 btnLast.Enabled = true;
                 btnNext.Enabled = true;
             }
 
             if (this.NMax == 0)
             {
                 btnNext.Enabled = false;
                 btnLast.Enabled = false;
                 btnFirst.Enabled = false;
                 btnPrev.Enabled = false;
             }
         }
 
         #endregion
 
         #region 按钮事件
         private void btnFirst_Click(object sender, EventArgs e)
         {
             PageCurrent = 1;
             this.Bind();
         }
 
         private void btnPrev_Click(object sender, EventArgs e)
         {
             PageCurrent -= 1;
             if (PageCurrent <= 0)
             {
                 PageCurrent = 1;
             }
             this.Bind();
         }
 
         private void btnNext_Click(object sender, EventArgs e)
         {
             this.PageCurrent += 1;
             if (PageCurrent > PageCount)
             {
                 PageCurrent = PageCount;
             }
             this.Bind();
         }
 
         private void btnLast_Click(object sender, EventArgs e)
         {
             PageCurrent = PageCount;
             this.Bind();
         }
 
         private void btnGo_Click(object sender, EventArgs e)
         {
             if (this.txtCurrentPage.Text != null && txtCurrentPage.Text != "")
             {
                 if (Int32.TryParse(txtCurrentPage.Text, out _pageCurrent))
                 {
                     this.Bind();
                 }
                 else
                 {
                     DialogHelper.ShowErrorMsg("输入数字格式错误!");
                 }
             }
         }
 
         private void txtCurrentPage_KeyDown(object sender, KeyEventArgs e)
         {
             if (e.KeyCode == Keys.Enter)
             {
                 this.Bind();
             }
         }
 
         private void bindingNavigatorMovePreviousItem_Click(object sender, EventArgs e)
         {
             if(OnBindingNavigatorMovePreviousItemClick != null)
             {
                 OnBindingNavigatorMovePreviousItemClick(this, null);
             }
         }
 
         private void bindingNavigatorMoveFirstItem_Click(object sender, EventArgs e)
         {
             if (OnBindingNavigatorMoveFirstItemClick != null)
             {
                 OnBindingNavigatorMoveFirstItemClick(this, null);
             }
         }
 
         private void bindingNavigatorMoveNextItem_Click(object sender, EventArgs e)
         {
             if (OnBindingNavigatorMoveNextItemClick != null)
             {
                 OnBindingNavigatorMoveNextItemClick(this, null);
             }
         }
 
         private void bindingNavigatorMoveLastItem_Click(object sender, EventArgs e)
         {
             if (OnBindingNavigatorMoveLastItemClick != null)
             {
                 OnBindingNavigatorMoveLastItemClick(this, null);
             }
         }
     #endregion
     }
     #endregion
 
     #region 自定义事件数据基类
     /// <summary>
     /// 自定义事件数据基类
     /// </summary>
     public class EventPagingArg : EventArgs
     {
         private int _intPageIndex;
         public EventPagingArg(int PageIndex)
         {
             _intPageIndex = PageIndex;
         }
     }
     #endregion
 
     #region 数据源提供(PageData)
     /// <summary>
     /// 数据源提供
     /// </summary>
     public class PageData
     {
         DataSet ds                         = null;
         private int    _PageSize           = 50;           //分页大小
         private int    _PageIndex          = 1;            //当前页
         private int    _PageCount          = 0;            //总页数
         private int    _TotalCount         = 0;            //总记录数
         private string _QueryFieldName     = "*";          //表字段FieldStr
         private bool   _isQueryTotalCounts = true;         //是否查询总的记录条数
         private string _TableName          = string.Empty; //表名        
         private string _OrderStr           = string.Empty; //排序_SortStr
         private string _QueryCondition     = string.Empty; //查询的条件 RowFilter
         private string _PrimaryKey         = string.Empty; //主键
 
         /// <summary>
         /// 是否查询总的记录条数
         /// </summary>
         public bool IsQueryTotalCounts
         {
             get { return _isQueryTotalCounts; }
             set { _isQueryTotalCounts = value; }
         }
 
         /// <summary>
         /// 分页大小(每页显示多少条数据)
         /// </summary>
         public int PageSize
         {
             get
             {
                 return _PageSize;
 
             }
             set
             {
                 _PageSize = value;
             }
         }
 
         /// <summary>
         /// 当前页
         /// </summary>
         public int PageIndex
         {
             get
             {
                 return _PageIndex;
             }
             set
             {
                 _PageIndex = value;
             }
         }
 
         /// <summary>
         /// 总页数
         /// </summary>
         public int PageCount
         {
             get
             {
                 return _PageCount;
             }
         }
 
         /// <summary>
         /// 总记录数
         /// </summary>
         public int TotalCount
         {
             get
             {
                 return _TotalCount;
             }
         }
 
         /// <summary>
         /// 表名或视图名
         /// </summary>
         public string TableName
         {
             get
             {
                 return _TableName;
             }
             set
             {
                 _TableName = value;
             }
         }
 
         /// <summary>
         /// 表字段FieldStr
         /// </summary>
         public string QueryFieldName
         {
             get
             {
                 return _QueryFieldName;
             }
             set
             {
                 _QueryFieldName = value;
             }
         }
 
         /// <summary>
         /// 排序字段
         /// </summary>
         public string OrderStr
         {
             get
             {
                 return _OrderStr;
             }
             set
             {
                 _OrderStr = value;
             }
         }
 
         /// <summary>
         /// 查询条件
         /// </summary>
         public string QueryCondition
         {
             get
             {
                 return _QueryCondition;
             }
             set
             {
                 _QueryCondition = value;
             }
         }
 
         /// <summary>
         /// 主键
         /// </summary>
         public string PrimaryKey
         {
             get 
             {
                 return _PrimaryKey;
             }
             set 
             {
                 _PrimaryKey = value;
             }
         }
 
         /// <summary>
         /// 得到分页数据
         /// </summary>
         /// <param name="connectionstring">连接字符串</param>
         /// <returns>DataSet</returns>
         public DataSet QueryDataTable(string connectionstring)
         {
             SqlParameter[] parameters = {
                     new SqlParameter("@Tables",      SqlDbType.VarChar,  255),
                     new SqlParameter("@PrimaryKey" , SqlDbType.VarChar , 255),    
                     new SqlParameter("@Sort",        SqlDbType.VarChar , 255),
                     new SqlParameter("@CurrentPage", SqlDbType.Int          ),
                     new SqlParameter("@PageSize",    SqlDbType.Int          ),                                    
                     new SqlParameter("@Fields",      SqlDbType.VarChar,  255),
                     new SqlParameter("@Filter",      SqlDbType.VarChar,  1000),
                     new SqlParameter("@Group" ,      SqlDbType.VarChar,  1000)
                     };
             parameters[0].Value = _TableName;
             parameters[1].Value = _PrimaryKey;
             parameters[2].Value = _OrderStr;
             parameters[3].Value = PageIndex;
             parameters[4].Value = PageSize;
             parameters[5].Value =_QueryFieldName;
             parameters[6].Value = _QueryCondition;
             parameters[7].Value = string.Empty;
             ds = null;
             ds = new DataSet();
             ds = DbHelperSQL.RunProcedure(connectionstring, "uspDividePage", parameters, "tbPageData");
 
             if (_isQueryTotalCounts)
             {
                 _TotalCount = GetTotalCount(connectionstring);
             }
 
             if (_TotalCount == 0)
             {
                 _PageIndex = 0;
                 _PageCount = 0;
             }
             else
             {
                 _PageCount = _TotalCount % _PageSize == 0 ? _TotalCount / _PageSize : _TotalCount / _PageSize + 1;
 
                 if (_PageIndex > _PageCount)
                 {
                     _PageIndex = _PageCount;
                     parameters[4].Value = _PageSize;
                     ds = QueryDataTable(connectionstring);
                 }
             }
 
             return ds;
         }
 
         /// <summary>
         /// 得到总的记录数
         /// </summary>
         /// <param name="connectionstring">连接字符串</param>
         /// <returns>总的记录数</returns>
         public int GetTotalCount(string connectionstring)
         {
             string strSql = " select count(1) from "+_TableName;
 
             if (_QueryCondition != string.Empty)
             {
                 strSql += " where " + _QueryCondition;
             }
 
             return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString(), connectionstring));
         }
     }
     #endregion
 }


© 2010 EricHu

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


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

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