ALTER PROCEDURE [dbo].[SP_GetRecordByPage]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = ' ' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @fldName + '] desc '
end
else
begin
set @strTmp = '> (select max '
set @strOrder = ' order by [ ' + @fldName + '] asc '
end
set @strSQL = 'select top ' + str(@PageSize) + ' * 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) + ' * from [ '
+ @tblName + '] where [ ' + @fldName + '] ' + @strTmp + '([ '
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [ '
+ @fldName + '] from [ ' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ' '
if @strWhere != ' '
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from [ '
+ @tblName + '] ' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] '+ ' where ' + @strWhere
exec (@strSQL)数据层public DataSet GetList(int startIndex, int endindex, string strWhere, int IsReCount)
{
SqlParameter[] parameters = {
new SqlParameter("@startIndex", SqlDbType.Int),
new SqlParameter("@endindex", SqlDbType.Int),
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@OrderfldName", SqlDbType.VarChar, 255),
new SqlParameter("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000)
}; parameters[0].Value = startIndex;
parameters[1].Value = endindex;
parameters[2].Value = "News";
parameters[3].Value = "NewsID,Title,NewsType,Description,BegTime,OutreachUrl,Publisher,Type";
parameters[4].Value = "NewsID";
parameters[5].Value = IsReCount;
parameters[6].Value = 1;
parameters[7].Value = strWhere;
return DbHelperSQL.RunProcedure("SP_GetRecordByPage", parameters, "ds");
}
访问层
public DataSet GetList(int startIndex, int endindex, string strWhere, int IsReCount)
{
return dal.GetList(startIndex,endindex,strWhere,IsReCount);
}
表示层
protected void init()
{ string strWhere = " 1 = 1 "; this.AspNetPager1.PageSize = 10;
this.AspNetPager1.AlwaysShow = true;
DataSet dsNews = newsManager.GetList(AspNetPager1.StartRecordIndex, AspNetPager1.EndRecordIndex, strWhere,0); newsList.DataSource = dsNews;
newsList.DataBind();
}
这个跟网上的有什么不一样吗 请高手解决
nt.Parse(bll.GetRecordCount(strWhere).Tables[0].Rows[0][0].ToString()); 这个方法我一没看明百 string strWhere = " 1 = 1 ";
55 ETHaiNan.BLL.ET_Video bll = new ETHaiNan.BLL.ET_Video();
56 this.anpager.RecordCount = int.Parse(bll.GetRecordCount(strWhere).Tables[0].Rows[0][0].ToString());
57 this.anpager.PageSize = 10;
58 this.anpager.AlwaysShow = true;
59 DataSet ds = bll.GetList(anpager.StartRecordIndex, anpager.EndRecordIndex,strWhere, 0);
60 this.rpt.DataSource = ds;
61 ds.Dispose();
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = ' ' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @fldName + '] desc '
end
else
begin
set @strTmp = '> (select max '
set @strOrder = ' order by [ ' + @fldName + '] asc '
end
set @strSQL = 'select top ' + str(@PageSize) + ' * 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) + ' * from [ '
+ @tblName + '] where [ ' + @fldName + '] ' + @strTmp + '([ '
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [ '
+ @fldName + '] from [ ' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ' '
if @strWhere != ' '
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from [ '
+ @tblName + '] ' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] '+ ' where ' + @strWhere
exec (@strSQL)数据层public DataSet GetList(int startIndex, int endindex, string strWhere, int IsReCount)
{
SqlParameter[] parameters = {
new SqlParameter("@startIndex", SqlDbType.Int),
new SqlParameter("@endindex", SqlDbType.Int),
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@OrderfldName", SqlDbType.VarChar, 255),
new SqlParameter("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000)
}; parameters[0].Value = startIndex;
parameters[1].Value = endindex;
parameters[2].Value = "News";
parameters[3].Value = "NewsID,Title,NewsType,Description,BegTime,OutreachUrl,Publisher,Type";
parameters[4].Value = "NewsID";
parameters[5].Value = IsReCount;
parameters[6].Value = 1;
parameters[7].Value = strWhere;
return DbHelperSQL.RunProcedure("SP_GetRecordByPage", parameters, "ds");
}
访问层
public DataSet GetList(int startIndex, int endindex, string strWhere, int IsReCount)
{
return dal.GetList(startIndex,endindex,strWhere,IsReCount);
}
表示层
protected void init()
{ string strWhere = " 1 = 1 "; this.AspNetPager1.PageSize = 10;
this.AspNetPager1.AlwaysShow = true;
DataSet dsNews = newsManager.GetList(AspNetPager1.StartRecordIndex, AspNetPager1.EndRecordIndex, strWhere,0); newsList.DataSource = dsNews;
newsList.DataBind();
}
这个跟网上的有什么不一样吗 请高手解决
nt.Parse(bll.GetRecordCount(strWhere).Tables[0].Rows[0][0].ToString()); 这个方法我一没看明百 string strWhere = " 1 = 1 ";
55 ETHaiNan.BLL.ET_Video bll = new ETHaiNan.BLL.ET_Video();
56 this.anpager.RecordCount = int.Parse(bll.GetRecordCount(strWhere).Tables[0].Rows[0][0].ToString());
57 this.anpager.PageSize = 10;
58 this.anpager.AlwaysShow = true;
59 DataSet ds = bll.GetList(anpager.StartRecordIndex, anpager.EndRecordIndex,strWhere, 0);
60 this.rpt.DataSource = ds;
61 ds.Dispose();
取值就是 Tables[0].Rows[0][0].ToString 首行首列嘛2。数据集绑定 人家用 GetList 你用你的 没有错
3。 这个是sql2000的分页 2005用row_number()
GetRecordCount根据查询条件获取记录数
StartRecordIndex, EndRecordIndex表示当前页记录的起始索引和结束索引