/// <summary>
/// 转换成SQL语句
/// </summary>
/// <returns>SQL语句</returns>
public override string ToSQL()
{
StringBuilder result = new StringBuilder();
result.Append("SELECT 1 FROM ").Append(From.ToSQL());
string joinStr = JoinCondition.ToSQL();
string conditionStr = Condition.ToSQL();
if (!string.IsNullOrWhiteSpace(joinStr))
{
result.Append(" WHERE ").Append(joinStr);
}
if (!string.IsNullOrWhiteSpace(conditionStr))
{
if (!string.IsNullOrWhiteSpace(joinStr))
{
result.Append(" AND ").Append(conditionStr);
}
else
{
result.Append(" WHERE ").Append(conditionStr);
}
}
return(result.ToString());
}
/// <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>
/// 转换成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());
}