using System;
using System.Web;
using System.Collections;
using System.Web.UI;
using System.Data;
using System.Data.SqlClient;
using WorkStatic.database;
namespace ShareSoft.PageClass
{
/// <summary>
/// 分页操作类
/// </summary>
public class oppage:IDisposable
{
#region 构造函数
public oppage(StateBag _ViewState)
{
ViewState=_ViewState;
}
#endregion
private StateBag ViewState=new StateBag();
#region 获取或设置数据库连接字符串
private string _Connection = string.Empty;
/// <summary>
/// 获取或设置数据库连接字符串
/// </summary>
public string Link
{
get
{
return _Connection;
}
set
{
_Connection = value;
}
}
#endregion
#region 获取或设置表名
/// <summary>
/// 获取或设置表名
/// </summary>
public string TableName
{
get
{
return Convert.ToString(ViewState["TableName"]);
}
set
{
ViewState["TableName"]=value;
}
}
#endregion
#region 获取或设置当前页号
private int _CurPage = 1;
/// <summary>
/// 获取或设置当前页号
/// </summary>
public int CurPage
{
get
{
return _CurPage;
}
set
{
_CurPage = value;
}
}
#endregion
#region 获取或设置每页显示的数量
/// <summary>
/// 获取或设置每页显示的数量
/// </summary>
public int PageSize
{
get
{
if(ViewState["PageSize"]==null)
ViewState["IsCount"]=10;
return Convert.ToInt32(ViewState["PageSize"]);
}
set
{
ViewState["PageSize"]=value;
}
}
#endregion
#region 是否计算总记录数
/// <summary>
/// 是否计算总记录数
/// </summary>
public enum CountType
{
/// <summary>
/// 计算总记录数
/// </summary>
Yes=1,
/// <summary>
/// 不计算总记录数
/// </summary>
No=0
}
/// <summary>
/// 获取或设置是否计算总记录数
/// </summary>
public CountType IsCount
{
get
{
if(ViewState["IsCount"]==null)
ViewState["IsCount"]=CountType.No;
return (CountType)ViewState["IsCount"];
}
set
{
ViewState["IsCount"]=value;
}
}
#endregion
#region 获取或设置排序表达式
/// <summary>
/// 获取或设置排序表达式
/// </summary>
public string OrderEexpression
{
get
{
return Convert.ToString(ViewState["OrderEexpression"]);
}
set
{
ViewState["OrderEexpression"]=value.ToUpper();
}
}
#endregion
#region 获取或设置要显示的字段列表
/// <summary>
/// 获取或设置要显示的字段列表
/// </summary>
public string FieldList
{
get
{
return Convert.ToString(ViewState["FieldList"]);
}
set
{
ViewState["FieldList"]=value.ToUpper();
}
}
#endregion
#region 获取或设置要显示的主键字段
/// <summary>
/// 获取或设置要显示的主键字段
/// </summary>
public string KeyField
{
get
{
return Convert.ToString(ViewState["KeyField"]);
}
set
{
ViewState["KeyField"]=value;
}
}
#endregion
#region 获取或设置查询条件
/// <summary>
/// 获取或设置查询条件
/// </summary>
public string ConditionEexpression
{
get
{
return Convert.ToString(ViewState["ConditionEexpression"]);
}
set
{
ViewState["ConditionEexpression"]=value;
}
}
#endregion
#region 获取或设置总页数
/// <summary>
/// 获取或设置总页数
/// </summary>
public int PageCount
{
get
{
return Convert.ToInt32(ViewState["PageCount"]);
}
set
{
ViewState["PageCount"]=value;
}
}
#endregion
#region 总记录数
/// <summary>
/// 总记录数
/// </summary>
public int RecordCount
{
get
{
return Convert.ToInt32(ViewState["RecordCount"]);
}
set
{
ViewState["RecordCount"]=value;
}
}
#endregion
#region 页面所需的DataView
private DataView _PageDataView = null;
/// <summary>
/// 页面所需的DataView
/// </summary>
public DataView PageDataView
{
get
{
return _PageDataView;
}
set
{
_PageDataView = value;
}
}
#endregion
#region 设置总页数
/// <summary>
/// 设置总页数
/// </summary>
private void SetPageCount()
{
if( (RecordCount % PageSize)>0 )
{
PageCount = (RecordCount/PageSize)+1;
}
else
{
PageCount = RecordCount/PageSize;
}
}
#endregion
#region 分页的主方法
/// <summary>
/// 分页的主方法
/// </summary>
public void main()
{
if(OrderEexpression==""&&KeyField!="")
{
OrderEexpression=KeyField + " ASC ";
}
#region 确定是否要统计总记录数
CountType MustCount;
if(IsCount==CountType.No)
{
if(CurPage==1)
{
MustCount=CountType.Yes;
}
else
{
MustCount=CountType.No;
}
}
else
{
MustCount=CountType.Yes;
}
#endregion
#region 参数
SqlParameter[] ps = new SqlParameter[9];
ps[0] = new SqlParameter("@TableName",SqlDbType.VarChar,50);
ps[0].Value=TableName;
ps[1] = new SqlParameter("@FieldList",SqlDbType.VarChar,255);
ps[1].Value=FieldList;
ps[2] = new SqlParameter("@KeyField",SqlDbType.VarChar,50,KeyField);
ps[2].Value=KeyField;
ps[3] = new SqlParameter("@PageSize",SqlDbType.Int);
ps[3].Value=PageSize;
ps[4] = new SqlParameter("@PageIndex",SqlDbType.Int);
ps[4].Value=CurPage;
ps[5] = new SqlParameter("@IsCount",SqlDbType.Bit);
ps[5].Value=(int)MustCount;
ps[6] = new SqlParameter("@OrderEexpression",SqlDbType.VarChar,100);
ps[6].Value=OrderEexpression;
ps[7] = new SqlParameter("@ConditionEexpression",SqlDbType.VarChar,500);
ps[7].Value=ConditionEexpression;
ps[8] = new SqlParameter("@RecordCount",SqlDbType.Int );
ps[8].Value=RecordCount;
#endregion
DataSet ds = SqlHelper.ExecuteDataset(Link,CommandType.StoredProcedure,"up_Page_Result",ps);
if(ds==null||ds.Tables.Count!=2)
{
PageDataView=null;
return;
}
else
{
RecordCount=Convert.ToInt32(ds.Tables[0].Rows[0][0]);
if(MustCount==CountType.Yes)
{
SetPageCount();
}
if(CurPage>1&&KeyField!="")
{
if(FieldList.IndexOf("*")==-1)
{
string[] ArrSortField=OrderEexpression.Replace("DESC","").Replace("ASC","").Split(',');
foreach(string SortField in ArrSortField)
{
if(FieldList.IndexOf(SortField.Trim())==-1)
{
PageDataView=null;
return;
}
}
}
ds.Tables[1].DefaultView.Sort=OrderEexpression;
}
PageDataView=ds.Tables[1].DefaultView;
}
}
#endregion
#region 释放对象
/// <summary>
/// 释放对象
/// </summary>
public void Dispose()
{
GC.SuppressFinalize(this);
}
#endregion
}
}
存储过程
-- 获取指定页的数据
CREATE PROCEDURE dbo.up_Page_Result
@TableName varchar(50), -- 表名
@FieldList varchar(255), -- 要显示的字段名
@KeyField varchar(50)='', -- 要的主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderEexpression varchar(100), -- 排序字段及方式(注意: 不要加ORDER BY)
@ConditionEexpression varchar(500) = '', -- 查询条件 (注意: 不要加 WHERE)
@RecordCount int =0 --总的记录数
AS
DECLARE @EXECSQL Nvarchar(2000) -- 主语句
DECLARE @ReverseOrderEexpression varchar(100) -- 排序类型
SET @OrderEexpression=UPPER(@OrderEexpression)
SET @OrderEexpression=' ORDER BY '+@OrderEexpression
SET @ReverseOrderEexpression=REPLACE(@OrderEexpression,'DESC','###')
SET @ReverseOrderEexpression=REPLACE(@ReverseOrderEexpression,'ASC','DESC')
SET @ReverseOrderEexpression=REPLACE(@ReverseOrderEexpression,'###','ASC')
IF @ConditionEexpression != ''
SET @ConditionEexpression = ' WHERE ' + @ConditionEexpression
ELSE
SET @ConditionEexpression = ' WHERE 1=1 '
---总的记录数
IF @IsCount != 0 OR @RecordCount=0
BEGIN
IF( @KeyField='' )
SET @EXECSQL = 'SELECT @RecordCount=COUNT('+'1'+') FROM [' + @TableName + ']' + @ConditionEexpression
ELSE
SET @EXECSQL = 'SELECT @RecordCount=COUNT(['+@KeyField+']) FROM [' + @TableName + ']' + @ConditionEexpression
--PRINT @EXECSQL
EXECUTE sp_executesql @EXECSQL,
N' @RecordCount int OUTPUT',
@RecordCount OUTPUT
SET @EXECSQL ='SELECT '+STR(@RecordCount)+' AS RecordCount '
EXEC (@EXECSQL)
END
ELSE
BEGIN
SET @EXECSQL ='SELECT '+STR(@RecordCount)+' AS RecordCount '
EXEC (@EXECSQL)
END
IF @PageIndex = 1
BEGIN
SET @EXECSQL = 'SELECT TOP ' + STR(@PageSize) + ' '+@FieldList+' FROM '+CHAR(13)
+'['+ @TableName + ']' + @ConditionEexpression + ' ' + @OrderEexpression
END
ELSE
BEGIN
IF( @KeyField='' )
BEGIN
DECLARE @TopNum int
IF(@PageIndex=CEILING( @RecordCount * 1.0 / @PageSize ) AND @RecordCount % @PageSize <> 0 )
SET @TopNum= @RecordCount % @PageSize
ELSE
SET @TopNum= @PageSize
SET @EXECSQL = 'SELECT TOP ' + STR(@TopNum) + ' '+@FieldList+' FROM'+CHAR(13)
+ ' ['+ @TableName + '] A WHERE ['+@KeyField+'] IN ( SELECT TOP ' + STR( @PageIndex*@PageSize ) +CHAR(13)
+ ' ['+ @KeyField + '] FROM [' + @TableName + '] ' + @ConditionEexpression + ' '+CHAR(13)
+ @OrderEexpression + ') '+ @ReverseOrderEexpression+CHAR(13)
END
ELSE
BEGIN
DECLARE @BeginIndex int, @EndIndex int
DECLARE @NewField varchar(100)
SET @NewField='[NEW_AutoId]'
SET @BeginIndex=@PageSize*(@PageIndex-1)
SET @EndIndex=@PageSize*@PageIndex-1
SET @EXECSQL=' SELECT '+@NewField+'=IDENTITY(int,0,1),'+@FieldList+' INTO #tb FROM '+@TableName+' '
+' '+@ConditionEexpression+' '+ @OrderEexpression
+' SELECT '+@FieldList+' FROM #tb WHERE '+@NewField+' BETWEEN '+STR(@BeginIndex)+' AND '+STR(@EndIndex)
+' DROP TABLE #tb'
END
END
PRINT @EXECSQL
EXEC (@EXECSQL)
GO
aspx页面
<%@ Page language="c#" Codebehind="pagetest.aspx.cs" AutoEventWireup="false" Inherits="WorkStatic.pagetest" %>
<%@ Register TagPrefix="MyCtl" Namespace="ShareSoft.PageClass" Assembly="WorkStatic" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>pagetest</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<FONT face="宋体">
<asp:datagrid id="dgpage" runat="server"></asp:datagrid></FONT><MYCTL:ASPNETPAGER id="anp1" runat="server" UrlPaging="False" ShowInputBox="Always" CustomInfoTextAlign="Right"
HorizontalAlign="Right" AlwaysShow="True" LastPageText="尾页" PrevPageText="上页" NextPageText="下页" FirstPageText="首页" ShowCustomInfoSection="Left" CustomInfoText="undefine"
CustomInfoSectionWidth="300px"></MYCTL:ASPNETPAGER></form>
</body>
</HTML>
cs文件:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using ShareSoft.PageClass;
namespace WorkStatic
{
/// <summary>
/// pagetest 的摘要说明。
/// </summary>
public class pagetest : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid dgpage;
protected ShareSoft.PageClass.AspNetPager anp1;
#region 页面初始化
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
mydateBind(1);
}
}
#endregion
#region 绑定数据
/// <summary>
/// 绑定数据
/// </summary>
/// <param name="page"></param>
private void mydateBind(int CurPage)
{
using(oppage mypage=new oppage(ViewState))
{
mypage.Link="data source=127.0.0.1;initial catalog=xxx;password=xxxjj;user id=xxxyy";
mypage.TableName="T_SoftInfo";
mypage.ConditionEexpression="CONTAINS(softname, '/"系统/" or /"图/"')";
mypage.PageSize=10;
mypage.CurPage=CurPage;
mypage.KeyField="Softid";
mypage.OrderEexpression="updatetime desc,softid desc";
mypage.FieldList="*";
mypage.main();
this.dgpage.DataSource=mypage.PageDataView;
this.dgpage.DataBind();
this.anp1.PageSize =10;
this.anp1.RecordCount = mypage.RecordCount;
this.anp1.CurrentPageIndex=CurPage;
this.anp1.DataBind();
this.anp1.CustomInfoText = "当前第 "+CurPage+"/"+ mypage.PageCount +" 页 共 "+mypage.RecordCount+" 条记录";
}
}
#endregion
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.anp1.PageChanged += new ShareSoft.PageClass.PageChangedEventHandler(this.anp1_PageChanged);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void anp1_PageChanged(object src, ShareSoft.PageClass.PageChangedEventArgs e)
{
anp1.CurrentPageIndex=e.NewPageIndex;
mydateBind(anp1.CurrentPageIndex);
}
}
}