コード例 #1
0
        /// <summary>
        ///     Loads the PageSize into CurrentPageItems
        /// </summary>
        /// <param name="dbAccess"></param>
        void IDataPager.LoadPage(DbAccessLayer dbAccess)
        {
            T[]        selectWhere = null;
            IDbCommand finalAppendCommand;

            if (string.IsNullOrEmpty(SqlVersion))
            {
#pragma warning disable 618
                SqlVersion = dbAccess.RunPrimetivSelect <string>("SELECT SERVERPROPERTY('productversion')")
                             .FirstOrDefault();
#pragma warning restore 618
            }

            SyncHelper(CurrentPageItems.Clear);
            if (pk == null)
            {
                pk = typeof(T).GetPK(dbAccess.Config);
            }

            if (CommandQuery != null)
            {
                TotalItemCount = new ElementProducer <T>(CommandQuery).CountInt().FirstOrDefault();
                MaxPage        = (long)Math.Ceiling((decimal)TotalItemCount / PageSize);

                RaiseNewPageLoading();
                var elements =
                    new ElementProducer <T>(CommandQuery).QueryD("OFFSET @PagedRows ROWS FETCH NEXT @PageSize ROWS ONLY",
                                                                 new
                {
                    PagedRows = (CurrentPage - 1) * PageSize,
                    PageSize
                }).ToArray();

                foreach (var item in elements)
                {
                    var item1 = item;
                    SyncHelper(() => CurrentPageItems.Add(item1));
                }

                RaiseNewPageLoaded();
            }
            else
            {
                if (AppendedComands.Any())
                {
                    if (BaseQuery == null)
                    {
                        BaseQuery = dbAccess.CreateSelect <T>();
                    }

                    finalAppendCommand = AppendedComands.Aggregate(BaseQuery,
                                                                   (current, comand) =>
                                                                   dbAccess.Database.MergeTextToParameters(current, comand, false, 1, true, false));
                }
                else
                {
                    if (BaseQuery == null)
                    {
                        BaseQuery = dbAccess.CreateSelect <T>();
                    }

                    finalAppendCommand = BaseQuery;
                }

                var selectMaxCommand = dbAccess
                                       .Query()
                                       .QueryText("WITH CTE AS")
                                       .InBracket(query => query.QueryCommand(finalAppendCommand))
                                       .QueryText("SELECT COUNT(1) FROM CTE")
                                       .ContainerObject
                                       .Compile();

                ////var selectMaxCommand = DbAccessLayerHelper.CreateCommand(s, "SELECT COUNT( * ) AS NR FROM " + TargetType.GetTableName());

                //if (finalAppendCommand != null)
                //    selectMaxCommand = DbAccessLayer.ConcatCommands(s, selectMaxCommand, finalAppendCommand);

                var maxItems = dbAccess.RunPrimetivSelect(typeof(long), selectMaxCommand).FirstOrDefault();
                if (maxItems != null)
                {
                    long parsedCount;
                    long.TryParse(maxItems.ToString(), out parsedCount);
                    TotalItemCount = parsedCount;
                    MaxPage        = (long)Math.Ceiling((decimal)parsedCount / PageSize);
                }

                //Check select strategy
                //IF version is or higher then 11.0.2100.60 we can use OFFSET and FETCH
                //esle we need to do it the old way

                RaiseNewPageLoading();
                IDbCommand command;

                if (CheckVersionForFetch())
                {
                    command = dbAccess
                              .Query()
                              .WithCte("CTE", cte => new SelectQuery <T>(cte.QueryCommand(finalAppendCommand)))
                              .QueryText("SELECT * FROM")
                              .QueryText("CTE")
                              .QueryText("ORDER BY")
                              .QueryD(pk)
                              .QueryD("ASC OFFSET @PagedRows ROWS FETCH NEXT @PageSize ROWS ONLY", new
                    {
                        PagedRows = (CurrentPage - 1) * PageSize,
                        PageSize
                    })
                              .ContainerObject
                              .Compile();
                }
                else
                {
                    // ReSharper disable ConvertToLambdaExpression
                    var selectQuery = dbAccess.Query()
                                      .WithCte("BASECTE", baseCte =>
                    {
                        if (BaseQuery != null)
                        {
                            return(baseCte.Select.Table <T>());
                        }

                        return(new SelectQuery <T>(baseCte.QueryCommand(finalAppendCommand)));
                    })
                                      .WithCte("CTE", cte =>
                    {
                        return(new SelectQuery <T>(cte.QueryText("SELECT * FROM (")
                                                   .Select.Table <T>()
                                                   .RowNumberOrder("@pk")
                                                   .WithParamerters(new { Pk = pk })
                                                   .QueryText("AS RowNr")
                                                   .QueryText(", BASECTE.* FROM BASECTE")
                                                   .QueryText(")")
                                                   .As("TBL")
                                                   .Where
                                                   .Column("RowNr")
                                                   .Is
                                                   .Between(page =>
                        {
                            return page.QueryText("@PagedRows * @PageSize + 1")
                            .WithParamerters(new
                            {
                                PagedRows = CurrentPage - 1,
                                PageSize
                            });
                        },
                                                            maxPage =>
                        {
                            return maxPage
                            .InBracket(calc => { return calc.QueryText("@PagedRows + 1"); })
                            .QueryText("* @PageSize");
                        }
                                                            )));
                    }, true)
                                      .QueryText("SELECT * FROM CTE");

                    command = selectQuery.ContainerObject.Compile();
                }

                selectWhere = dbAccess.SelectNative(typeof(T), command, true).Cast <T>().ToArray();

                foreach (var item in selectWhere)
                {
                    var item1 = item;
                    SyncHelper(() => CurrentPageItems.Add(item1));
                }

                RaiseNewPageLoaded();
            }
        }
