三层结构中怎样使用AspNetPager控件并结合分页存储过程使用
是这样的我在三层结构中做开发，想对GridView分页，于是我选择了AspNetPager控件7.0.2,但是我的数据库是SQL 2000，不像SQL 2005中有ROW_NUMBER()函数，只好使用存储过程。在网上找到个通用的存储过程
[code=SQL]
------------------------------------
--用途：分页存储过程(对有主键的表效率极高)
--说明：
------------------------------------
CREATE PROCEDURE UP_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)
GO
[/code]
然后我在三层中调用
[code=C#]
BLL层
public DataSet GetList(int PageSize, int PageIndex, string strWhere)
{
return dal.GetList(PageSize, PageIndex, strWhere);
}
IDAL层
DataSet GetList(int PageSize,int PageIndex,string strWhere);
DAL层
///<summary>
///分页获取数据列表
///</summary>
public DataSet GetList(int PageSize,int PageIndex,string strWhere)
{
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("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "Li_Admin";
parameters[1].Value = "adminID";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere;
return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
}
DbHelperSQL中函数
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
= CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}
web层调用
public void BindData(string strWhere)
{
string StrWhere = null;
ViewState["vsKey"] = null;
if (strWhere != "")
{
ViewState["vsKey"] = strWhere;
StrWhere = strWhere;
}
Li.BLL.Li_Admin admin_bll = new Li.BLL.Li_Admin();
AspNetPager1.RecordCount = admin_bll.GetCount(strWhere);
DataSet ds = admin_bll.GetList(AspNetPager1.PageSize,AspNetPager1.CurrentPageIndex - 1, StrWhere);
this.GridView1.DataSource = ds; //这里报错，内容为IListSource 不包含任何数据源。
this.GridView1.DataBind();
}
[/code]
请大家帮帮忙，告诉我是什么问题，谢谢大家。
在DataSet中指明绑定表试试
