分享一个Winform下的分页控件

前两天有一个简单的C/S项目用到分页，因为是Winform下,没有现成的，自己也懒得写，就找了下，看到了ycmoon的一个控件

http://www.cnblogs.com/ycmoon/archive/2010/01/07/1640689.html

参考后，做了简化，只保留了分页的部分，主要是点击事件的Delegate，未做过多测试，有兴趣的朋友可以下载源码自行修改，如有好的建议，也可以给我反馈。3w@live.cn,效果如下：

控件设计界面：

设计时：

运行时：

附带一个取分页数据的存储过程：

View Code

Create DataBase Db_TonyPaging
go

use Db_TonyPaging
go



if exists ( select 1
from sysobjects
where id = object_id ( ' DepartDemo ' )
and type = ' U ' )
drop table DepartDemo
go

/* ============================================================== */
/* Table: DepartDemo */
/* ============================================================== */
create table DepartDemo (
PKID int identity ( 1 , 1 ),
DName nvarchar ( 200 ) null ,
DCode nvarchar ( 500 ) null ,
Manager nvarchar ( 50 ) null ,
ParentID int null default 0 ,
AddUser nvarchar ( 50 ) null ,
AddTime datetime null ,
ModUser nvarchar ( 50 ) null ,
ModTime datetime null ,
CurState smallint not null default 0 ,
Remark nvarchar ( 500 ) null ,
F1 int not null default 0 ,
F2 nvarchar ( 300 ) null ,
constraint PK_DEPARTDEMO primary key (PKID)
)
go

truncate table DepartDemo
go

/* **************创建54 条测试数据*********************
****************downmoo 3w@live.cn ************** */

declare @d datetime
set @d = getdate ()

declare @i int
set @i = 1
while @i <= 54
begin
-- 插入一条测试数据
insert into DepartDemo
select ' 国家统计局房产审计 ' + Cast ( @i as Nvarchar ( 10 )) + ' 科 ' , ' 0 ' , ' 胡不归 ' , 0 , ' DemoUser ' , getdate (),
'' , ' 1900-01-01 ' , 1 , ' 专业评估全国房价，为老百姓谋福祉 ' , 0 , ''
set @i = @i + 1
end

go





-- ***********分页存储过程用于SQL server2005/2008、2008R2****************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [ dbo ] . [ ZJF_CPP_GetPagedRecordFor2005_2008 ]
( @Table varchar ( 1000 ), -- 表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
@TIndex nvarchar ( 100 ), -- 主键，可以带表头 a.AID
@Column nvarchar ( 2000 ) = ' * ' , -- 读取字段
@Sql nvarchar ( 3000 ) = '' , -- Where条件
@PageIndex int = 1 , -- 开始页码
@PageSize int = 10 , -- 页大小
@Sort nvarchar ( 200 ) = '' -- 排序字段
)
AS

DECLARE @strWhere varchar ( 2000 )
declare @strsql nvarchar ( 3900 )
IF @Sql IS NOT NULL AND len ( ltrim ( rtrim ( @Sql ))) > 0
BEGIN
SET @strWhere = ' WHERE ' + @Sql + ' '
END
ELSE
BEGIN
SET @strWhere = ''
END

if ( charindex ( ltrim ( rtrim ( @TIndex )), @Sort ) = 0 )
begin
if ( @Sort = '' )
set @Sort = @TIndex + ' DESC '
else
set @Sort = @Sort + ' , ' + @TIndex + ' DESC '
end
IF @PageIndex < 1
SET @PageIndex = 1

