/// <summary>
/// Makes the query gather a count of total records as the FIRST result set. REQUIRES Sql Server 2012.
/// </summary>
/// <typeparam name="TFilter">The type of the filter.</typeparam>
/// <typeparam name="TResult">The type of the result.</typeparam>
/// <param name="processor">The processor.</param>
/// <param name="format">The format.</param>
/// <param name="rowCountParameterName">Column name to return the total row count.</param>
/// <returns>ICommandExpression<TFilter>.</returns>
/// <exception cref="System.ArgumentException">Only CommandType.Text CommandBuilder Expressions can be dynamically paged.
/// or
/// CommandText must contain an Order By clause to be paged.</exception>
public static CommandTransform GatherTotalRowCountSeperate <TFilter>(
ICommandProcessorWithResults <TFilter> processor,
string totalRowCountColumnName = "TotalRowCount")
{
var commandInfo = processor.CommandResultInfo.GetCommandInfo();
if (commandInfo.DbCommandType != CommandType.Text)
{
throw new ArgumentException("Only CommandType.Text CommandBuilder Expressions can be dynamically paged.");
}
const string format = "SELECT {1} = COUNT(*)" + "\r\n" +
"FROM (" + "\r\n" +
"{0} " + "\r\n" +
") [count]" + "\r\n\r\n" +
"{0}";
return(new CommandTransform(
"TotalRowCountSeperate",
info => new ExecutableCommandInfo
{
CommandText = string.Format(format, info.CommandText, totalRowCountColumnName),
DbCommandType = info.DbCommandType,
Parameters = info.Parameters
}));
}
/// <summary>
/// Makes the query gather a count of total records. REQUIRES Sql Server 2012.
/// </summary>
/// <typeparam name="TFilter">The type of the filter.</typeparam>
/// <typeparam name="TResult">The type of the result.</typeparam>
/// <param name="processor">The processor.</param>
/// <param name="format">The format.</param>
/// <param name="rowCountParameterName">Column name to return the total row count.</param>
/// <returns>ICommandExpression<TFilter>.</returns>
/// <exception cref="System.ArgumentException">Only CommandType.Text CommandBuilder Expressions can be dynamically paged.
/// or
/// CommandText must contain an Order By clause to be paged.</exception>
public static CommandTransform GatherTotalRowCount <TFilter>(
ICommandProcessorWithResults <TFilter> processor,
string totalRowCountColumnName = "TotalRowCount")
{
var commandInfo = processor.CommandResultInfo.GetCommandInfo();
if (commandInfo.DbCommandType != CommandType.Text)
{
throw new ArgumentException("Only CommandType.Text CommandBuilder Expressions can be dynamically paged.");
}
const string CteCountWrapper =
";WITH SUSANOO_CTE AS(\r\n" +
"\t{0})\r\n" +
"FROM SUSANOO_CTE query\r\n" +
"CROSS APPLY (SELECT {1} = COUNT(*) FROM SUSANOO_CTE) [Count]";
return(new CommandTransform(
"TotalRowCount",
info => new ExecutableCommandInfo
{
CommandText = string.Format(CteCountWrapper, info.CommandText, totalRowCountColumnName),
DbCommandType = info.DbCommandType,
Parameters = info.Parameters
}));
}
/// <summary>
/// Builds the where filter.
/// </summary>
/// <typeparam name="TFilter">The type of the t filter.</typeparam>
/// <typeparam name="TResult">The type of the t result.</typeparam>
/// <param name="processor">The processor.</param>
/// <param name="options">The options.</param>
/// <returns>ICommandExpression<TFilter>.</returns>
public static CommandTransform WhereFilter <TFilter, TResult>(ICommandProcessorWithResults <TFilter> processor, Type resultType, ExpandoObject options)
{
var whereFilterModifier = new CommandTransform("WhereFilter",
new WhereFilterTransformFactory <TFilter>(processor, resultType, options)
.BuildWhereFilterTransform);
return(whereFilterModifier);
}
/// <summary>
/// Builds the where filter.
/// </summary>
/// <typeparam name="TFilter">The type of the t filter.</typeparam>
/// <typeparam name="TResult">The type of the t result.</typeparam>
/// <param name="processor">The processor.</param>
/// <param name="optionsObject">The options object.</param>
/// <returns>ICommandExpression<TFilter>.</returns>
public static CommandTransform WhereFilter <TFilter>(ICommandProcessorWithResults <TFilter> processor, Type resultType, object optionsObject = null)
{
var whereFilterModifier = new CommandTransform("WhereFilter",
new WhereFilterTransformFactory <TFilter>(processor, resultType, optionsObject != null ? optionsObject.ToExpando() : new ExpandoObject())
.BuildWhereFilterTransform);
return(whereFilterModifier);
}
/// <summary>
/// Makes the query a paged query using OFFSET/FETCH. REQUIRES Sql Server 2012.
/// </summary>
/// <typeparam name="TFilter">The type of the filter.</typeparam>
/// <typeparam name="TResult">The type of the result.</typeparam>
/// <param name="processor">The processor.</param>
/// <param name="rowCountParameterName">Name of the row count parameter.</param>
/// <param name="pageNumberParameterName">Name of the page number parameter.</param>
/// <param name="computePageNumber">if set to <c>true</c> computes the page number.</param>
/// <returns>ICommandExpression<TFilter>.</returns>
/// <exception cref="System.ArgumentException">Only CommandType.Text CommandBuilder Expressions can be dynamically paged.
/// or
/// CommandText must contain an Order By clause to be paged.</exception>
public static CommandTransform OffsetFetch <TFilter>(
ICommandProcessorWithResults <TFilter> processor,
string rowCountParameterName = "RowCount", string pageNumberParameterName = "PageNumber", bool computePageNumber = true)
{
var format = computePageNumber
? "\r\nOFFSET (@{0} - 1) * @{1} ROWS" +
"\r\nFETCH NEXT @{1} ROWS ONLY"
: "\r\nOFFSET @{0} ROWS" +
"\r\nFETCH NEXT @{1} ROWS ONLY";
return(OffsetFetch(processor, format, rowCountParameterName, pageNumberParameterName));
}
/// <summary>
/// Adds a total row count to the query wrapper.
/// </summary>
/// <typeparam name="TFilter">The type of the filter.</typeparam>
/// <param name="processor">The processor.</param>
/// <param name="totalRowsColumnName">Total name of the total row count column.</param>
/// <param name="format">The format.</param>
/// <returns>ICommandExpression<TFilter>.</returns>
/// <exception cref="System.ArgumentException">Only CommandType.Text CommandBuilder Expressions can be dynamically built.</exception>
/// <exception cref="ArgumentException">Only CommandType.Text CommandBuilder Expressions can be dynamically built.</exception>
public static CommandTransform QueryWrapperWithTotalRowCount <TFilter>(
ICommandProcessorWithResults <TFilter> processor,
string totalRowsColumnName = "TotalRows", string format = "{0} = COUNT(*) OVER ()")
{
var commandInfo = processor.CommandResultInfo.GetCommandInfo();
if (commandInfo.DbCommandType != CommandType.Text)
{
throw new ArgumentException("Only CommandType.Text CommandBuilder Expressions can be dynamically built.");
}
var totalsFormat = format;
var totalText = string.Format(totalsFormat, totalRowsColumnName);
var wrapper = Transforms.QueryWrapper(totalText);
return(wrapper);
}
/// <summary>
/// Makes the query a paged query using OFFSET/FETCH. REQUIRES Sql Server 2012.
/// </summary>
/// <typeparam name="TFilter">The type of the filter.</typeparam>
/// <typeparam name="TResult">The type of the result.</typeparam>
/// <param name="processor">The processor.</param>
/// <param name="format">The format.</param>
/// <param name="rowCountParameterName">Name of the row count parameter.</param>
/// <param name="pageNumberParameterName">Name of the page number parameter.</param>
/// <returns>ICommandExpression<TFilter>.</returns>
/// <exception cref="System.ArgumentException">Only CommandType.Text CommandBuilder Expressions can be dynamically paged.
/// or
/// CommandText must contain an Order By clause to be paged.</exception>
public static CommandTransform OffsetFetch <TFilter>(
ICommandProcessorWithResults <TFilter> processor, string format,
string rowCountParameterName = "RowCount", string pageNumberParameterName = "PageNumber")
{
var commandInfo = processor.CommandResultInfo.GetCommandInfo();
if (commandInfo.DbCommandType != CommandType.Text)
{
throw new ArgumentException("Only CommandType.Text CommandBuilder Expressions can be dynamically paged.");
}
return(new CommandTransform(
"OFFSET/FETCH",
info => new ExecutableCommandInfo
{
CommandText = string.Concat(info.CommandText,
string.Format(format, pageNumberParameterName, rowCountParameterName)),
DbCommandType = info.DbCommandType,
Parameters = info.Parameters
}));
}
public WhereFilterTransformFactory(ICommandProcessorWithResults <TFilter> processor, Type resultType, IDictionary <string, object> whereFilterOptions)
{
_processor = processor;
_resultType = resultType;
WhereFilterOptions = whereFilterOptions;
}
