不说废话,大牛初始亦菜鸟,成功之路贵执行,马上行动!如果想获得更优质的显示效果,请在Chrome、Firefox等现代浏览器浏览本站。

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

:: 码农生涯 EricHu 913℃ 0评论

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

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

   摘要

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

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

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

  用到的分页存储过程: 

-- =============================================
-- Author: EricHu  QQ:406590790 WebSite:http://www.cnblogs.com/huyong/
-- Create date: 2008-10-25
-- Description: 千万数量级分页存储过程
-- Modify Date: 2010-10-26
-- =============================================  

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO

 ALTERPROCEDURE[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
 ***************************************************************/
 (
@Tablesvarchar(2000),
@PrimaryKeyvarchar(500),
@Sortvarchar(500) =NULL,
@CurrentPageint=1,
@PageSizeint=10,
@Fieldsvarchar(2000) ='*',
@Filtervarchar(1000) =NULL,
@Groupvarchar(1000) =NULL
 )
AS
/*默认排序*/
IF@SortISNULLOR@Sort=''
SET@Sort=@PrimaryKey

DECLARE@SortTablevarchar(1000)
DECLARE@SortNamevarchar(1000)
DECLARE@strSortColumnvarchar(1000)
DECLARE@operatorchar(2)
DECLARE@typevarchar(1000)
DECLARE@precint

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

IFCHARINDEX('.', @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 =@SortTableAND c.name =@SortName

IFCHARINDEX('char', @type) >0
SET@type=@type+'('+CAST(@precASvarchar) +')'

DECLARE@strPageSizevarchar(500)
DECLARE@strStartRowvarchar(500)
DECLARE@strFiltervarchar(1000)
DECLARE@strSimpleFiltervarchar(1000)
DECLARE@strGroupvarchar(1000)
/*默认当前页*/
IF@CurrentPage<1
SET@CurrentPage=1

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

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

IF@GroupISNOTNULLAND@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+'
')
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO 

  本控件类图  

   本控件UcpageControl类详细信息


   本控件PageData类详细信息


   本控件核心代码

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

/// <summary>
    /// 数据源提供
    /// 
    ///     修改记录:
    ///     2010-12-19 胡勇 修改int GetTotalCount(string connectionstring)方法
    ///                     当查询条件为空时,高效得到指定表的记录总数。
    /// </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;
            //}
            string strSql = "";
            if (_QueryCondition != string.Empty)
            {
                strSql = " select count(1) from " + _TableName + " where " + _QueryCondition; ;
            }
            else
            {
                strSql = "select rows from sys.sysindexes where id = object_id('" 
                       + _TableName + "') and indid in (0,1) ";//当查询条件为空时,高效得到记录总数
            }

            return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString(), connectionstring));
        }

   窗体调用方法

一、设置窗体调用公共方法。

       #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*/
CREATEDATABASE dbTest
go

/*二、创建数据表*/
USE[dbTest]
GO

CREATETABLE[dbo].[tbTestData](
    [UniqueID][bigint]IDENTITY(20000,1) NOTNULL,
    [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]NULLCONSTRAINT[DF_tbTestData_InsetDataTime]DEFAULT (getdate()),
 CONSTRAINT[PK_tbTestData]PRIMARYKEYCLUSTERED 
(
    [UniqueID]ASC
)WITH (IGNORE_DUP_KEY =OFF) ON[PRIMARY]
) ON[PRIMARY]
GO

/*三、增加测试数据*/
declare@countbigint
select@count=1
while@count<=5000000
begin
    insertinto tbTestData
    values('Company'+cast(@countasvarchar),'CompanyCode'+cast(@countasvarchar)
          ,'Address'+cast(@countasvarchar),'Owner'+cast(@countasvarchar)
          ,'Memo'+cast(@countasvarchar),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:80368704@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>80368704@qq.com</Email>
    /// </author>
    /// </summary>
    [ToolboxItem(true)]
    [DefaultEvent("EventPaging")]
    [ToolboxBitmap(typeof(UcPageControl), "Images.UcPageControlIcon.png")]
    [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>
    /// 数据源提供
    /// 
    ///     修改记录:
    ///     2010-12-19 胡勇 修改int GetTotalCount(string connectionstring)方法
    ///                     当查询条件为空时,高效得到指定表的记录总数。
    /// </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;
            //}
            string strSql = "";
            if (_QueryCondition != string.Empty)
            {
                strSql = " select count(1) from " + _TableName + " where " + _QueryCondition; ;
            }
            else
            {
                strSql = "select rows from sys.sysindexes where id = object_id('" 
                       + _TableName + "') and indid in (0,1) ";//当查询条件为空时,高效得到记录总数
            }

            return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString(), connectionstring));
        }
    }
    #endregion
}

转载请注明:RDIFramework.NET » 原创企业级控件库之大数据量分页控件

喜欢 (2)or分享 (0)
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址