コード例 #2
0
        public virtual void LoadPage(DbAccessLayer dbAccess)
        {
            RaiseNewPageLoading();
            SyncHelper(CurrentPageItems.Clear);
            if (pk == null)
            {
                pk = typeof(T).GetPK(dbAccess.Config);
            }
            IDbCommand finalAppendCommand;

            if (CommandQuery != null)
            {
                TotalItemCount = new ElementProducer <T>(CommandQuery).CountInt().FirstOrDefault();
                MaxPage        = (long)Math.Ceiling((decimal)TotalItemCount / PageSize);

                RaiseNewPageLoading();
                var elements =
                    new ElementProducer <T>(CommandQuery).QueryD("LIMIT @PagedRows, @PageSize",
                                                                 new
                {
                    PagedRows = (CurrentPage - 1) * PageSize,
                    PageSize
                }).ToArray();

                foreach (var item in elements)
                {
                    var item1 = item;
                    SyncHelper(() => CurrentPageItems.Add(item1));
                }

                RaiseNewPageLoaded();
            }
            else
            {
                if (AppendedComands.Any())
                {
                    if (BaseQuery == null)
                    {
                        BaseQuery = dbAccess.CreateSelect <T>();
                    }

                    finalAppendCommand = AppendedComands.Aggregate(BaseQuery,
                                                                   (current, comand) =>
                                                                   dbAccess.Database.MergeTextToParameters(current, comand, false, 1, true, false));
                }
                else
                {
                    if (BaseQuery == null)
                    {
                        BaseQuery = dbAccess.CreateSelect <T>();
                    }

                    finalAppendCommand = BaseQuery;
                }
                var selectMaxCommand = dbAccess
                                       .Query()
                                       .QueryText("WITH CTE AS")
                                       .InBracket(query => query.QueryCommand(finalAppendCommand))
                                       .QueryText("SELECT COUNT(1) FROM CTE")
                                       .ContainerObject
                                       .Compile();

                ////var selectMaxCommand = DbAccessLayerHelper.CreateCommand(s, "SELECT COUNT( * ) AS NR FROM " + TargetType.GetTableName());

                //if (finalAppendCommand != null)
                //    selectMaxCommand = DbAccessLayer.ConcatCommands(s, selectMaxCommand, finalAppendCommand);

                var maxItems = dbAccess.RunPrimetivSelect(typeof(long), selectMaxCommand).FirstOrDefault();
                if (maxItems != null)
                {
                    long parsedCount;
                    long.TryParse(maxItems.ToString(), out parsedCount);
                    TotalItemCount = parsedCount;
                    MaxPage        = (long)Math.Ceiling((decimal)parsedCount / PageSize);
                }

                //Check select strategy
                //IF version is or higher then 11.0.2100.60 we can use OFFSET and FETCH
                //esle we need to do it the old way

                RaiseNewPageLoading();
                IDbCommand command;
                command = dbAccess
                          .Query()
                          .WithCte("CTE", cte => new SelectQuery <T>(cte.QueryCommand(finalAppendCommand)))
                          .QueryText("SELECT * FROM")
                          .QueryText("CTE")
                          .QueryD("LIMIT @PagedRows, @PageSize;", new
                {
                    PagedRows = (CurrentPage - 1) * PageSize,
                    PageSize
                })
                          .ContainerObject
                          .Compile();

                var selectWhere = dbAccess.SelectNative(typeof(T), command, true).Cast <T>().ToArray();

                foreach (var item in selectWhere)
                {
                    var item1 = item;
                    SyncHelper(() => CurrentPageItems.Add(item1));
                }

                RaiseNewPageLoaded();
            }
        }
