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.Text;
public partial class ess_bxyw_khda_Hfqk_Manage : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
ViewState["page"] = "1";
ViewState["where"] = "''''";
string sql1,sql = "exec pagination 'V_hfqk','[id],[lsh], [khxm], [khsj], [cx], [dph], [gcfs], [cpmy1],[cpmy2],[cpmy3],[fwmy1],[fwmy2], [fwmy3], [dlsmy1], [dlsmy2], [dlsmy3]','id',5,";
sql1 = sql + ViewState["page"];
sql1 += ",0,0,";
sql1 += ViewState["where"];
string sql2 = sql + ViewState["page"] + ",1,0," + ViewState["where"];
ViewState["total"] = DbOperateClass.DbOperate.GetSelectObj(sql2).ToString();
Repeater1.DataSource = DbOperateClass.DbOperate.GetDataSet(sql1);
Repeater1.DataBind();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
}
protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.AlternatingItem || e.Item.ItemType == ListItemType.Item)
{
((RadioButtonList)e.Item.FindControl("Rbl_gcfs")).SelectedValue = ((Label)e.Item.FindControl("Lab_gcfs")).Text;
((RadioButtonList)e.Item.FindControl("Rbl_cpmy1")).SelectedValue = ((Label)e.Item.FindControl("Lab_cpmy1")).Text;
((RadioButtonList)e.Item.FindControl("Rbl_cpmy2")).SelectedValue = ((Label)e.Item.FindControl("Lab_cpmy2")).Text;
((RadioButtonList)e.Item.FindControl("Rbl_cpmy3")).SelectedValue = ((Label)e.Item.FindControl("Lab_cpmy3")).Text;
((RadioButtonList)e.Item.FindControl("Rbl_fwmy1")).SelectedValue = ((Label)e.Item.FindControl("Lab_fwmy1")).Text;
((RadioButtonList)e.Item.FindControl("Rbl_fwmy2")).SelectedValue = ((Label)e.Item.FindControl("Lab_fwmy2")).Text;
((RadioButtonList)e.Item.FindControl("Rbl_fwmy3")).SelectedValue = ((Label)e.Item.FindControl("Lab_fwmy3")).Text;
((RadioButtonList)e.Item.FindControl("Rbl_dlsmy1")).SelectedValue = ((Label)e.Item.FindControl("Lab_dlsmy1")).Text;
((RadioButtonList)e.Item.FindControl("Rbl_dlsmy2")).SelectedValue = ((Label)e.Item.FindControl("Lab_dlsmy2")).Text;
((RadioButtonList)e.Item.FindControl("Rbl_dlsmy3")).SelectedValue = ((Label)e.Item.FindControl("Lab_dlsmy3")).Text;
}
}
protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "up")
{
}
if (e.CommandName == "down")
{
}
}
protected void Repeater1_ItemCreated(object sender, RepeaterItemEventArgs e)
{
}
protected void BtnSelect_Click(object sender, EventArgs e)
{
}
protected void BtnDel_Click(object sender, EventArgs e)
{
}
public void RepeaterBind(string page)
{
string sql1, sql = "exec pagination 'V_hfqk','[id],[lsh], [khxm], [khsj], [cx], [dph], [gcfs], [cpmy1],[cpmy2],[cpmy3],[fwmy1],[fwmy2], [fwmy3], [dlsmy1], [dlsmy2], [dlsmy3]','id',5,";
sql1 = sql + page;
sql1 += ",0,0,";
sql1 += ViewState["where"];
Repeater1.DataSource = DbOperateClass.DbOperate.GetDataSet(sql1);
Repeater1.DataBind();
}
protected void Btn_GoPage_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(Tb_no.Text.Trim()))
return;
int page = Convert.ToInt32(Tb_no.Text.Trim());
if (page < 1 || page > Math.Ceiling(Convert.ToInt32(ViewState["total"]) / 5.0))
return;
ViewState["page"] = Tb_no.Text;
RepeaterBind(ViewState["page"].ToString());
}
protected void Lb_Pre_Click(object sender, EventArgs e)
{
int page = Convert.ToInt32(ViewState["page"]);
if (page > 1)
{
ViewState["page"] = ((int)(page - 1)).ToString();
RepeaterBind(ViewState["page"].ToString());
}
}
protected void Lb_Next_Click(object sender, EventArgs e)
{
int page = Convert.ToInt32(ViewState["page"]);
if (page < Math.Ceiling(Convert.ToInt32(ViewState["total"]) / 5.0))
{
ViewState["page"] = ((int)(page + 1)).ToString();
RepeaterBind(ViewState["page"].ToString());
}
}
}
分页存储过程:
CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='''', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 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 --如果@OrderType不是0,就执行降序,这句很重要!
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
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)
GO