/// <summary>
/// Get list of all artworks
/// </summary>
/// <returns>DataTable of artworks</returns>
public DataTable GetArtWorksList()
{
string sql = SelectStatement.Replace("Artists.LastName, ", " ");
sql += " ORDER BY " + OrderByFields.Replace("YearOfWork,", " ");
return(DataHelper.GetDataTable(sql, null));
}
public TableSchema AsTableSchema()
{
var fields = Fields.Select(f => f.AsFieldSchema()).ToList();
var dateTimeField = fields.FirstOrDefault(f => f.Name == DateTimeField.Name);
var sortFieldsHashSet = new HashSet <string>(OrderByFields.Select(f => f.Name));
var sortFields = fields.Where(f => sortFieldsHashSet.Contains(f.Name)).ToList();
return(new TableSchema(Name, fields, dateTimeField, sortFields));
}
public override string ToSql()
{
var sqlBuilder = new StringBuilder();
var selectFields = new List <string>();
var sqlJoinBuilder = new StringBuilder();
selectFields = SqlServerHelper.BraketFieldNames(Fields.Select(f => f.ColumnName), TableAlias).ToList();
foreach (var join in Joins)
{
var rightTableAlias = join.RightQuery.TableAlias;
foreach (var field in join.RightQuery.Fields)
{
selectFields.Add(string.Format("{0}.[{1}]", rightTableAlias, field.ColumnName));
}
sqlJoinBuilder.Append(join.ToSql());
}
if (TopRows > 0)
{
sqlBuilder.Append(string.Format("{0} {1} {2} {3} ", Verb, EntityMapper.SqlGenerator.Top, TopRows, string.Join(",", selectFields)));
}
else
{
sqlBuilder.Append(string.Format("{0} {1} ", Verb, string.Join(",", selectFields)));
}
if (EntityMapper.SqlGenerator.IncludeDbNameInQuery)
{
sqlBuilder.Append(string.Format("FROM [{0}].[{1}].[{2}] {3} ", DatabaseName, SchemaName, TableName, TableAlias));
}
else
{
sqlBuilder.Append(string.Format("FROM [{0}].[{1}] {2} ", SchemaName, TableName, TableAlias));
}
sqlBuilder.Append(sqlJoinBuilder.ToString());
if (PredicateParts != null && PredicateParts.Any())
{
sqlBuilder.Append(string.Format("WHERE {0} ", PredicateParts.ToSql()));
}
else if (Predicate != null)
{
sqlBuilder.Append(string.Format("WHERE {0} ", Predicate.ToSql()));
}
if (OrderByFields != null)
{
sqlBuilder.Append(string.Format("ORDER BY {0}", string.Join(",", OrderByFields.Select(f => f.ToSql()))));
}
return(sqlBuilder.ToString());
}
private void Clear()
{
SqlBuilder.Clear();
JoinRelations.Clear();
WhereClause.Clear();
Parameters.Clear();
OrderByFields.Clear();
GroupByFields.Clear();
SelectFields.Clear();
}
/// <summary>
/// 添加降序
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="orderBy"></param>
/// <returns></returns>
public JoinQuery OrderByDesc <TEntity>(Expression <Func <TEntity, dynamic> > orderBy)
{
DynamicVisitor visitor = new DynamicVisitor(WithAlias);
visitor.Translate(orderBy);
foreach (DynamicMember c in visitor.DynamicMembers)
{
OrderByFields.AppendFormat("{0} DESC,", c.Field);
}
return(this);
}
public override IQuery OrderDescending()
{
if (string.IsNullOrEmpty(_attributeName))
{
throw new ArgumentException("Descend field not set.");
}
OrderByFields.Add(_attributeName);
OrderByType = OrderByConstants.OrderByDesc;
_attributeName = null;
return(this);
}
public override string ToSql()
{
var sqlBuilder = new StringBuilder();
var selectFields = new List <string>();
var sqlJoinBuilder = new StringBuilder();
selectFields.AddRange(Fields.Select(f => string.Format("{0}.{1}", TableAlias, f.ColumnName)));
foreach (var join in Joins)
{
var rightTableAlias = join.RightQuery.TableAlias;
foreach (var field in join.RightQuery.Fields)
{
selectFields.Add(string.Format("{0}.{1}", rightTableAlias, field.ColumnName));
}
sqlJoinBuilder.Append(join.ToSql());
}
if (TopRows > 0)
{
sqlBuilder.Append(string.Format("{0} {1} {2} {3} ", Verb, EntityMapper.SqlGenerator.Top, TopRows, string.Join(",", selectFields)));
}
else
{
sqlBuilder.Append(string.Format("{0} {1} ", Verb, string.Join(",", selectFields)));
}
sqlBuilder.Append(string.Format("FROM {0} {1} ", TableName, TableAlias));
sqlBuilder.Append(sqlJoinBuilder.ToString());
if (PredicateParts != null && PredicateParts.Any())
{
sqlBuilder.Append(string.Format("WHERE {0} ", PredicateParts.ToSql()));
}
else if (Predicate != null)
{
sqlBuilder.Append(string.Format("WHERE {0} ", Predicate.ToSql()));
}
if (OrderByFields != null)
{
sqlBuilder.Append(string.Format("ORDER BY {0}", string.Join(",", OrderByFields.Select(f => f.ToSql()))));
}
return(sqlBuilder.ToString());
}
protected override Expression VisitMethodCall(MethodCallExpression methodExp)
{
if (methodExp.Method.Name == "OrderByDescending" || methodExp.Method.Name == "ThenByDescending")
{
OrderByFields.Clear();
OrderBy = "DESC";
Expression obj = this.Visit(((LambdaExpression)methodExp.Arguments[1]).Body);
OrderByFieldsStack.Insert(0, OrderByFields.ToString().TrimEnd(','));
OrderByFields.Clear();
if (methodExp.Arguments[0].NodeType == ExpressionType.Call)
{
this.Visit(methodExp.Arguments[0]);
}
return(methodExp);
}
else if (methodExp.Method.Name == "OrderBy" || methodExp.Method.Name == "ThenBy")
{
OrderByFields.Clear();
OrderBy = "ASC";
Expression obj = this.Visit(((LambdaExpression)methodExp.Arguments[1]).Body);
OrderByFieldsStack.Insert(0, OrderByFields.ToString().TrimEnd(','));
OrderByFields.Clear();
if (methodExp.Arguments[0].NodeType == ExpressionType.Call)
{
this.Visit(methodExp.Arguments[0]);
}
return(methodExp);
}
else
{
Expression obj = this.Visit(methodExp.Object);
object result = Expression.Lambda(methodExp).Compile().DynamicInvoke();
var r = Expression.Constant(result, methodExp.Method.ReturnType);
this.Visit(r);
return(r);
}
throw new NotSupportedException(string.Format("lambda表达式不支持使用此'{0}'方法", methodExp.Method.Name));
}
protected override Expression VisitMember(MemberExpression memberExp)
{
if (memberExp.Expression != null && memberExp.Expression.NodeType == ExpressionType.Parameter)
{
var typeName = memberExp.Expression.Type.Name;
string fieldName = GetFieldName(typeName, memberExp.Member.Name);
if (!string.IsNullOrEmpty(Alias))
{
fieldName = Alias + "." + GetFieldName(typeName, memberExp.Member.Name);
}
OrderByFields.Append(string.Format("{0} {1},", fieldName, OrderBy));
return(memberExp);
}
else if (memberExp.NodeType == ExpressionType.MemberAccess)
{
object result = Expression.Lambda(memberExp).Compile().DynamicInvoke();
var r = Expression.Constant(result, result.GetType());
this.Visit(r);
return(r);
}
throw new NotSupportedException(string.Format("成员 '{0}’不支持", memberExp.Member.Name));
}
/// <summary>
/// 多表连查分页
/// </summary>
/// <typeparam name="TModel"></typeparam>
/// <param name="page"></param>
/// <returns></returns>
public PageDataSource <TModel> GetPage <TModel>(PageFilter page)
{
List <JoinRelation> tables = new List <JoinRelation>();
SqlBuilder.AppendFormat(" SELECT {0} FROM {1}", SelectFields.ToString().TrimEnd(','), JoinRelations[0].LeftTable);
if (WithAlias)
{
SqlBuilder.AppendFormat(" AS {0}", JoinRelations[0].LeftTableAlias);
}
foreach (JoinRelation j in JoinRelations)
{
if (j.JoinType == JoinType.Outer)
{
SqlBuilder.Append(" LEFT OUTER JOIN ");
}
else
{
SqlBuilder.Append(" INNER JOIN ");
}
if (WithAlias)
{
if (tables.Count(m => m.LeftTableAlias == j.RightTableAlias || m.RightTableAlias == j.RightTableAlias) == 0)
{
SqlBuilder.AppendFormat("{0} AS {1}", j.RightTable, j.RightTableAlias);
tables.Add(j);
}
else
{
SqlBuilder.AppendFormat("{0} AS {1}", j.LeftTable, j.LeftTableAlias);
tables.Add(j);
}
}
else
{
if (tables.Count(m => m.LeftTable == j.RightTable || m.RightTable == j.RightTable) == 0)
{
SqlBuilder.Append(j.RightTable);
tables.Add(j);
}
else
{
SqlBuilder.Append(j.LeftTable);
tables.Add(j);
}
}
SqlBuilder.AppendFormat(" ON {0}", j.OnSql.TrimEnd("AND".ToCharArray()));
}
if (WhereClause.Length > 0)
{
SqlBuilder.AppendFormat(" WHERE {0}", WhereClause.ToString().Trim().TrimEnd("AND".ToCharArray()));
}
if (GroupByFields.Length > 0)
{
SqlBuilder.AppendFormat(" GROUP BY {0}", GroupByFields.ToString().Trim().TrimEnd(','));
}
if (OrderByFields.Length > 0)
{
page.OrderText = OrderByFields.ToString().Trim().TrimEnd(',');
}
//开始组装sql
//开始组装sql
PageDataSource <TModel> result = DbContext.GetPage <TModel>(SqlBuilder.ToString(), page, Parameters.ToArray());
if (this.IsAutoDisposeDbContext)
{
DbContext.Dispose();
}
return(result);
}
/// <summary>
/// 返回数量
/// </summary>
/// <returns></returns>
public int GetCount()
{
List <JoinRelation> tables = new List <JoinRelation>();
SqlBuilder.AppendFormat(" SELECT COUNT(1) AS CT FROM {0}", JoinRelations[0].LeftTable);
if (WithAlias)
{
SqlBuilder.AppendFormat(" AS {0}", JoinRelations[0].LeftTableAlias);
}
foreach (JoinRelation j in JoinRelations)
{
if (j.JoinType == JoinType.Outer)
{
SqlBuilder.Append(" LEFT OUTER JOIN ");
}
else
{
SqlBuilder.Append(" INNER JOIN ");
}
if (WithAlias)
{
if (tables.Count(m => m.LeftTableAlias == j.RightTableAlias || m.RightTableAlias == j.RightTableAlias) == 0)
{
SqlBuilder.AppendFormat("{0} AS {1}", j.RightTable, j.RightTableAlias);
tables.Add(j);
}
else
{
SqlBuilder.AppendFormat("{0} AS {1}", j.LeftTable, j.LeftTableAlias);
tables.Add(j);
}
}
else
{
if (tables.Count(m => m.LeftTable == j.RightTable || m.RightTable == j.RightTable) == 0)
{
SqlBuilder.Append(j.RightTable);
tables.Add(j);
}
else
{
SqlBuilder.Append(j.LeftTable);
tables.Add(j);
}
}
SqlBuilder.AppendFormat(" ON {0}", j.OnSql.TrimEnd("AND".ToCharArray()));
}
if (WhereClause.Length > 0)
{
SqlBuilder.AppendFormat(" WHERE {0}", WhereClause.ToString().Trim().TrimEnd("AND".ToCharArray()));
}
if (GroupByFields.Length > 0)
{
SqlBuilder.AppendFormat(" GROUP BY {0}", GroupByFields.ToString().Trim().TrimEnd(','));
}
if (OrderByFields.Length > 0)
{
SqlBuilder.AppendFormat(" ORDER BY {0}", OrderByFields.ToString().Trim().TrimEnd(','));
}
//开始组装sql
int result = DbContext.GetSingle <int>(SqlBuilder.ToString(), Parameters.ToArray());
if (this.IsAutoDisposeDbContext)
{
DbContext.Dispose();
}
return(result);
}
