aspnetpager控件使用方法

xiaoxiao2021-03-01 32

最近开发项目的时候用到了分页控件ASPNETPAGET，现在将使用心得记录在此 1添加存储过程 --第二个存储过程 /* *************************************************************** ** 千万数量级分页存储过程 ** *************************************************************** 参数说明: 1.Tables :表名称,视图 2.PrimaryKey :主关键字 3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc 4.CurrentPage :当前页码 5.PageSize :分页尺寸 6.Filter :过滤语句，不带Where 7.Group :Group语句,不带Group By ***************************************************************/ CREATE PROCEDURE ComPagerData( @Tables varchar(1000), @PrimaryKey varchar(100), @Sort varchar(200) = NULL, @CurrentPage int = 1, @PageSize int = 10, @Fields varchar(1000) = '*', @Filter varchar(1000) = NULL, @Group varchar(1000) = NULL ) AS /*默认排序*/ IF @Sort IS NULL OR @Sort = '' SET @Sort = @PrimaryKey DECLARE @SortTable varchar(100) DECLARE @SortName varchar(100) DECLARE @strSortColumn varchar(200) DECLARE @operator char(2) DECLARE @type varchar(100) DECLARE @prec int /*设定排序语句.*/ IF CHARINDEX('DESC',@Sort)>0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '') SET @operator = '<=' END ELSE BEGIN IF CHARINDEX('ASC', @Sort) > 0 SET @strSortColumn = REPLACE(@Sort, 'ASC', '') SET @operator = '>=' END IF CHARINDEX('.', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn)) SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE BEGIN SET @SortTable = @Tables SET @SortName = @strSortColumn END SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize varchar(50) DECLARE @strStartRow varchar(50) DECLARE @strFilter varchar(1000) DECLARE @strSimpleFilter varchar(1000) DECLARE @strGroup varchar(1000) /*默认当前页*/ IF @CurrentPage < 1 SET @CurrentPage = 1 /*设置分页参数.*/ SET @strPageSize = CAST(@PageSize AS varchar(50)) SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50)) /*筛选以及分组语句.*/ IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' WHERE ' + @Filter + ' ' SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = '' SET @strFilter = '' END IF @Group IS NOT NULL AND @Group != '' SET @strGroup = ' GROUP BY ' + @Group + ' ' ELSE SET @strGroup = '' /*执行查询语句*/ EXEC( ' DECLARE @SortColumn ' + @type + ' SET ROWCOUNT ' + @strStartRow + ' SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' SET ROWCOUNT ' + @strPageSize + ' SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' ' ) GO 和存储过程 -- Add the parameters for the stored procedure here -- ============================================= -- Author: <caizhenfang> -- ALTER date: <2007-05-30> -- Description: <读取数据记录数量> -- ============================================= CREATE PROCEDURE ComPagerCount( @Tables varchar(50), --表 @PrimaryKey varchar(50), --主键 @Filter varchar(800) = NULL, --:条件过滤，不带 Where @Sort varchar(200) = Null, --:排序，不带 order by @PageSize int, --分页大小 @CurrentPage int, --分页索引 @docount bit --if true 则仅读取总记录数 ) as Declare @sCondit varchar(800) Declare @sOrder varchar(200) if @Filter IS NOT NULL AND @Filter != '' SET @sCondit = ' WHERE ' + @Filter else SET @sCondit = '' if @Sort IS Null And @Sort = '' Set @sOrder = ' ORDER By '+ @PrimaryKey else Set @sOrder = ' ORDER By '+ @Sort BEGIN Begin Tran set nocount on IF(@docount=1) exec('select count('+ @PrimaryKey +') from '+ @Tables + @sCondit) ELSE begin declare @PageLowerBound int declare @PageUpperBound int set @PageLowerBound=(@CurrentPage-1)*@PageSize set @PageUpperBound=@PageLowerBound+@PageSize create table #pageindex(id int identity(1,1) not null,nid int) set rowcount @PageUpperBound exec('insert into #pageindex(nid) select '+ @PrimaryKey +' from '+ @Tables + @sCondit + @sOrder ) Exec('SELECT O.* FROM '+ @Tables +' O,#pageindex p WHERE O.' + @PrimaryKey +'=p.nid and p.id>'+ @PageLowerBound +' and p.id<='+ @PageUpperBound +' order by p.id') END set nocount off If (@@Error=0) Commit Tran Else ROLLBACK Tran END GO 2添加类库 /// //名称：SelectPager.cs //用途：分页函数 //作者：蔡振芳 //时间：2007-5-22 //修改： //描述：所有列表分页函数 using System; using System.Collections; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.Common; using Microsoft.Practices.EnterpriseLibrary.Data; using Microsoft.Practices.EnterpriseLibrary.Common; namespace Dinge.CnGameAD.DataAccess { public class SelectPager { /// <summary> /// 执行存储过程，返回数据记录条数 /// </summary> /// <param name="PageIndex">开始索引</param> /// <param name="hashtable">存储过程参数列表,参数必须包括 Key1:@Tables，Value1:表名称，Key2:@Filter，Value2:查询字符串</param> /// <param name="DataBaseSelect">数据库选择 1：CnGameAD_Base 基础数据库，2：CnGameAD_Report报表数据库，3：CnGameAD_Log日志数据库</param> /// <returns>返回整型</returns> public static int ReadRowsCount(Hashtable hashtable,int DataBaseSelect) { int row = 0; Database DBAdmin = DatabaseFactory.CreateDatabase("EipString");//连接数据库类别 DataSet DSAdmin = null; try { DbCommand ComAdmin = DBAdmin.GetStoredProcCommand("ComPagerCount");//分页存储过程 DBAdmin.AddInParameter(ComAdmin, "@PrimaryKey", DbType.String, "ID");//表主键 DBAdmin.AddInParameter(ComAdmin, "@PageSize", DbType.Int32, 10);//分页大小 DBAdmin.AddInParameter(ComAdmin, "@CurrentPage", DbType.Int32, 1);//当前页 DBAdmin.AddInParameter(ComAdmin, "@docount", DbType.Int32, 1);//是只统计数据 foreach (DictionaryEntry fn in hashtable) { DBAdmin.AddInParameter(ComAdmin, (string)fn.Key, DbType.String, (string)fn.Value); } //得到输出参数的值,注意转化返回值类型 row = (int)DBAdmin.ExecuteScalar(ComAdmin); } catch (Exception ex) { return 0; } return row; } /// <summary> /// 执行存储过程，返回分页数据集 /// </summary> /// <param name="PrimaryKey">存储过程名</param> /// <param name="CurrentPage">表主键</param> /// <param name="PageSize">分页大小</param> /// <param name="oValue">存储过程参数数组，参数必须包括 Key1:@Tables，Value1:表名称，Key2:@Filter，Value2:查询字符串</param> /// Key3:@Fields，Value3:选择字段名，Key4:@Sort，Value4:排序方式，不带Order By可以为'' /// Key3:@Group，Value3:分组方式，不带Group By,可以为'' /// /// <param name="DataBaseSelect">数据库选择 1：CnGameAD_Base 基础数据库，2：CnGameAD_Report报表数据库，3：CnGameAD_Log日志数据库</param> /// <returns>DataSet</returns> public static DataSet ReadPageData(int PageIndex, int PageSize, Hashtable hashtable, int DataBaseSelect) { Database DBAdmin = DatabaseFactory.CreateDatabase("EipString");//连接数据库类别 DataSet DSAdmin = null; try { //DBAdmin = DatabaseFactory.CreateDatabase("CnGameAD_Base"); DbCommand ComAdmin = DBAdmin.GetStoredProcCommand("ComPagerData");//分页存储过程 DBAdmin.AddInParameter(ComAdmin, "@PrimaryKey", DbType.String, "ID");//表主键 DBAdmin.AddInParameter(ComAdmin, "@PageSize", DbType.Int32, PageSize);//分页大小 DBAdmin.AddInParameter(ComAdmin, "@CurrentPage", DbType.Int32, PageIndex);//当前页 foreach (DictionaryEntry fn in hashtable) { DBAdmin.AddInParameter(ComAdmin, (string)fn.Key, DbType.String, (string)fn.Value); } DSAdmin = new DataSet(); DSAdmin = DBAdmin.ExecuteDataSet(ComAdmin); } catch (Exception ex) { return null; } return DSAdmin; } } } 3在第2步中引入企业库名称空间 4实现分页程序 using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using Dinge.CnGameAD.DataAccess; public partial class TestAspNetPager : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { LoadDataBind(); } } /// <summary> /// 数据查询与绑定 /// </summary> /// <param name="PageIndex">开始页</param> private void LoadDataBind() { Hashtable hsTableCount = new Hashtable();//记录查询数据数量 Hashtable hsTableData = new Hashtable();//记录查询结果数据集 建立逻辑对象 string keywords = "ID"; string Fields = string.Empty;//查询字段 string Filter = string.Empty;//查询条件 string Group = string.Empty;//分组方式，不带 Group By string Sort = string.Empty;//排序方式，不带 Order By Sort = "id DESC"; Fields = "ID"; Fields = "ID,reportowner,MtSucceedNum,MtErorNum,MoNum"; // Filter = Filter + "reportowner" + "=1" + " and "; Filter = Filter + "reportowner=" + Convert.ToInt64(Session["ID"]) +" and "; Filter = Filter + "1=1"; hsTableCount.Add("@Tables", "eip_reportNote");//操作的表名 hsTableCount.Add("@Filter", Filter); //获取符合条件的记录总数 Pager.RecordCount = SelectPager.ReadRowsCount(hsTableCount, 1); hsTableData.Add("@Tables", "eip_reportNote");//操作的表名 hsTableData.Add("@Group", Group); hsTableData.Add("@Sort", Sort); hsTableData.Add("@Fields", Fields); hsTableData.Add("@Filter", Filter); //分页获取符合条件的记录并绑定 gvData.DataSource = SelectPager.ReadPageData(Pager.CurrentPageIndex, Pager.PageSize, hsTableData, 1); gvData.DataBind(); //Clear(); } public int MtNum(object container,string mtok,string mtero) { string Smtok = DataBinder.GetPropertyValue(container, mtok).ToString(); string Smtero = DataBinder.GetPropertyValue(container, mtero).ToString(); Int32 Return = 0; Return = Convert.ToInt32(Smtok) + Convert.ToInt32(Smtero); return Return; } protected void gvData_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType.Equals(DataControlRowType.DataRow)) { e.Row.Attributes.Add("onmouseover", "c=this.style.backgroundColor;this.style.backgroundColor='#ffffff'"); e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=c"); } } /// <summary> /// 网格删除事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void gvData_RowDeleting(object sender, GridViewDeleteEventArgs e) { } /// <summary> /// 分页事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void pager_PageChanged(object sender, EventArgs e) { LoadDataBind(); } //public string MtNum(object container, string MtSucceedNum, string MtErorNum) //{ // string Return; // int mtall,mtsucc,mter; // mtsucc=Convert.ToInt32(MtSucceedNum); // mter=Convert.ToInt32(MtErorNum); // mtall=mtsucc + mter; // Return = mtall.ToString(); // return Return; //} /// <summary> /// 批量审核 /// </summary> //private void CheckMultiple() //{ // string ID = string.Empty; // string State = string.Empty; // Hashtable hsTable = new Hashtable(); // //建立逻辑对象 // //DAABExternal _DAABExternal = new DAABExternal(); //} /// <summary> /// 查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> /// <summary> /// 网格颜色事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> } 就是这些代码拼在一起及解决问题了，近期将整理一下思路重写这篇文站相关资源：AspNetPager控件

转载请注明原文地址: https://www.6miu.com/read-4150228.html

技术

最新回复(0)