首先,需要定义一个数据库存储过程,用来获得指定页的数据记录,大致的数据结构和存储过程如下(在SQL Server 2000下):
字段名 | 类型 | 备注 |
EmployeeID | Int | 自增字段,主键 |
EmployeeName | Varchar(20) |
|
Salary | Int |
|
CellPhone | Varchar(20) |
|
EmailAddress | Varchar(20) |
|
存储过程为:
CREATE PROCEDURE GetEmployees @EmployeeNum int, @StartNO int, @@TotalCount INT OUTPUT, @@RealNum INT OUTPUT AS
-- Get employees from DB through the specific number and the start position
DECLARE @PreRecCount VARCHAR( 10 )
DECLARE @CurRecCount VARCHAR( 10 )
SELECT @@TotalCount = COUNT(*) FROM EmployeeInfo
IF @@TotalCount > ( @StartNO + 1 ) * @EmployeeNum
SET @@RealNum = @EmployeeNum
ELSE
SET @@RealNum = @@TotalCount - @StartNO * @EmployeeNum
-- Get employees by the computed number
SET @CurRecCount = CAST( @StartNO * @EmployeeNum + @@RealNum AS VARCHAR( 10 ) )
IF @STARTNO = 0
EXEC( 'SELECT TOP ' + @CurRecCount + ' * FROM EmployeeInfo ORDER BY EmployeeID ASC' )
ELSE
BEGIN
SET @PreRecCount = CAST( @StartNO * @ EmployeeNum AS VARCHAR( 10 ) )
EXEC( 'SELECT TOP ' + @CurRecCount + ' * FROM EmployeeInfo WHERE EmployeeID NOT IN '
+ '(SELECT TOP ' + @PreRecCount + ' EmployeeID FROM EmployeeInfo ORDER BY EmployeeID ASC) '
+ 'ORDER BY EmployeeID ASC'
)
END
GO
然后就是调用存储过程来进行显示,比较完整的源代码如下:
//------------------------ Multi Page Demo ------------------------------------
//-----------------------------------------------------------------------------
//---File:frmMultiPagesDemo.cs
//---Description:The main form file to show how to use pages in datagrid
//---Author:Knight
//---Date:Mar.23, 2006
//-----------------------------------------------------------------------------
//-----------------------{ Multi Page Demo }-----------------------------------
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace CSDataGrid
{
/// <summary>
/// Summary description for frmMultiPages.
/// </summary>
public class frmMultiPages : System.Windows.Forms.Form
{
private System.Windows.Forms.Label lblPageInfo;
private System.Windows.Forms.Button btnPrevious;
private System.Windows.Forms.Button btnNext;
protected SqlConnection sqlConn = new SqlConnection();
protected SqlDataAdapter sqlDAdapter = null;
protected DataSet sqlRecordSet = null;
private int nCurPageNum = 0;
private const int REC_NUM_PER_PAGE = 3;
private int nTotalCount;
private int nRealNum;
private int nTotalPage;
private System.Windows.Forms.DataGrid dtgUserInfo;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public frmMultiPages()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.dtgUserInfo = new System.Windows.Forms.DataGrid();
this.lblPageInfo = new System.Windows.Forms.Label();
this.btnPrevious = new System.Windows.Forms.Button();
this.btnNext = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dtgUserInfo)).BeginInit();
this.SuspendLayout();
//
// dtgUserInfo
//
this.dtgUserInfo.DataMember = "";
this.dtgUserInfo.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dtgUserInfo.Location = new System.Drawing.Point(16, 16);
this.dtgUserInfo.Name = "dtgUserInfo";
this.dtgUserInfo.Size = new System.Drawing.Size(552, 416);
this.dtgUserInfo.TabIndex = 0;
//
// lblPageInfo
//
this.lblPageInfo.AutoSize = true;
this.lblPageInfo.Location = new System.Drawing.Point(16, 440);
this.lblPageInfo.Name = "lblPageInfo";
this.lblPageInfo.Size = new System.Drawing.Size(83, 16);
this.lblPageInfo.TabIndex = 1;
this.lblPageInfo.Text = "{0} of {1} Pages";
//
// btnPrevious
//
this.btnPrevious.Location = new System.Drawing.Point(408, 440);
this.btnPrevious.Name = "btnPrevious";
this.btnPrevious.TabIndex = 2;
this.btnPrevious.Text = "Previous";
this.btnPrevious.Click += new System.EventHandler(this.btnPrevious_Click);
//
// btnNext
//
this.btnNext.Location = new System.Drawing.Point(488, 440);
this.btnNext.Name = "btnNext";
this.btnNext.TabIndex = 3;
this.btnNext.Text = "Next";
this.btnNext.Click += new System.EventHandler(this.btnNext_Click);
//
// frmMultiPages
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(584, 469);
this.Controls.Add(this.btnNext);
this.Controls.Add(this.btnPrevious);
this.Controls.Add(this.lblPageInfo);
this.Controls.Add(this.dtgUserInfo);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;
this.MaximizeBox = false;
this.Name = "frmMultiPages";
this.Text = "Multi Pages In DataGrid";
this.Load += new System.EventHandler(this.frmMultiPages_Load);
((System.ComponentModel.ISupportInitialize)(this.dtgUserInfo)).EndInit();
this.ResumeLayout(false);
}
#endregion
private void frmMultiPages_Load(object sender, System.EventArgs e)
{
//Set connection string
sqlConn.ConnectionString = yourconnectionstring;
//Connect to DB
if( ConnectDB() )
{
//Bind data to datagrid
BindData();
}
}
/// <summary>
/// Connect to DB
/// </summary>
/// <returns>If connected, return True; else return False</returns>
private bool ConnectDB()
{
//Check current connection's state
try
{
if( sqlConn.State == ConnectionState.Closed
|| sqlConn.State == ConnectionState.Broken )
{
//Connection is not available
sqlConn.Close();
}
else
//Connection is available
return true;
}
catch{};
//Re-connect
try
{
sqlConn.Open();
}
catch(SqlException e)
{
//Sql's exception
MessageBox.Show( e.Message );
}
catch(Exception e)
{
//Other exception
MessageBox.Show( e.Message );
}
if( sqlConn.State == ConnectionState.Closed
|| sqlConn.State == ConnectionState.Broken )
//Connection is not available
return false;
else
//Connection is available
return true;
}
private void AddDGStyle()
{
DataGridTableStyle ts1 = new DataGridTableStyle();
//specify the table from dataset (required step)
ts1.MappingName = "EmployeeInfo";
PropertyDescriptorCollection pdc = this.BindingContext
[sqlRecordSet, "EmployeeInfo"].GetItemProperties();
DataGridColumnStyle TextCol = new DataGridTextBoxColumn( pdc["EmployeeID"], "i" );
TextCol.MappingName = "EmployeeID";
TextCol.HeaderText = "EmployeeID";
TextCol.Width = 0;
TextCol.ReadOnly = true;
ts1.GridColumnStyles.Add(TextCol);
TextCol = new DataGridTextBoxColumn();
TextCol.MappingName = "EmployeeName";
TextCol.HeaderText = "Employee Name";
TextCol.Width = 100;
ts1.GridColumnStyles.Add(TextCol);
TextCol = new DataGridTextBoxColumn( pdc["Salary"], "i" );
TextCol.MappingName = "Salary";
TextCol.HeaderText = "Salary";
TextCol.Width = 80;
ts1.GridColumnStyles.Add(TextCol);
TextCol = new DataGridTextBoxColumn();
TextCol.MappingName = "CellPhone";
TextCol.HeaderText = "Cell Phone";
TextCol.Width = 80;
ts1.GridColumnStyles.Add(TextCol);
TextCol = new DataGridTextBoxColumn();
TextCol.MappingName = "EmailAddress";
TextCol.HeaderText = "Email Address";
TextCol.Width = 100;
ts1.GridColumnStyles.Add(TextCol);
dtgUserInfo.TableStyles.Add(ts1);
}
private void GetEmployeeData()
{
sqlDAdapter = new SqlDataAdapter( );
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlComm.CommandText = "GetEmployees";
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.Add ( "@EmployeeNum", REC_NUM_PER_PAGE );
sqlComm.Parameters.Add ( "@StartNO", nCurPageNum );
SqlParameter sqlPar = sqlComm.Parameters.Add ( "@@TotalCount", SqlDbType.Int );
sqlPar.Direction = ParameterDirection.Output;
sqlPar = sqlComm.Parameters.Add ( "@@RealNum", SqlDbType.Int );
sqlPar.Direction = ParameterDirection.Output;
sqlDAdapter.SelectCommand = sqlComm;
//Fill dataset
sqlRecordSet = new DataSet();
sqlDAdapter.Fill( sqlRecordSet, "EmployeeInfo" );
nTotalCount = Convert.ToInt32( sqlComm.Parameters["@@TotalCount"].Value.ToString() );
nRealNum = Convert.ToInt32( sqlComm.Parameters["@@RealNum"].Value.ToString() );
sqlComm.Dispose();
nTotalPage = nTotalCount - nTotalCount % REC_NUM_PER_PAGE;
nTotalPage /= REC_NUM_PER_PAGE;
nTotalPage++;
lblPageInfo.Text = string.Format( "{0} of {1} pages", nCurPageNum + 1, nTotalPage );
if( nCurPageNum + 1 >= nTotalPage )
btnNext.Enabled = false;
else
btnNext.Enabled = true;
if( nCurPageNum == 0 )
btnPrevious.Enabled = false;
else
btnPrevious.Enabled = true;
}
private void BindData()
{
GetEmployeeData();
//Bind datagrid with dataset
dtgUserInfo.SetDataBinding( sqlRecordSet, "EmployeeInfo");
//Add datagrid style
AddDGStyle();
}
private void ReBindData()
{
GetEmployeeData();
//Bind datagrid with dataset
dtgUserInfo.SetDataBinding( sqlRecordSet, "EmployeeInfo");
}
private void btnPrevious_Click(object sender, System.EventArgs e)
{
nCurPageNum--;
sqlRecordSet.Tables["EmployeeInfo"].Rows.Clear();
ReBindData();
}
private void btnNext_Click(object sender, System.EventArgs e)
{
nCurPageNum++;
sqlRecordSet.Tables["EmployeeInfo"].Rows.Clear();
ReBindData();
}
}
}
发表于 @ 2006年03月23日 12:33:00|评论(6 <script type=text/javascript>AddFeedbackCountStack("635885")</script> )|编辑
- 构建最佳 SOA 安全知识!
- SOA 安全性基础知识系列三部分之一
![](http://info-database.csdn.net/Upload/2008-04-21/ibm-logo.png)
- 在 Eclipse 中构建 Web 服务
- 如何创建和部署有用的 PHP 项目 了解契约优先开发原来!
![](http://info-database.csdn.net/Upload/2008-04-21/ibm-logo.png)
评论
# spring21st 发表于2006-05-27 10:28:00 IP: 222.43.172.*-
实现效果漂亮!
谢谢
# itmingong 发表于2006-12-08 15:37:23 IP: 218.20.61.*-
EXEC( 'SELECT TOP ' + @CurRecCount + ' * FROM EmployeeInfo WHERE EmployeeID NOT IN ' + '(SELECT TOP ' + @PreRecCount + ' EmployeeID FROM EmployeeInfo ORDER BY EmployeeID ASC) ' + 'ORDER BY EmployeeID ASC' ) 这是错误的
# chunhongwu 发表于2006-12-16 17:01:30 IP: 202.118.75.*-
EXEC( 'SELECT TOP ' + @CurRecCount + ' * FROM EmployeeInfo WHERE EmployeeID NOT IN ' + '(SELECT TOP ' + @PreRecCount + ' EmployeeID FROM EmployeeInfo ORDER BY EmployeeID ASC) ' + 'ORDER BY EmployeeID ASC' ) 这是错误的 这个错在哪儿了?说详细点
# canxing2004 发表于2007-01-19 18:48:53 IP: 211.69.141.*-
像Access这样不能生成存储过程的咋办了。。。??
# Knight94 发表于2007-01-20 11:09:54 IP: 210.77.27.*-
to canxing2004
参看这篇文章
探讨DataGrid的几种分页方法的优劣
http://blog.csdn.net/Knight94/archive/2006/03/27/640008.aspx