欢迎光临
我们一直在努力

将以前写的sql2分查找法通用分页存储过程算法 改成.net类实现_asp.net技巧

建站超值云服务器,限时71元/月

using System;


namespace CountryPark.DAL
…{
    /**//**//**//// <summary>
    /// PageList 的摘要说明。
    /// </summary>
    public sealed class PageList
    …{
        static PageList()
        …{
        }
       
        /**//**//**//// <summary>
        /// 分页查询数据记录总数获取
        /// </summary>
        /// <param name=”_tbName”>—-要显示的表或多个表的连接</param>
        /// <param name=”_ID”>—-主表的主键</param>
        /// <param name=”_strCondition”>—-查询条件,不需where</param>       
        /// <param name=”_Dist”>—-是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
        /// <returns></returns>
        public static string getPageListCounts(string _tbName, string _ID, string _strCondition, int _Dist)
        …{           
            //—存放取得查询结果总数的查询语句                   
            //—对含有DISTINCT的查询进行SQL构造
            //—对含有DISTINCT的总数查询进行SQL构造
            string strTmp=””, SqlSelect=””, SqlCounts=””;
           
            if (_Dist == 0)
            …{
                SqlSelect = “SELECT “;
                SqlCounts = “COUNT(*)”;
            }
            else
            …{
                SqlSelect = “SELECT DISTINCT “;
                SqlCounts = “COUNT(DISTINCT “+ _ID +”)”;
            }
            if (_strCondition == string.Empty)
            …{
                strTmp = SqlSelect +” @Counts=”+ SqlCounts +” FROM “+ _tbName;
            }
            else
            …{
                strTmp = SqlSelect +” @Counts=”+ SqlCounts +” FROM “+ ” WHERE (1=1) “+ _strCondition;
            }
            return strTmp;
        }



        /**//**//**//// <summary>
        /// 获取分页数据查询SQL
        /// </summary>
        /// <param name=”_tbName”>—-要显示的表或多个表的连接</param>
        /// <param name=”_fldName”>—-要显示的字段列表</param>
        /// <param name=”_PageSize”>—-每页显示的记录个数</param>
        /// <param name=”_Page”>—-要显示那一页的记录</param>
        /// <param name=”_PageCount”>—-查询结果分页后的总页数</param>
        /// <param name=”_Counts”>—-查询到的记录数</param>
        /// <param name=”_fldSort”>—-排序字段列表或条件(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)–程序传参如: SortA Asc,SortB Desc,SortC )</param>
        /// <param name=”_Sort”>—-排序方法,0为升序,1为降序</param>
        /// <param name=”_strCondition”>—-查询条件,不需where</param>
        /// <param name=”_ID”>—-主表的主键</param>
        /// <param name=”_Dist”>—-是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
        /// <returns></returns>                                                                          
        public static string getPageListSql(string _tbName, string _fldName, int _PageSize, int _Page, out int _PageCount, int _Counts, string _fldSort, int _Sort, string _strCondition, string _ID, int _Dist)
        …{               
            string strTmp=””; //—strTmp用于返回的SQL语句
            string SqlSelect=””, strSortType=””, strFSortType=””;  


            if (_Dist == 0)
            …{
                SqlSelect = “SELECT “;           
            }
            else
            …{
                SqlSelect = “SELECT DISTINCT “;               
            }


            if (_Sort == 0)
            …{
                strFSortType = ” ASC”;
                strSortType = ” DESC”;
            }
            else
            …{
                strFSortType = ” DESC”;
                strSortType = ” ASC”;
            }


//            —-取得查询结果总数量—–
            int tmpCounts = 1;
            if (_Counts != 0)
            …{
                tmpCounts = _Counts;
            }
//          –取得分页总数
            _PageCount = (tmpCounts + _PageSize – 1)/_PageSize;
            //    /**//**当前页大于总页数 取最后一页**/
            if (_Page > _PageCount)
            …{
                _Page = _PageCount;
            }
            if (_Page <= 0)
            …{
                _Page = 1;
            }
//          –/*—–数据分页2分处理——-*/
            int pageIndex = tmpCounts/_PageSize;
            int lastCount = tmpCounts%_PageSize;
            if (lastCount > 0)
            …{
                pageIndex = pageIndex + 1;
            }
            else
            …{
                lastCount = _PageSize;
            }
            if (_strCondition == string.Empty) // –没有设置显示条件
            …{
                if (pageIndex < 2 || _Page <= (pageIndex/2 + pageIndex%2))  //–前半部分数据处理
                …{
                    if (_Page == 1)
                    …{
                        strTmp = SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +” ORDER BY “+ _fldSort +” “+ strFSortType;
                    }
                    else
                    …{
                        strTmp = SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +” WHERE “+ _ID +” <(SELECT MIN(“+ _ID +”) FROM (“+ SqlSelect +” TOP “+ _PageSize*(_Page-1) +” “+ _ID +” FROM “+ _tbName +
                            ” ORDER BY “+ _fldSort +” “+ strFSortType +”) AS TBMinID) ORDER BY “+ _fldSort +” “+ strFSortType;
                    }
                }
                else
                …{
                    _Page = pageIndex – _Page + 1; //后半部分数据处理
                    if (_Page <= 1) //–最后一页数据显示
                    …{
                        strTmp = SqlSelect +” * FROM (“+ SqlSelect +” TOP “+ lastCount +” “+ _fldName +” FROM “+ _tbName +” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TempTB”+ ” ORDER BY “+ _fldSort +” “+ strFSortType;
                    }
                    else
                    …{
                        strTmp = SqlSelect +” * FROM (“+ SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +
                            ” WHERE “+ _ID +” >(SELECT MAX(“+ _ID +”) FROM(“+ SqlSelect +” TOP “+ (_PageSize*(_Page-2)+lastCount) +” “+ _ID +” FROM “+ _tbName +
                            ” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TBMaxID) ORDER BY “+ _fldSort +” “+ strSortType +”) AS TempTB ORDER BY “+ _fldSort +” “+ strFSortType;
                    }
                }
            }
            else // –有查询条件
            …{
                if (pageIndex < 2 || _Page <=(pageIndex/2 + pageIndex%2))//–前半部分数据处理
                …{
                    if (_Page == 1)
                    …{
                        strTmp = SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +”WHERE 1=1 “+ _strCondition +” ORDER BY “+ _fldSort +” “+ strFSortType;
                    }
                    else
                    …{
                        strTmp = SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +
                            ” WHERE “+ _ID +” <(SELECT MIN(“+ _ID +”) FROM (“+ SqlSelect +” TOP “+ (_PageSize*(_Page-1)) +” “+ _ID +” FROM ” +_tbName +
                            ” WHERE 1=1 “+ _strCondition +” ORDER BY “+ _fldSort +” “+ strFSortType +”) AS TBMaxID) “+ _strCondition +
                            ” ORDER BY “+ _fldSort +” “+ strFSortType;                           
                    }
                }
                else //–后半部分数据处理
                …{
                    _Page = pageIndex-_Page+1;
                    if (_Page <= 1) //–最后一页数据显示
                    …{
                        strTmp = SqlSelect +” * FROM (“+ SqlSelect +” TOP “+ lastCount +” “+ _fldName +” FROM “+ _tbName +
                            ” WHERE 1=1 “+ _strCondition +” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TempTB ORDER BY “+ _fldSort +” “+ strFSortType;
                    }
                    else
                    …{  
                        strTmp = SqlSelect +” * FROM (“+ SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +
                            ” WHERE “+ _ID +” >(SELECT MAX(“+ _ID +”) FROM(“+ SqlSelect +” TOP “+ (_PageSize*(_Page-2)+ lastCount) +” “+ _ID +” FROM “+ _tbName +
                            ” WHERE 1=1 “+ _strCondition +” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TBMaxID) “+ _strCondition +
                            ” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TempTB ORDER BY “+ _fldSort +” “+ strFSortType;
                    }
                }
            }


            return strTmp;
        }
    }
}
–以上代码是针对之前写的TOP MAX模式的分页存储过程修改
–以上分页算法对SQL SERVER 和 ACCESS同样有效
参见:http://www.cnblogs.com/hertcloud/archive/2005/12/21/301327.html


 


 


//调用函数例子
public IList getParkDataList(string key, int curPage, out int pageCount, int pageSize, int Counts)
        …{
           
            IList list = new ArrayList();


            string SECLECT_FIELD = “T_Park.ParkID, T_Park.ParkTitle, T_Park.ParkLetter, T_ParkArea.AreaName, T_ParkType.ParkTypeName “;
            string SECLECT_TABLE = “T_ParkType INNER JOIN (T_ParkArea INNER JOIN T_Park ON T_ParkArea.ParkAreaID = T_Park.ParkAreaID) ON T_ParkType.ParkTypeID = T_Park.ParkTypeID”;
            string SECLECT_CONDITION = string.Empty;



            if (key != string.Empty)
            …{
                SECLECT_CONDITION = ” AND T_Park.ParkTitle like %”+ key +”%”;
            }


            string SELECT_ID = “ParkID”;
            string SELECT_FLDSORT = “ParkID”;
            int SELECT_SORT = 1;
            int SELECT_DIST = 0;
            string SQL = PageList.getPageListSql(SECLECT_TABLE, SECLECT_FIELD, pageSize, curPage, out pageCount, Counts, SELECT_FLDSORT, SELECT_SORT, SECLECT_CONDITION, SELECT_ID, SELECT_DIST);
            //string strCondition;       
            OleDb db = new OleDb();
            ParkBE park;           
            using(OleDbDataReader dr = (OleDbDataReader)db.ExecuteReader(CommonFun.GetConnectionString(), CommandType.Text, SQL))
            …{
                while (dr.Read())
                …{   
                    park = new ParkBE();
                    park.ParkID = Convert.ToInt32(dr[0]);
                    park.ParkTitle = dr[1].ToString();
                    park.ParkLetter = dr[2].ToString();
                    park.ParkAreaName = dr[3].ToString();
                    park.ParkTypeName = dr[4].ToString();
                    list.Add(park);
                }
            }           
            return list;
        }
http://blog.csdn.net/todaywlq/archive/2007/01/29/1497418.aspx

赞(0)
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com 特别注意:本站所有转载文章言论不代表本站观点! 本站所提供的图片等素材,版权归原作者所有,如需使用,请与原作者联系。未经允许不得转载:IDC资讯中心 » 将以前写的sql2分查找法通用分页存储过程算法 改成.net类实现_asp.net技巧
分享到: 更多 (0)