コード例 #1
0
        public override string BuildQuerySQL(SQLServerVisitor <TextContent> visitor, out IEnumerable <Parameter> parameters)
        {
            var innerVisitor = new SQLServerVisitor <Models.TextContent>(visitor.Parameters);

            innerVisitor.Visite(categoriesQuery.InnerQuery.Expression);
            var innerExecutor = (new TextContentTranslator()).Translate(categoriesQuery.InnerQuery);
            var innerQuerySQL = innerExecutor.BuildQuerySQL(innerVisitor, out parameters);

            string selectClause = "*";

            if (visitor.SelectFields != null && visitor.SelectFields.Length > 0)
            {
                selectClause = string.Join(",", visitor.SelectFields);
            }

            string whereClause = visitor.WhereClause;

            var paraName = visitor.AppendParameter(categoriesQuery.CategoryFolder.FullName);

            whereClause = whereClause + " AND FolderName=" + paraName;


            string sql = string.Format(@"
            SELECT {0} FROM [{1}] category
               WHERE  EXISTS(
                        SELECT ContentCategory.CategoryUUID 
                            FROM [{2}] ContentCategory,
                                ({3})content
                            WHERE content.UUID = ContentCategory.UUID AND ContentCategory.CategoryUUID = category.UUID 
                      ) AND {4}", selectClause
                                       , categoriesQuery.CategoryFolder.GetSchema().GetTableName()
                                       , categoriesQuery.Repository.GetCategoryTableName()
                                       , innerQuerySQL
                                       , whereClause);

            return(sql);
        }
コード例 #2
0
        protected virtual SqlCommand BuildCommand(SQLServerVisitor <T> visitor)
        {
            string whereClause = "1=1";

            if (!string.IsNullOrEmpty(visitor.WhereClause))
            {
                whereClause = visitor.WhereClause;
            }
            string orderBy = "Id DESC";

            if (visitor.OrderClauses != null && visitor.OrderClauses.Count() > 0)
            {
                orderBy = ToOrderString(visitor);
            }
            IEnumerable <Parameter> parameters;
            string sql = BuildQuerySQL(visitor, out parameters);

            if (visitor.Take != 0)
            {
                sql = string.Format(@"
                SELECT * 
                    FROM (
                        SELECT *,ROW_NUMBER() OVER(ORDER BY {0}) AS RowIndex
                            FROM ({1})content
                         )paging
                    WHERE (RowIndex > {2}) AND (RowIndex <= {3}) "
                                    , orderBy
                                    , sql
                                    , visitor.Skip
                                    , visitor.Skip + visitor.Take);
            }
            else
            {
                sql = sql + " ORDER BY " + orderBy;
            }
            return(BuildCommand(sql, parameters));
        }
コード例 #3
0
ファイル: SimpleQueryExecutor.cs プロジェクト: Epitomy/CMS
        private IEnumerable <string> GetCategoryClause(Repository repository, IEnumerable <IContentQuery <TextContent> > categoryQueries, List <Parameter> parameters)
        {
            TextContentTranslator translator        = new TextContentTranslator();
            List <string>         categoryQueryList = new List <string>();

            foreach (var categoryQuery in categoryQueries)
            {
                var executor        = translator.Translate(categoryQuery);
                var categoryVisitor = new SQLServerVisitor <TextContent>(parameters);
                categoryVisitor.Visite(categoryQuery.Expression);

                IEnumerable <Parameter> outParameters;
                var categoryQuerySQL = executor.BuildQuerySQL(categoryVisitor, out outParameters);

                categoryQueryList.Add(string.Format(@"EXISTS(
                        SELECT ContentCategory.CategoryUUID 
                            FROM [{0}] ContentCategory,
                                ({1})category
                            WHERE content.UUID = ContentCategory.UUID AND ContentCategory.CategoryUUID = category.UUID 
                      )", repository.GetCategoryTableName()
                                                    , categoryQuerySQL));
            }
            return(categoryQueryList);
        }
コード例 #4
0
        private IEnumerable <T> List(SQLServerVisitor <T> visitor)
        {
            var           command = BuildCommand(visitor);
            SqlConnection connection;
            List <T>      list = new List <T>();

            using (var dataReader = SQLServerHelper.ExecuteReader(ContentQuery.Repository, command, out connection))
            {
                try
                {
                    while (dataReader.Read())
                    {
                        T content = new T();
                        list.Add(dataReader.ToContent(content));
                    }
                }
                finally
                {
                    dataReader.Close();
                    connection.Close();
                }
            }
            return(list);
        }
コード例 #5
0
 public abstract string BuildQuerySQL(SQLServerVisitor <T> visitor, out IEnumerable <Parameter> parameters);
コード例 #6
0
 protected static string ChangeOrderDirectionOrderString(SQLServerVisitor <T> visitor)
 {
     return(string.Join(",", visitor.OrderClauses.Distinct(new OrderClauseComparer()).Select(it => "[" + it.FieldName + "] " + (it.Descending ? "ASC" : "DESC")).ToArray()));
 }