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