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