■结合一个存储过程,将分页做成最简单,请看...... 结合一个存储过程,将分页做成最简单,请看以下源码 此分页类所操作的存储过程#region 此分页类所操作的存储过程 /**//********************************************************* * * 功能强大,配合以下这个存储过程 * * *******************************************************/ /**//* -- Pager 1,10,0,0, 'EmployeeID>2 and EmployeeID<5 ' , 'Employees','*','LastName',0 CREATE PROCEDURE Pager @PageIndex int,--索引页 1 @PageSize int,--每页数量2 @RecordCount int out,--总行数3 @PageCount int out,--总页数4 @WhereCondition Nvarchar(1000),--查询条件5 @TableName nvarchar(500),--查询表名6 @SelectStr nvarchar(500) = '*',--查询的列7 @Order nvarchar(500),--排序的列8 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 9 @Groupby NVarChar(100) = '' AS declare @strSQL nvarchar(2000) -- 主语句 declare @strTmp nvarchar(1000) -- 临时变量 declare @strOrder nvarchar(1000) -- 排序类型 if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by ' + @Order +' desc' end else begin set @strTmp = '>(select max' set @strOrder = ' order by ' + @Order +' asc' end set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from ' + @TableName + ' where ' + @Order + '' + @strTmp + '([' + @Order + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @Order + '] from ' + @TableName + '' + @strOrder + ') as tblTmp)' + @Groupby + @strOrder if @WhereCondition != '' set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from ' + @TableName + ' where ' + @Order + '' + @strTmp + '([' + @Order + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @Order + '] from ' + @TableName + ' where (' + @WhereCondition + ') ' + @strOrder + ') as tblTmp) and (' + @WhereCondition + ') ' + @Groupby + @strOrder if @PageIndex = 1 begin set @strTmp = '' if @WhereCondition != '' set @strTmp = ' where (' + @WhereCondition + ')' set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from ' + @TableName + '' + @strTmp + ' ' + @Groupby + @strOrder end exec (@strSQL) --print @strSQL IF @WhereCondition <>'' Begin SET @strTmp = 'SELECT -1 FROM ' + @TableName + ' Where ' + (@WhereCondition) End ELSE Begin SET @strTmp = 'SELECT -1 FROM ' + @TableName End EXEC SP_EXECUTESQL @strTmp SET @RecordCount = @@RowCount -- 获取总页数 -- "CEILING"函数:取得不小于某数的最小整数 SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) GO *****************************************************************************/ /**//**************************************************************************** * * 用法 * * ***************************************************************************/ /**//* Dim ts As String = Request.Form.Item("txtDate") If (ts = "" Or ts Is Nothing) Then ts = Request.QueryString("txtDate") End If Dim ts2 As String = Request.Form.Item("txtDate2") If (ts2 = "" Or ts2 Is Nothing) Then ts2 = Request.QueryString("txtDate2") End If Dim ps As String = Request.Form.Item("pageIndex") If (ps = "" Or ps Is Nothing) Then ps = Request.QueryString("pageIndex") End If Dim t As Integer = 2 Dim p As Integer = 1 If ts Is Nothing Then ts = "" End If If ps Is Nothing Then ps = "" End If If Not (ps = "") Then p = Integer.Parse(ps) End If Dim pager As Pager = New Pager pager.PageIndex = p pager.PageSize = 20 pager.PageMode = PageMode.Str pager.WhereCondition = "TheDate between convert(datetime,'" + ts + "') and convert(datetime,'" + ts2 + "')" 'pager.WhereCondition = " convert(char(10),TheDate,120)= '" + ts + "'" pager.TableName = "LoadCountlog" pager.SelectStr = "*" pager.Order = "ID" pager.OrderType = False Dim dt As System.Data.DataTable = pager.GetDatas(p) myDataGrid.DataSource = dt myDataGrid.DataBind() Dim goUrl As String = "WebForm1.aspx?txtDate=" + ts + "&txtDate2=" + ts2 Me.Label3.Text = "共:" + pager.PageCount.ToString + "页," + pager.RecordCount.ToString() + "条 " + pager.OutPager(pager, goUrl, False) + "" */ #endregion using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Text; namespace solucky { /**//// /// 分页模式 /// public enum PageMode { /**//// /// 数字分页 /// Num =0, /**//// /// 字符分页 /// Str =1 } /**//// /// 分页类,能过存储过程进行分页,功能相当强大。 /// public class Pager { private int pageIndex = 0; private int recordCount = 0; private int pageSize = 20; private int pageCount = 0; private int rowCount = 0; private string tableName = ""; private string whereCondition = "1=1"; private string selectStr = "*"; private string order = ""; private string procedure ="pager"; private bool orderType = true; private PageMode pageMode =PageMode.Num; private string sqlConnectionString = ConfigurationSettings.AppSettings["database"]; private string databaseOwner = "dbo"; 数据连接#region 数据连接 /**//// /// 数据连接字符串 /// private string SqlConnectionString { get { return this.sqlConnectionString; } set { this.sqlConnectionString=value; } } /**//// ///获取连接实例 /// /// private SqlConnection GetSqlConnectionString() { try { return new SqlConnection(SqlConnectionString); } catch { throw new Exception("SQL Connection String is invalid."); } } /**//// /// 数据对象所有者 /// private string DatabaseOwner { get { return this.databaseOwner; } set{ this.databaseOwner=value; } } #endregion public Pager() { // // TODO: 在此处添加构造函数逻辑 // //Enum.Parse(tyo } public Pager(string connstr ) { if (connstr!=null) this.SqlConnectionString=connstr; } #region /**//// /// 所要操作的存储过程名称,已有默认的分页存储过程 /// public string Procedure { get{ return this.procedure ; } set { if (value==null || value.Length <=0) { this.procedure="pager"; } else { this.procedure=value; } } } /**//// /// 当前所要显示的页面数 /// public int PageIndex { get { return this.pageIndex; } set { this.pageIndex = value; } } /**//// /// 总的页面数 /// public int PageCount { get { return this.pageCount; } set { this.pageCount = value; } } /**//// /// 总行数 /// public int RecordCount { get { return this.recordCount; } set { this.recordCount = value; } } /**//// /// 每页条数 /// public int PageSize { get { return this.pageSize; } set { this.pageSize = value; } } /**//// /// 表名称 /// public string TableName { get { return tableName; } set { this.tableName = value; } } /**//// /// 条件查询 /// public string WhereCondition { get { return whereCondition; } set { whereCondition = value; } } /**//// /// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号 /// public string SelectStr { get { return selectStr; } set { selectStr = value; } } /**//// /// 排序的列 /// public string Order { get { return order; } set { order = value; } } /**//// /// 排序类型 true:asc false:desc /// public bool OrderType { get { return orderType; } set { orderType = value; } } /**//// /// 分页模式 /// public PageMode PageMode { get { return this.pageMode; } set { this.pageMode = value; } } /**//// /// 得到当前返回的数量 /// public int RowCount { get { return this.rowCount; } } private string groupby; public string Groupby { get { return this.groupby; } set { this.groupby = value; } } #endregion /**//// /// 分页查寻结果 /// public DataTable GetDatas(int pageIndex) { this.pageIndex = pageIndex; Pager pager = this; //pager.pageIndex = pageIndex; DataTable returnTb = Pagination(ref pager).Tables[0]; this.rowCount = returnTb.Rows.Count; return returnTb; } /**//// /// 分页操作存储过程函数 /// /// /// private DataSet Pagination(ref Pager pager) { using ( SqlConnection myConnection = GetSqlConnectionString() ) { SqlDataAdapter myCommand = new SqlDataAdapter(pager.databaseOwner + "."+pager.Procedure, myConnection); myCommand.SelectCommand.CommandType = CommandType.StoredProcedure; SqlParameter parameterPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int); parameterPageIndex.Value = pager.PageIndex; myCommand.SelectCommand.Parameters.Add(parameterPageIndex); SqlParameter parameterPageSize = new SqlParameter("@PageSize", SqlDbType.Int); parameterPageSize.Value = pager.PageSize; myCommand.SelectCommand.Parameters.Add(parameterPageSize); SqlParameter parameterRecordCount = new SqlParameter("@RecordCount", SqlDbType.Int); parameterRecordCount.Value = 0; parameterRecordCount.Direction = ParameterDirection.InputOutput; myCommand.SelectCommand.Parameters.Add(parameterRecordCount); SqlParameter parameterPageCount = new SqlParameter("@PageCount", SqlDbType.Int); parameterPageCount.Value = 0; parameterPageCount.Direction = ParameterDirection.InputOutput; myCommand.SelectCommand.Parameters.Add(parameterPageCount); SqlParameter parameterWhereCondition = new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500); parameterWhereCondition.Value = pager.WhereCondition; myCommand.SelectCommand.Parameters.Add(parameterWhereCondition); SqlParameter parameterTableName = new SqlParameter("@TableName", SqlDbType.NVarChar,500); parameterTableName.Value = pager.TableName; myCommand.SelectCommand.Parameters.Add(parameterTableName); SqlParameter parameterOrder = new SqlParameter("@Order", SqlDbType.NVarChar,500); parameterOrder.Value = pager.Order; myCommand.SelectCommand.Parameters.Add(parameterOrder); SqlParameter parameterSelectStr = new SqlParameter("@SelectStr", SqlDbType.NVarChar,500); parameterSelectStr.Value = pager.SelectStr; myCommand.SelectCommand.Parameters.Add(parameterSelectStr); SqlParameter parameterGroupby = new SqlParameter("@Groupby", SqlDbType.NVarChar, 100); parameterGroupby.Value = pager.Groupby; myCommand.SelectCommand.Parameters.Add(parameterGroupby); SqlParameter parameterOrderType = new SqlParameter("@OrderType", SqlDbType.Bit); parameterOrderType.Value = pager.OrderType==false?0:1; myCommand.SelectCommand.Parameters.Add(parameterOrderType); DataSet returnDS = new DataSet(); //SqlDataAdapter sqlDA = myCommand.crnew SqlDataAdapter(myCommand); myCommand.Fill(returnDS); pager.PageCount = (int)parameterPageCount.Value; pager.RecordCount = (int)parameterRecordCount.Value; return returnDS; } } 生成分页#region 生成分页 /**//// /// 生成分页格式 /// /// /// /// /// public string OutPager(Pager pager,string url,bool isBr) { StringBuilder returnOurWml; if(isBr) { returnOurWml= new StringBuilder("["+ pager.PageCount.ToString() + "页," + pager.RecordCount.ToString() +"条] "); } else { returnOurWml = new StringBuilder(); } if (pager.PageMode == PageMode.Num) { //分页每行显示的数量 int pagersCount = 10; int pagers = 0; int startInt = 1; int endInt = pager.PageCount; int i = 1; string endStr = ""; if (pager.PageCount>pagersCount) { //double k = ; pagers = pager.PageIndex / pagersCount; if (pagers == 0) { pagers = 1; } else if((pager.PageIndex % pagersCount)!=0) { pagers +=1; } endInt = pagers * pagersCount; if (pager.PageIndex <= endInt) { startInt = endInt +1 - pagersCount; if (startInt <1) { startInt = 1; } } //显示数量不足时pagersCount if (endInt>=pager.PageCount) { endInt = pager.PageCount; } else { //if (pager.PageIndex) endStr = " endStr += url + "&pageIndex=" + (endInt + 1).ToString() + "\" title='第"+ (endInt + 1).ToString()+"页'>"; endStr += ">>"; endStr += " "; } if (pagers > 1) { returnOurWml.Append(" returnOurWml.Append(url + "&pageIndex=" + (startInt - 1).ToString() + "\" title='第"+ (startInt - 1).ToString()+"页'>"); returnOurWml.Append("<<"); returnOurWml.Append(" "); } } for (i = startInt; i<=endInt;i++) { if (i!=pager.PageIndex) { returnOurWml.Append(" returnOurWml.Append(url + "&pageIndex=" + i.ToString() + "\" title='第"+ i.ToString()+"页'>"); returnOurWml.Append("["+i.ToString() + "]"); returnOurWml.Append(" "); } else { returnOurWml.Append(""+ i.ToString() + ""); } } returnOurWml.Append(endStr); return returnOurWml.Append(" ").ToString(); } else { if ( pager.PageIndex > 1) { returnOurWml.Append(" returnOurWml.Append(url + "&pageIndex=" + (pager.PageIndex -1).ToString() + "\">"); returnOurWml.Append("上一页"); returnOurWml.Append(" "); } if (pager.PageIndex < pager.PageCount) { returnOurWml.Append(pager.PageIndex.ToString()); returnOurWml.Append(" returnOurWml.Append(url + "&pageIndex=" + (pager.PageIndex +1).ToString() + "\">"); returnOurWml.Append("下一页"); returnOurWml.Append(" "); } return returnOurWml.Append(" ").ToString(); } } #endregion } } |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-29 21:32 , Processed in 0.312565 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.