AspNetPager1+SQL2000 分页存储过程(asp.net2.0)
时间:2010-09-27 来源:裴鹏
//SQL2000 分布储存过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[futures_GetPagedReCord]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0 --如果@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
else
begin
if @OrderType != 0
begin
set @strTmp = '<(SELECT MIN'
set @strOrder = ' ORDER BY [' + @fldName +'] DESC'
--如果@OrderType不是0,就执行降序
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)
// 获取数据列表
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[futures_GetRecordCount]
@strSQL varchar(1000)
AS
begin
select @strSQL=@strSQL
end
exec (@strSQL)
//aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
InitDataBind();
}
}
void InitDataBind()
{
string strBread = DDListBreed.SelectedItem.Text;
string strName = DDListName.SelectedItem.Text;
string strWhere = "1=1";
if (strBread!= "")
{
strWhere += " and breed='" + strBread + "'";
}
if (strName != "")
{
strWhere += " and marketname='" + strName + "'";
}
this.AspNetPager1.RecordCount = int.Parse(breedBll.GetListCount(strWhere).Tables[0].Rows[0][0].ToString());
DataSet ds = breedBll.GetList(this.AspNetPager1.PageSize, this.AspNetPager1.CurrentPageIndex, strWhere, "1");
this.breedGV.DataSource = ds;
this.breedGV.DataBind();
ds.Dispose();
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
InitDataBind();
}
//BLL
public DataSet GetListCount(string strWhere)
{
return dal.GetListCount(strWhere);
}
public DataSet GetList(int PageSize, int PageIndex, string strWhere, string OrderType)
{
return dal.GetList(PageSize, PageIndex, strWhere, OrderType);
}
//DAL
/// <summary>
/// 计算数据总数
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public DataSet GetListCount(string strWhere)
{
SqlParameter[] parameters = {
new SqlParameter("@strSQL", SqlDbType.VarChar,1000)
};
string strSql = "select count(*) from A";
if (strWhere != "")
{
strSql += " where " + strWhere + "";
}
parameters[0].Value = strSql;
return DbHelperSQL.RunProcedure("futures_GetRecordCount", parameters, "ds");
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(int PageSize, int PageIndex, string strWhere, string OrderType)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@doCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "A";
parameters[1].Value = "updatedate";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = int.Parse(OrderType);
parameters[6].Value = strWhere;
return DbHelperSQL.RunProcedure("futures_GetPagedReCord", parameters, "ds");
}