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)); } }
private ResultStoredViewModel <DateTime> Traslate(OutputStored <DateTime> dataChiusura) { var result = new ResultStoredViewModel <DateTime>() { Entita = dataChiusura.Entita }; if (dataChiusura.Errore) { result.ImpostaErrore(dataChiusura.Error_Number.Value, dataChiusura.Error_msg.Value); } return(result); }
private ResultStoredViewModel <DispaccioViewModel> Traslate(OutputStored <NewDispaccioIn> nuovoDispaccio) { var result = new ResultStoredViewModel <DispaccioViewModel>() { Entita = Traslate(nuovoDispaccio.Entita) }; if (nuovoDispaccio.Errore) { result.ImpostaErrore(nuovoDispaccio.Error_Number.Value, nuovoDispaccio.Error_msg.Value); } return(result); }
private void FormatStringAsDate(ResultStoredViewModel <string[][]> result) { if (result.Entita != null) { for (int k = 0; k < result.Entita.GetLength(0); k++) { for (int l = 0; l < result.Entita[k].GetLength(0); l++) { DateTime dateFormatted; if (DateTime.TryParse(result.Entita[k][l], out dateFormatted)) { result.Entita[k][l] = dateFormatted.ToShortDateString(); } } } } }
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)); } } }