public static async Task Save(QueryExecutionContext query, QueryResponseContext response)
{
string?statement = NormalizeSql(query.GetSqlStatement());
if (statement is null)
{
return;
}
string sql;
string code = QueryHistory.GetCode(ComputeHash(statement), query.Server.Type);
var history = await LoadByCode(code);
if (history is null)
{
history = QueryHistory.Create(code, statement, query, response);
sql = "INSERT INTO query (code, status, type, sql, star, execution_count, last_executed_on, last_environment, last_database) VALUES " +
"( " +
$"'{history.Code}', " +
$"{(int)history.Status}, " +
$"{(int)history.Type}, " +
$"'{history.Sql.Replace("'", "''")}', " +
$"{(history.Star ? 1 : 0)}, " +
$"{history.ExecutionCount}, " +
$"'{history.LastExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"'{history.Stats.Last().Environment}', " +
$"'{history.Stats.Last().Database}' " +
");";
}
else
{
history.UpdateStatistics(query, response);
sql = $"UPDATE query SET " +
$"status = {(int)history.Status}, " +
$"execution_count = {history.ExecutionCount}, " +
$"last_executed_on = '{history.LastExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"last_environment = '{history.Stats.Last().Environment}', " +
$"last_database = '{history.Stats.Last().Database}' " +
$"WHERE code = '{history.Code}';";
}
var stat = history.Stats.Last();
sql += "INSERT INTO stat (code, status, executed_on, environment, database, server_connection, elapsed, row_count, records_affected) VALUES " +
"( " +
$"'{history.Code}', " +
$"{(int)stat.Status}, " +
$"'{stat.ExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"'{stat.Environment}', " +
$"'{stat.Database}', " +
$"'{stat.ServerConnection}', " +
$"{stat.Elapsed}, " +
$"{stat.RowCount}, " +
$"{stat.RecordsAffected}" +
");";
await ServerConnection.ExecuteNonQuery(sql);
}
public static async Task Save(QueryExecutionContext query, QueryResponseContext response)
{
string?statement = NormalizeSql(query.GetSqlStatement());
if (statement is null)
{
return;
}
string sql;
string hash = ComputeHash(statement);
var history = await LoadByHash(hash);
if (history is null)
{
history = QueryHistory.Create(statement, hash, query, response);
sql = "INSERT INTO [data] (type, server_connection, sql, hash, executed_on, status, elapsed, row_count, records_affected, execution_count, star) VALUES " +
"( " +
$"{(int)history.Type}, " +
$"'{history.ServerConnection}', " +
$"'{history.Sql.Replace("'", "''")}', " +
$"'{history.Hash}', " +
$"'{history.ExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"{(int)history.Status}, " +
$"{history.Elapsed}, " +
$"{history.RowCount}, " +
$"{history.RecordsAffected}, " +
$"{history.ExecutionCount}, " +
$"{(history.Star ? 1 : 0)}" +
");";
}
else
{
history.UpdateStatistics(query, response);
sql = "UPDATE [data] " +
$"SET server_connection = '{history.ServerConnection}', " +
$"executed_on = '{history.ExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"status = {(int)history.Status}, " +
$"elapsed = {history.Elapsed}, " +
$"row_count = {history.RowCount}, " +
$"records_affected = {history.RecordsAffected}, " +
$"execution_count = {history.ExecutionCount} " +
$"WHERE id = {history.Id};";
}
await ServerConnection.ExecuteNonQuery(sql);
}
public async Task <ActionResult <QueryResponseContext> > SearchHistory([FromQuery] QueryHistoryQuery?query)
{
var response = new QueryResponseContext("id-history");
response.Columns.AddRange(QueryHistoryColumns);
try
{
var history = await QueryManager.History.Load(query ?? new QueryHistoryQuery());
response.Rows.AddRange(history);
response.Status = QueryResponseStatus.Succeeded;
}
catch (Exception ex)
{
response.Error = ex.Message;
response.Status = QueryResponseStatus.Failed;
}
return(Ok(response));
}
private static async Task <QueryResponseContext> ExecuteQuery(this QueryExecutionContext context, CancellationToken cancellationToken)
{
return(await context.Server.Execute(context.Database, context.GetSqlStatement() !, async (dbCommand, ct) =>
{
var response = new QueryResponseContext(context.Id);
var sw = new Stopwatch();
try
{
sw.Start();
using var ctr = ct.Register(() => dbCommand.Cancel());
using var dataReader = await dbCommand.ExecuteReaderAsync(ct);
do
{
var columnNames = new List <string>();
response.Columns.Clear();
response.Rows.Clear();
for (int i = 0; i < dataReader.FieldCount; i++)
{
columnNames.Add(columnNames.Contains(dataReader.GetName(i))
? dataReader.GetName(i) + (i + 1)
: dataReader.GetName(i));
response.Columns.Add(new ColumnDefinition(columnNames[i], dataReader.GetDataTypeName(i)));
}
while (await dataReader.ReadAsync(ct))
{
var dataRow = new ExpandoObject() as IDictionary <string, object?>;
for (int i = 0; i < dataReader.FieldCount; i++)
{
var value = dataReader[i];
dataRow.Add(columnNames[i], value is DBNull ? null : value);
}
response.Rows.Add(dataRow);
}
} while (await dataReader.NextResultAsync(ct));
response.RecordsAffected = dataReader.RecordsAffected;
}
catch (Exception ex)
{
if (ct.IsCancellationRequested)
{
response.Status = QueryResponseStatus.Canceled;
response.Columns.Clear();
response.Rows.Clear();
}
else
{
response.Status = QueryResponseStatus.Failed;
response.Error = ex.Message;
if (int.TryParse(ex.Data["Position"]?.ToString(), out int position))
{
response.ErrorPosition = position;
}
}
}
finally
{
response.Elapsed = sw.ElapsedMilliseconds;
await IgnoreErrorsAsync(() => History.Save(context, response));
}
return response;
}, dbCommand => dbCommand.CommandTimeout = 0, cancellationToken));
}
