// methods to execute generic SQL queries using Dapper
public static DynamicParameters GetParam(this DataTablesContext context)
{
var param = new DynamicParameters();
param.Add(DataTablesContext.SearchToken, context.Request.Search?.Value);
return(param);
}
/// <summary>
/// Runs the context data query and returns an IEnumerable of one or more TSource records.
/// </summary>
public static async Task <IEnumerable <TSource> > DataAsync <TSource>(this DataTablesContext context)
{
try
{
return(await DataTablesContext.QueryAsync <TSource>(context.Query, context.GetParam()));
}
catch (Exception ex)
{
context.Request.Error += string.Format("{0}: {1}\nSqlQuery: {2} using: {3}\n{4}",
ex.GetType().Name,
ex.Message.TrimEnd('.'),
context.Query,
Newtonsoft.Json.JsonConvert.SerializeObject(context.GetParam()),
ex.StackTrace);
return(new List <TSource>());
}
}
/// <summary>
/// Runs the context COUNT query and return the integer result.
/// </summary>
public static async Task <int> CountAsync(this DataTablesContext context)
{
var query = $@"SELECT COUNT(*) FROM ( {context.Query} ) [COUNT_DERIVED];";
try
{
return(await DataTablesContext.QuerySingleAsync <int>(query, context.GetParam()));
}
catch (Exception ex)
{
context.Request.Error += string.Format("{0}: {1}\nSqlQuery: {2} using: {3}\n{4}",
ex.GetType().Name,
ex.Message.TrimEnd('.'),
query,
Newtonsoft.Json.JsonConvert.SerializeObject(context.GetParam()),
ex.StackTrace);
return(0);
}
}
public static DataTablesContext Where(this DataTablesContext context)
{
try
{
// Note: the use of percent wilcard around search value.
context.Query = string.Concat(context.Query, (string.IsNullOrWhiteSpace(context.Request.Search?.Value))
? " WHERE 1=1"
: " WHERE " + string.Join(" OR ", context.Request.Columns
.Where(c => c.Searchable)
.Select(c => $@"[{c.Name}] LIKE '%'+@{DataTablesContext.SearchToken}+'%'")));
}
catch (Exception ex)
{
context.Request.Error += string.Format("{0}: {1}\n{2}",
ex.GetType().Name,
ex.Message.TrimEnd('.'),
ex.StackTrace);
}
return(context);
}
public static DataTablesContext SkipTake(this DataTablesContext context)
{
try
{
// OFFSET-FETCH works beautifully to give us a specific page of rows
// from the full result set, but requires the use of ORDER BY.
var offset = Math.Max(0, context.Request.Start);
var fetch = Math.Max(0, context.Request.Length);
context.Query = string.Concat(context.Query, $@" OFFSET {offset} ROWS" +
(fetch > 0 ? $" FETCH NEXT {fetch} ROWS ONLY" : string.Empty));
}
catch (Exception ex)
{
context.Request.Error += string.Format("{0}: {1}\n{2}",
ex.GetType().Name,
ex.Message.TrimEnd('.'),
ex.StackTrace);
}
return(context);
}
// methods to format fragments of a SQL query in the form:
// SELECT * FROM (BASE-QUERY) WHERE-CLAUSE ORDER-BY OFFSET-FETCH
// where:
// BASE-QUERY is the SQL query to populate a list of data-model-class objects.
// the base query is a complete SQL query without ORDER-BY or GROUP-BY declaritives,
// and may include one or more JOIN to populate lists of data-model-class objects,
// example: SELECT * FROM [table]
// WHERE-CLAUSE is the SQL query fragment used to filter results by search string.
// example: WHERE [column0] LIKE '%@SearchValue%' OR [column1] LIKE '%@SearchValue%' OR ...
// ORDER-BY orders results by one or more columns. example: ORDER BY [column1] DESC
// OFFSET-FETCH limits result set returns for a given page. example: OFFSET 20 ROWS
// FETCH NEXT 10 ROWS ONLY
// fragments are used to build three different queries returning:
// count of all displayable rows in table
// count of filtered (using search item) rows in table
// list of (optionally) filtered and ordered data rows for one page in DataTables UI table.
public static DataTablesContext OrderBy(this DataTablesContext context)
{
try
{
context.Query = string.Concat(context.Query, context.Request.Order.Count == 0
// when no orderby specified, provide generic ORDER BY
// because ORDER BY is required when using OFFSET-FETCH.
? @" ORDER BY 1"
: @" ORDER BY " + string.Join(",", context.Request.Order
.Select(o => $@"[{context.Request.Columns[o.Column].Name}]"
+ (o.Descending ? " DESC" : "")
)
));
}
catch (Exception ex)
{
context.Request.Error += string.Format("{0}: {1}\n{2}",
ex.GetType().Name,
ex.Message.TrimEnd('.'),
ex.StackTrace);
}
return(context);
}