if @PageIndex = 1 -- 第一页提高性能
begin
set @strsql = ' select top ' + str ( @PageSize ) + ' ' + @Column + ' from ' + @Table + ' ' + @strWhere + ' ORDER BY ' + @Sort
end
else
begin
/**//**//**//* Execute dynamic query */
DECLARE @START_ID nvarchar ( 50 )
DECLARE @END_ID nvarchar ( 50 )
SET @START_ID = convert ( nvarchar ( 50 ),( @PageIndex - 1 ) * @PageSize + 1 )
SET @END_ID = convert ( nvarchar ( 50 ), @PageIndex * @PageSize )
set @strsql = ' SELECT ' + @Column + '
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @Sort + ' ) AS rownum,
' + @Column + '
FROM ' + @Table + ' WITH(NOLOCK) ' + @strWhere + ' ) AS D
WHERE rownum BETWEEN ' + @START_ID + ' AND ' + @END_ID + ' ORDER BY ' + @Sort
END
EXEC ( @strsql )
print @strsql
set @strsql = ' SELECT Count(1) as TotalRecords FROM ' + @Table + ' WITH(NOLOCK) ' + @strWhere
print @strsql
EXEC ( @strsql )

在WinForm项目中，需要设置控件的总记录数RecordCount（由分页存储过程计算得出），和翻页事件winFormPager1_PageIndexChanged。

测试源码如下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DemoPager
{
public partial class frmMain : Form
{
public frmMain()
{
InitializeComponent();
}

#region Members
// 总记录数
public int RecordCount = 0 ;
private string strConn = @" Data Source=ap2\vegnet;Initial Catalog=Db_TonyPaging;Integrated Security=SSPI; " ;
// "Server=localhost;database=Db_TonyPaging;uid=sa;pwd=sa;";
private string strProcedure = " ZJF_CPP_GetPagedRecordFor2005_2008 " ;
#endregion

#region Methods

/// <summary>
/// 绑定第Index页的数据
/// </summary>
/// <param name="Index"></param>
private void BindDataWithPage( int Index)
{
winFormPager1.PageIndex = Index;
// winFormPager1.PageSize = 10;; ;
dgvList.DataSource = GetData(strConn, strProcedure, Index, winFormPager1.PageSize);
// 获取并设置总记录数
winFormPager1.RecordCount = RecordCount;
}


/// <summary>
/// 获取数据源
/// </summary>
/// <param name="conn"></param>
/// <param name="strProcedure"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
private DataTable GetData( string conn, string strProcedure, int pageIndex, int pageSize)
{

using (SqlConnection connection = new SqlConnection(conn))
{
SqlCommand command = new SqlCommand(strProcedure, connection);
command.CommandType = CommandType.StoredProcedure; // 采用存储过程
// 存储过程参数
command.Parameters.Add( " @Table " , SqlDbType.NVarChar, 1000 ).Value = " DepartDemo " ;
command.Parameters.Add( " @TIndex " , SqlDbType.NVarChar, 100 ).Value = " PKID " ;
command.Parameters.Add( " @Column " , SqlDbType.NVarChar, 2000 ).Value = " * " ;
command.Parameters.Add( " @Sql " , SqlDbType.NVarChar, 3000 ).Value = " 1=1 " ;
command.Parameters.Add( " @PageIndex " , SqlDbType.Int, 8 ).Value = pageIndex.ToString();
command.Parameters.Add( " @PageSize " , SqlDbType.Int, 8 ).Value = pageSize.ToString();
command.Parameters.Add( " @Sort " , SqlDbType.NVarChar, 200 ).Value = " PKID desc " ;
// 打开连接
if (connection.State != ConnectionState.Open) { connection.Open(); }
try
{
// 填充数据
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
// 获取总记录数
RecordCount = Convert.ToInt32(ds.Tables[ 1 ].Rows[ 0 ][ 0 ]);
// 返回数据集
return ds.Tables[ 0 ];

}
catch (SqlException err)
{
MessageBox.Show(err.Message);
return null ; ;
}
finally
{
connection.Close();
}
}
}
#endregion

#region Events
private void frmMain_Load( object sender, EventArgs e)
{
// 不自动生成列
dgvList.AutoGenerateColumns = false ;
// 绑定数据
BindDataWithPage( 1 );
}

/// <summary>
/// 翻页事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void winFormPager1_PageIndexChanged( object sender, EventArgs e)
{
BindDataWithPage(winFormPager1.PageIndex);
}
#endregion
}
}

下载控件源码及演示程序(含SQL)

下载地址一

下载地址二

转载于:https://www.cnblogs.com/downmoon/archive/2011/02/10/1950789.html