SqlSERVER分页函数（补充了额外查询条件的处理）

/// 

        /// MS SQLSERVER 分页SQL语句生成器，同样适用于ACCESS数据库(edit:2008.3.29)
        /// 

        /// 原始SQL语句
        /// 在分页前要替换的字符串，用于分页前的筛选
        /// 页大小
        /// 页码
        /// 记录总数
        /// 生成SQL分页语句
        private static string MakePageSQLStringByMSSQL(string strSQLInfo,string strWhere,int PageSize,int PageNumber,int AllCount)
        ...{
            分页位置分析#region 分页位置分析
            string strSQLType=string.Empty ;
            if(AllCount!=0)
            ...{
                if(PageNumber==1) //首页
                ...{
                    strSQLType="First";
                }
                else if(PageSize*PageNumber>AllCount) //最后的页 @@LeftSize
                ...{
                    PageSize=AllCount-PageSize*(PageNumber-1);
                    strSQLType="Last";
                }
                else //中间页
                ...{
                    strSQLType="Mid";
                }
            }
            else if(AllCount<0) //特殊处理 dth,2006.10.19
            ...{
                strSQLType="First";
            }
            else
            ...{
                strSQLType="Count";
            }

            #endregion
            
            SQL 复杂度分析#region SQL 复杂度分析 
            //SQL 复杂度分析 开始
            bool SqlFlag=true;//简单SQL标记
            string TestSQL=strSQLInfo.ToUpper ();
            int n=TestSQL.IndexOf ("SELECT ",0);
            n=TestSQL.IndexOf ("SELECT ",n+7);
            if(n==-1)
            ...{
                //可能是简单的查询，再次处理
                n=TestSQL.IndexOf (" JOIN ",n+7);
                if(n!=-1) SqlFlag=false;
                else
                ...{
                    //判断From 谓词情况
                    n=TestSQL.IndexOf("FROM ",9);
                    if(n==-1) return "";
                    //计算 WHERE 谓词的位置
                    int m=TestSQL.IndexOf ("WHERE ",n+5);
                    // 如果没有WHERE 谓词
                    if(m==-1) m=TestSQL.IndexOf ("ORDER BY ",n+5);
                    //如果没有ORDER BY 谓词，那么无法排序，退出；
                    if(m==-1) 
                        throw new Exception ("查询语句分析：当前没有为分页查询指定排序字段！请适当修改SQL语句。 "+strSQLInfo);
                    string strTableName=TestSQL.Substring (n,m-n);
                    //表名中有 , 号表示是多表查询
                    if(strTableName.IndexOf (",")!=-1)
                        SqlFlag=false;
                }
            }
            else
            ...{
                //有子查询；
                SqlFlag=false;
            }
            //SQL 复杂度分析 结束
            #endregion

            排序语法分析#region 排序语法分析
            //排序语法分析 开始
            int iOrderAt=strSQLInfo.ToLower ().LastIndexOf ("order by ");
            //如果没有ORDER BY 谓词，那么无法排序分页，退出；
            if(iOrderAt==-1)
                throw new Exception ("查询语句分析：当前没有为分页查询指定排序字段！请适当修改SQL语句。 "+strSQLInfo);

            string strOrder=strSQLInfo.Substring (iOrderAt+9);
            strSQLInfo=strSQLInfo.Substring(0,iOrderAt);
            string[] strArrOrder=strOrder.Split (new char []...{','});
            for(int i=0;i<strArrOrder.Length ;i++)
            ...{
                string[] strArrTemp=(strArrOrder[i].Trim ()+" ").Split (new char[]...{' '});
                //压缩多余空格
                for(int j=1;j<strArrTemp.Length ;j++)
                ...{
                    if(strArrTemp[j].Trim ()=="")
                    ...{
                        continue;
                    }
                    else
                    ...{
                        strArrTemp[1]=strArrTemp[j];
                        if(j >1 ) strArrTemp[j]="";
                        break;
                    }
                }
                //判断字段的排序类型
                switch(strArrTemp[1].Trim ().ToUpper ())
                ...{
                    case "DESC":
                        strArrTemp[1]="ASC";
                        break;
                    case "ASC":
                        strArrTemp[1]="DESC";
                        break;
                    default:
                        //未指定排序类型，默认为降序
                        strArrTemp[1]="DESC";
                        break;
                }
                //消除排序字段对象限定符
                if(strArrTemp[0].IndexOf (".")!=-1)
                    strArrTemp[0]=strArrTemp[0].Substring (strArrTemp[0].IndexOf (".")+1);
                strArrOrder[i]=string.Join (" ",strArrTemp);

            }
            //生成反向排序语句
            string strNewOrder=string.Join (",",strArrOrder).Trim ();
            strOrder=strNewOrder.Replace ("ASC","ASC0").Replace ("DESC","ASC").Replace ("ASC0","DESC");
            //排序语法分析结束
            #endregion

            构造分页查询#region 构造分页查询
            string SQL=string.Empty ;
            if(!SqlFlag)
            ...{
                //复杂查询处理
                switch(strSQLType.ToUpper ())
                ...{
                    case "FIRST":
                        SQL="Select Top @@PageSize * FROM ( " +strSQLInfo+
                            " ) P_T0 @@Where ORDER BY "+strOrder;
                        break;
                    case "MID":
                        SQL=@"SELECT Top @@PageSize * FROM
                         (SELECT Top @@PageSize * FROM
                           (
                             SELECT Top @@Page_Size_Number * FROM (";
                        SQL+=" "+strSQLInfo+" ) P_T0 @@Where ORDER BY "+strOrder+" ";
                        SQL+=@") P_T1
            ORDER BY "+ strNewOrder +") P_T2  "+
                            "ORDER BY "+strOrder;
                        break;
                    case "LAST":
                        SQL=@"SELECT * FROM (     
                          Select Top @@LeftSize * FROM ("+" "+strSQLInfo+" ";
                        SQL+=" ) P_T0 @@Where ORDER BY "+ strNewOrder+" "+ 
                            " ) P_T1 ORDER BY "+strOrder;
                        break;
                    case "COUNT":
                        SQL="Select COUNT(*) FROM ( " +strSQLInfo+" ) P_Count @@Where";
                        break;
                    default:
                        SQL=strSQLInfo+strOrder;//还原
                        break;
                }

            }
            else
            ...{
                //简单查询处理
                switch(strSQLType.ToUpper ())
                ...{
                    case "FIRST":
                        SQL=strSQLInfo.ToUpper().Replace ("SELECT ","SELECT TOP @@PageSize "); 
                        SQL+="  @@Where ORDER BY "+strOrder;
                        break;
                    case "MID":
                        string strRep=@"SELECT Top @@PageSize * FROM
                         (SELECT Top @@PageSize * FROM
                           (
                             SELECT Top @@Page_Size_Number  ";
                        SQL=strSQLInfo.ToUpper().Replace ("SELECT ",strRep); 
                        SQL+="  @@Where ORDER BY "+strOrder;
                        SQL+="  ) P_T0 ORDER BY "+ strNewOrder+" "+ 
                            " ) P_T1 ORDER BY "+strOrder;
                        break;
                    case "LAST":
                        string strRep2=@"SELECT * FROM (     
                          Select Top @@LeftSize ";
                        SQL=strSQLInfo.ToUpper().Replace ("SELECT ",strRep2);
                        SQL+=" @@Where ORDER BY "+ strNewOrder+" "+ 
                            " ) P_T1 ORDER BY "+strOrder;
                        break;
                    case "COUNT":
                        SQL="Select COUNT(*) FROM ( " +strSQLInfo+" @@Where) P_Count ";//edit 2008.3.29
                        break;
                    default:
                        SQL=strSQLInfo+strOrder;//还原
                        break;
                }
            }

            //执行分页参数替换
            SQL=SQL.Replace ("@@PageSize",PageSize.ToString ())
                .Replace ("@@Page_Size_Number",Convert.ToString (PageSize * PageNumber))
                .Replace ("@@LeftSize",PageSize.ToString ());//
                //.Replace ("@@Where",strWhere);
            //针对用户的额外条件处理：
            if(strWhere!="" && strWhere.ToUpper ().Trim ().StartsWith ("WHERE "))
            ...{
                throw new Exception ("分页额外查询条件不能带Where谓词！");
            }
            if(!SqlFlag)
            ...{
                if(strWhere!="") strWhere=" Where "+strWhere;
                SQL=SQL.Replace ("@@Where",strWhere);
            }
            else
            ...{
                if(strWhere!="") strWhere=" And ("+strWhere+")";
                SQL=SQL.Replace ("@@Where",strWhere);
            }
            return SQL;
            #endregion
        }