public async Task <ResultStoredViewModel <string[][]> > ExecuteQuery(QueryManagerViewModel qmvm)
{
var requestUrl = CreateRequestUri(string.Format(System.Globalization.CultureInfo.InvariantCulture,
ControllerQueryManager + "ExecuteQuery"));
return(await PostAsync <string[][], QueryManagerViewModel>(requestUrl, qmvm));
}
public async Task <IActionResult> Index(IFormCollection form, string idQuery)
{
await LoadQuerys();
var queryParameter = form.Keys.ToDictionary(k => k, v => form[v].FirstOrDefault());
queryParameter.Remove("__RequestVerificationToken");
queryParameter.Remove("idQuery");
var qmvm = new QueryManagerViewModel()
{
Querys = TempData.Get <IEnumerable <QueryViewModel> >("Querys"),
QueryParameters = queryParameter,
SelectedQuery = TempData.Get <IEnumerable <QueryViewModel> >("Querys").ToList()
.Find(z => z.IdQuery == idQuery)
};
var result = await ApiClientFactory.Instance.ExecuteQuery(qmvm);
if (result.Errore())
{
TempData.Add("errore", result.MessaggioErrore);
}
else
{
qmvm.Resultset = result.Entita;
}
TempData.Keep("Querys");
return(View("Index", qmvm));
}
public async Task <IActionResult> ExecuteQuery(QueryManagerViewModel queryManagerViewModel)
{
var result = new ResultStoredViewModel <string[][]>();
//var pattern = @"\$\#([a-zA-Z0-9_()/',=\s\.]+)\#\$"; [17/06 Non funzionante (FD)]
var pattern = @"\$#(.+?)\#\$";
_logger.LogInformation("Stored procedure execution: DammiQuery");
try
{
var querySQL = new DammiQuery(RepositoryContext);
queryManagerViewModel.SelectedQuery.SQL =
querySQL.Get(Convert.ToInt32(queryManagerViewModel.SelectedQuery.IdQuery)).Result.Testo;
//ricerca dei parametri compresi tra $# e #$ nell'SQL
string[] queryParameters = Regex.Matches(queryManagerViewModel.SelectedQuery.SQL, pattern)
.Cast <Match>()
.Select(s => s.Groups[0].Value)
.Distinct()
.ToArray();
// crea un dictionary che utilizza come chiave ciascuna parte della query compresa tra $# e #$ e come valore il parametro passato da
//URL la cui chiave รจ contenuta nel testo della parte della query compresa tra $# e #$
var parametersValue = new Dictionary <string, string>();
foreach (var item in queryParameters)
{
foreach (var par in queryManagerViewModel.QueryParameters)
{
if (item.Contains(par.Key))
{
parametersValue.Add(item, par.Value);
}
}
}
var queryToExecute = Regex.Replace(queryManagerViewModel.SelectedQuery.SQL, pattern,
m => parametersValue.ContainsKey(m.Value) ?
string.Format("'{0}'", parametersValue[m.Value]) : m.Value);
// esegue la query con i parametri passati da URL
_logger.LogInformation("Query execution");
await EseguiQuery(queryManagerViewModel, result, queryToExecute);
FormatStringAsDate(result);
return(Ok(result));
}
catch (Exception ex)
{
_logger.LogError(ex, ex.Message);
return(Content(ex.Message));
}
}
public async Task <IActionResult> Index()
{
await LoadQuerys();
var result = new QueryManagerViewModel()
{
Querys = TempData.Get <IEnumerable <QueryViewModel> >("Querys")
};
return(View("Index", result));
}
private async Task EseguiQuery(QueryManagerViewModel queryManagerViewModel, ResultStoredViewModel <string[][]> result, string queryToExecute)
{
var connectionString = RepositoryContext.ConnectionString;
using (var connection = new SqlConnection(connectionString))
using (var cmd = new SqlCommand(queryToExecute, connection))
{
connection.Open();
try
{
result.Entita = await ExecuteQuery(cmd);
}
catch (Exception queryException)
{
result.ImpostaErrore(queryException.HResult,
string.Format("{0} - {1}", queryManagerViewModel.SelectedQuery.Descrizione,
queryException.Message));
}
}
}
