/// <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();
}
}
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();
}
}
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();
}