版權聲明：本文為部落客原創文章，未經部落客允許不得轉載。 https://blog.csdn.net/chinahuyong/article/details/6084638
原創企業級控件庫之大資料量分頁控件
釋出日期：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:[email protected] *****]
*----------------------------------------------------------------------*/
#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>[email protected]</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
原創作品，轉貼請注明作者和出處，留此資訊。
------------------------------------------------
cnBlobs:
http://www.cnblogs.com/huyong/CSDN：
http://blog.csdn.net/chinahuyong![DB2表壓縮功能[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)