private static void BuildGroupByClause(QuerySpec spec, SqlCommand res, StringBuilder sql, TableSpec table)
{
if (spec.groupBy != null)
{
sql.Append(" GROUP BY ").Append(spec.groupBy);
}
}
/// <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(")");
}
}
}
