aspnetpager控件使用方法

xiaoxiao2021-03-01  28

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

最新回复(0)