/// <summary>
/// Inserts a TOP statement
/// </summary>
/// <typeparam name="TPoco"></typeparam>
/// <param name="query">The query.</param>
/// <param name="top">The top.</param>
/// <returns></returns>
/// <exception cref="NotSupportedException">For the Selected DB type is no Top implementations Available</exception>
public static ElementProducer <TPoco> Top <TPoco>(this ElementProducer <TPoco> query, uint top)
{
if (query.ContainerObject.AccessLayer.Database.TargetDatabase != DbAccessType.MsSql)
{
throw new InvalidOperationException(string.Format("Invalid Target Database {0} by using the MSSQL extentions", query.ContainerObject.AccessLayer.Database.TargetDatabase));
}
QueryBuilderX wrapper = new QueryBuilderX(query.ContainerObject.AccessLayer).QueryD(string.Format("SELECT TOP {0} * FROM (", top)).Append(query).QueryD(")");
return(new ElementProducer <TPoco>(wrapper));
}
/// <summary>
/// Creates a Common Table Expression that selects a Specific type
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="TN">The type of the n.</typeparam>
/// <param name="query">The query.</param>
/// <param name="cteName">Name of the cte.</param>
/// <param name="subCte">if set to <c>true</c> [sub cte].</param>
/// <returns></returns>
public static ElementProducer <TN> AsCte <T, TN>(this ElementProducer <T> query,
string cteName,
bool subCte = false)
{
var cp = new RootQuery(query.ContainerObject.AccessLayer);
var prefix = string.Format("WITH {0} AS (", cteName);
cp = cp.QueryText(prefix);
foreach (var genericQueryPart in query.ContainerObject.Parts)
{
cp = cp.Add(genericQueryPart);
}
return(new ElementProducer <TN>(cp.Add(new CteQueryPart(")")).QueryText(string.Format("SELECT {1} FROM {0}", cteName,
query.ContainerObject.AccessLayer.Config.GetOrCreateClassInfoCache(typeof(TN))
.GetSchemaMapping()
.Aggregate((e, f) => e + ", " + f))), query.CurrentIdentifier));
}
/// <summary>
/// Creates an Equal to statement by using a subquery
/// </summary>
/// <typeparam name="TGPoco">The type of the g poco.</typeparam>
/// <param name="value">The value.</param>
/// <returns></returns>
/// <exception cref="NotSupportedException">Invalid value</exception>
public ConditionalEvalQuery <TPoco> EqualsTo <TGPoco>(ElementProducer <TGPoco> value)
{
switch (State.Operator)
{
case Operator.Is:
if (value == null)
{
return(new ConditionalEvalQuery <TPoco>(this.QueryText("IS NULL"), State));
}
return(QueryOperatorValue("=", value));
case Operator.Not:
if (value == null)
{
return(new ConditionalEvalQuery <TPoco>(this.QueryText("NOT NULL"), State));
}
return(QueryOperatorValue("<>", value));
default:
throw new NotSupportedException("Invalid value");
}
}
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();
}
}
/// <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();
}
}
/// <summary>
/// Creates an TSQL OrderBy statment
/// </summary>
/// <typeparam name="TPoco">The type of the poco.</typeparam>
/// <typeparam name="TE">The type of the e.</typeparam>
/// <param name="query">The query.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="desc">if set to <c>true</c> [desc].</param>
/// <returns></returns>
public static ConditionalQuery <TPoco> OrderByDesc <TPoco, TE>(this ElementProducer <TPoco> query,
Expression <Func <TPoco, TE> > columnName, bool desc = false)
{
return(new ConditionalQuery <TPoco>(query.QueryText("ORDER BY {0} DESC", columnName.GetPropertyInfoFromLamdba()),
new CondtionBuilderState(query.CurrentIdentifier)));
}
/// <summary>
/// Creates an TSQL OrderBy statment
/// </summary>
/// <typeparam name="TPoco">The type of the poco.</typeparam>
/// <param name="query">The query.</param>
/// <param name="over">The over.</param>
/// <returns></returns>
public static ConditionalQuery <TPoco> OrderByDesc <TPoco>(this ElementProducer <TPoco> query, string over)
{
return(new ConditionalQuery <TPoco>(query.QueryText("ORDER BY {0} DESC", over),
new CondtionBuilderState(query.CurrentIdentifier)));
}
/// <summary>
/// Adds a JOIN to the Statement
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="TE">The type of the e.</typeparam>
/// <typeparam name="TAggregation">The type of the aggregation.</typeparam>
/// <param name="query">The query.</param>
/// <param name="mode">The mode.</param>
/// <returns></returns>
public static ElementProducer <TAggregation> Join <T, TE, TAggregation>(this ElementProducer <T> query, JoinMode mode)
{
return(Join <T, TE, TAggregation>(query, mode.JoinType));
}
/// <summary>
/// Adds a LEFT JOIN to the Statement
/// </summary>
/// <typeparam name="TLeft">The type of the left.</typeparam>
/// <typeparam name="TRight">The type of the right.</typeparam>
/// <typeparam name="TAggregation">The type of the aggregation.</typeparam>
/// <param name="query">The query.</param>
/// <param name="mode">The mode.</param>
/// <returns></returns>
public static ElementProducer <TAggregation> Join <TLeft, TRight, TAggregation>(this ElementProducer <TLeft> query,
string mode = null)
{
if (mode == null)
{
mode = "";
}
var accessLayer = query.ContainerObject.AccessLayer;
var sourcePK = typeof(TLeft).GetFK(typeof(TRight), query.ContainerObject.AccessLayer.Config);
if (sourcePK == null)
{
throw new ArgumentNullException("No Fk for this Constalation found");
}
var targetPK = typeof(TRight).GetPK(query.ContainerObject.AccessLayer.Config);
if (targetPK == null)
{
throw new ArgumentNullException("No pk for this Constalation found");
}
var targetTable = accessLayer.GetClassInfo(typeof(TRight)).TableName;
var sourceTable = accessLayer.GetClassInfo(typeof(TLeft)).TableName;
return
(new ElementProducer <TAggregation>(
query.QueryText("{4} JOIN {0} ON {0}.{1} = {3}.{2}", targetTable, targetPK, sourcePK, sourceTable, mode),
query.CurrentIdentifier));
}
/// <summary>
/// Append an RowNumberOrder part
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query">The query.</param>
/// <param name="over">The over.</param>
/// <param name="desc">if set to <c>true</c> [desc].</param>
/// <returns></returns>
public static ElementProducer <T> RowNumberOrder <T>(this ElementProducer <T> query, string over, bool desc = false)
{
return(query.QueryText("ROW_NUMBER() OVER (ORDER BY {0} {1})", over, desc ? "DESC" : "ASC"));
}
/// <summary>
/// Append an AS part
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query">The query.</param>
/// <param name="alias">The alias.</param>
/// <returns></returns>
public static ElementProducer <T> As <T>(this ElementProducer <T> query, string alias)
{
return(query.QueryText("AS " + alias));
}
/// <summary>
/// Creates an TSQL Apply statement
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query">The query.</param>
/// <param name="mode">The mode.</param>
/// <param name="innerText">The inner text.</param>
/// <returns></returns>
public static ElementProducer <T> Apply <T>(this ElementProducer <T> query,
ApplyMode mode,
Func <RootQuery, IQueryBuilder> innerText)
{
return(new ElementProducer <T>(new RootQuery(query.QueryText(mode.ApplyType)).InBracket(innerText), query.CurrentIdentifier));
}
/// <summary>
/// Creates a FOR XML statement that uses the name of the given type to allow the .net XML Serilizer to read the output
/// </summary>
/// <typeparam name="TPoco">The type of the poco.</typeparam>
/// <param name="query">The query.</param>
/// <param name="target">The target.</param>
/// <returns></returns>
public static ElementProducer <string> ForXml <TPoco>(this ElementProducer <TPoco> query, Type target)
{
return(new ElementProducer <string>(query.QueryText("FOR XML PATH('{0}'),ROOT('ArrayOf{0}'), TYPE", target.Name),
query.CurrentIdentifier));
}
/// <summary>
/// Prepaires an Conditional Query
/// </summary>
public ConditionalEvalQuery <TPoco> QueryOperatorValue <TGPoco>(string operators, ElementProducer <TGPoco> sub)
{
var eval = this.QueryText(operators + "(");
foreach (var genericQueryPart in sub.ContainerObject.Parts)
{
eval = eval.Add(genericQueryPart);
}
eval = this.QueryText(")");
return(new ConditionalEvalQuery <TPoco>(eval, State));
}