private static void BuildOrderByClause(QuerySpec spec, StringBuilder sql)
{
if (spec.order != null && spec.order.Count > 0)
{
bool first = true;
foreach (DictionaryEntry entry in spec.order)
{
if (first)
{
sql.Append(" ORDER BY ");
first = false;
}
else
{
sql.Append(" , ");
}
sql.Append(entry.Key.ToString()).Append(" ").Append(entry.Value.ToString());
}
}
}
private static void BuildOffsetFetchClause(QuerySpec spec, StringBuilder sql)
{
if (spec.top >= 0 && spec.skip <= 0)
{
return; // This case is covered with TOP
}
// At this point we know that spec.skip is != null
if (spec.order == null || spec.order.Keys.Count == 0)
{
sql.Append(" ORDER BY 1 "); // Add mandatory order by clause if it is not already there.
}
if (spec.skip >= 0)
{
sql.AppendFormat(" OFFSET {0} ROWS ", spec.skip);
}
if (spec.top >= 0)
{
sql.AppendFormat(" FETCH NEXT {0} ROWS ONLY ", spec.top);
}
}
public static SqlCommand Build(QuerySpec spec, TableSpec table)
{
SqlCommand res = new SqlCommand();
StringBuilder sql = new StringBuilder();
BuildSelectFromClause(spec, table, sql);
// We should build WHERE clause even if we need just a count.
// Client may need count of filtered rows.
BuildWherePredicate(spec, sql, table);
BuildGroupByClause(spec, res, sql, table);
if (!spec.count)
{
// Don't need ORDER BY for count
BuildOrderByClause(spec, sql);
// Don't need pagination for count
BuildOffsetFetchClause(spec, sql);
}
res.CommandText = sql.ToString();
AddParametersToSqlCommand(spec, res);
res.CommandTimeout = 360;
return(res);
}
/// <summary>
/// co11 LIKE @kwd OR col2 LIKE @kwd ...
/// OR
/// (col1 LIKE @srch1 AND col2 LIKE srch2 ...)
/// </summary>
/// <param name="spec"></param>
/// <param name="res"></param>
private static void BuildWherePredicate(QuerySpec spec, StringBuilder sql, TableSpec table)
{
bool isWhereClauseAdded = false;
if (!string.IsNullOrEmpty(spec.predicate))
{
// This is T-SQL predicate that is provided via Url (e.g. using OData $filter clause)
sql.Append(" WHERE (").Append(spec.predicate).Append(")");
isWhereClauseAdded = true;
}
if (!string.IsNullOrEmpty(spec.keyword))
{
if (!isWhereClauseAdded)
{
sql.Append(" WHERE (");
isWhereClauseAdded = true;
}
else
{
sql.Append(" OR (");
}
bool isFirstColumn = true;
foreach (var column in table.columns)
{
if (!isFirstColumn)
{
sql.Append(" OR ");
}
sql.Append("(").Append(column.Name).Append(" like @kwd)");
isFirstColumn = false;
}
sql.Append(" ) "); // Add closing ) for WHERE ( or OR ( that is added in this block
}
// Add filter predicates for individual columns.
if (spec.columnFilter != null && spec.columnFilter.Count > 0)
{
bool isFirstColumn = true, isWhereClauseAddedInColumnFiler = false;
foreach (DictionaryEntry entry in spec.columnFilter)
{
if (!string.IsNullOrEmpty(entry.Value.ToString()))
{
if (isFirstColumn)
{
if (!isWhereClauseAdded)
{
sql.Append(" WHERE (");
}
else
{
sql.Append(" OR (");
}
isWhereClauseAddedInColumnFiler = true;
}
else
{
sql.Append(" AND ");
}
sql.Append("(").Append(entry.Key.ToString()).Append(" LIKE @").Append(entry.Key.ToString()).Append(")");
isFirstColumn = false;
}
}
if (isWhereClauseAddedInColumnFiler)
{
sql.Append(")");
}
}
}
private static void Initialize(HttpRequest req, TableSpec tableSpec, ODataHandler.Metadata metadata, object id, out QuerySpec querySpec, out SqlCommand sql)
{
if (_log == null)
{
_log = StartUp.GetLogger <RequestHandler>();
}
querySpec = MsSql.OData.UriParser.Parse(tableSpec, req);
if (id != null)
{
querySpec.predicate = tableSpec.primaryKey + " = @Id";
var p = new SqlParameter("Id", id);
if (querySpec.parameters == null)
{
querySpec.parameters = new System.Collections.Generic.LinkedList <SqlParameter>();
}
querySpec.parameters.AddFirst(p);
}
sql = QueryBuilder.Build(querySpec, tableSpec);
if (id != null)
{
sql = sql.AsSingleJson();
}
else if (!querySpec.count)
{
if (metadata == ODataHandler.Metadata.NONE)
{
sql = sql.AsJson("value");
}
else
{
sql = sql.AsJson();
}
}
}