分页存储过程:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[pagination]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int , -- 页尺寸
@PageIndex int, -- 页码
@doCount bit , -- 返回记录总数, 非 0 值则返回
@OrderType bit , -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
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
--如果是第一页就执行以上代码,这样会加快执行速度
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)=======================================================================================
App_Code下写的调用类:
//存储过程分页调用
public DataSet GetPageDataset(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)
{
//string connString = ConfigurationSettings.AppSettings["connstr"];
//SqlConnection conn = new SqlConnection(connString);
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["connstr"]);
SqlCommand comm = new SqlCommand("pagination", conn); comm.Parameters.Add(new SqlParameter("@tblName", SqlDbType.VarChar));//表名
comm.Parameters[0].Value = tblName;
comm.Parameters.Add(new SqlParameter("@strGetFields", SqlDbType.VarChar));//返回的列
comm.Parameters[1].Value = strGetFields;
comm.Parameters.Add(new SqlParameter("@fldName", SqlDbType.VarChar));//排序的字段名
comm.Parameters[2].Value = fldName;
comm.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int));//页尺寸
comm.Parameters[3].Value = PageSize;
comm.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int));//页码
comm.Parameters[4].Value = PageIndex;
comm.Parameters.Add(new SqlParameter("@doCount", SqlDbType.Int));//是否返回记录总数,0为不返回,1为返回
comm.Parameters[5].Value = doCount;
comm.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.Int));//设置排序类型,0为升序,非0为降序
comm.Parameters[6].Value = OrderType;
comm.Parameters.Add(new SqlParameter("@strWhere", SqlDbType.VarChar));//where语句
comm.Parameters[7].Value = strWhere;
comm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
return ds;
} public string GoToPager(int page, int pageSize, int Count, string Url)
{
string strHtml = "";
int prevPage = page - 1;
int nextPage = page + 1;
int startPage;
int pageCount = (int)Math.Ceiling((double)Count / pageSize);
strHtml += "<div class='pageContent' style='text-align:Left;overflow:hidden;height:25px;line-height:140%'>";
strHtml += "<span style='float:left'>页码: " + page + " / " + pageCount + " 总记录:" + Count + "</span>";
strHtml += "<div class='page' style='float:Right'><ul><li class='pages'>";
if (prevPage < 1)
{
strHtml += "<span title='首页'>首页 </span>";
strHtml += "<span title='上一页'>上一页 </span>";
}
else
{
strHtml += "<span title='首页'><a href='" + Url + "=1'>首页</a> </span>";
strHtml += "<span title='上一页'><a href='" + Url + "=" + prevPage + "'>上一页</a> </span>";
}
if (page % 10 == 0)
{
startPage = page - 9;
}
else
{
startPage = page - page % 10 + 1;
}
if (startPage > 10) strHtml += "<span title='前10页'><a href='" + Url + "=" + (startPage - 1) + "'>...</a></span>";
for (int i = startPage; i < startPage + 10; i++)
{
if (i > pageCount) break;
if (i == page)
{
strHtml += "<span title='页 " + i + "'> <font color='#ff0000'>[" + i + "]</font> </span>";
}
else
{
strHtml += "<span title='页 " + i + "'> <a href='" + Url + "=" + i + "'>[" + i + "]</a> </span>";
}
}
if (pageCount >= startPage + 10) strHtml += "<span title='后10页'><a href='" + Url + "=" + (startPage + 10) + "'>...</a></span>";
if (nextPage > pageCount)
{
strHtml += "<span title='下一页'> 下一页 </span>";
strHtml += "<span title='末页'>末页 </span>";
}
else
{
strHtml += "<span title='下一页'> <a href='" + Url + "=" + nextPage + "'>下一页</a> </span>";
strHtml += "<span title='末页'><a href='" + Url + "=" + pageCount + "'>末页</a> </span>";
}
strHtml += "</li></ul></div></div>";
return strHtml;
}
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[pagination]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int , -- 页尺寸
@PageIndex int, -- 页码
@doCount bit , -- 返回记录总数, 非 0 值则返回
@OrderType bit , -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
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
--如果是第一页就执行以上代码,这样会加快执行速度
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)=======================================================================================
App_Code下写的调用类:
//存储过程分页调用
public DataSet GetPageDataset(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)
{
//string connString = ConfigurationSettings.AppSettings["connstr"];
//SqlConnection conn = new SqlConnection(connString);
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["connstr"]);
SqlCommand comm = new SqlCommand("pagination", conn); comm.Parameters.Add(new SqlParameter("@tblName", SqlDbType.VarChar));//表名
comm.Parameters[0].Value = tblName;
comm.Parameters.Add(new SqlParameter("@strGetFields", SqlDbType.VarChar));//返回的列
comm.Parameters[1].Value = strGetFields;
comm.Parameters.Add(new SqlParameter("@fldName", SqlDbType.VarChar));//排序的字段名
comm.Parameters[2].Value = fldName;
comm.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int));//页尺寸
comm.Parameters[3].Value = PageSize;
comm.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int));//页码
comm.Parameters[4].Value = PageIndex;
comm.Parameters.Add(new SqlParameter("@doCount", SqlDbType.Int));//是否返回记录总数,0为不返回,1为返回
comm.Parameters[5].Value = doCount;
comm.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.Int));//设置排序类型,0为升序,非0为降序
comm.Parameters[6].Value = OrderType;
comm.Parameters.Add(new SqlParameter("@strWhere", SqlDbType.VarChar));//where语句
comm.Parameters[7].Value = strWhere;
comm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
return ds;
} public string GoToPager(int page, int pageSize, int Count, string Url)
{
string strHtml = "";
int prevPage = page - 1;
int nextPage = page + 1;
int startPage;
int pageCount = (int)Math.Ceiling((double)Count / pageSize);
strHtml += "<div class='pageContent' style='text-align:Left;overflow:hidden;height:25px;line-height:140%'>";
strHtml += "<span style='float:left'>页码: " + page + " / " + pageCount + " 总记录:" + Count + "</span>";
strHtml += "<div class='page' style='float:Right'><ul><li class='pages'>";
if (prevPage < 1)
{
strHtml += "<span title='首页'>首页 </span>";
strHtml += "<span title='上一页'>上一页 </span>";
}
else
{
strHtml += "<span title='首页'><a href='" + Url + "=1'>首页</a> </span>";
strHtml += "<span title='上一页'><a href='" + Url + "=" + prevPage + "'>上一页</a> </span>";
}
if (page % 10 == 0)
{
startPage = page - 9;
}
else
{
startPage = page - page % 10 + 1;
}
if (startPage > 10) strHtml += "<span title='前10页'><a href='" + Url + "=" + (startPage - 1) + "'>...</a></span>";
for (int i = startPage; i < startPage + 10; i++)
{
if (i > pageCount) break;
if (i == page)
{
strHtml += "<span title='页 " + i + "'> <font color='#ff0000'>[" + i + "]</font> </span>";
}
else
{
strHtml += "<span title='页 " + i + "'> <a href='" + Url + "=" + i + "'>[" + i + "]</a> </span>";
}
}
if (pageCount >= startPage + 10) strHtml += "<span title='后10页'><a href='" + Url + "=" + (startPage + 10) + "'>...</a></span>";
if (nextPage > pageCount)
{
strHtml += "<span title='下一页'> 下一页 </span>";
strHtml += "<span title='末页'>末页 </span>";
}
else
{
strHtml += "<span title='下一页'> <a href='" + Url + "=" + nextPage + "'>下一页</a> </span>";
strHtml += "<span title='末页'><a href='" + Url + "=" + pageCount + "'>末页</a> </span>";
}
strHtml += "</li></ul></div></div>";
return strHtml;
}
解决方案 »
- 请大家快来帮帮小弟吧,救救我!
- mvc2.0中view与viewdata的区别
- Repeater怎样在后台获取它里面td里面的值==========================
- 转投j2me是否有前途?
- FireFox下不能设置disabled。
- 一个小问题。。 急~~路过的进来看看~~~~
- 小弟太笨了
- 有难度的一个字符串截取的程序!送分求助!
- JavaScriptSerializer问题
- 在一个页面修改保存后退出,再进去还以修改前的信息,要刷新一下才改过来,请问怎么办才不用刷新?
- AspNetPager控件的问题
- 紧急求助!!!本人的程序为什么没有办法发布到服务器,.vb文件全部失去作用,只有静态的html起作用!
====================================================================
调用页面后台:page.cs (测试)using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;public partial class adminsys_page : System.Web.UI.Page
{
pub b = new pub(); //实例化基类
protected int curPageno;
protected int recordCount;
protected int pageCount;
protected void Page_Load(object sender, EventArgs e)
{
string searchWhere = " 1=1 ";
//构造查询SQL语句略
//........
//......
GridView1.DataSource = b.GetPageDataset("news", "id,title", "id", 2, curPageno,2, 0, searchWhere); // 执行存储过程
GridView1.DataBind();
if (this.pageCount > 0)
{
if (this.curPageno > this.pageCount)
{
this.curPageno = this.pageCount;
}
}
Label_TurnPage.Text = b.GoToPager(curPageno, 10, pageCount, "news.aspx"); //绑定分页?
}}
==============================================
前台: page.aspx (测试)<%@ Page Language="C#" AutoEventWireup="true" CodeFile="page.aspx.cs" Inherits="adminsys_page" %>
<!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" AutoGenerateColumns=true runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"
Width="640px">
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#E3EAEB" />
<EditRowStyle BackColor="#7C6F57" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<column>
<asp:TemplateField HeaderStyle-Width="100px" HeaderText="ID">
<ItemTemplate>
<span style="color:blue"><%# Eval("id") %></span>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderStyle-Width="100px" HeaderText="标题">
<ItemTemplate>
<span style="color:blue"><%# Eval("title") %></span>
</ItemTemplate>
</asp:TemplateField>
</columc>
</asp:GridView>
</div>
<div>
<asp:Label ID="Label_TurnPage" runat="server" Text="Label"></asp:Label>
</div> </form>
</body>
</html>
==============================================
[size=30px]问题:[/size]
我现在的程序(并没有贴出来,上面的页面是临时作为测试的代码),原来用GridView控件结合一个自定义控件分页,没有用到存储过程。感觉效率很低,现在采用存储过程分页,遇到下面的问题:
1.调用页面的后台(.cs)好像不只这么多内容?还需要那些东西,该怎么写?
2.前台分页部分的调用,好像也不对,而且出现页码[-8][-7][-6]...[0]?
3.GridView提示":“System.Data.DataRowView”不包含名为“id”的属性。"?请帮我看看详细前后台调用方法,大部分代码已经有了,帮我实现困扰已经的存储过程分页调用这个问题,一定给分给你!
http://www.upschool.com.cn/edu/1319/2007/18/10du265370_1.shtml
http://www.knowsky.com/344499.html
<asp:label id="Label2" runat="server" Font-Size="9pt">共</asp:label><FONT face="宋体"> </FONT>
</FONT><asp:label id="lbl_RecordCnt" runat="server" Font-Size="9pt"></asp:label>
<FONT face="宋体"> </FONT>
<asp:label id="Label3" runat="server" Font-Size="9pt">项</asp:label>
<FONT face="宋体"> </FONT>
<asp:label id="Label4" runat="server" Font-Size="9pt" ForeColor="Black">|</asp:label>
<FONT face="宋体"> </FONT>
<asp:linkbutton id="lkbFirst" runat="server" Font-Size="9pt" Enabled="False" ForeColor="Black" CommandArgument="First">首页</asp:linkbutton><FONT face="宋体"> </FONT><asp:linkbutton id="lkbPre" runat="server" Font-Size="9pt" Enabled="False" ForeColor="Black" CommandArgument="Pre">上一页</asp:linkbutton><FONT face="宋体"> </FONT><asp:linkbutton id="lkbNext" runat="server" Font-Size="9pt" Enabled="False" ForeColor="Black" CommandArgument="Next">下一页</asp:linkbutton><FONT face="宋体"> </FONT><asp:linkbutton id="lkbLast" runat="server" Font-Size="9pt" Enabled="False" ForeColor="Black" CommandArgument="Last">末页</asp:linkbutton><FONT face="宋体"> </FONT><asp:label id="Label5" runat="server" Font-Size="9pt" ForeColor="Black">|</asp:label><FONT face="宋体"> </FONT><asp:label id="Label6" runat="server" Font-Size="9pt">转</asp:label><asp:textbox id="txt_CurrentPage" runat="server" Enabled="False" Width="35px" Height="18px" AutoPostBack="True"></asp:textbox><FONT face="宋体"></FONT>
<asp:label id="Label8" runat="server" Font-Size="9pt" ForeColor="Black">/</asp:label><FONT face="宋体"> </FONT>
<asp:label id="lbl_PageCnt" runat="server" Font-Size="9pt"></asp:label><FONT face="宋体"> </FONT>
<asp:label id="Label9" runat="server" Font-Size="9pt">页</asp:label>后台:GetPagerForSql.ascx.cs
[code=C#]///namespace PagerSet1
//{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient; /// <summary>
/// 配合存储过程分页自定义控件(Sql Server)
/// By cherish58
/// </summary>
public class adminsys_include_GetPagerForSql : System.Web.UI.UserControl
{
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label Label9;
protected System.Web.UI.WebControls.Label lbl_PageCnt;
protected System.Web.UI.WebControls.Label Label6;
protected System.Web.UI.WebControls.LinkButton lkbLast;
protected System.Web.UI.WebControls.LinkButton lkbNext;
protected System.Web.UI.WebControls.LinkButton lkbPre;
protected System.Web.UI.WebControls.LinkButton lkbFirst;
protected System.Web.UI.WebControls.Label Label3;
protected System.Web.UI.WebControls.Label lbl_RecordCnt;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Label Label4;
protected System.Web.UI.WebControls.Label Label5;
protected System.Web.UI.WebControls.Label Label7;
protected System.Web.UI.WebControls.Label Label8;
protected System.Web.UI.WebControls.TextBox txt_CurrentPage; #region 全局变量 /// <summary>
/// 获得数据库连接字符
/// </summary>
protected string strconn = System.Configuration.ConfigurationSettings.AppSettings["connstr"].ToString() ; /// <summary>
/// 初始登陆时是否绑定数据(是为true,否为false),默认为false
/// </summary>
public bool InitBindData = false ; #endregion #region 属性 /// <summary>
/// 表名,必须赋初值
/// </summary>
public string TableName
{
get{return ViewState["TableName"].ToString();}
set{ViewState["TableName"] = value;}
}
/// <summary>
/// 返回的列名,默认为全部
/// </summary>
public string RetColumns
{
get{return ViewState["RetColumns"].ToString();}
set{ViewState["RetColumns"] = value;}
}
/// <summary>
/// 查询条件字符串,默认为空
/// </summary>
public string SqlWhere
{
get{return ViewState["SqlWhere"].ToString();}
set{ViewState["SqlWhere"] = value;}
}
/// <summary>
/// 排序字段,必须赋初值
/// </summary>
public string OrderField
{
get{return ViewState["OrderField"].ToString();}
set{ViewState["OrderField"] = value;}
}
/// <summary>
/// 排序类型(升序为asc,降序为desc),默认为升序
/// </summary>
public string OrderType
{
get{return ViewState["OrderType"].ToString();}
set{ViewState["OrderType"] = value;}
}
/// <summary>
/// 每页显示记录数,默认为10条
/// </summary>
public int PageSize
{
get{return int.Parse(ViewState["PageSize"].ToString());}
set{ViewState["PageSize"] = value;}
}
/// <summary>
/// 初始显示为第几页,默认为第1页
/// </summary>
public int CurrentPage
{
get{return int.Parse(ViewState["CurrentPage"].ToString());}
set{ViewState["CurrentPage"] = value;}
}
/// <summary>
/// 数据列表控件名称,必须赋初值
/// </summary>
public string DataControlName
{
get{return ViewState["DataControlName"].ToString();}
set{ViewState["DataControlName"] = value;}
} #endregion #region Page_Load private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
if(this.InitBindData)
{
//默认显示为第几页
ViewState["CurrentPage"] = ViewState["CurrentPage"] == null || ViewState["CurrentPage"].ToString() == "" ? "1" : ViewState["CurrentPage"].ToString() ;
//每页显示记录总数
ViewState["PageSize"] = ViewState["PageSize"] == null || ViewState["PageSize"].ToString() == "" ? 10 : int.Parse(ViewState["PageSize"].ToString()) ; this.BindGridData() ;
}
}
} #endregion #region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器
/// 修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.lkbFirst.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.lkbPre.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.lkbNext.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.lkbLast.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
this.txt_CurrentPage.TextChanged += new System.EventHandler(this.txt_CurrentPage_TextChanged);
this.Load += new System.EventHandler(this.Page_Load); }
#endregion//==============================待续
#region 分页 ChangePage private void ChangePage(object sender, System.Web.UI.WebControls.CommandEventArgs e)
{
int PageCount = this.GetPageCount() ;
int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString()) ;
string Change = e.CommandArgument.ToString() ;
if(Change == "Pre") //上一页
{
if(CurrentPage <= 1)
{
ViewState["CurrentPage"] = 1;
}
else
{
ViewState["CurrentPage"] = CurrentPage - 1 ;
}
}
else if(Change == "Next") //下一页
{
if(CurrentPage >= PageCount)
{
ViewState["CurrentPage"] = PageCount ;
}
else
{
ViewState["CurrentPage"] = CurrentPage + 1 ;
}
}
else if(Change == "First") //首页
{
ViewState["CurrentPage"] = 1 ;
}
else //末页
{
ViewState["CurrentPage"] = PageCount ;
}
//显示当前页
this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString() ;
this.ProData() ;
} #endregion #region 绑定数据 /// <summary>
/// 设置分页相关的参数
/// </summary>
private void BindGridData()
{
//记录总数
this.lbl_RecordCnt.Text = this.GetRecordCount().ToString() ;
//总页数
this.lbl_PageCnt.Text = this.GetPageCount().ToString() ;
if(this.lbl_PageCnt.Text != "0")
{
//当前页
this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString() ;
}
else
this.txt_CurrentPage.Text = "0" ; //避免翻页后再查询出现列表没记录的情况
if(int.Parse(this.lbl_RecordCnt.Text) <= int.Parse(ViewState["PageSize"].ToString()))
{
ViewState["CurrentPage"] = 1 ;
this.txt_CurrentPage.Text = "1" ;
} //绑定数据
this.ProData() ;
} #endregion #region 处理数据集 /// <summary>
/// 处理数据集
/// </summary>
/// <returns></returns>
private void ProData()
{
SqlConnection conn = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand("MyPagination",conn);
conn.Open() ;
cmd.CommandType = CommandType.StoredProcedure ;
cmd.Parameters.Add("@tblName",""+ViewState["TableName"].ToString()+"") ;
string retcolumns = ViewState["RetColumns"] == null || ViewState["RetColumns"].ToString() == "" ? "*" : ViewState["RetColumns"].ToString() ;
cmd.Parameters.Add("@RetColumns",retcolumns) ;
string sqlwhere = ViewState["SqlWhere"] == null || ViewState["SqlWhere"].ToString() == "" ? "" : ViewState["SqlWhere"].ToString() ;
cmd.Parameters.Add("@strWhere",sqlwhere) ;
cmd.Parameters.Add("@Orderfld",""+ViewState["OrderField"].ToString()+"") ;
cmd.Parameters.Add("@PageIndex",int.Parse(ViewState["CurrentPage"].ToString())) ;
cmd.Parameters.Add("@PageSize",""+int.Parse(ViewState["PageSize"].ToString())+"") ;
string ordertype = ViewState["OrderType"] == null || ViewState["OrderType"].ToString() == "" ? "asc" : ViewState["OrderType"].ToString() ;
cmd.Parameters.Add("@OrderType",ordertype) ;
SqlDataAdapter da = new SqlDataAdapter() ;
da.SelectCommand = cmd ;
DataSet ds = new DataSet() ;
da.Fill(ds) ; //找到父页面控件并绑定(这里只对DataGrid控件绑定)
DataGrid dg = (DataGrid)this.Page.FindControl(""+ViewState["DataControlName"].ToString()+"") ;
dg.DataSource = ds ;
dg.DataBind() ; da.Dispose() ;
cmd.Dispose() ;
conn.Close() ; //控制分页按扭状态
this.StatsLinkButton() ;
} #endregion #region 控制分页按扭状态 private void StatsLinkButton()
{
int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString()) ;
int PageCount = this.GetPageCount() ;
if(PageCount > 0)
this.txt_CurrentPage.Enabled = true ;
else
this.txt_CurrentPage.Enabled = false ; //若当前页为第一页
if(CurrentPage <=1 )
{
this.lkbFirst.Enabled = false ;
this.lkbPre.Enabled = false ;
}
else
{
this.lkbFirst.Enabled = true ;
this.lkbPre.Enabled = true ;
}
//若当前页为最后页
if(CurrentPage >= PageCount)
{
this.lkbLast.Enabled = false ;
this.lkbNext.Enabled = false ;
}
else
{
this.lkbLast.Enabled = true ;
this.lkbNext.Enabled = true ;
}
} #endregion #region 得到记录总数、总页数 //记录总数
private int GetRecordCount()
{
int RecordCount = 0 ;
string sql = "select count(*) from "+ViewState["TableName"].ToString()+" where 1=1" ;
if(ViewState["SqlWhere"] != null && ViewState["SqlWhere"].ToString() != "")
sql = sql + " and "+ViewState["SqlWhere"].ToString()+"" ; SqlConnection conn = new SqlConnection(strconn) ;
SqlCommand cmd = new SqlCommand(sql,conn) ;
conn.Open() ;
RecordCount = int.Parse(cmd.ExecuteScalar().ToString()) ;
cmd.Dispose() ;
conn.Close() ;
return RecordCount ;
}
//===================================(待续)
[/code]
{
int PageCount = this.GetPageCount() ;
int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString()) ;
string Change = e.CommandArgument.ToString() ;
if(Change == "Pre") //上一页
{
if(CurrentPage <= 1)
{
ViewState["CurrentPage"] = 1;
}
else
{
ViewState["CurrentPage"] = CurrentPage - 1 ;
}
}
else if(Change == "Next") //下一页
{
if(CurrentPage >= PageCount)
{
ViewState["CurrentPage"] = PageCount ;
}
else
{
ViewState["CurrentPage"] = CurrentPage + 1 ;
}
}
else if(Change == "First") //首页
{
ViewState["CurrentPage"] = 1 ;
}
else //末页
{
ViewState["CurrentPage"] = PageCount ;
}
//显示当前页
this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString() ;
this.ProData() ;
} #endregion #region 绑定数据 /// <summary>
/// 设置分页相关的参数
/// </summary>
private void BindGridData()
{
//记录总数
this.lbl_RecordCnt.Text = this.GetRecordCount().ToString() ;
//总页数
this.lbl_PageCnt.Text = this.GetPageCount().ToString() ;
if(this.lbl_PageCnt.Text != "0")
{
//当前页
this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString() ;
}
else
this.txt_CurrentPage.Text = "0" ; //避免翻页后再查询出现列表没记录的情况
if(int.Parse(this.lbl_RecordCnt.Text) <= int.Parse(ViewState["PageSize"].ToString()))
{
ViewState["CurrentPage"] = 1 ;
this.txt_CurrentPage.Text = "1" ;
} //绑定数据
this.ProData() ;
} #endregion #region 处理数据集 /// <summary>
/// 处理数据集
/// </summary>
/// <returns></returns>
private void ProData()
{
SqlConnection conn = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand("MyPagination",conn);
conn.Open() ;
cmd.CommandType = CommandType.StoredProcedure ;
cmd.Parameters.Add("@tblName",""+ViewState["TableName"].ToString()+"") ;
string retcolumns = ViewState["RetColumns"] == null || ViewState["RetColumns"].ToString() == "" ? "*" : ViewState["RetColumns"].ToString() ;
cmd.Parameters.Add("@RetColumns",retcolumns) ;
string sqlwhere = ViewState["SqlWhere"] == null || ViewState["SqlWhere"].ToString() == "" ? "" : ViewState["SqlWhere"].ToString() ;
cmd.Parameters.Add("@strWhere",sqlwhere) ;
cmd.Parameters.Add("@Orderfld",""+ViewState["OrderField"].ToString()+"") ;
cmd.Parameters.Add("@PageIndex",int.Parse(ViewState["CurrentPage"].ToString())) ;
cmd.Parameters.Add("@PageSize",""+int.Parse(ViewState["PageSize"].ToString())+"") ;
string ordertype = ViewState["OrderType"] == null || ViewState["OrderType"].ToString() == "" ? "asc" : ViewState["OrderType"].ToString() ;
cmd.Parameters.Add("@OrderType",ordertype) ;
SqlDataAdapter da = new SqlDataAdapter() ;
da.SelectCommand = cmd ;
DataSet ds = new DataSet() ;
da.Fill(ds) ; //找到父页面控件并绑定(这里只对DataGrid控件绑定)
DataGrid dg = (DataGrid)this.Page.FindControl(""+ViewState["DataControlName"].ToString()+"") ;
dg.DataSource = ds ;
dg.DataBind() ; da.Dispose() ;
cmd.Dispose() ;
conn.Close() ; //控制分页按扭状态
this.StatsLinkButton() ;
} #endregion #region 控制分页按扭状态 private void StatsLinkButton()
{
int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString()) ;
int PageCount = this.GetPageCount() ;
if(PageCount > 0)
this.txt_CurrentPage.Enabled = true ;
else
this.txt_CurrentPage.Enabled = false ; //若当前页为第一页
if(CurrentPage <=1 )
{
this.lkbFirst.Enabled = false ;
this.lkbPre.Enabled = false ;
}
else
{
this.lkbFirst.Enabled = true ;
this.lkbPre.Enabled = true ;
}
//若当前页为最后页
if(CurrentPage >= PageCount)
{
this.lkbLast.Enabled = false ;
this.lkbNext.Enabled = false ;
}
else
{
this.lkbLast.Enabled = true ;
this.lkbNext.Enabled = true ;
}
} #endregion #region 得到记录总数、总页数 //记录总数
private int GetRecordCount()
{
int RecordCount = 0 ;
string sql = "select count(*) from "+ViewState["TableName"].ToString()+" where 1=1" ;
if(ViewState["SqlWhere"] != null && ViewState["SqlWhere"].ToString() != "")
sql = sql + " and "+ViewState["SqlWhere"].ToString()+"" ; SqlConnection conn = new SqlConnection(strconn) ;
SqlCommand cmd = new SqlCommand(sql,conn) ;
conn.Open() ;
RecordCount = int.Parse(cmd.ExecuteScalar().ToString()) ;
cmd.Dispose() ;
conn.Close() ;
return RecordCount ;
} //总页数
private int GetPageCount()
{
int RecordCount = 0 ;
int YeShu = 0 ;
int psize = int.Parse(ViewState["PageSize"].ToString()) ; string sql = "select count(*) from "+ViewState["TableName"].ToString()+" where 1=1" ;
if(ViewState["SqlWhere"] != null && ViewState["SqlWhere"].ToString() != "")
sql = sql + " and "+ViewState["SqlWhere"].ToString()+"" ; SqlConnection conn = new SqlConnection(strconn) ;
SqlCommand cmd = new SqlCommand(sql,conn) ;
conn.Open() ;
RecordCount = int.Parse(cmd.ExecuteScalar().ToString()) ;
cmd.Dispose() ;
conn.Close() ;
YeShu = RecordCount % psize ;
if(YeShu == 0)
{
return RecordCount/psize ;
}
else
{
return RecordCount/psize + 1 ;
}
}
#endregion #region 跳转