private async Task <int> GetRecordsTotalAsync(DbConnection connection, DataTablesInputModel inputModel)
{
var sb = new StringBuilder();
using (var command = connection.CreateCommand())
{
AddCountSelectClauseToCommand(command, sb, inputModel);
command.CommandText = sb.ToString();
object result = await command.ExecuteScalarAsync();
return(Convert.ToInt32(result));
}
}
public async Task <DataTablesViewModel> GetResultsAsync(DataTablesInputModel inputModel)
{
using (var connection = CreateConnection())
{
await connection.OpenAsync();
int draw = inputModel.Draw;
int recordsTotal = await GetRecordsTotalAsync(connection, inputModel);
int recordsFiltered = await GetRecordsFilteredAsync(connection, inputModel);
IReadOnlyCollection <object[]> data = await GetDataAsync(connection, inputModel);
return(new DataTablesViewModel(draw, recordsTotal, recordsFiltered, data));
}
}
private void AddLimitClauseToCommand(DbCommand command, StringBuilder sb, DataTablesInputModel inputModel)
{
var limitParameter = command.CreateParameter();
limitParameter.ParameterName = "@limit";
limitParameter.Value = inputModel.Length;
command.Parameters.Add(limitParameter);
var offsetParameter = command.CreateParameter();
offsetParameter.ParameterName = "@offset";
offsetParameter.Value = inputModel.Start;
command.Parameters.Add(offsetParameter);
sb.Append($" LIMIT {limitParameter.ParameterName} OFFSET {offsetParameter.ParameterName}");
}
private void AddOrderByClauseToCommand(DbCommand command, StringBuilder sb, DataTablesInputModel inputModel)
{
var sortableColumns = inputModel.ColumnDefinitions
.Where(col => col.SortingPriority.HasValue)
.OrderBy(col => col.SortingPriority.Value)
.Select(col => $"{col.Name} {(col.SortingDirection == DataTablesSortingDirection.Descending ? "DESC" : "ASC")}")
.ToArray();
bool hasSortableColumns = sortableColumns.Any();
if (!hasSortableColumns)
{
return;
}
string joinedColumns = string.Join(", ", sortableColumns);
sb.Append($" ORDER BY {joinedColumns}");
}
private void AddWhereClauseToCommand(DbCommand command, StringBuilder sb, DataTablesInputModel inputModel)
{
var columnsWithSearchValue = inputModel.ColumnDefinitions.Where(col => !string.IsNullOrWhiteSpace(col.Search)).ToArray();
var globallySearchableColumns = inputModel.ColumnDefinitions.Where(col => col.GloballySearchable).ToArray();
bool hasColumnsWithSearchValue = columnsWithSearchValue.Length > 0;
bool hasGlobalSearch = !string.IsNullOrWhiteSpace(inputModel.Search) && globallySearchableColumns.Length > 0;
if (!hasColumnsWithSearchValue && !hasGlobalSearch)
{
return;
}
sb.Append(" WHERE 1=1");
int whereParameterIndex = 0;
if (hasColumnsWithSearchValue)
{
foreach (var columnWithSearchValue in columnsWithSearchValue)
{
var parameter = command.CreateParameter();
parameter.ParameterName = $"@w{whereParameterIndex++}";
parameter.Value = $"%{columnWithSearchValue.Search}%";
sb.Append($" AND {columnWithSearchValue.Name} LIKE {parameter.ParameterName}");
command.Parameters.Add(parameter);
}
}
if (hasGlobalSearch)
{
sb.Append(" AND (0=1");
foreach (var globallySearchableColumn in globallySearchableColumns)
{
var parameter = command.CreateParameter();
parameter.ParameterName = $"@w{whereParameterIndex++}";
parameter.Value = $"%{inputModel.Search}%";
sb.Append($" OR {globallySearchableColumn.Name} LIKE {parameter.ParameterName}");
command.Parameters.Add(parameter);
}
sb.Append(")");
}
}
private void AddCountSelectClauseToCommand(DbCommand command, StringBuilder sb, DataTablesInputModel inputModel)
{
sb.Append($"SELECT COUNT(*) FROM {inputModel.TableName}");
}
private void AddSelectClauseToCommand(DbCommand command, StringBuilder sb, DataTablesInputModel inputModel)
{
var fieldNames = string.Join(", ", inputModel.ColumnDefinitions.Select(col => col.Name));
sb.Append($"SELECT {fieldNames} FROM {inputModel.TableName}");
}
private async Task <IReadOnlyCollection <object[]> > GetDataAsync(DbConnection connection, DataTablesInputModel inputModel)
{
var dataRows = new List <object[]>();
var sb = new StringBuilder();
using (var command = connection.CreateCommand())
{
AddSelectClauseToCommand(command, sb, inputModel);
AddWhereClauseToCommand(command, sb, inputModel);
AddOrderByClauseToCommand(command, sb, inputModel);
AddLimitClauseToCommand(command, sb, inputModel);
command.CommandText = sb.ToString();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var dataRow = new object[reader.FieldCount];
dataRows.Add(dataRow);
reader.GetValues(dataRow);
}
}
}
return(dataRows.AsReadOnly());
}
public async Task <IActionResult> IndexData(DataTablesInputModel inputModel, [FromServices] DataTablesService dataTablesService)
{
var result = await dataTablesService.GetResultsAsync(inputModel);
return(Json(result));
}
