利用aspnetPager控件实现分页
1)引入AspNetPager.dll
2)建立类Class_PageHelper.cs代码如下:
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace b2b._Class
{
public class Class_PageHelper
{
public Class_PageHelper(string tblName, string strGetFields, string fldName, int pageSize, int pageIndex, bool doCount, bool orderType, string strWhere, string SqlConnectionStr)
{
//
// TODO: 在此处添加构造函数逻辑
//
this.tblName = tblName;
this.strGetFields = strGetFields;
this.fldName = fldName;
this.pageSize = pageSize;
this.pageIndex = pageIndex;
this.orderType = orderType;
this.doCount = doCount;
this.strWhere = strWhere;
this.SqlConnectionStr = SqlConnectionStr;
}
//定义属性
private string tblName;
public string TblName
{
get { return tblName; }
set { tblName = value; }
}
private string strGetFields = "*";
public string StrGetFields
{
get { return strGetFields; }
set { strGetFields = value; }
}
private string fldName = string.Empty;
public string FldName
{
get { return fldName; }
set { fldName = value; }
}
private int pageSize = 10;
public int PageSize
{
get { return pageSize; }
set { pageSize = value; }
}
private int pageIndex = 1;
public int PageIndex
{
get { return pageIndex; }
set { pageIndex = value; }
}
private bool doCount = false;
public bool DoCount
{
get { return doCount; }
set { doCount = value; }
}
private bool orderType = false;
public bool OrderType
{
get { return orderType; }
set { orderType = value; }
}
private string strWhere = string.Empty;
public string StrWhere
{
get { return strWhere; }
set { strWhere = value; }
}
private string SqlConnectionStr;
//返回一个SqlConnection
public SqlConnection CreateCon()
{
string SqlConnectionStr = this.SqlConnectionStr;
try
{
SqlConnection con = new SqlConnection(SqlConnectionStr);
return con;
}
catch (Exception)
{
return null;
}
}
//返回记录总数
public int GetRowCount()
{
if (!this.doCount)
{
throw new ArgumentException("要返回总数统计，DoCount属性一定为true");
}
SqlConnection con = this.CreateCon();
int rowCount;
try
{
con.Open();
SqlCommand smd = new SqlCommand("u_sys_pagination3", con);
smd.CommandType = CommandType.StoredProcedure;
//付值给参数
smd.Parameters.Add(new SqlParameter("@tblName", this.tblName));
smd.Parameters.Add(new SqlParameter("@strGetFields", this.strGetFields));
smd.Parameters.Add(new SqlParameter("@fldName", this.fldName));
smd.Parameters.Add(new SqlParameter("@pageSize", this.pageSize));
smd.Parameters.Add(new SqlParameter("@pageIndex", this.pageIndex));
smd.Parameters.Add(new SqlParameter("@doCount", this.doCount));
smd.Parameters.Add(new SqlParameter("@OrderType", this.orderType));
smd.Parameters.Add(new SqlParameter("@strWhere", this.strWhere));
rowCount = Convert.ToInt32(smd.ExecuteScalar());
smd.Parameters.Clear();
smd.Dispose();
}
catch (Exception)
{
rowCount = 0;
}
con.Close();
return rowCount;
}
public int GetPageCount()
{
return (int)Math.Ceiling((double)this.GetRowCount() / (double)this.pageSize);
}
//返回一个SqlDataReader
public SqlDataReader GetDataReader()
{
if (this.doCount)
{
throw new ArgumentException("要返回记录集，DoCount属性一定为false");
}
// System.Web.HttpContext.Current.Response.Write(pageindex);
SqlConnection con;
SqlDataReader sdr;
try
{
con = this.CreateCon();
con.Open();
SqlCommand smd = new SqlCommand("u_sys_pagination3", con);
smd.CommandType = CommandType.StoredProcedure;
//付值给参数
smd.Parameters.Add(new SqlParameter("@tblName", this.tblName));
smd.Parameters.Add(new SqlParameter("@strGetFields", this.strGetFields));
smd.Parameters.Add(new SqlParameter("@fldName", this.fldName));
smd.Parameters.Add(new SqlParameter("@pageSize", this.pageSize));
smd.Parameters.Add(new SqlParameter("@pageIndex", this.pageIndex));
smd.Parameters.Add(new SqlParameter("@doCount", this.doCount));
smd.Parameters.Add(new SqlParameter("@OrderType", this.orderType));
smd.Parameters.Add(new SqlParameter("@strWhere", this.strWhere));
sdr = smd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
smd.Parameters.Clear();
smd.Dispose();
}
catch (Exception)
{
return null;
}
return sdr;
}
public DataSet GetDataSet()
{
if (this.doCount)
{
throw new ArgumentException("要返回记录集，DoCount属性一定为false");
}
SqlConnection con;
SqlDataAdapter sda;
DataSet ds = new DataSet();
try
{
con = this.CreateCon();
con.Open();
SqlCommand smd = new SqlCommand("u_sys_pagination3", con);
smd.CommandType = CommandType.StoredProcedure;
//付值给参数
smd.Parameters.Add(new SqlParameter("@tblName", this.tblName));
smd.Parameters.Add(new SqlParameter("@strGetFields", this.strGetFields));
smd.Parameters.Add(new SqlParameter("@fldName", this.fldName));
smd.Parameters.Add(new SqlParameter("@pageSize", this.pageSize));
smd.Parameters.Add(new SqlParameter("@pageIndex", this.pageIndex));
smd.Parameters.Add(new SqlParameter("@doCount", this.doCount));
smd.Parameters.Add(new SqlParameter("@OrderType", this.orderType));
smd.Parameters.Add(new SqlParameter("@strWhere", this.strWhere));
// HttpContext.Current.Response.Write("jiangjie");
sda = new SqlDataAdapter(smd);
sda.Fill(ds);
smd.Parameters.Clear();
smd.Dispose();
}
catch (Exception)
{
return null;
}
sda.Dispose();
con.Close();
return ds;
}
}
}
3)建立存储过程
CREATE PROCEDURE u_sys_pagination3
@tblName varchar(4000), -- 表名
@strGetFields varchar(200) = '*', -- 需要返回的列
@fldName varchar(200) = ' ', -- 排序的字段名
@pageSize int = 10, -- 页尺寸
@pageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@orderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(4000) = ' ' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(8000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=' '
set @strSQL = 'select count(*) as Total from '+@tblName+' where '+@strWhere
else
set @strSQL = 'select count(*) as Total from '+@tblName+''
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况：
else
begin
if @orderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName + ' desc'
--如果@orderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName + ' asc'
end
if @pageIndex = 1
begin
if @strWhere != ' '
set @strSQL = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tblName+' where '+@strWhere+' '+@strOrder
else
set @strSQL = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tblName+' '+@strOrder
--print(@strSQL)
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top '+str(@pageSize)+' '+@strGetFields+' from '
+@tblName+' where '+@fldName+'' +@strTmp+ '('+@fldName+')
from (select top '+str((@pageIndex-1)*@pageSize)+' '+@fldName+'
from '+@tblName+' '+@strOrder+') as tblTmp) '+@strOrder
if @strWhere != ' '
set @strSQL = 'select top ' +str(@pageSize)+' '+@strGetFields+' from '
+@tblName+' where '+@fldName+' '+@strTmp+' ('
+@fldName+') from (select top ' +str((@pageIndex-1)*@pageSize)+' '
+@fldName+' from '+@tblName+' where ' +@strWhere+ ' '
+@strOrder+') as tblTmp) and '+@strWhere+' '+@strOrder
end
end
exec (@strSQL)
4)建立前台页面
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Pager.aspx.cs" Inherits="Pager" %>
<%@ Register assembly="AspNetPager" namespace="Wuqi.Webdiyer" tagprefix="webdiyer" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server"
Height="274px" Width="863px">
</asp:GridView>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server"
CustomInfoHTML="第%CurrentPageIndex%页，共%PageCount%页，每页%PageSize%条"
FirstPageText="首页" LastPageText="尾页" LayoutType="Table" NextPageText="下一页"
onpagechanged="AspNetPager1_PageChanged" PageIndexBoxType="DropDownList"
PagingButtonLayoutType="Span" PrevPageText="上一页" ShowCustomInfoSection="Left"
ShowPageIndexBox="Always" SubmitButtonText="Go" TextAfterPageIndexBox="页"
TextBeforePageIndexBox="转到">
</webdiyer:AspNetPager>
</div>
</form>
</body>
</html>
5)建立后台页面
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Configuration;
using System.Data.SqlClient;
using b2b._Class;
public partial class Pager :BaseClass
{
public string tblName = "orders";
public string strGetFields = "*";
public string fldName = "orderid";
public int pageSize = 10;
public int pageIndex;
public bool doCount = false;
public bool orderType = true;
public string strWhere = "orderid is not null";
public int jumpPage;
public string SqlConnectionStr =ConfigurationManager.ConnectionStrings["eHRCONNECTIONSTRING"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["pageIndex"] = 1;//保存一个为1的页面索引值到ViewState
ViewState["strWhere"] = strWhere;
gvUserDataBind();
}
}
private void gvUserDataBind()
{
pageIndex = (int)ViewState["pageIndex"];
strWhere = ViewState["strWhere"].ToString();
Class_PageHelper ph = new Class_PageHelper(this.tblName, this.strGetFields, this.fldName, this.pageSize, this.pageIndex, this.doCount, this.orderType, this.strWhere, this.SqlConnectionStr);
DataSet ds = ph.GetDataSet();
this.AspNetPager1.RecordCount = this.recordCount;
this.AspNetPager1.PageSize = this.pageSize;
DataTable dt = ds.Tables[0];
if(dt.Rows.Count == 0)
{
dt.Rows.Add(dt.NewRow());
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
int columnCount = dt.Columns.Count;
this.GridView1.Rows[0].Cells.Clear();
this.GridView1.Rows[0].Cells.Add(new TableCell());
//this.GridView1.Columns[0].Visible = false;
this.GridView1.Rows[0].Cells[0].ColumnSpan = columnCount + 1;
this.GridView1.Rows[0].Cells[0].Text = "没有记录";
this.GridView1.Rows[0].Cells[0].Style.Add("text-align", "center");
this.GridView1.Rows[0].Cells[0].Style.Add("color", "red");
this.GridView1.Rows[0].Attributes.Add("ondblclick", "return false;");
}
else
{
this.GridView1.DataKeyNames = new string[] { "OrderID"};
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
//this.GridView1.Columns[0].Visible = true;
}
dt.Dispose();
ds.Dispose();
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
ViewState["pageIndex"] = this.AspNetPager1.CurrentPageIndex;
gvUserDataBind();
}
/// <summary>
/// 返回页总数
/// </summary>
public int pageCount
{
get
{
pageIndex = (int)ViewState["pageIndex"];
strWhere = ViewState["strWhere"].ToString();
Class_PageHelper ph = new Class_PageHelper(this.tblName, this.strGetFields, this.fldName, this.pageSize, this.pageIndex, true, this.orderType, this.strWhere, this.SqlConnectionStr);
return ph.GetPageCount();
}
}
/// <summary>
/// 返回记录总数
/// </summary>
public int recordCount
{
get
{
pageIndex = (int)ViewState["pageIndex"];
strWhere = ViewState["strWhere"].ToString();
Class_PageHelper ph = new Class_PageHelper(this.tblName, this.strGetFields, this.fldName, this.pageSize, this.pageIndex, true, this.orderType, this.strWhere, this.SqlConnectionStr);
return ph.GetRowCount();
}
}
}
