private string SelectSql()
{
StringBuilder sb = new StringBuilder();
// Clean select list
CleanSelectList(false);
string selectList = BaseTable().ToSql();
foreach (Table t in Tables.Skip(1))
{
string fields = t.ToSql();
selectList += !string.IsNullOrEmpty(fields) ? ", " + fields : "";
}
//
// SELECT
//
sb.AppendFormat("SELECT {1} {2} {0}\r\n", selectList, Distinct ? "DISTINCT" : "", Top.HasValue ? "TOP " + Top.Value.ToString() : "");
//
// INTO
//
if (SelectIntoTable != null)
{
sb.AppendFormat(" INTO {0}\r\n", SelectIntoTable.ReferenceName);
}
//
// FROM
//
sb.AppendFormat(" FROM {0}\r\n", BaseTable().ReferenceName);
//
// JOINS
//
foreach (Join j in JoinConditions)
{
sb.AppendFormat("{0}\r\n", j.ToSql());
}
//
// WHERE
//
string where = WhereConditions.ToSql();
if (where != "()" && !string.IsNullOrEmpty(where))
{
sb.AppendFormat("WHERE {0}\r\n", where);
}
//
// TODO: Group by
//
// ORDER BY
if (OrderByClause.Count > 0)
{
sb.AppendFormat(" ORDER BY {0}", OrderByClause.First().ToSql());
foreach (OrderBy order in OrderByClause.Skip(1))
{
sb.AppendFormat(", {0}", order.ToSql());
}
sb.Append("\r\n");
}
//
// Post SQL stuff
//
if (this.SelectIntoTable != null && this.SelectIntoTable.OutputTable)
{
sb.AppendFormat("SELECT {0} FROM {1}\r\n", SelectIntoTable.ToSql(), SelectIntoTable.ReferenceName);
if (SelectIntoTable.OrderByClause.Count > 0)
{
sb.AppendFormat(" ORDER BY {0}", SelectIntoTable.OrderByClause.First().ToSql());
foreach (OrderBy order in SelectIntoTable.OrderByClause.Skip(1))
{
sb.AppendFormat(", {0}", order.ToSql());
}
sb.Append("\r\n");
}
}
//
// Sub Queries
//
if (_SubQueries.Count > 0)
{
foreach (SqlBuilder sub in _SubQueries.Values)
{
sb.AppendFormat("\r\n-- Sub Query\r\n{0}\r\n", sub.ToSql(Format));
}
}
return(sb.ToString());
}