MESProductionWeb/AntORM/Ant.ORM/DataBaise/HelpSQL/Pager.cs

using System.Text;
using System;
using Ant.Data;

namespace Ant.ORM.SQL
{
    /// <summary>
    /// 分页语句类
    /// </summary>
    internal class Pager
    {
        public static string GetSql(DatabaseType dalType, int pageIndex, int pageSize, string where, string tableName, int rowCount, string columns)//增加了指定列功能
        {
            if (string.IsNullOrEmpty(columns))
            {
                columns = "*";
            }
            if (string.IsNullOrEmpty(where))
            {
                where = "1=1";
            }
            if (pageIndex == 0 || pageSize == 0)
            {
                return string.Format(selectAll, columns, tableName, where);
            }
            else if (pageIndex == 1)//第一页
            {
                switch (dalType)
                {
                    case DatabaseType.OleDb:
                    case DatabaseType.MSSQLServer:
                    case DatabaseType.Oracle:
                        return string.Format(selectAll, "top " + pageSize + " " + columns, tableName, GetWhereFixOrderBy(where));
                    case DatabaseType.SQLite:
                    case DatabaseType.MySQL:
                        return string.Format(selectAll, columns, tableName, GetWhereFixOrderBy(where) + " limit " + pageSize);
                }
            }
            int max = (pageIndex - 1) * pageSize;
            switch (dalType)
            {
                case DatabaseType.OleDb:
                case DatabaseType.MSSQLServer:
                case DatabaseType.Oracle:
                    return string.Format(AccessPageTop3, rowCount - max > pageSize ? pageSize : rowCount - max, pageIndex * pageSize + " " + columns, tableName, where, GetOrderBy(where, true), GetOrderBy(where, false));
                case DatabaseType.SQLite:
                case DatabaseType.MySQL:
                    return string.Format(selectAll, columns, tableName, GetWhereFixOrderBy(where) + " limit " + pageSize + " offset " + max);
            }
            throw new Exception("Pager::No Be Support:" + dalType.ToString());
        }

        private const string selectAll = "select {0} from {1} where {2}";
        private const string AccessPageTop3 = @"select top {0} * from (select top {0} * from (select top {1} from {2} where {3}) v {4}) v {5}";
        private static string GetWhereFixOrderBy(string where)
        {
            if (where.ToLower().IndexOf("order by") == -1)
            {
                where += " order by id";
            }
            return where;
        }
        private static string GetOrderBy(string where, bool reverse)
        {
            string orderby = " order by id ";

            int index = where.ToLower().IndexOf("order by");
            if (index > -1)
            {
                where = where.Substring(index).ToUpper();
                orderby = reverse ? where.Replace("DESC", "DE#SC").Replace("ASC", "DESC").Replace("DE#SC", "ASC") : where;
            }
            else
            {
                orderby += reverse ? "desc" : "asc";
            }
            return orderby.ToLower();
        }
        private static bool IsOrderByDesc(string where)
        {
            int index = where.ToLower().IndexOf("order by");
            if (index > -1 && where.ToLower().IndexOf("desc") > -1)
            {
                return true;
            }
            return false;
        }
    }
}