Exemple #1
0
        public virtual ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
        {
            // 不分页
            if (pageIndex == 1)
            {
                ToList(pageSize, isDistinct); return(this);
            }

            var strSelectSql   = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql    = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql  = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
            var strDistinctSql = isDistinct ? "Distinct " : string.Empty;

            Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && ExpBuilder.Map.FieldMap.PrimaryState.Key == null, "不指定排序字段时,需要设置主键ID");

            strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? string.Format("{0} ASC", ExpBuilder.Map.FieldMap.PrimaryState.Value.FieldAtt.Name) : strOrderBySql);
            var strOrderBySqlReverse = strOrderBySql.Replace(" DESC", " [倒序]").Replace("ASC", "DESC").Replace("[倒序]", "ASC");

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }

            Sql.AppendFormat("SELECT {0}TOP {2} {1} FROM (SELECT TOP {3} * FROM {4} {5} {6}) a  {7};", strDistinctSql, strSelectSql, pageSize, pageSize * pageIndex, Name, strWhereSql, strOrderBySql, strOrderBySqlReverse);
            return(this);
        }
        public override ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false)
        {
            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            var strTopSql      = top > 0 ? $"TOP {top}" : string.Empty;
            var strDistinctSql = isDistinct ? "Distinct " : string.Empty;

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }
            if (!string.IsNullOrWhiteSpace(strOrderBySql))
            {
                strOrderBySql = "ORDER BY " + strOrderBySql;
            }

            if (!isRand)
            {
                Sql.Append($"SELECT {strDistinctSql}{strTopSql} {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}");
            }
            else if (string.IsNullOrWhiteSpace(strOrderBySql))
            {
                Sql.Append(string.Format("SELECT {0}{1} {2}{5} FROM {3} {4} BY Rnd(-(TestID+\" & Rnd() & \"))", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, isDistinct ? ",Rnd(-(TestID+\" & Rnd() & \")) as newid" : ""));
            }
            else
            {
                Sql.Append(string.Format("SELECT {2} FROM (SELECT {0}{1} *{6} FROM {3} {4} BY Rnd(-(TestID+\" & Rnd() & \"))) a {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, isDistinct ? ",Rnd(-(TestID+\" & Rnd() & \")) as newid" : ""));
            }
            return(this);
        }
Exemple #3
0
        public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
        {
            // 不分页
            if (pageIndex == 1)
            {
                ToList(pageSize, isDistinct); return(this);
            }

            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            var strDistinctSql = isDistinct ? "Distinct " : string.Empty;

            if (string.IsNullOrWhiteSpace(strSelectSql))
            {
                strSelectSql = "*";
            }
            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }
            if (!string.IsNullOrWhiteSpace(strOrderBySql))
            {
                strOrderBySql = "ORDER BY " + strOrderBySql;
            }

            Sql.AppendFormat("SELECT {0}{1} FROM {2} {3} {4} LIMIT {5},{6}", strDistinctSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, pageSize * (pageIndex - 1), pageSize);
            return(this);
        }
Exemple #4
0
        public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
        {
            // 不分页
            if (pageIndex == 1)
            {
                ToList(pageSize, isDistinct);
                return(this);
            }

            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            var strDistinctSql = isDistinct ? "Distinct" : string.Empty;

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }

            Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && (ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Count == 0), "不指定排序字段时,需要设置主键ID");

            strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? $"{ConvertHelper.ToString(ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Select(o => o.Value.Name), ",")} ASC" : strOrderBySql);

            Sql.Append(string.Format("SELECT {1} FROM (SELECT {0} {1},ROW_NUMBER() OVER({2}) as Row FROM {3} {4}) a WHERE Row BETWEEN {5} AND {6};", strDistinctSql, strSelectSql, strOrderBySql, Name, strWhereSql, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize));
            return(this);
        }
Exemple #5
0
        public virtual ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false)
        {
            var strSelectSql   = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql    = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql  = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
            var strTopSql      = top > 0 ? string.Format("TOP {0} ", top) : string.Empty;
            var strDistinctSql = isDistinct ? "Distinct " : string.Empty;

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }
            if (!string.IsNullOrWhiteSpace(strOrderBySql))
            {
                strOrderBySql = "ORDER BY " + strOrderBySql;
            }

            if (!isRand)
            {
                Sql.AppendFormat("SELECT {0}{1}{2} FROM {3} {4} {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql);
            }
            else if (string.IsNullOrWhiteSpace(strOrderBySql))
            {
                Sql.AppendFormat("SELECT {0}{1}{2}{5} FROM {3} {4} ORDER BY NEWID()", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, isDistinct ? ",NEWID() as newid" : "");
            }
            else
            {
                Sql.AppendFormat("SELECT {2} FROM (SELECT {0} {1} *{6} FROM {3} {4} ORDER BY NEWID()) a {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, isDistinct ? ",NEWID() as newid" : "");
            }
            return(this);
        }
Exemple #6
0
        public override ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false)
        {
            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            var strDistinctSql = isDistinct ? "Distinct " : string.Empty;
            var randField      = ",dbms_random.value as newid";

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }
            if (!string.IsNullOrWhiteSpace(strOrderBySql))
            {
                strOrderBySql = "ORDER BY " + strOrderBySql;
            }

            if (!isRand)
            {
                Sql.Append(BuilderTop(top, $"SELECT {strDistinctSql}{strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}"));
            }
            else if (!isDistinct && string.IsNullOrWhiteSpace(strOrderBySql))
            {
                Sql.Append(BuilderTop(top, $"SELECT {strSelectSql}{randField} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} ORDER BY dbms_random.value"));
            }
            else
            {
                Sql.Append(BuilderTop(top, $"SELECT * {randField} FROM (SELECT {strDistinctSql} {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}) s ORDER BY dbms_random.value"));
            }
            return(this);
        }
