存储过程:
CREATE PROCEDURE [dbo].[ThePagerIndex]
@strTable varchar(50) = '[dbo].[zp_ReleaseJob]',
@strField varchar(50) = '*',
@intTop int = 5000,
@pageSize int = 2,
@pageIndex int = 1,
@strWhere varchar(50) = '1=1',
@strSortKey varchar(50) = 'rel_ID',
@strSortField varchar(50) = 'rel_ID desc',
@strOrderBy bit = 1,
@pageCount int OUTPUT,
@RecordCount int OUTPUT
--@UsedTime int OUTPUT
AS
SET NOCOUNT ON
Declare @sqlcount INT
Declare @timediff DATETIME
select @timediff=getdate()
Begin Tran
DECLARE @sql nvarchar(4000),@where1 varchar(300),@where2 varchar(300)
IF @strWhere is null or rtrim(@strWhere)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件
SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件
END
--SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
IF @intTop<=0
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
ELSE
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(300)) +' '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
--print @sqlEXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
SELECT @pageCount=CEILING((@sqlcount+0.0)/@pageSize) --计算总页数
SELECT @RecordCount = @sqlcount --设置总记录数
IF @pageIndex=1 --第一页
BEGIN
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(300))+' '+@strField+' FROM '+@strTable+ @where2+'ORDER BY '+ @strSortField
END
Else
BEGIN
IF @strOrderBy=0
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(300))+' '+@strField+ ' FROM '+@strTable+@where1+@strSortKey+'>(SELECT MAX('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(300))+' '+ @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
ELSE
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(300))+' '+@strField+' FROM '+@strTable+@where1+@strSortKey+'<(SELECT MIN('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(300))+' '+ @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
END
EXEC(@sql)
--print @sql
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit TRAN
--set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as 耗时
Return @sqlcount
End
GOcs代码:
int currPage = 1;
int PageSize = 2;
public void Bind()
{
int pageCount, RecordCount; item.DataSource = GetPage(currPage, PageSize, out pageCount, out RecordCount);
item.DataBind();
anp.RecordCount = RecordCount;
anp.CurrentPageIndex = currPage;
anp.PageSize = PageSize;
} protected void anp_PageChanged(object sender, EventArgs e)
{
int pageCount, RecordCount;
item.DataSource = GetPage(anp.CurrentPageIndex, PageSize, out pageCount, out RecordCount);
item.DataBind();
} public DataSet GetPage(int pageindex, int _pageSize, out int pageCount, out int RecordCount)
{
pageCount = 0;
RecordCount = 0; string con = GetConnectionStringDao.GetConn();
SqlConnection conn = new SqlConnection(con);
SqlCommand objcmd = new SqlCommand(".ThePagerIndex", conn);
objcmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] para ={
new SqlParameter("@strTable",SqlDbType.VarChar,-1),
new SqlParameter("@strField",SqlDbType.VarChar,-1),
new SqlParameter("@pageSize",SqlDbType.Int),
new SqlParameter("@pageIndex",SqlDbType.Int),
new SqlParameter("@strSortKey",SqlDbType.VarChar,-1),
new SqlParameter("@strSortField",SqlDbType.VarChar,-1),
new SqlParameter("@strOrderBy",SqlDbType.Bit),
new SqlParameter("@pageCount",SqlDbType.Int),
new SqlParameter("@RecordCount",SqlDbType.Int),
new SqlParameter("@intTop",SqlDbType.Int,-1)
}; para[0].Value = "OK";
para[1].Value = "*";
para[2].Value = _pageSize;
para[3].Value = pageindex;
para[4].Value = "ID";
para[5].Value = "ID desc";
para[6].Value = 1;
para[7].Value = pageCount;
para[7].Direction = ParameterDirection.Output;
para[8].Value = RecordCount;
para[8].Direction = ParameterDirection.Output;
para[9].Value = -1; objcmd.Parameters.AddRange(para); conn.Open();
DataSet dataSet = new DataSet();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = objcmd;
sqlDA.Fill(dataSet,"zp_ReleaseJob");
return dataSet; RecordCount = Convert.ToInt32(objcmd.Parameters["@RecordCount"].Value);
pageCount = Convert.ToInt32(objcmd.Parameters["@pageCount"].Value);
conn.Close();
conn.Dispose();
}
CREATE PROCEDURE [dbo].[ThePagerIndex]
@strTable varchar(50) = '[dbo].[zp_ReleaseJob]',
@strField varchar(50) = '*',
@intTop int = 5000,
@pageSize int = 2,
@pageIndex int = 1,
@strWhere varchar(50) = '1=1',
@strSortKey varchar(50) = 'rel_ID',
@strSortField varchar(50) = 'rel_ID desc',
@strOrderBy bit = 1,
@pageCount int OUTPUT,
@RecordCount int OUTPUT
--@UsedTime int OUTPUT
AS
SET NOCOUNT ON
Declare @sqlcount INT
Declare @timediff DATETIME
select @timediff=getdate()
Begin Tran
DECLARE @sql nvarchar(4000),@where1 varchar(300),@where2 varchar(300)
IF @strWhere is null or rtrim(@strWhere)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件
SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件
END
--SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
IF @intTop<=0
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
ELSE
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(300)) +' '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
--print @sqlEXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
SELECT @pageCount=CEILING((@sqlcount+0.0)/@pageSize) --计算总页数
SELECT @RecordCount = @sqlcount --设置总记录数
IF @pageIndex=1 --第一页
BEGIN
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(300))+' '+@strField+' FROM '+@strTable+ @where2+'ORDER BY '+ @strSortField
END
Else
BEGIN
IF @strOrderBy=0
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(300))+' '+@strField+ ' FROM '+@strTable+@where1+@strSortKey+'>(SELECT MAX('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(300))+' '+ @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
ELSE
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(300))+' '+@strField+' FROM '+@strTable+@where1+@strSortKey+'<(SELECT MIN('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(300))+' '+ @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
END
EXEC(@sql)
--print @sql
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit TRAN
--set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as 耗时
Return @sqlcount
End
GOcs代码:
int currPage = 1;
int PageSize = 2;
public void Bind()
{
int pageCount, RecordCount; item.DataSource = GetPage(currPage, PageSize, out pageCount, out RecordCount);
item.DataBind();
anp.RecordCount = RecordCount;
anp.CurrentPageIndex = currPage;
anp.PageSize = PageSize;
} protected void anp_PageChanged(object sender, EventArgs e)
{
int pageCount, RecordCount;
item.DataSource = GetPage(anp.CurrentPageIndex, PageSize, out pageCount, out RecordCount);
item.DataBind();
} public DataSet GetPage(int pageindex, int _pageSize, out int pageCount, out int RecordCount)
{
pageCount = 0;
RecordCount = 0; string con = GetConnectionStringDao.GetConn();
SqlConnection conn = new SqlConnection(con);
SqlCommand objcmd = new SqlCommand(".ThePagerIndex", conn);
objcmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] para ={
new SqlParameter("@strTable",SqlDbType.VarChar,-1),
new SqlParameter("@strField",SqlDbType.VarChar,-1),
new SqlParameter("@pageSize",SqlDbType.Int),
new SqlParameter("@pageIndex",SqlDbType.Int),
new SqlParameter("@strSortKey",SqlDbType.VarChar,-1),
new SqlParameter("@strSortField",SqlDbType.VarChar,-1),
new SqlParameter("@strOrderBy",SqlDbType.Bit),
new SqlParameter("@pageCount",SqlDbType.Int),
new SqlParameter("@RecordCount",SqlDbType.Int),
new SqlParameter("@intTop",SqlDbType.Int,-1)
}; para[0].Value = "OK";
para[1].Value = "*";
para[2].Value = _pageSize;
para[3].Value = pageindex;
para[4].Value = "ID";
para[5].Value = "ID desc";
para[6].Value = 1;
para[7].Value = pageCount;
para[7].Direction = ParameterDirection.Output;
para[8].Value = RecordCount;
para[8].Direction = ParameterDirection.Output;
para[9].Value = -1; objcmd.Parameters.AddRange(para); conn.Open();
DataSet dataSet = new DataSet();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = objcmd;
sqlDA.Fill(dataSet,"zp_ReleaseJob");
return dataSet; RecordCount = Convert.ToInt32(objcmd.Parameters["@RecordCount"].Value);
pageCount = Convert.ToInt32(objcmd.Parameters["@pageCount"].Value);
conn.Close();
conn.Dispose();
}
解决方案 »
- 模拟提交分页的例子
- ASP.NET中进行消息处理(MSMQ)
- ASP.NET 中Input Type=Text放入到Table中时右边被复盖的问题
- 关于UpdatePanel控件不能实现局部刷新的问题
- 两个问题.获得datagrid中当前行和删除datatable中指定一行的问题.
- 能否用asp.net实现对远程计算机的控制?
- 高分求购Repeater结合CheckBox的例子
- -------------------如何这样生成预览图------------------
- 各位大侠:我怎么在ASPX的后台代码文件里触发HTML文件里面的客户端BUTTON的OnClick事件
- 两个问题百分拿:上传的文件(50分)?四舍五入(50分)?
- 数据库连接!急!!
- Repeater绑定后,如何更改绑定后的某个img的图片
要使用 AspNetPager 分页控件,必须指定它的 RecordCount 属性,指定并编写PageChanged 事件的处理程序。 RecordCount 属性指定要分页的所有数据的总项数,若未指定该值或该值小于等于 PageSize ,则AspNetPager控件不会显示任何内容。若未指定并编写 PageChanged 事件处理程序,则当用户点击页导航元素或在页索引文本框中手式输入页索引并提交时AspNetPager不会跳转到指定的页。 AspNetPager控件在它的 PageChanged 事件处理程序中将传递事件数据的 PageChangedEventArgs 的 NewPageIndex值赋给 AspNetPager的 CurrentPageIndex属性,然后重新将新的数据与数据显示控件绑定。 RecordCount :当页面第一次加载时,应以编程方式将数据表中所有要分页的记录的总数赋予该属性。AspNetPager根据要分页的所有数据的总项数和 PageSize 属性来计算显示所有数据需要的总页数,即 PageCount的值。 PageSize :该值获取或设置数据呈现控件每次要显示数据表中的的数据的项数,AspNetPager根据该值和 RecordCount 来计算显示所有数据需要的总页数,即 PageCount的值。
属性说明:CustomInfoSectionWidth="240px"指定用户信息的宽度,也可设为百分比FirstPageText,PrevPageText,NextPageText,LastPageText分别为设定首页,前页,下页,末页导航按钮要显示的文字,这里使用的是webdings字体图片。ShowCustomInfoSection="Left"设定用户信息显示在导航按钮左右方向,或者不显示,这里设为左边显示。CustomInfoHTML设定用户信息内容ShowCustomInfoSection="Auto"设定页面导航框是否显示,Auto为默认的选项,当页面数大于ShowBoxThreshold设定的数值时才显示页面导航框,也可可以设为总显示与不显示。页面导航框用两种形式,默认为输入文本框,可以设置PageIndexBoxType="DropDownList"而使用下拉框方式。
protected void anp_PageChanged(object sender, EventArgs e)
{
int pageCount, RecordCount;
item.DataSource = GetPage(anp.CurrentPageIndex, PageSize, out pageCount, out RecordCount);
item.DataBind();
}
换成protected void anp_PageChanged(object sender, EventArgs e)
{
int pageCount, RecordCount;
item.DataSource = GetPage(anp.CurrentPageIndex, PageSize, out pageCount, out RecordCount);
anp.RecordCount=pageCount;//这个给aspnetpager赋值 记录总数 item.DataBind();
}
{
int pageCount, RecordCount;
item.DataSource = GetPage(anp.CurrentPageIndex, PageSize, out pageCount, out RecordCount);
anp.RecordCount=pageCount;//这个给aspnetpager赋值 记录总数
item.DataBind();
}
自己开发一个这样的控件是不是什么难事。
主要是状态,页面上客服点击的状态都需要记录,然后显示。做好办法就是利用 ajax 去实现。异步刷新,这样客户体验也会得到提升。