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