Ejemplo n.º 1
0
 private bool IsExistsDbNameWithCache(string dbName)
 {
     try
     {
         string key = dalType.ToString() + "." + dbName;
         if (dbList.ContainsKey(key))
         {
             return(dbList[key]);
         }
         bool result = IsExistsDbName(dbName);
         dbList.Add(key, result);
         return(result);
     }
     catch
     {
         return(true);
     }
 }
Ejemplo n.º 2
0
        private static DalBase GetDalBaseBy(ConnObject co)
        {
            DalType dalType = co.Master.ConnDalType;

            //License.Check(providerName);//¿ò¼ÜÄ£¿éÊÚȨ¼ì²â¡£
            switch (dalType)
            {
            case DalType.MsSql:
                return(new MsSqlDal(co));

            case DalType.Access:
                return(new OleDbDal(co));

            case DalType.Oracle:
                return(new OracleDal(co));

            case DalType.SQLite:
                return(new SQLiteDal(co));

            case DalType.MySql:
                return(new MySQLDal(co));

            case DalType.Sybase:
                return(new SybaseDal(co));

            case DalType.PostgreSQL:
                return(new PostgreDal(co));

            case DalType.Txt:
            case DalType.Xml:
                return(new NoSqlDal(co));
            }
            return((DalBase)Error.Throw(string.Format("GetHelper:{0} No Be Support Now!", dalType.ToString())));
        }
