public override string Write(SQSelectQuery q)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT");
for (int i = 0; i < q.Columns.Count; i++)
{
SQAliasableObject col = q.Columns[i];
sb.AppendLine((i == 0 ? "\t" : "\t,") + Write(col.Actual) + (string.IsNullOrEmpty(col.Alias) ? string.Empty : " '" + col.Alias + "'"));
}
// branch off from original string builder to create
// a get row count statement
StringBuilder sb2 = new StringBuilder();
sb2.AppendLine("FROM " + Write(q.From));
if (q.Condition != null)
{
sb2.AppendLine(string.Format("WHERE {0}", Write(q.Condition)));
}
if (q.SortColumns.Count > 0)
{
List <string> cols = new List <string>();
foreach (SQSortColumn col in q.SortColumns)
{
cols.Add(col.Column + (col.Direction == SortOrder.Ascending ? " ASC"
: col.Direction == SortOrder.Descending ? " DESC" : ""));
}
sb2.AppendLine("ORDER BY " + string.Join(", ", cols.ToArray()));
}
sb.AppendLine(sb2.ToString());
if (q.Top > 0)
{
sb.AppendLine("LIMIT " + q.Top);
}
else if (q.RecordCount > 0)
{
sb.AppendLine(string.Format("LIMIT {0} OFFSET {1}", q.RecordCount, q.RecordStart));
}
if (q.IncludeTotalRows)
{
sb.AppendLine(";SELECT COUNT(*)\r\n" + sb2.ToString());
}
return(sb.ToString());
}
public override string Write(SQSelectQuery q)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT");
if (q.Distinct)
{
sb.AppendLine("\tDISTINCT");
}
for (int i = 0; i < q.Columns.Count; i++)
{
SQAliasableObject col = q.Columns[i];
sb.AppendLine((i == 0 ? "\t" : "\t,") + col.Actual.Write(this) + (string.IsNullOrEmpty(col.Alias) ? string.Empty : " '" + col.Alias + "'"));
}
sb.AppendLine("FROM " + Write(q.From));
if (q.Condition != null)
{
sb.AppendLine(string.Format("WHERE {0}", Write(q.Condition)));
}
if (q.SortColumns.Count > 0)
{
List <string> cols = new List <string>();
foreach (SQSortColumn col in q.SortColumns)
{
cols.Add(col.Column + (col.Direction == SortOrder.Ascending ? " ASC"
: col.Direction == SortOrder.Descending ? " DESC" : ""));
}
sb.AppendLine("ORDER BY " + string.Join(", ", cols.ToArray()));
}
if (q.Top > 0)
{
sb.AppendLine("LIMIT 0, " + q.Top);
}
else if (q.RecordCount > 0)
{
sb.AppendLine(string.Format("LIMIT {0}, {1}", q.RecordStart, q.RecordCount));
}
// TODO: total rows
return(sb.ToString());
}
public override string Write(SQSelectQuery q)
{
string sortColumns = null;
if (q.SortColumns.Count > 0)
{
List <string> cols = new List <string>();
foreach (SQSortColumn col in q.SortColumns)
{
cols.Add(col.Column + (col.Direction == SortOrder.Ascending ? " ASC"
: col.Direction == SortOrder.Descending ? " DESC" : ""));
}
sortColumns = "ORDER BY " + string.Join(", ", cols.ToArray());
}
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT");
if (q.Distinct)
{
sb.AppendLine("\tDISTINCT");
}
if (q.Top > 0)
{
sb.AppendLine("\tTOP " + q.Top);
}
for (int i = 0; i < q.Columns.Count; i++)
{
SQAliasableObject col = q.Columns[i];
sb.AppendLine((i == 0 ? "\t" : "\t,") + Write(col.Actual) + (string.IsNullOrEmpty(col.Alias) ? string.Empty : " [" + col.Alias + "]"));
}
if (q.IncludeTotalRows)
{
sb.AppendLine(string.Format("\t,ROW_NUMBER() OVER({0}) [{1}]", sortColumns, "_RowNum"));
}
sb.AppendLine("FROM " + Write(q.From));
if (q.Condition != null)
{
sb.AppendLine(string.Format("WHERE {0}", Write(q.Condition)));
}
if (!q.IncludeTotalRows)
{
sb.AppendLine(sortColumns);
}
else
{
// set up paging as an outer query
sb.Insert(0, @";WITH Paged AS
(
");
string pagingWhere = "";
if (q.IncludeTotalRows)
{
pagingWhere = string.Format("WHERE _RowNum BETWEEN {0} AND {1}", q.RecordStart, q.RecordStart + q.RecordCount - 1);
}
sb.AppendLine(@"
)
SELECT R.*, C._RowCount
FROM (SELECT * FROM Paged " + pagingWhere + @") AS R
FULL JOIN (SELECT MAX(_RowNum) _RowCount FROM Paged) AS C ON
C._RowCount = -1
ORDER BY _RowCount DESC
");
}
return(sb.ToString());
}
