新项目开始了,先前总结了以前做项目的缺点,问题特别突出的是关于GridView控件分页的问题,想了很久,几乎每个页面都要用到GridView控件,而且按照原来的拖放式修改起来很麻烦,所以开始菜鸟分页用户控件的制作。
采用的方法肯定是存储过程分页了,自己SQL又学得差,只会那几条,写不出来那么好的,所以在网上找了一个认为比较好的,看看下面的代码:
Code
CREATE PROCEDURE dbo.proc_ListPage
(
@tblName nvarchar(200), ----要显示的表或多个表的连接
@fldName nvarchar(500) = '*', ----要显示的字段列表
@pageSize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output, ----查询到的记录数
@fldSort nvarchar(200) = null, ----排序字段列表或条件
@Sort bit = 0, ----排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ')
@strCondition nvarchar(1000) = null, ----查询条件,不需where
@ID nvarchar(150), ----主表的主键
@Dist bit = 0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
Declare @strSortType nvarchar(10) ----数据排序规则A
Declare @strFSortType nvarchar(10) ----数据排序规则B
Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
if @Dist = 0
begin
set @SqlSelect = 'select '
set @SqlCounts = 'Count(*)'
end
else
begin
set @SqlSelect = 'select distinct '
set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
set @strFSortType=' ASC '
set @strSortType=' DESC '
end
else
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end
----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts
--取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
/**//**当前页大于总页数 取最后一页**/
if @page>@pageCount
set @page=@pageCount
--/*-----数据分页2分处理-------*/
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize
--//***显示分页
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' order by '+ @fldSort +' '+ @strFSortType
end
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
else --有查询条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType
end
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
------返回查询结果-----
exec sp_executesql @strTmp
--print @strTmp
SET NOCOUNT OFF
GO
CREATE PROCEDURE dbo.proc_ListPage
(
@tblName nvarchar(200), ----要显示的表或多个表的连接
@fldName nvarchar(500) = '*', ----要显示的字段列表
@pageSize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output, ----查询到的记录数
@fldSort nvarchar(200) = null, ----排序字段列表或条件
@Sort bit = 0, ----排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ')
@strCondition nvarchar(1000) = null, ----查询条件,不需where
@ID nvarchar(150), ----主表的主键
@Dist bit = 0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
Declare @strSortType nvarchar(10) ----数据排序规则A
Declare @strFSortType nvarchar(10) ----数据排序规则B
Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
if @Dist = 0
begin
set @SqlSelect = 'select '
set @SqlCounts = 'Count(*)'
end
else
begin
set @SqlSelect = 'select distinct '
set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
set @strFSortType=' ASC '
set @strSortType=' DESC '
end
else
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end
----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts
--取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
/**//**当前页大于总页数 取最后一页**/
if @page>@pageCount
set @page=@pageCount
--/*-----数据分页2分处理-------*/
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize
--//***显示分页
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' order by '+ @fldSort +' '+ @strFSortType
end
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
else --有查询条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType
end
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
------返回查询结果-----
exec sp_executesql @strTmp
--print @strTmp
SET NOCOUNT OFF
GO
好了,研究了下上面的SQL代码,所在VS2005里面做一个分页用户控件,就是专门对上面的那个存储过程的,原理基本上是通过用户控件来控制显示的条数和页码,应该是吧?看看下面的代码。
前台:
<%
@ Control Language
=
"
C#
"
AutoEventWireup
=
"
true
"
CodeFile
=
"
PageControl.ascx.cs
"
Inherits
=
"
Page
"
%>
< table width ="" >
< tr >
< td > 第 < asp:Label ID ="labdang" runat ="server" Text ="1" ></ asp:Label > 页/共 < asp:Label ID ="labgong" runat ="server" Text ="" ></ asp:Label > 页 </ td >
< td >< asp:LinkButton ID ="lkbfirst" runat ="server" OnClick ="lkbfirst_Click" > 第一页 </ asp:LinkButton ></ td >
< td >< asp:LinkButton ID ="lkblast" runat ="server" OnClick ="lkblast_Click" > 上一页 </ asp:LinkButton ></ td >
< td >< asp:LinkButton ID ="lkbnext" runat ="server" OnClick ="lkbnext_Click" > 下一页 </ asp:LinkButton ></ td >
< td >< asp:LinkButton ID ="lkbback" runat ="server" OnClick ="lkbback_Click" > 最后页 </ asp:LinkButton ></ td >
< td > 跳转 < asp:TextBox ID ="tbxgo" runat ="server" Width ="30px" > 1 </ asp:TextBox ></ td >
< td >< asp:Button ID ="btngo" runat ="server" Text ="跳转" Width ="60px" OnClick ="btngo_Click" /></ td >
< td >
每页显示
< asp:DropDownList ID ="drpcount" runat ="server" AutoPostBack ="True" OnSelectedIndexChanged ="drpcount_SelectedIndexChanged" >
< asp:ListItem > 10 </ asp:ListItem >
< asp:ListItem > 15 </ asp:ListItem >
< asp:ListItem > 20 </ asp:ListItem >
< asp:ListItem > 50 </ asp:ListItem >
< asp:ListItem > 100 </ asp:ListItem >
</ asp:DropDownList > 条
</ td >
</ tr >
</ table >
< table width ="" >
< tr >
< td > 第 < asp:Label ID ="labdang" runat ="server" Text ="1" ></ asp:Label > 页/共 < asp:Label ID ="labgong" runat ="server" Text ="" ></ asp:Label > 页 </ td >
< td >< asp:LinkButton ID ="lkbfirst" runat ="server" OnClick ="lkbfirst_Click" > 第一页 </ asp:LinkButton ></ td >
< td >< asp:LinkButton ID ="lkblast" runat ="server" OnClick ="lkblast_Click" > 上一页 </ asp:LinkButton ></ td >
< td >< asp:LinkButton ID ="lkbnext" runat ="server" OnClick ="lkbnext_Click" > 下一页 </ asp:LinkButton ></ td >
< td >< asp:LinkButton ID ="lkbback" runat ="server" OnClick ="lkbback_Click" > 最后页 </ asp:LinkButton ></ td >
< td > 跳转 < asp:TextBox ID ="tbxgo" runat ="server" Width ="30px" > 1 </ asp:TextBox ></ td >
< td >< asp:Button ID ="btngo" runat ="server" Text ="跳转" Width ="60px" OnClick ="btngo_Click" /></ td >
< td >
每页显示
< asp:DropDownList ID ="drpcount" runat ="server" AutoPostBack ="True" OnSelectedIndexChanged ="drpcount_SelectedIndexChanged" >
< asp:ListItem > 10 </ asp:ListItem >
< asp:ListItem > 15 </ asp:ListItem >
< asp:ListItem > 20 </ asp:ListItem >
< asp:ListItem > 50 </ asp:ListItem >
< asp:ListItem > 100 </ asp:ListItem >
</ asp:DropDownList > 条
</ td >
</ tr >
</ table >
后台:
using
System;
using System.Data;
using System.Data.SqlClient;
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;
public partial class Page : System.Web.UI.UserControl
{
#region 定义变量
// 第几页
private int ofcount = 1 ;
// 共几页
private int pagecounts;
// 跳转到几页
private int gocount;
// 显示多少条
private int showcounts;
// 申明委托,执行翻页后数据绑定的页面层方法
public delegate void RefreshPage();
private RefreshPage refresh;
// 表名
private string _TBName;
// 字段
private string _Field;
// 条件
private string _WhereStr;
#endregion
#region 定义属性
/// <summary>
/// 设置数据源表名
/// </summary>
public string TBname
{
set { _TBName = value; }
}
/// <summary>
/// 设置字段,使用","隔开
/// </summary>
public string Field
{
set { _Field = value; }
}
/// <summary>
/// 设置查询的条件
/// </summary>
public string WhereStr
{
set { _WhereStr = value; }
}
public RefreshPage Refresh
{
get
{
return refresh;
}
set
{
refresh = value;
}
}
/// <summary>
/// 设置总页数
/// </summary>
private int SetCounts
{
get
{
return Convert.ToInt32(labgong.Text);
}
set
{
labgong.Text = value.ToString();
}
}
/// <summary>
/// 获取请求第几页的index
/// </summary>
private int OfCount
{
get
{
return ofcount;
}
}
/// <summary>
/// 获取输入的每页显示多少条
/// </summary>
private int ShowCounts
{
get
{
try
{
showcounts = Convert.ToInt32(drpcount.SelectedValue);
return showcounts;
}
catch
{
return 10 ;
}
}
}
#endregion
#region 定义方法
public DataSet GvDataBind()
{
showcounts = Convert.ToInt32(drpcount.SelectedValue);
return GetDataSet();
}
#endregion
#region 定义事件
protected void Page_Load( object sender, EventArgs e)
{
// 初始化加载
if ( ! IsPostBack)
{
}
}
protected void btngo_Click( object sender, EventArgs e)
{
// 跳转
int tmp = 0 ;
try
{
tmp = Convert.ToInt32(tbxgo.Text);
}
catch
{
tmp = 1 ;
tbxgo.Text = " 1 " ;
}
if (tmp <= 0 )
{
tmp = 1 ;
}
int tmp2 = Convert.ToInt32(labgong.Text);
if (tmp > tmp2)
{
ofcount = tmp2;
}
else
{
ofcount = tmp;
}
labdang.Text = ofcount.ToString();
tbxgo.Text = ofcount.ToString();
Refresh();
}
protected void lkbfirst_Click( object sender, EventArgs e)
{
// 第一页
ofcount = 1 ;
labdang.Text = ofcount.ToString();
Refresh();
}
protected void lkblast_Click( object sender, EventArgs e)
{
// 上一页
int tmp = Convert.ToInt32(labdang.Text);
tmp = tmp - 1 ;
if (tmp <= 0 )
{
tmp = 1 ;
}
ofcount = tmp;
labdang.Text = tmp.ToString();
Refresh();
}
protected void lkbnext_Click( object sender, EventArgs e)
{
// 下一页
int tmp = Convert.ToInt32(labgong.Text);
int tmp2 = Convert.ToInt32(labdang.Text);
tmp2 = tmp2 + 1 ;
if (tmp2 > tmp)
{
ofcount = tmp;
labdang.Text = tmp.ToString();
}
else
{
ofcount = tmp2;
labdang.Text = tmp2.ToString();
}
Refresh();
}
protected void lkbback_Click( object sender, EventArgs e)
{
// 最后一页
ofcount = Convert.ToInt32(labgong.Text);
labdang.Text = ofcount.ToString();
Refresh();
}
protected void drpcount_SelectedIndexChanged( object sender, EventArgs e)
{
// 设置显示条数
showcounts = Convert.ToInt32(drpcount.SelectedValue);
labdang.Text = " 1 " ;
Refresh();
}
#endregion
#region 数据源
private DataSet GetDataSet()
{
WMS.DataAccess.DataBase ado = new WMS.DataAccess.DataBase();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = " proc_ListPage " ;
// 存储过程分页,表名
SqlParameter tbname = new SqlParameter( " @tblName " , SqlDbType.VarChar, 50 );
tbname.Value = _TBName;
cmd.Parameters.Add(tbname);
// 字段
SqlParameter fldName = new SqlParameter( " @fldName " , SqlDbType.VarChar, 50 );
fldName.Value = _Field;
cmd.Parameters.Add(fldName);
// 每页显示多少条
SqlParameter pageSize = new SqlParameter( " @pageSize " , SqlDbType.VarChar, 10 );
pageSize.Value = showcounts;
cmd.Parameters.Add(pageSize);
// 显示第几页
SqlParameter page = new SqlParameter( " @page " , SqlDbType.VarChar, 20 );
page.Value = ofcount;
cmd.Parameters.Add(page);
// 总页数
SqlParameter pageCount = new SqlParameter( " @pageCount " , SqlDbType.VarChar, 20 );
pageCount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pageCount);
// 总条数
SqlParameter Counts = new SqlParameter( " @Counts " , SqlDbType.VarChar, 20 );
Counts.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Counts);
// 排序字段
SqlParameter fldSort = new SqlParameter( " @fldSort " , SqlDbType.VarChar, 20 );
fldSort.Value = " flngID " ;
cmd.Parameters.Add(fldSort);
// 排序方式
SqlParameter Sort = new SqlParameter( " @Sort " , SqlDbType.VarChar, 20 );
Sort.Value = " 1 " ;
cmd.Parameters.Add(Sort);
// 查询条件
SqlParameter strCondition = new SqlParameter( " @strCondition " , SqlDbType.VarChar, 100 );
strCondition.Value = _WhereStr;
cmd.Parameters.Add(strCondition);
// 表主键
SqlParameter ID = new SqlParameter( " @ID " , SqlDbType.VarChar, 10 );
ID.Value = " flngID " ;
cmd.Parameters.Add(ID);
SqlParameter Dist = new SqlParameter( " @Dist " , SqlDbType.Bit);
Dist.Value = 0 ;
cmd.Parameters.Add(Dist);
DataSet ds = ado.GetDataSet( ref cmd);
pagecounts = Convert.ToInt32(pageCount.Value);
labgong.Text = pagecounts.ToString();
return ds;
}
#endregion
}
using System.Data;
using System.Data.SqlClient;
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;
public partial class Page : System.Web.UI.UserControl
{
#region 定义变量
// 第几页
private int ofcount = 1 ;
// 共几页
private int pagecounts;
// 跳转到几页
private int gocount;
// 显示多少条
private int showcounts;
// 申明委托,执行翻页后数据绑定的页面层方法
public delegate void RefreshPage();
private RefreshPage refresh;
// 表名
private string _TBName;
// 字段
private string _Field;
// 条件
private string _WhereStr;
#endregion
#region 定义属性
/// <summary>
/// 设置数据源表名
/// </summary>
public string TBname
{
set { _TBName = value; }
}
/// <summary>
/// 设置字段,使用","隔开
/// </summary>
public string Field
{
set { _Field = value; }
}
/// <summary>
/// 设置查询的条件
/// </summary>
public string WhereStr
{
set { _WhereStr = value; }
}
public RefreshPage Refresh
{
get
{
return refresh;
}
set
{
refresh = value;
}
}
/// <summary>
/// 设置总页数
/// </summary>
private int SetCounts
{
get
{
return Convert.ToInt32(labgong.Text);
}
set
{
labgong.Text = value.ToString();
}
}
/// <summary>
/// 获取请求第几页的index
/// </summary>
private int OfCount
{
get
{
return ofcount;
}
}
/// <summary>
/// 获取输入的每页显示多少条
/// </summary>
private int ShowCounts
{
get
{
try
{
showcounts = Convert.ToInt32(drpcount.SelectedValue);
return showcounts;
}
catch
{
return 10 ;
}
}
}
#endregion
#region 定义方法
public DataSet GvDataBind()
{
showcounts = Convert.ToInt32(drpcount.SelectedValue);
return GetDataSet();
}
#endregion
#region 定义事件
protected void Page_Load( object sender, EventArgs e)
{
// 初始化加载
if ( ! IsPostBack)
{
}
}
protected void btngo_Click( object sender, EventArgs e)
{
// 跳转
int tmp = 0 ;
try
{
tmp = Convert.ToInt32(tbxgo.Text);
}
catch
{
tmp = 1 ;
tbxgo.Text = " 1 " ;
}
if (tmp <= 0 )
{
tmp = 1 ;
}
int tmp2 = Convert.ToInt32(labgong.Text);
if (tmp > tmp2)
{
ofcount = tmp2;
}
else
{
ofcount = tmp;
}
labdang.Text = ofcount.ToString();
tbxgo.Text = ofcount.ToString();
Refresh();
}
protected void lkbfirst_Click( object sender, EventArgs e)
{
// 第一页
ofcount = 1 ;
labdang.Text = ofcount.ToString();
Refresh();
}
protected void lkblast_Click( object sender, EventArgs e)
{
// 上一页
int tmp = Convert.ToInt32(labdang.Text);
tmp = tmp - 1 ;
if (tmp <= 0 )
{
tmp = 1 ;
}
ofcount = tmp;
labdang.Text = tmp.ToString();
Refresh();
}
protected void lkbnext_Click( object sender, EventArgs e)
{
// 下一页
int tmp = Convert.ToInt32(labgong.Text);
int tmp2 = Convert.ToInt32(labdang.Text);
tmp2 = tmp2 + 1 ;
if (tmp2 > tmp)
{
ofcount = tmp;
labdang.Text = tmp.ToString();
}
else
{
ofcount = tmp2;
labdang.Text = tmp2.ToString();
}
Refresh();
}
protected void lkbback_Click( object sender, EventArgs e)
{
// 最后一页
ofcount = Convert.ToInt32(labgong.Text);
labdang.Text = ofcount.ToString();
Refresh();
}
protected void drpcount_SelectedIndexChanged( object sender, EventArgs e)
{
// 设置显示条数
showcounts = Convert.ToInt32(drpcount.SelectedValue);
labdang.Text = " 1 " ;
Refresh();
}
#endregion
#region 数据源
private DataSet GetDataSet()
{
WMS.DataAccess.DataBase ado = new WMS.DataAccess.DataBase();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = " proc_ListPage " ;
// 存储过程分页,表名
SqlParameter tbname = new SqlParameter( " @tblName " , SqlDbType.VarChar, 50 );
tbname.Value = _TBName;
cmd.Parameters.Add(tbname);
// 字段
SqlParameter fldName = new SqlParameter( " @fldName " , SqlDbType.VarChar, 50 );
fldName.Value = _Field;
cmd.Parameters.Add(fldName);
// 每页显示多少条
SqlParameter pageSize = new SqlParameter( " @pageSize " , SqlDbType.VarChar, 10 );
pageSize.Value = showcounts;
cmd.Parameters.Add(pageSize);
// 显示第几页
SqlParameter page = new SqlParameter( " @page " , SqlDbType.VarChar, 20 );
page.Value = ofcount;
cmd.Parameters.Add(page);
// 总页数
SqlParameter pageCount = new SqlParameter( " @pageCount " , SqlDbType.VarChar, 20 );
pageCount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pageCount);
// 总条数
SqlParameter Counts = new SqlParameter( " @Counts " , SqlDbType.VarChar, 20 );
Counts.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Counts);
// 排序字段
SqlParameter fldSort = new SqlParameter( " @fldSort " , SqlDbType.VarChar, 20 );
fldSort.Value = " flngID " ;
cmd.Parameters.Add(fldSort);
// 排序方式
SqlParameter Sort = new SqlParameter( " @Sort " , SqlDbType.VarChar, 20 );
Sort.Value = " 1 " ;
cmd.Parameters.Add(Sort);
// 查询条件
SqlParameter strCondition = new SqlParameter( " @strCondition " , SqlDbType.VarChar, 100 );
strCondition.Value = _WhereStr;
cmd.Parameters.Add(strCondition);
// 表主键
SqlParameter ID = new SqlParameter( " @ID " , SqlDbType.VarChar, 10 );
ID.Value = " flngID " ;
cmd.Parameters.Add(ID);
SqlParameter Dist = new SqlParameter( " @Dist " , SqlDbType.Bit);
Dist.Value = 0 ;
cmd.Parameters.Add(Dist);
DataSet ds = ado.GetDataSet( ref cmd);
pagecounts = Convert.ToInt32(pageCount.Value);
labgong.Text = pagecounts.ToString();
return ds;
}
#endregion
}
好了,大功告成了,再通过页面调用试试,怎么调用:
protected
void
Page_Load(
object
sender, EventArgs e)
{
p1.Refresh = gvDataBind;
if ( ! IsPostBack)
{
gvDataBind();
}
}
private void gvDataBind()
{
p1.TBname = " Customers " ;
p1.Field = " CustomerID, CompanyName " ;
p1.WhereStr = string .Empty;
DataSet ds = p1.GvDataBind();
GridView1.DataSource = ds;
GridView1.DataBind();
}
{
p1.Refresh = gvDataBind;
if ( ! IsPostBack)
{
gvDataBind();
}
}
private void gvDataBind()
{
p1.TBname = " Customers " ;
p1.Field = " CustomerID, CompanyName " ;
p1.WhereStr = string .Empty;
DataSet ds = p1.GvDataBind();
GridView1.DataSource = ds;
GridView1.DataBind();
}
应该可以了吧?