Ejemplo n.º 3
0
        public static string GetSql(DalType dalType, string version, int pageIndex, int pageSize, object objWhere, string tableName, int rowCount, string columns, string primaryKey, bool primaryKeyIsIdentity)
        {
            if (string.IsNullOrEmpty(columns))
            {
                columns = "*";
            }
            pageIndex    = pageIndex == 0 ? 1 : pageIndex;
            string where = SqlFormat.GetIFieldSql(objWhere);
            if (string.IsNullOrEmpty(where))
            {
                where = "1=1";
            }
            if (pageSize == 0)
            {
                return(string.Format(top1Pager, columns, tableName, where));
            }
            if (rowCount > 0)//分页查询。
            {
                where = SqlCreate.AddOrderBy(where, primaryKey);
            }
            int    topN     = pageIndex * pageSize;//Top N 最大数
            int    max      = (pageIndex - 1) * pageSize;
            int    rowStart = (pageIndex - 1) * pageSize + 1;
            int    rowEnd   = rowStart + pageSize - 1;
            string orderBy  = string.Empty;

            if (pageIndex == 1 && dalType != DalType.Oracle)//第一页(oracle时 rownum 在排序条件为非数字时,和row_number()的不一样,会导致结果差异,所以分页统一用row_number()。)
            {
                switch (dalType)
                {
                case DalType.Access:
                case DalType.MsSql:
                case DalType.Sybase:
                    return(string.Format(top1Pager, "top " + pageSize + " " + columns, tableName, where));

                //case DalType.Oracle:
                //    return string.Format(top1Pager, columns, tableName, "rownum<=" + pageSize + " and " + where);
                case DalType.SQLite:
                case DalType.MySql:
                    return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize));
                }
            }
            else
            {
                switch (dalType)
                {
                case DalType.Access:
                case DalType.MsSql:
                case DalType.Sybase:
                    int leftNum   = rowCount % pageSize;
                    int pageCount = leftNum == 0 ? rowCount / pageSize : rowCount / pageSize + 1;                                                                                                                             //页数
                    if (pageIndex == pageCount && dalType != DalType.Sybase)                                                                                                                                                  // 最后一页Sybase 不支持双Top order by
                    {
                        return(string.Format(top2Pager, pageSize + " " + columns, "top " + (leftNum == 0 ? pageSize : leftNum) + " * ", tableName, ReverseOrderBy(where, primaryKey), GetOrderBy(where, false, primaryKey))); //反序
                    }
                    if ((pageCount > 1000 || rowCount > 100000) && pageIndex > pageCount / 2)                                                                                                                                 // 页数过后半段,反转查询
                    {
                        orderBy = GetOrderBy(where, false, primaryKey);
                        where   = ReverseOrderBy(where, primaryKey); //事先反转一次。
                        topN    = rowCount - max;                    //取后面的
                        int rowStartTemp = rowCount - rowEnd;
                        rowEnd   = rowCount - rowStart;
                        rowStart = rowStartTemp;
                    }
                    break;
                }
            }


            switch (dalType)
            {
            case DalType.MsSql:
            case DalType.Oracle:
                if (version.StartsWith("08"))
                {
                    goto temtable;
                    // goto top3;//sql 2000
                }
                int index = tableName.LastIndexOf(')');
                if (index > 0)
                {
                    tableName = tableName.Substring(0, index + 1);
                }
                string v         = dalType == DalType.Oracle ? "" : " v";
                string onlyWhere = "where " + SqlCreate.RemoveOrderBy(where);
                onlyWhere = SqlFormat.RemoveWhereOneEqualsOne(onlyWhere);
                return(string.Format(rowNumberPager, GetOrderBy(where, false, primaryKey), (columns == "*" ? "t.*" : columns), tableName, onlyWhere, v, rowStart, rowEnd));

            case DalType.Sybase:
temtable:
                if (primaryKeyIsIdentity)
                {
                    bool isOk = columns == "*";
                    if (!isOk)
                    {
                        string   kv    = SqlFormat.NotKeyword(primaryKey);
                        string[] items = columns.Split(',');
                        foreach (string item in items)
                        {
                            if (string.Compare(SqlFormat.NotKeyword(item), kv, StringComparison.OrdinalIgnoreCase) == 0)
                            {
                                isOk = true;
                                break;
                            }
                        }
                    }
                    else
                    {
                        columns = "t.*";
                        index   = tableName.LastIndexOf(')');
                        if (index > 0)
                        {
                            tableName = tableName.Substring(0, index + 1);
                        }
                        tableName += " t ";
                    }
                    if (isOk)
                    {
                        return(string.Format(tempTablePagerWithIdentity, DateTime.Now.Millisecond, topN, primaryKey, tableName, where, pageSize, columns, rowStart, rowEnd, orderBy));
                    }
                }
                return(string.Format(tempTablePager, DateTime.Now.Millisecond, pageIndex * pageSize + " " + columns, tableName, where, pageSize, rowStart, rowEnd, orderBy));

            case DalType.Access:
top3:
                if (!string.IsNullOrEmpty(orderBy))     // 反转查询
                {
                    return(string.Format(top4Pager, columns, (rowCount - max > pageSize ? pageSize : rowCount - max), topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey), orderBy));
                }
                return(string.Format(top3Pager, (rowCount - max > pageSize ? pageSize : rowCount - max), columns, topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey)));

            case DalType.SQLite:
            case DalType.MySql:
                if (max > 500000 && primaryKeyIsIdentity && Convert.ToString(objWhere) == "" && !tableName.Contains(" "))    //单表大数量时的优化成主键访问。
                {
                    where = string.Format("{0}>=(select {0} from {1} limit {2}, 1) limit {3}", primaryKey, tableName, max, pageSize);
                    return(string.Format(top1Pager, columns, tableName, where));
                }
                return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + max));
            }
            return((string)Error.Throw("Pager::No Be Support:" + dalType.ToString()));
        }
