public IOrderedQueryable <T> AddOrderThen <T>(IOrderedQueryable <T> query, OrderByCondition condition) where T : class { if (condition.SortOrder == SortOrder.Ascending) { return(AddOrderBy(query, SortingType.OrderThenBy, condition.Member, condition.Parameter)); } return(AddOrderBy(query, SortingType.OrderThenByDescending, condition.Member, condition.Parameter)); }
/// <summary> /// 转换成SQL语句 /// </summary> /// <returns>Select SQL语句</returns> public override string ToSQL() { if (PageCount == 0) { return(base.ToSQL()); } else { //判断元数据是否被签出 var stringBuilder = new StringBuilder(); var orderbyCondition = OrderByCondition.ToSQL(); var rowNumber = string.Format("ROW_NUMBER() Over({0}) as row_num", orderbyCondition); stringBuilder.Append(string.Format("SELECT * FROM (SELECT {0},{1} FROM {2}", SelectList.ToSQL(), rowNumber, From.ToSQL())); foreach (SqlElement element in MainFromItem.ChildCollection) { if (element is InnerJoinItem && (element as InnerJoinItem).IsExtendItem) { stringBuilder.Append((element as InnerJoinItem).ToSQLEx()); } } var joinCondition = JoinCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(joinCondition)) { stringBuilder.AppendFormat("WHERE {0} ", joinCondition); } var filterCondition = FilterCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(filterCondition)) { if (string.IsNullOrWhiteSpace(joinCondition)) { stringBuilder.AppendFormat(" WHERE {0} ", filterCondition); } else { stringBuilder.AppendFormat(" AND {0} ", filterCondition); } } if (PageCount > 0) { stringBuilder.AppendFormat(") as tabledata WHERE row_num BETWEEN ({0}-1)*{1}+1 AND ({0}-1)*{1}+{1}", PageIndex, PageCount); } return(stringBuilder.ToString()); } }
/// <summary> /// 转换成SQL语句 /// </summary> /// <returns>Select SQL语句</returns> public override string ToSQL() { if (TopSize <= 0) { return(base.ToSQL()); } else { //判断元数据是否被签出 var stringBuilder = new StringBuilder(); stringBuilder.Append(string.Format("SELECT TOP {0} {1} FROM {2}", TopSize, SelectList.ToSQL(), From.ToSQL())); foreach (SqlElement element in MainFromItem.ChildCollection) { if (element is InnerJoinItem && (element as InnerJoinItem).IsExtendItem) { stringBuilder.Append((element as InnerJoinItem).ToSQLEx()); } } var joinCondition = JoinCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(joinCondition)) { stringBuilder.AppendFormat("WHERE {0} ", joinCondition); } var filterCondition = FilterCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(filterCondition)) { if (string.IsNullOrWhiteSpace(joinCondition)) { stringBuilder.AppendFormat(" WHERE {0} ", filterCondition); } else { stringBuilder.AppendFormat(" AND {0} ", filterCondition); } } var orderbyCondition = OrderByCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(orderbyCondition)) { stringBuilder.AppendFormat(" ORDER BY {0}", orderbyCondition); } return(stringBuilder.ToString()); } }
/// <summary> /// 转换成SQL语句 /// </summary> /// <returns>Select SQL语句</returns> public override string ToSQL() { var stringBuilder = new StringBuilder(); stringBuilder.Append(string.Format("(SELECT {0} FROM {1} ", SelectList.ToSQL(), From.ToSQL())); foreach (SqlElement element in MainFromItem.ChildCollection) { if (element is InnerJoinItem && (element as InnerJoinItem).IsExtendItem) { stringBuilder.Append((element as InnerJoinItem).ToSQLEx()); } } var joinCondition = JoinCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(joinCondition)) { stringBuilder.AppendFormat("WHERE {0} ", joinCondition); } var filterCondition = FilterCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(filterCondition)) { if (string.IsNullOrWhiteSpace(joinCondition)) { stringBuilder.AppendFormat("WHERE {0} ", filterCondition); } else { stringBuilder.AppendFormat("AND {0} ", filterCondition); } } var orderbyCondition = OrderByCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(orderbyCondition)) { stringBuilder.AppendFormat("ORDER BY {0}", orderbyCondition); } stringBuilder.AppendFormat(") {0}", TableAlias); return(stringBuilder.ToString()); }
/// <summary> /// 克隆 /// </summary> /// <returns>查询SQL副本</returns> public override object Clone() { var newObject = base.Clone() as SelectSqlStatement; if (SelectList != null) { newObject.SelectList = SelectList.Clone() as SelectFieldListStatement; } if (From != null) { newObject.From = From.Clone() as From; } if (JoinCondition != null) { newObject.JoinCondition = JoinCondition.Clone() as JoinConditionStatement; } if (OrderByCondition != null) { newObject.OrderByCondition = OrderByCondition.Clone() as ConditionStatement; } if (FilterCondition != null) { newObject.FilterCondition = FilterCondition.Clone() as FilterConditionStatement; } if (MainFromItem != null) { newObject.MainFromItem = MainFromItem.Clone() as FromItem; } newObject.AliasCount = AliasCount; newObject.DictFieldAliasMapping = new Dictionary <string, string>(); foreach (KeyValuePair <string, string> map in DictFieldAliasMapping) { newObject.DictFieldAliasMapping.Add(map.Key, map.Value); } return(newObject); }
/// <summary> /// 转换成SQL语句 /// </summary> /// <returns>Select SQL语句</returns> public override string ToSQL() { var sqlFrom = string.Empty; var stringBuilder = new StringBuilder(); //Retrieve cached query SQL statement //if (base.SqlBuildingInfo.CurrentNode == null) //{ // sqlFrom = SQLStatementManager.Instance. // GetSqlStringByCache(SqlBuildingInfo.CurrentDataObject.ID, RequestTokenID); //} //else //{ // sqlFrom = SQLStatementManager.Instance. // GetSqlStringByCache(CommonObjectID, NodeID, TableName, queryType.ToString(), RequestTokenID); //} if (string.IsNullOrWhiteSpace(sqlFrom)) { sqlFrom = string.Format("SELECT {0} FROM {1}", SelectList.ToSQL(), From.ToSQL()); #region Put into cache // Put into cache //if (base.SqlBuildingInfo.CurrentNode == null) //{ // SQLStatementManager.Instance. // SaveSqlStringToCache(SqlBuildingInfo.CurrentDataObject.ID, RequestTokenID, sqlFrom); //} //else //{ // SQLStatementManager.Instance. // SaveSqlStringToCache(CommonObjectID, NodeID, TableName, queryType.ToString(), RequestTokenID, sqlFrom); //} #endregion } stringBuilder.Append(sqlFrom); foreach (SqlElement element in MainFromItem.ChildCollection) { if (element is InnerJoinItem) { stringBuilder.Append((element as InnerJoinItem).ToSQLEx()); } else if (element is LeftJoinItem) { stringBuilder.Append(element.ToSQL()); } } var joinCondition = JoinCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(joinCondition)) { stringBuilder.AppendFormat("WHERE {0} ", joinCondition); } var filterCondition = FilterCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(filterCondition)) { if (string.IsNullOrWhiteSpace(joinCondition)) { stringBuilder.AppendFormat(" WHERE {0} ", filterCondition); } else { stringBuilder.AppendFormat(" AND {0} ", filterCondition); } } var orderbyCondition = OrderByCondition.ToSQL(); if (!string.IsNullOrWhiteSpace(orderbyCondition)) { stringBuilder.AppendFormat(" ORDER BY {0}", orderbyCondition); } return(stringBuilder.ToString()); }