Exemple #7
0
        public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
        {
            // 不分页
            if (pageIndex == 1)
            {
                ToList(pageSize, isDistinct);
                return(this);
            }

            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            var strDistinctSql = isDistinct ? "Distinct " : string.Empty;

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }
            if (!string.IsNullOrWhiteSpace(strOrderBySql))
            {
                strOrderBySql = "ORDER BY " + strOrderBySql;
            }

            Sql.Append($"SELECT {strDistinctSql}{strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql} LIMIT {pageSize} OFFSET {pageSize * (pageIndex - 1)}");
            return(this);
        }
        public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
        {
            // 不分页
            if (pageIndex == 1)
            {
                ToList(pageSize, isDistinct);
                return(this);
            }

            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            var strDistinctSql = isDistinct ? "Distinct" : string.Empty;

            Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Count == 0, "不指定排序字段时,需要设置主键ID");

            strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? $"{ConvertHelper.ToString(ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Select(o => o.Value.Name), ",")} ASC" : strOrderBySql);
            var strOrderBySqlReverse = strOrderBySql.Replace(" DESC", " [倒序]").Replace("ASC", "DESC").Replace("[倒序]", "ASC");

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }

            Sql.Append(string.Format("SELECT {0} TOP {2} {1} FROM (SELECT TOP {3} {1} FROM {4} {5} {6}) a  {7};", strDistinctSql, strSelectSql, pageSize, pageSize * pageIndex, Name, strWhereSql, strOrderBySql, strOrderBySqlReverse));
            return(this);
        }
Exemple #9
0
        public void Exploration()
        {
            Expression <Func <DummyA, object> > expression = (a) => new { Truls = a.B1, Morten = a.B1.Tittel };

            var visitor = new SelectVisitor();

            visitor.Visit(expression.Body);

            Assert.AreEqual(1, visitor.RelatedObjects.Count());
            Assert.AreEqual("B1", visitor.RelatedObjects.ElementAt(0));
        }
Exemple #10
0
        public virtual ISqlParam Min()
        {
            var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql  = WhereVisitor.Visit(ExpBuilder.ExpWhere);

            if (string.IsNullOrWhiteSpace(strSelectSql))
            {
                strSelectSql = "0";
            }
            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }

            Sql.AppendFormat("SELECT MIN({0}) FROM {1} {2}", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql);
            return(this);
        }
        public override ISqlParam GetValue()
        {
            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }
            if (!string.IsNullOrWhiteSpace(strOrderBySql))
            {
                strOrderBySql = "ORDER BY " + strOrderBySql;
            }

            Sql.Append($"SELECT TOP 1 {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}");
            return(this);
        }
Exemple #12
0
        public virtual ISqlParam ToEntity()
        {
            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }
            if (!string.IsNullOrWhiteSpace(strOrderBySql))
            {
                strOrderBySql = "ORDER BY " + strOrderBySql;
            }

            Sql.AppendFormat("SELECT TOP 1 {0} FROM {1} {2} {3}", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql);
            return(this);
        }
Exemple #13
0
        public override ISqlParam GetValue()
        {
            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }
            if (!string.IsNullOrWhiteSpace(strOrderBySql))
            {
                strOrderBySql = "ORDER BY " + strOrderBySql;
            }

            Sql.AppendFormat("SELECT {0} FROM {1} {2} {3} rownum <=1", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql);
            return(this);
        }
        public override ISqlParam ToEntity()
        {
            var strSelectSql  = SelectVisitor.Visit(ExpBuilder.ExpSelect);
            var strWhereSql   = WhereVisitor.Visit(ExpBuilder.ExpWhere);
            var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);

            if (!string.IsNullOrWhiteSpace(strWhereSql))
            {
                strWhereSql = "WHERE " + strWhereSql;
            }
            if (!string.IsNullOrWhiteSpace(strOrderBySql))
            {
                strOrderBySql = "ORDER BY " + strOrderBySql;
            }

            var strTopSql = (string.IsNullOrWhiteSpace(strWhereSql) ? "WHERE" : "AND") + " rownum <=1";

            Sql.Append($"SELECT {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strTopSql} {strOrderBySql}");
            return(this);
        }