public async Task <ActionResult <List <Models.ReasonForDeparture> > > GetReasonForDepartureList() { System.Text.StringBuilder sqlStatement; DateTime processingDateTime; NpgsqlConnection sqlConnection; NpgsqlCommand sqlCommandGetResultOfCandidacyList; NpgsqlDataReader sqlDataReaderGetResultOfCandidacyList; try { List <Models.ReasonForDeparture> returnValue = new List <Models.ReasonForDeparture>(); processingDateTime = System.DateTime.Now; using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"])) { await sqlConnection.OpenAsync(); sqlStatement = new System.Text.StringBuilder(); sqlStatement.Append("SELECT r.reason_for_departure_id, r.description "); sqlStatement.Append(" FROM reason_for_departure 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.ReasonForDeparture reasonForDeparture = new Models.ReasonForDeparture(); reasonForDeparture.reasonForDepartureId = sqlDataReaderGetResultOfCandidacyList.GetInt32(ApplicationValues.REASON_FOR_DEPARTURE_LIST_QUERY_RESULT_COLUMN_OFFSET_REASON_FOR_DEPARTURE_ID); reasonForDeparture.description = sqlDataReaderGetResultOfCandidacyList.GetString(ApplicationValues.REASON_FOR_DEPARTURE_LIST_QUERY_RESULT_COLUMN_OFFSET_DESCRIPTION); returnValue.Add(reasonForDeparture); } ; 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::GetReasonForDepartureList(). 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)); } } // GetReasonForDepartureList()
public async Task <ActionResult <Models.ReasonForDeparture> > GetReasonForDeparture(int reasonForDepartureId) { System.Text.StringBuilder sqlStatement; DateTime processingDateTime; NpgsqlConnection sqlConnection; NpgsqlCommand sqlCommandGetReasonForDeparture; NpgsqlDataReader sqlDataReaderGetReasonForDeparture; try { Models.ReasonForDeparture returnValue = new Models.ReasonForDeparture(); 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 reason_for_departure r "); sqlStatement.Append(" WHERE r.reason_for_departure_id = @reason_for_departure_id "); sqlCommandGetReasonForDeparture = sqlConnection.CreateCommand(); sqlCommandGetReasonForDeparture.CommandText = sqlStatement.ToString(); sqlCommandGetReasonForDeparture.CommandTimeout = 600; sqlCommandGetReasonForDeparture.Parameters.Add(new NpgsqlParameter("@reason_for_departure_id", NpgsqlTypes.NpgsqlDbType.Integer)); sqlCommandGetReasonForDeparture.Parameters["@reason_for_departure_id"].Value = 0; await sqlCommandGetReasonForDeparture.PrepareAsync(); sqlCommandGetReasonForDeparture.Parameters["@reason_for_departure_id"].Value = reasonForDepartureId; using (sqlDataReaderGetReasonForDeparture = await sqlCommandGetReasonForDeparture.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection)) { if (await sqlDataReaderGetReasonForDeparture.ReadAsync()) { returnValue.reasonForDepartureId = reasonForDepartureId; returnValue.description = sqlDataReaderGetReasonForDeparture.GetString(ApplicationValues.REASON_FOR_DEPARTURE_QUERY_RESULT_COLUMN_OFFSET_DESCRIPTION); } ; await sqlDataReaderGetReasonForDeparture.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::GetReasonForDeparture(). 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)); } } // GetReasonForDeparture()