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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
/// <summary>
/// Initializes a new instance of the <see cref="Procedure"/> class.
/// </summary>
/// <param name="prefix">The prefix used on stored procedure names.</param>
public Procedure(dac.TSqlObject tSqlObject, string prefix, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
{
this.Prefix = prefix ?? "";
this.RawName = tSqlObject.Name.Parts.Last();
this.Name = this.RawName.Substring(this.Prefix.Length);
this.Parameters = tSqlObject.GetReferenced(dac.Procedure.Parameters).Select(x => new Parameter(x, primaryKeys, foreignKeys));
TSqlFragment fragment;
TSqlModelUtils.TryGetFragmentForAnalysis(tSqlObject, out fragment);
var selectVisitor = new SelectVisitor();
fragment.Accept(selectVisitor);
var bodyColumnTypes = tSqlObject.GetReferenced(dac.Procedure.BodyDependencies)
.Where(x => x.ObjectType.Name == "Column")
.GroupBy(bd => string.Join(".", bd.Name.Parts))
.Select(grp => grp.First())
.ToDictionary(
key => string.Join(".", key.Name.Parts),
val => new DataType
{
Map = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, val.GetReferenced(dac.Column.DataType).First().Name.Parts.Last()),
Nullable = dac.Column.Nullable.GetValue <bool>(val)
},
StringComparer.InvariantCultureIgnoreCase);
var unions = selectVisitor.Nodes.OfType <BinaryQueryExpression>().Select(bq => GetQueryFromUnion(bq)).Where(x => x != null);
var selects = selectVisitor.Nodes.OfType <QuerySpecification>().Concat(unions);
this.Selects = selects.Select(s => new Select(s, bodyColumnTypes)).ToList();
}
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);
}
/// <summary>
/// Initializes a new instance of the <see cref="Procedure"/> class.
/// </summary>
/// <param name="prefix">The prefix used on stored procedure names.</param>
public Procedure(dac.TSqlObject tSqlObject, string prefix, IEnumerable<dac.TSqlObject> primaryKeys, IDictionary<dac.TSqlObject, IEnumerable<ForeignKeyConstraintDefinition>> foreignKeys)
{
this.Prefix = prefix ?? "";
this.RawName = tSqlObject.Name.Parts.Last();
this.Name = this.RawName.Substring(this.Prefix.Length);
this.Parameters = tSqlObject.GetReferenced(dac.Procedure.Parameters).Select(x => new Parameter(x, primaryKeys, foreignKeys));
TSqlFragment fragment;
TSqlModelUtils.TryGetFragmentForAnalysis(tSqlObject, out fragment);
var selectVisitor = new SelectVisitor();
fragment.Accept(selectVisitor);
var bodyColumnTypes = tSqlObject.GetReferenced(dac.Procedure.BodyDependencies)
.Where(x => x.ObjectType.Name == "Column")
.GroupBy(bd => string.Join(".", bd.Name.Parts))
.Select(grp => grp.First())
.ToDictionary(
key => string.Join(".", key.Name.Parts),
val => new DataType
{
Map = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, val.GetReferenced(dac.Column.DataType).First().Name.Parts.Last()),
Nullable = dac.Column.Nullable.GetValue<bool>(val)
},
StringComparer.InvariantCultureIgnoreCase);
var unions = selectVisitor.Nodes.OfType<BinaryQueryExpression>().Select(bq => GetQueryFromUnion(bq)).Where(x => x != null);
var selects = selectVisitor.Nodes.OfType<QuerySpecification>().Concat(unions);
this.Selects = selects.Select(s => new Select(s, bodyColumnTypes)).ToList();
}
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));
}
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);
}
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);
}
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);
}
