/// <summary>Executes the command asynchronously</summary>
/// <param name="cancellationToken">The cancellation token in scope for the operation</param>
/// <returns>The result of the command</returns>
public override async Task <SqlQueryResult <TEntity> > ExecuteAsync(CancellationToken cancellationToken)
{
SqlQueryResult result = await _databaseCommander.ExecuteSqlAsync((SqlQueryRequest)CommandRequest, cancellationToken);
List <TEntity> entities = MapToEntities(result.DataTable);
return(new SqlQueryResult <TEntity>(entities));
}
private async Task CleanupDuplicateStatements(CancellationToken cancellationToken)
{
DataTable dataTable = await _commander.ExecuteSqlAsync(GetDuplicateStatementsSql, cancellationToken);
if (dataTable.Rows.Count == 0)
{
return;
}
foreach (DataRow dataRow in dataTable.Rows)
{
string sourceLink = dataRow.GetSafeString("SourceLink");
int count = dataRow.GetSafeInt32("Count");
DataTable statementIds = await _commander.ExecuteSqlAsync($"SELECT TOP {count - 1} [StatementId] FROM [dbo].[Statement] (NOLOCK) WHERE [SourceLink] = '{sourceLink}' AND [IsLatest] = 0 ORDER BY [StatementId] DESC", cancellationToken);
foreach (DataRow statementRow in statementIds.Rows)
{
long statementId = statementRow.GetSafeInt64("StatementId");
await _commander.ExecuteNonQueryAsync($"DELETE FROM [dbo].[Statement] WHERE [StatementId] = {statementId}", cancellationToken);
}
}
}
/// <summary>
/// Executes the command asynchronously
/// </summary>
/// <param name="request">The command request</param>
/// <param name="cancellationToken">The cancellation token in scope for the operation</param>
/// <returns>The result of the command</returns>
public async Task <PaginationResult> ExecuteAsync(PaginationRequest request, CancellationToken cancellationToken)
{
string sqlPagination = GetPaginationSql(request);
string sqlPaginationCount = GetSqlRequestCount(request);
// These 2 database commands can be executed in parallel
Task <DataTable> getDataTask = _databaseCommander.ExecuteSqlAsync(sqlPagination, cancellationToken);
Task <int> getCountTask = _databaseCommander.ExecuteScalarAsync <int>(sqlPaginationCount, cancellationToken);
await Task.WhenAll(getDataTask, getCountTask);
// Get the results of the commands executed asynchronously
DataTable dataTable = await getDataTask;
int totalCount = await getCountTask;
return(new PaginationResult(dataTable, totalCount));
}
public override async Task ProcessAsync(CancellationToken cancellationToken)
{
try
{
string sql = RunSql();
DataTable dataTable = await DatabaseCommander.ExecuteSqlAsync(sql, cancellationToken);
PreProcess(dataTable);
for (int i = 0; i <= dataTable.Rows.Count - 1; i++)
{
ProcessDataRow(dataTable.Rows[i]);
}
PostProcess(dataTable);
}
catch (Exception e)
{
OnException(e);
}
}
private async Task <List <string> > GetNullableColumns(CancellationToken cancellationToken)
{
string sql =
@"SELECT
SCHEMA_NAME(o.schema_id) AS [Schema]
, o.name AS [Object]
, c.column_id AS [ObjectOrder]
, c.name AS [ObjectName]
, TYPE_NAME(c.user_type_id) AS [ObjectDataType]
, -1 AS [ObjectMaxLength]
, CAST(c.is_nullable AS int) AS [ObjectNullable]
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE c.object_id = OBJECT_ID('{0}.{1}')
ORDER BY 1, 2, 3";
DataTable dataTable = await _databaseCommander
.ExecuteSqlAsync(string.Format(sql, _schemaName, _tableName), cancellationToken);
return(dataTable.AsEnumerable()
.Where(dr => dr.GetSafeBoolean("ObjectNullable"))
.Select(dr => dr.GetSafeString("ObjectName"))
.ToList());
}
/// <summary>Executes the command asynchronously</summary>
/// <param name="cancellationToken">The cancellation token in scope for the operation</param>
/// <returns>The result of the command</returns>
public override async Task <SqlQueryResult> ExecuteAsync(CancellationToken cancellationToken)
{
return(await _databaseCommander.ExecuteSqlAsync((SqlQueryRequest)CommandRequest, cancellationToken));
}