/// <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); }