就是用的陕北吴旗娃的分页控件,大家帮帮忙
CREATE procedure RPhone_page
(@pagesize int,
@pageindex int,
@docount bit,
@sj1 varchar(20),
@sj2 varchar(20))
as
set nocount on
if(@docount=1)
select count(id) from RPhone
else
begin
create table #indextable(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into #indextable(nid) select id from RPhone order by Pdate desc
if(@sj1=""and @sj2="")
begin
select O.id,O.cid,O.Pdate,O.Pphone ,substring(O.Pmemo,0,6) as dd ,O.Pmemo from RPhone O,#indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
if(@sj1!="")
begin
select O.id,O.cid,O.Pdate,O.Pphone,substring(O.Pmemo,0,6) as dd,o.Pmemo from RPhone O,#indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound and convert(varchar(12),Pdate,112) >=convert(varchar(12),@sj1,112)order by t.id
end
if(@sj2!="")
begin
select O.id,O.cid,O.Pdate,O.Pphone,substring(O.Pmemo,0,6) as dd ,O.Pmemo from RPhone O,#indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound and convert(varchar(12),Pdate,112) <=convert(varchar(12),@sj2,112)order by t.id
end
if(@sj2!="" and @sj1!="")
begin
select O.id,O.cid,O.Pdate,O.Pphone,substring(O.Pmemo,0,6) as dd ,O.Pmemo from RPhone O,#indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound and convert(varchar(12),@sj1,112) <= convert(varchar(12),Pdate,112) and convert(varchar(12),Pdate,112) <=convert(varchar(12),@sj2,112) order by t.id
end
end
set nocount off
GO
我像这样做后,条件变后就是总页数,记录数不变,
CREATE procedure RPhone_page
(@pagesize int,
@pageindex int,
@docount bit,
@sj1 varchar(20),
@sj2 varchar(20))
as
set nocount on
if(@docount=1)
select count(id) from RPhone
else
begin
create table #indextable(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into #indextable(nid) select id from RPhone order by Pdate desc
if(@sj1=""and @sj2="")
begin
select O.id,O.cid,O.Pdate,O.Pphone ,substring(O.Pmemo,0,6) as dd ,O.Pmemo from RPhone O,#indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
if(@sj1!="")
begin
select O.id,O.cid,O.Pdate,O.Pphone,substring(O.Pmemo,0,6) as dd,o.Pmemo from RPhone O,#indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound and convert(varchar(12),Pdate,112) >=convert(varchar(12),@sj1,112)order by t.id
end
if(@sj2!="")
begin
select O.id,O.cid,O.Pdate,O.Pphone,substring(O.Pmemo,0,6) as dd ,O.Pmemo from RPhone O,#indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound and convert(varchar(12),Pdate,112) <=convert(varchar(12),@sj2,112)order by t.id
end
if(@sj2!="" and @sj1!="")
begin
select O.id,O.cid,O.Pdate,O.Pphone,substring(O.Pmemo,0,6) as dd ,O.Pmemo from RPhone O,#indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound and convert(varchar(12),@sj1,112) <= convert(varchar(12),Pdate,112) and convert(varchar(12),Pdate,112) <=convert(varchar(12),@sj2,112) order by t.id
end
end
set nocount off
GO
我像这样做后,条件变后就是总页数,记录数不变,
解决方案 »
- htmlEditor 源码里的图片地址问题
- 删除字典!
- 谁能给我推荐一个c# winfrom控件的书?
- C# winform 中 我在picturebox中得到一张图片,我想通过点击这张图片时,该图片在另一个picturebox中显示,这怎么办呢?
- 如何让鼠标自动去点击某条记录让它处于选中状态,比如用DataGridView控件
- C# winform窗体的大小在不同的分辨率下怎么设置啊?
- winform 中 引用.dll文件的问题
- [STAThread]去掉则不能运行openFileDialog1.ShowDialog(),为什么?
- 求助:子结构封送问题!
- 我保持了好久的第一次在这里没了。。。。呜呜。。。。。
- 16位颜色时屏幕抓图失真!
- 关于发布服务的一个初级问题
给你我用的:
// Repeater 自定义分页
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if (!Page.IsPostBack)
{
this.Label1.Text = "1";//初始页1
Repeater_C();
}
}private void Repeater_C()
{
int pag=Convert.ToInt32(this.Label1.Text);//设置当前页
SqlConnection con=new SqlConnection("server=.;database=MARILINDB;uid=sa;pwd=sa;");//实例化连接
SqlDataAdapter sda=new SqlDataAdapter();//建立一个数据适配器对象
sda.SelectCommand=new SqlCommand("select * from ArticleContent_TBL",con);//实例化SelectCommand,并用他从数据库读出全部数据
DataSet ds=new DataSet();//定义一个数据集填充
sda.Fill(ds,"ArticleContent_TBL");//使用适配器填充数据集到本地表“name” PagedDataSource ps=new PagedDataSource();//实例化一个PagedDataSource,这个本来是封装是DATAGRID里面的
ps.DataSource=ds.Tables["ArticleContent_TBL"].DefaultView;//设置他的数据源为ds.Tables["name"].DefaultView数据视图
ps.AllowPaging=true;//允许分页
ps.PageSize=10;//每页显示数量
ps.CurrentPageIndex=pag-1;//当前页码,因为页是从0开始的,所以要减1
this.Button1.Enabled=true;//按钮当前状态
this.Button2.Enabled=true;
this.Label5.Text=ps.PageCount.ToString();
if(pag==1)
{
this.Button1.Enabled=false;//如果当前页是 1 ,上一页按钮不可用
}
if(pag==ps.PageCount)
{
this.Button2.Enabled=false;//如果当前页是最后一页 ,下一页按钮不可用
}
this.Repeater1.DataSource=ps;
this.Repeater1.DataBind();
/// 分页函数
/// </summary>
/// <param name="sql">查询的SQL语句</param>
/// <param name="startNum">起始行</param>
/// <param name="maxNum">共显示的行数</param>
/// <returns>返回用参数sql查询出的表中记录的总数</returns>
string sql="查询字符串";
void DGPagination(string sql,int startNum,int maxNum)
{
try
{
DataSet dataset=returnDS(sql,startNum,maxNum);
dataset.Tables[0].Columns.Add("IspassStr"); //绑定表格
this.DataGrid1.DataSource=dataset.Tables[0];
this.DataGrid1.DataBind(); dataset=returnDS(sql);
//记录总的个数
int sum=dataset.Tables[0].Rows.Count;
//显示记录总的个数
this.labUserNum.Text=sum.ToString();
//总页数
if(sum%p_count==0)
{
this.labPageNum.Text=(sum/p_count).ToString();
}
else
{
this.labPageNum.Text=(sum/p_count+1).ToString();
}
this.labTopUserNum.Text=this.labUserNum.Text;
this.labTopPageNum.Text=this.labPageNum.Text; //当前的页数
this.labNum.Text="1";
this.labTopNum.Text="1";
//得到一共应该显示的页数
this.labSum.Text=this.labPageNum.Text;
this.labTopSum.Text=this.labPageNum.Text;
if(sum<=p_count)
{
link_allfalse();
return;
} //如果起始行为0,设置页码和总页码
if(startNum==0)
{
this.link_BOF();
}
return;
}
catch(Exception ex)
{
Response.wriet(ex.ToString());
}
}
void link_allfalse()
{
this.link1.Enabled=false;
this.link2.Enabled=false;
this.link3.Enabled=false;
this.link4.Enabled=false;
this.linkTop1.Enabled=false;
this.linkTop2.Enabled=false;
this.linkTop3.Enabled=false;
this.linkTop4.Enabled=false; } void link_alltrue()
{
this.link1.Enabled=true;
this.link2.Enabled=true;
this.link3.Enabled=true;
this.link4.Enabled=true;
this.linkTop1.Enabled=true;
this.linkTop2.Enabled=true;
this.linkTop3.Enabled=true;
this.linkTop4.Enabled=true;
}
//如果是记录首
void link_BOF()
{
this.link1.Enabled=false;
this.link2.Enabled=false;
this.link3.Enabled=true;
this.link4.Enabled=true;
this.linkTop1.Enabled=false;
this.linkTop2.Enabled=false;
this.linkTop3.Enabled=true;
this.linkTop4.Enabled=true;
}
//如果是记录尾
void link_EOF()
{
this.link1.Enabled=true;
this.link2.Enabled=true;
this.link3.Enabled=false;
this.link4.Enabled=false;
this.linkTop1.Enabled=true;
this.linkTop2.Enabled=true;
this.linkTop3.Enabled=false;
this.linkTop4.Enabled=false;
}
void link_middle()
{
this.link1.Enabled=true;
this.link2.Enabled=true;
this.link3.Enabled=true;
this.link4.Enabled=true;
this.linkTop1.Enabled=true;
this.linkTop2.Enabled=true;
this.linkTop3.Enabled=true;
this.linkTop4.Enabled=true;
}
//第一页
private void link1_Click(object sender, System.EventArgs e)
{
//绑定
this.DGPagination(sql,0,p_count);
} //上一页
private void link2_Click(object sender, System.EventArgs e)
{
int sum=int.Parse(this.labSum.Text);
int num=int.Parse(this.labNum.Text);
this.DGPagination(sql,num*p_count-2*p_count,p_count);
this.labNum.Text =(num-1).ToString();
this.labTopNum.Text=this.labNum.Text;
if((num-1)==1)
{
this.link_BOF();
}
else
{
this.link_middle();
}
} //下一页
private void link3_Click(object sender, System.EventArgs e)
{
int sum=int.Parse(this.labSum.Text);
int num=int.Parse(this.labNum.Text);
this.DGPagination(sql,num*p_count,p_count);
this.labNum.Text =(num+1).ToString();
this.labTopNum.Text=this.labNum.Text;
if((num+1)==sum)
{
this.link_EOF();
}
else
{
this.link_middle();
}
} //最后一页
private void link4_Click(object sender, System.EventArgs e)
{
int sum=int.Parse(this.labSum.Text);
this.DGPagination(sql,(sum-1)*p_count,p_count);
this.labNum.Text =sum.ToString();
this.labTopNum.Text=this.labNum.Text;
this.link_EOF();
}
//转页操作
private void IBtnGo_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
int sum=int.Parse(this.labSum.Text);
int num=int.Parse(this.labNum.Text);
if(int.Parse(this.txtGo.Text)>sum)
{
this.txtGo.Text=this.labNum.Text;
}
if(int.Parse(this.txtGo.Text)<1)
{
this.txtGo.Text=this.labNum.Text;
}
int page=int.Parse(this.txtGo.Text);
int startCount=(page-1)*p_count;
this.DGPagination(sql,startCount,p_count);
this.labNum.Text =page.ToString();
this.labTopNum.Text=this.labNum.Text;
this.txtTopGo.Text=this.txtGo.Text;
if(startCount<p_count)
{
this.link_allfalse();
return;
}
if(page==sum)
{
this.link_EOF();
}
else if(page==1)
{
this.link_BOF();
}
else
{
this.link_middle();
}
}
//Top第一页
private void linkTop1_Click(object sender, System.EventArgs e)
{
this.DGPagination(sql,0,p_count);
}
//top上页
private void linkTop2_Click(object sender, System.EventArgs e)
{
int sum=int.Parse(this.labSum.Text);
int num=int.Parse(this.labNum.Text);
this.DGPagination(sql,num*p_count-2*p_count,p_count);
this.labNum.Text =(num-1).ToString();
this.labTopNum.Text=this.labNum.Text;
if((num-1)==1)
{
this.link_BOF();
}
else
{
this.link_middle();
}
}
//top下页
private void linkTop3_Click(object sender, System.EventArgs e)
{
int sum=int.Parse(this.labSum.Text);
int num=int.Parse(this.labNum.Text);
this.DGPagination(sql,num*p_count,p_count);
this.labNum.Text =(num+1).ToString();
this.labTopNum.Text=this.labNum.Text;
if((num+1)==sum)
{
this.link_EOF();
}
else
{
this.link_middle();
}
}
//top最后页
private void linkTop4_Click(object sender, System.EventArgs e)
{
int sum=int.Parse(this.labSum.Text);
this.DGPagination(sql,(sum-1)*p_count,p_count);
this.labNum.Text =sum.ToString();
this.labTopNum.Text=this.labNum.Text;
this.link_EOF();
}
//top转
private void IBtnTopGo_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
int sum=int.Parse(this.labSum.Text);
int num=int.Parse(this.labNum.Text);
if(int.Parse(this.txtTopGo.Text)>sum)
{
this.txtTopGo.Text=this.labNum.Text;
}
if(int.Parse(this.txtTopGo.Text)<1)
{
this.txtTopGo.Text=this.labNum.Text;
}
int page=int.Parse(this.txtTopGo.Text);
int starcount=(page-1)*p_count;
this.DGPagination(sql,starcount,p_count);
this.labTopNum.Text =sum.ToString();
this.labNum.Text =page.ToString();
this.labTopNum.Text=this.labNum.Text;
this.txtGo.Text=this.txtTopGo.Text;
if(starcount<p_count)
{
this.link_allfalse();
return;
}
if(page==sum)
{
this.link_EOF();
}
else if(page==1)
{
this.link_BOF();
}
else
{
this.link_middle();
}
} /// <summary>
/// 返回DataSet
/// </summary>
/// <param name="sql"></param>
/// <param name="startNum">开始的索引</param>
/// <param name="maxNum">每页记录个数</param>
/// <returns>返回该记录集</returns>
public DataSet returnDS(string sql,int startNum,int maxNum)
{
SqlConnection conn==new SqlConnection("连接数据源");
DataSet ds=new DataSet();
SqlCommand cmd=new SqlCommand(sql,conn);
cmd.CommandTimeout=20;
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=cmd;
try
{
da.Fill(ds,startNum,maxNum,"table");
}
catch(Exception e)
{
conn.Close();
write.writerLog(e.ToString());
return ds;
}
conn.Close();
return ds;
}