public async Task <ActionResult <List <Models.ResultOfCandidacy> > > GetResultOfCandidacyList() { System.Text.StringBuilder sqlStatement; DateTime processingDateTime; NpgsqlConnection sqlConnection; NpgsqlCommand sqlCommandGetResultOfCandidacyList; NpgsqlDataReader sqlDataReaderGetResultOfCandidacyList; try { List <Models.ResultOfCandidacy> returnValue = new List <Models.ResultOfCandidacy>(); processingDateTime = System.DateTime.Now; using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"])) { await sqlConnection.OpenAsync(); sqlStatement = new System.Text.StringBuilder(); sqlStatement.Append("SELECT r.result_of_candidacy_id, r.description "); sqlStatement.Append(" FROM result_of_candidacy r "); sqlStatement.Append(" ORDER BY r.description "); sqlCommandGetResultOfCandidacyList = sqlConnection.CreateCommand(); sqlCommandGetResultOfCandidacyList.CommandText = sqlStatement.ToString(); sqlCommandGetResultOfCandidacyList.CommandTimeout = 600; await sqlCommandGetResultOfCandidacyList.PrepareAsync(); using (sqlDataReaderGetResultOfCandidacyList = await sqlCommandGetResultOfCandidacyList.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection)) { while (await sqlDataReaderGetResultOfCandidacyList.ReadAsync()) { Models.ResultOfCandidacy resultOfCandidacy = new Models.ResultOfCandidacy(); resultOfCandidacy.resultOfCandidacyId = sqlDataReaderGetResultOfCandidacyList.GetInt32(ApplicationValues.RESULT_OF_CANDIDACY_LIST_QUERY_RESULT_COLUMN_OFFSET_RESULT_OF_CANDIDACY_ID); resultOfCandidacy.description = sqlDataReaderGetResultOfCandidacyList.GetString(ApplicationValues.RESULT_OF_CANDIDACY_LIST_QUERY_RESULT_COLUMN_OFFSET_DESCRIPTION); returnValue.Add(resultOfCandidacy); } ; await sqlDataReaderGetResultOfCandidacyList.CloseAsync(); }; await sqlConnection.CloseAsync(); } // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"])) return(Ok(returnValue)); } catch (Exception ex1) { logger.LogError(string.Format("Unhandled exception occurred in DictionaryWSController::GetResultOfCandidacyList(). Message is {0}", ex1.Message)); if (ex1.InnerException != null) { logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message)); if (ex1.InnerException.InnerException != null) { logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message)); } } logger.LogError(string.Format("{0}", ex1.StackTrace)); return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message)); } } // GetResultOfCandidacyList()
public async Task <ActionResult <Models.ResultOfCandidacy> > GetResultOfCandidacy(int resultOfCandidacyId) { System.Text.StringBuilder sqlStatement; DateTime processingDateTime; NpgsqlConnection sqlConnection; NpgsqlCommand sqlCommandGetResultOfCandidacy; NpgsqlDataReader sqlDataReaderGetResultOfCandidacy; try { Models.ResultOfCandidacy returnValue = new Models.ResultOfCandidacy(); processingDateTime = System.DateTime.Now; using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"])) { await sqlConnection.OpenAsync(); sqlStatement = new System.Text.StringBuilder(); sqlStatement.Append("SELECT r.description "); sqlStatement.Append(" FROM result_of_candidacy r "); sqlStatement.Append(" WHERE r.result_of_candidacy_id = @result_of_candidacy_id "); sqlCommandGetResultOfCandidacy = sqlConnection.CreateCommand(); sqlCommandGetResultOfCandidacy.CommandText = sqlStatement.ToString(); sqlCommandGetResultOfCandidacy.CommandTimeout = 600; sqlCommandGetResultOfCandidacy.Parameters.Add(new NpgsqlParameter("@result_of_candidacy_id", NpgsqlTypes.NpgsqlDbType.Integer)); sqlCommandGetResultOfCandidacy.Parameters["@result_of_candidacy_id"].Value = 0; await sqlCommandGetResultOfCandidacy.PrepareAsync(); sqlCommandGetResultOfCandidacy.Parameters["@result_of_candidacy_id"].Value = resultOfCandidacyId; using (sqlDataReaderGetResultOfCandidacy = await sqlCommandGetResultOfCandidacy.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection)) { if (await sqlDataReaderGetResultOfCandidacy.ReadAsync()) { returnValue.resultOfCandidacyId = resultOfCandidacyId; returnValue.description = sqlDataReaderGetResultOfCandidacy.GetString(ApplicationValues.RESULT_OF_CANDIDACY_QUERY_RESULT_COLUMN_OFFSET_DESCRIPTION); } ; await sqlDataReaderGetResultOfCandidacy.CloseAsync(); }; await sqlConnection.CloseAsync(); } // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"])) return(Ok(returnValue)); } catch (Exception ex1) { logger.LogError(string.Format("Unhandled exception occurred in DictionaryWSController::GetResultOfCandidacy(). Message is {0}", ex1.Message)); if (ex1.InnerException != null) { logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message)); if (ex1.InnerException.InnerException != null) { logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message)); } } logger.LogError(string.Format("{0}", ex1.StackTrace)); return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message)); } } // GetResultOfCandidacy()