Ejemplo n.º 4
0
        public static string GetSql(DalType dalType, string version, int pageIndex, int pageSize, object objWhere, string tableName, int rowCount, string columns, string primaryKey, bool primaryKeyIsIdentity)
        {
            if (string.IsNullOrEmpty(columns))
            {
                columns = "*";
            }
            pageIndex = pageIndex == 0 ? 1 : pageIndex;
            string where = SqlFormat.GetIFieldSql(objWhere);
            if (string.IsNullOrEmpty(where))
            {
                where = "1=1";
            }
            if (pageSize == 0)
            {
                return string.Format(top1Pager, columns, tableName, where);
            }
            if (rowCount > 0)//分页查询。
            {
                where = SqlCreate.AddOrderBy(where, primaryKey);
            }
            int topN = pageIndex * pageSize;//Top N 最大数
            int max = (pageIndex - 1) * pageSize;
            int rowStart = (pageIndex - 1) * pageSize + 1;
            int rowEnd = rowStart + pageSize - 1;
            string orderBy = string.Empty;
            if (pageIndex == 1)//第一页
            {
                switch (dalType)
                {
                    case DalType.Access:
                    case DalType.MsSql:
                    case DalType.Sybase:
                        return string.Format(top1Pager, "top " + pageSize + " " + columns, tableName, where);
                    case DalType.Oracle:
                        return string.Format(top1Pager, columns, tableName, "rownum<=" + pageSize + " and " + where);
                    case DalType.SQLite:
                    case DalType.MySql:
                        return string.Format(top1Pager, columns, tableName, where + " limit " + pageSize);
                }
            }
            else
            {

                switch (dalType)
                {
                    case DalType.Access:
                    case DalType.MsSql:
                    case DalType.Sybase:
                        int leftNum = rowCount % pageSize;
                        int pageCount = leftNum == 0 ? rowCount / pageSize : rowCount / pageSize + 1;//页数
                        if (pageIndex == pageCount && dalType != DalType.Sybase) // 最后一页Sybase 不支持双Top order by
                        {
                            return string.Format(top2Pager, pageSize, "top " + (leftNum == 0 ? pageSize : leftNum) + " " + columns, tableName, ReverseOrderBy(where, primaryKey), GetOrderBy(where, false, primaryKey));//反序
                        }
                        if ((pageCount > 1000 || rowCount > 100000) && pageIndex > pageCount / 2) // 页数过后半段,反转查询
                        {
                            orderBy = GetOrderBy(where, false, primaryKey);
                            where = ReverseOrderBy(where, primaryKey);//事先反转一次。
                            topN = rowCount - max;//取后面的
                            int rowStartTemp = rowCount - rowEnd;
                            rowEnd = rowCount - rowStart;
                            rowStart = rowStartTemp;
                        }
                        break;

                }
            }

            switch (dalType)
            {
                case DalType.MsSql:
                case DalType.Oracle:
                    if (version.StartsWith("08"))
                    {
                        goto temtable;
                        // goto top3;//sql 2000
                    }
                    int index = tableName.LastIndexOf(')');
                    if (index > 0)
                    {
                        tableName = tableName.Substring(0, index + 1);
                    }
                    string v = dalType == DalType.Oracle ? "" : " v";
                    string onlyWhere = "where " + SqlCreate.RemoveOrderBy(where);
                    onlyWhere = SqlFormat.RemoveWhereOneEqualsOne(onlyWhere);
                    return string.Format(rowNumberPager, GetOrderBy(where, false, primaryKey), (columns == "*" ? "t.*" : columns), tableName, onlyWhere, v, rowStart, rowEnd);
                case DalType.Sybase:
                temtable:
                    if (primaryKeyIsIdentity)
                    {
                        bool isOk = columns == "*";
                        if (!isOk)
                        {
                            string kv = SqlFormat.NotKeyword(primaryKey);
                            string[] items = columns.Split(',');
                            foreach (string item in items)
                            {
                                if (string.Compare(SqlFormat.NotKeyword(item), kv, StringComparison.OrdinalIgnoreCase) == 0)
                                {
                                    isOk = true;
                                    break;
                                }
                            }
                        }
                        else
                        {
                            columns = "t.*";
                            index = tableName.LastIndexOf(')');
                            if (index > 0)
                            {
                                tableName = tableName.Substring(0, index + 1);
                            }
                            tableName += " t ";
                        }
                        if (isOk)
                        {

                            return string.Format(tempTablePagerWithIdentity, DateTime.Now.Millisecond, topN, primaryKey, tableName, where, pageSize, columns, rowStart, rowEnd, orderBy);
                        }
                    }
                    return string.Format(tempTablePager, DateTime.Now.Millisecond, pageIndex * pageSize + " " + columns, tableName, where, pageSize, rowStart, rowEnd, orderBy);
                case DalType.Access:
                top3:
                    if (!string.IsNullOrEmpty(orderBy)) // 反转查询
                    {
                        return string.Format(top4Pager, (rowCount - max > pageSize ? pageSize : rowCount - max), topN + " " + columns, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey), orderBy);
                    }
                    return string.Format(top3Pager, (rowCount - max > pageSize ? pageSize : rowCount - max), topN + " " + columns, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey));
                case DalType.SQLite:
                case DalType.MySql:
                    return string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + max);
            }
            return (string)Error.Throw("Pager::No Be Support:" + dalType.ToString());
        }