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