private string DeleteSql()
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("DELETE {0}\r\n", BaseTable().Alias);
sb.AppendFormat(" FROM {0}\r\n", BaseTable().ReferenceName);
foreach (Join j in JoinConditions)
{
sb.AppendFormat("{0}\r\n", j.ToSql());
}
string where = WhereConditions.ToSql();
if (!string.IsNullOrEmpty(where))
{
sb.AppendFormat("WHERE {0}\r\n", where);
}
return(sb.ToString());
}
private string UpdateSql()
{
StringBuilder sb = new StringBuilder();
UpdateTable BaseTable = this.BaseTable() as UpdateTable;
string declare = "";
string set = "";
string outputSelect = "";
SqlBuilder tb = this.TopBuilder;
foreach (ParameterField field in BaseTable.FieldList.OfType <ParameterField>())
{
tb.AddDeclaration(field.ParameterName, field.DeclareParameter());
set += field.SetParameter() + "\r\n";
}
if (BaseTable.Output != null && BaseTable.Output.ParameterTable.FieldList.Count > 0)
{
tb.AddDeclaration(BaseTable.Output.ParameterName, BaseTable.Output.DeclareParameter());
outputSelect += BaseTable.Output.SetParameter() + "\r\n";
}
sb.AppendLine(declare);
sb.AppendLine(set);
sb.AppendFormat("UPDATE {0}\r\n", BaseTable.Name);
sb.AppendFormat(" SET {0}\r\n", BaseTable.ToSql());
if (BaseTable.Output != null && BaseTable.Output.ParameterTable.FieldList.Count > 0)
{
sb.AppendFormat("OUTPUT {0}\r\n", BaseTable.Output.ToSql());
}
sb.AppendFormat(" FROM {0}\r\n", BaseTable.ReferenceName);
foreach (Join j in JoinConditions)
{
sb.AppendFormat("{0}\r\n", j.ToSql());
}
string where = WhereConditions.ToSql();
if (!string.IsNullOrEmpty(where))
{
sb.AppendFormat("WHERE {0}\r\n", where);
}
if (!string.IsNullOrEmpty(outputSelect))
{
sb.AppendLine(outputSelect);
}
return(sb.ToString());
}
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());
}