Repeater控件的分页效果
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" HorizontalAlign="Center" OnPageChanged="AspNetPager1_PageChanged" Width="100%" FirstPageText="首页" NextPageText="下一页" PrevPageText="上一页" LastPageText="尾页" AlwaysShow="True" ></webdiyer:AspNetPager>
后台绑定代码：
void Bind() { int pageSize = 10 ; XieKe.BLL.Content con = new XieKe.BLL.Content(); PageBase pb = new PageBase(); pb.OrderType = 0;//0 升序;1 降序 pb.PageIndex = this.AspNetPager1.CurrentPageIndex; pb.PageSize = pageSize; pb.ProcedureName = "pagination"; pb.StrWhere = " "; pb.StrGetFields = "*"; pb.FldName = "id"; pb.TblName = "tb_ReceiveRecord"; int count = 0;//总条数 pb.DoCount = 1; //表示只获取总数 con.GetList(pb, ref count); pb.DoCount = 0; //表示获取数据 DataTable dt = con.GetList(pb, ref count).Tables[0]; this.Repeater1.DataSource = dt; this.Repeater1.DataBind(); //数据分页 AspNetPager1.PageSize = pageSize; AspNetPager1.RecordCount = count; if (count <= pb.PageSize) AspNetPager1.Visible = false; else AspNetPager1.Visible = true; }
DAL层调用代码：
/// <summary> /// 分页获取数据列表 /// </summary> /// <param name="pb">分页基本信息</param> /// <param name="count">返回总条数</param> /// <returns></returns> public DataSet GetList(XieKe.Model.PageBase pb, ref int count) { SqlParameter[] parameters = { new SqlParameter("@tblName", SqlDbType.NVarChar, 4000), new SqlParameter("@strGetFields", SqlDbType.VarChar, 255), new SqlParameter("@fldName", SqlDbType.NVarChar, 255), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@doCount", SqlDbType.Int), new SqlParameter("@OrderType", SqlDbType.Int), new SqlParameter("@strWhere", SqlDbType.NVarChar,2000), }; parameters[0].Value = pb.TblName; parameters[1].Value = pb.StrGetFields; parameters[2].Value = pb.FldName; parameters[3].Value = pb.PageSize; parameters[4].Value = pb.PageIndex; parameters[5].Value = pb.DoCount; parameters[6].Value = pb.OrderType; parameters[7].Value = pb.StrWhere; DataSet ds = new DataSet(); ds = RunProcedure(pb.ProcedureName, parameters, "ds"); if (pb.DoCount == 1) { if (ds.Tables.Count == 1) count = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString()); } return ds; } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> 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; } } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand</returns> private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = 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; }
/// <summary> /// 分页实体类 /// </summary> public class PageBase { private string _orderName; public string OrderName { get { return _orderName; } set { _orderName = value; } } private string tblName; /// <summary> /// 需要查询的表名 仅限单表查询 /// </summary> public string TblName { get { return tblName; } set { tblName = value; } } private string strGetFields = "*"; /// <summary> /// 显示字段 /// </summary> public string StrGetFields { get { return strGetFields; } set { strGetFields = value; } } private string fldName; /// <summary> /// 主键名称 /// </summary> public string FldName { get { return fldName; } set { fldName = value; } } private int pageSize = 12; /// <summary> /// 每页显示多少记录数 /// </summary> public int PageSize { get { return pageSize; } set { pageSize = value; } } private int pageIndex = 1; /// <summary> /// 当前页数 /// </summary> public int PageIndex { get { return pageIndex; } set { pageIndex = value; } } private int doCount = 0; /// <summary> /// 排序类型0显示记录列表，1返回总条数 /// </summary> public int DoCount { get { return doCount; } set { doCount = value; } } private int orderType = 1; /// <summary> /// 排序方式0正序、1倒序 /// </summary> public int OrderType { get { return orderType; } set { orderType = value; } } private string strWhere; /// <summary> /// SQL语句的条件 /// </summary> public string StrWhere { get { return strWhere; } set { strWhere = value; } } private string procedureName; /// <summary> /// 存储过程名称 /// </summary> public string ProcedureName { get { return procedureName; } set { procedureName = value; } } }
sql存储过程：
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[pagination] @tblName varchar(4000), -- 表名 @strGetFields varchar(1000) = '*', -- 需要返回的列 @fldName varchar(255)='', -- 排序的字段名(可包含如TABLE.FLDNAME形式） @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @doCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(5000) -- 主语句 declare @strTmp varchar(110) -- 临时变量 declare @strOrder varchar(400) -- 排序类型 declare @fldName_t varchar(255) -- 在分页时用的排序字段名，不包含多表并列时的表名 set @fldName_t = right(@fldName,len(@fldName) - CHARINDEX('.',@fldName)) if @doCount != 0 begin if @strWhere !='' set @strSQL = 'select count(*) as Total from (select '+ @strGetFields +' from ' + @tblName + ' where '+@strWhere +') as A' --该写法解决带 group by 的问题 else set @strSQL = 'select count(*) as Total from (select '+ @strGetFields +' from ' + @tblName + ') as A' end --以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况 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 or @PageIndex=0 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代码 if @strWhere != '' set @strSQL='select * from (select top 100 percent Row_Number() over('+ @strOrder + ') as num,'+@strGetFields+ ' from '+ @tblName + ' where ' + @strWhere + ' )as tb_temp where tb_temp.num between ' + str(((@PageIndex-1)*@PageSize)+1) + ' and ' + str(@PageIndex*@PageSize) + ' ' + @strOrder else set @strSQL='select * from (select top 100 percent Row_Number() over('+ @strOrder + ') as num,'+@strGetFields+ ' from '+ @tblName + ' )as tb_temp where tb_temp.num between ' + str(((@PageIndex-1)*@PageSize)+1) + ' and ' + str(@PageIndex*@PageSize) + ' ' + @strOrder end end exec (@strsql)
