/// <summary>
/// Ejecuta los comandos de una cadena SQL
/// </summary>
internal async Task ExecuteAsync(IDbProvider provider, string sql, Models.ArgumentListModel arguments, TimeSpan timeout, CancellationToken cancellationToken)
{
using (BlockLogModel block = Manager.Logger.Default.CreateBlock(LogModel.LogType.Info, "Execute script"))
{
if (string.IsNullOrWhiteSpace(sql))
{
block.Error("The query is empty");
}
else
{
List <SqlSectionModel> scripts = new SqlParser().Tokenize(sql, arguments.Constants.ToDictionary(), out string error);
if (!string.IsNullOrWhiteSpace(error))
{
block.Error(error);
}
else
{
int scriptsExecuted = 0;
// Ejecuta los scripts
if (scripts.Count > 0)
{
scriptsExecuted = await ExecuteCommandsAsync(block, provider, scripts,
ConvertParameters(provider, arguments.Parameters),
timeout, cancellationToken);
}
// Log
if (scriptsExecuted == 0)
{
block.Error("The query is empty");
}
else
{
block.Info($"{scriptsExecuted} command/s executed");
}
}
}
}
}
/// <summary>
/// Obtiene el datatable de una consulta
/// </summary>
internal async Task <DataTable> GetDataTableAsync(IDbProvider provider, string query, Models.ArgumentListModel arguments,
int actualPage, int pageSize, TimeSpan timeout, CancellationToken cancellationToken)
{
DataTable result = null;
// Normaliza los argumentos
if (arguments == null)
{
arguments = new Models.ArgumentListModel();
}
// Obtiene la tabla
using (BlockLogModel block = Manager.Logger.Default.CreateBlock(LogModel.LogType.Info, "Execute query"))
{
if (string.IsNullOrWhiteSpace(query))
{
block.Error("The query is empty");
}
else
{
List <SqlSectionModel> scripts = new SqlParser().Tokenize(query, arguments.Constants.ToDictionary(), out string error);
if (!string.IsNullOrWhiteSpace(error))
{
block.Error(error);
}
else
{
ParametersDbCollection parametersDb = ConvertParameters(provider, arguments.Parameters);
// Obtiene el datatable
foreach (SqlSectionModel script in scripts)
{
if (script.Type == SqlSectionModel.SectionType.Sql)
{
string sql = provider.SqlHelper.ConvertSqlNoParameters(script.Content, parametersDb).TrimIgnoreNull();
if (!string.IsNullOrWhiteSpace(sql))
{
// Log
block.Info($"Executing: {sql}");
// Obtiene la consulta
if (sql.StartsWith("SELECT", StringComparison.CurrentCultureIgnoreCase) ||
sql.StartsWith("WITH ", StringComparison.CurrentCultureIgnoreCase) ||
sql.StartsWith("EXECUTE ", StringComparison.CurrentCultureIgnoreCase) ||
sql.StartsWith("EXEC ", StringComparison.CurrentCultureIgnoreCase))
{
if (pageSize == 0)
{
result = await provider.GetDataTableAsync(sql, null, CommandType.Text, timeout, cancellationToken);
}
else
{
result = await provider.GetDataTableAsync(sql, null, CommandType.Text, actualPage, pageSize, timeout, cancellationToken);
}
}
else
{
result = await ExecuteScalarQueryAsync(provider, sql, timeout, cancellationToken);
}
}
}
}
// Log
block.Info("End query");
}
}
}
// Devuelve la última tabla obtenida
return(result);
}
/// <summary>
/// Obtiene el plan de ejecución de una consulta
/// </summary>
internal async Task <DataTable> GetExecutionPlanAsync(IDbProvider provider, string query, Models.ArgumentListModel arguments,
TimeSpan timeout, CancellationToken cancellationToken)
{
DataTable result = null;
// Obtiene la tabla
using (BlockLogModel block = Manager.Logger.Default.CreateBlock(LogModel.LogType.Info, "Get execution plan"))
{
if (string.IsNullOrWhiteSpace(query))
{
block.Error("The query is empty");
}
else
{
List <SqlSectionModel> scripts = new SqlParser().Tokenize(query, arguments.Constants.ToDictionary(), out string error);
if (!string.IsNullOrWhiteSpace(error))
{
block.Error(error);
}
else
{
ParametersDbCollection parametersDb = ConvertParameters(provider, arguments.Parameters);
// Obtiene el datatable
foreach (SqlSectionModel script in scripts)
{
if (script.Type == SqlSectionModel.SectionType.Sql)
{
string sql = provider.SqlHelper.ConvertSqlNoParameters(script.Content, parametersDb).TrimIgnoreNull();
if (!string.IsNullOrWhiteSpace(sql))
{
// Log
block.Info($"Get execution plan: {sql}");
// Obtiene el plan de ejecución
result = await provider.GetExecutionPlanAsync(sql, null, CommandType.Text, timeout, cancellationToken);
}
}
}
// Log
block.Info("End query");
}
}
}
// Devuelve la última tabla obtenida
return(result);
}
/// <summary>
/// Obtiene el datareader de una consulta
/// </summary>
internal async Task <DbDataReader> ExecuteReaderAsync(IDbProvider provider, string query, Models.ArgumentListModel arguments,
TimeSpan timeout, CancellationToken cancellationToken)
{
List <SqlSectionModel> scripts = new SqlParser().Tokenize(query, arguments.Constants.ToDictionary(), out string error);
if (!string.IsNullOrWhiteSpace(error))
{
throw new Exception(error);
}
else
{
ParametersDbCollection parametersDb = ConvertParameters(provider, arguments.Parameters);
// Obtiene el datareader
foreach (SqlSectionModel script in scripts)
{
if (script.Type == SqlSectionModel.SectionType.Sql)
{
string sql = provider.SqlHelper.ConvertSqlNoParameters(script.Content, parametersDb).TrimIgnoreNull();
if (!string.IsNullOrWhiteSpace(sql))
{
return(await provider.ExecuteReaderAsync(sql, null, CommandType.Text, timeout, cancellationToken));
}
}
}
// Si ha llegado hasta aquí es porque no ha leído nada
return(null);
}
}