コード例 #3
0
        void IDataPager.LoadPage(DbAccessLayer dbAccess)
        {
            T[]        selectWhere = null;
            IDbCommand finalAppendCommand;

            if (AppendedComands.Any())
            {
                if (BaseQuery == null)
                {
                    BaseQuery = dbAccess.CreateSelect <T>();
                }

                finalAppendCommand = AppendedComands.Aggregate(BaseQuery,
                                                               (current, comand) => dbAccess.Database.MergeTextToParameters(current, comand, false, 1, true, false));
            }
            else
            {
                if (BaseQuery == null)
                {
                    BaseQuery = dbAccess.CreateSelect <T>();
                }

                finalAppendCommand = BaseQuery;
            }

            SyncHelper(CurrentPageItems.Clear);

            var pk = TargetType.GetPK(dbAccess.Config);

            var selectMaxCommand = dbAccess
                                   .Query()
                                   .WithCte("CTE", cte => new SelectQuery <T>(cte.QueryCommand(finalAppendCommand)))
                                   .QueryText("SELECT COUNT(*) FROM CTE")
                                   .ContainerObject
                                   .Compile();

            var maxItems = dbAccess.RunPrimetivSelect(typeof(long), selectMaxCommand).FirstOrDefault();

            if (maxItems != null)
            {
                long parsedCount;
                long.TryParse(maxItems.ToString(), out parsedCount);
                TotalItemCount = parsedCount;
                MaxPage        = (long)Math.Ceiling((decimal)parsedCount / PageSize);
            }

            RaiseNewPageLoading();
            IDbCommand command;

            command = dbAccess.Query()
                      .WithCte("CTE", cte => new SelectQuery <T>(cte.QueryCommand(finalAppendCommand)))
                      .QueryText("SELECT * FROM CTE")
                      .QueryD("ASC LIMIT @PageSize OFFSET @PagedRows", new
            {
                PagedRows = (CurrentPage - 1) * PageSize,
                PageSize
            })
                      .ContainerObject
                      .Compile();

            selectWhere = dbAccess.SelectNative(TargetType, command, true).Cast <T>().ToArray();

            foreach (T item in selectWhere)
            {
                var item1 = item;
                SyncHelper(() => CurrentPageItems.Add(item1));
            }

            if (CurrentPage > MaxPage)
            {
                CurrentPage = MaxPage;
            }

            RaiseNewPageLoaded();
        }