public async Task <ActionResult <Models.CandidateForElection> > UpdateCandidateForElection([FromBody] Models.CandidateForElection candidateForElection)
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandUpdateCandidateForElection;
try
{
Models.CandidateForElection returnValue = new Models.CandidateForElection();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("UPDATE candidate_for_election ");
sqlStatement.Append(" SET person_id = @person_id, election_for_territory_id = @election_for_territory_id, ");
sqlStatement.Append(" distinct_elected_office_for_territory_id = @distinct_elected_office_for_territory_id, ");
sqlStatement.Append(" political_party_id = @political_party_id, result_of_candidacy_id = @result_of_candidacy_id, ");
sqlStatement.Append(" record_last_updated_date_time = @record_last_updated_date_time ");
sqlStatement.Append(" WHERE candidate_for_election_id = @candidate_for_election_id ");
sqlCommandUpdateCandidateForElection = sqlConnection.CreateCommand();
sqlCommandUpdateCandidateForElection.CommandText = sqlStatement.ToString();
sqlCommandUpdateCandidateForElection.CommandTimeout = 600;
sqlCommandUpdateCandidateForElection.Parameters.Add(new NpgsqlParameter("@person_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandUpdateCandidateForElection.Parameters.Add(new NpgsqlParameter("@election_for_territory_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandUpdateCandidateForElection.Parameters.Add(new NpgsqlParameter("@distinct_elected_office_for_territory_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandUpdateCandidateForElection.Parameters.Add(new NpgsqlParameter("@political_party_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandUpdateCandidateForElection.Parameters.Add(new NpgsqlParameter("@result_of_candidacy_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandUpdateCandidateForElection.Parameters.Add(new NpgsqlParameter("@record_added_date_time", NpgsqlTypes.NpgsqlDbType.Timestamp));
sqlCommandUpdateCandidateForElection.Parameters.Add(new NpgsqlParameter("@record_last_updated_date_time", NpgsqlTypes.NpgsqlDbType.Timestamp));
sqlCommandUpdateCandidateForElection.Parameters["@person_id"].Value = 0;
sqlCommandUpdateCandidateForElection.Parameters["@election_for_territory_id"].Value = 0;
sqlCommandUpdateCandidateForElection.Parameters["@distinct_elected_office_for_territory_id"].Value = 0;
sqlCommandUpdateCandidateForElection.Parameters["@political_party_id"].Value = 0;
sqlCommandUpdateCandidateForElection.Parameters["@result_of_candidacy_id"].Value = 0;
sqlCommandUpdateCandidateForElection.Parameters["@record_last_updated_date_time"].Value = DateTime.MinValue;
sqlCommandUpdateCandidateForElection.Parameters["@candidate_for_election_id"].Value = 0;
await sqlCommandUpdateCandidateForElection.PrepareAsync();
sqlCommandUpdateCandidateForElection.Parameters["@person_id"].Value = candidateForElection.personId;
sqlCommandUpdateCandidateForElection.Parameters["@election_for_territory_id"].Value = candidateForElection.electionForTerritoryId;
sqlCommandUpdateCandidateForElection.Parameters["@distinct_elected_office_for_territory_id"].Value = candidateForElection.distinctElectedOfficeForTerritoryId;
sqlCommandUpdateCandidateForElection.Parameters["@political_party_id"].Value = candidateForElection.politicalPartyId;
sqlCommandUpdateCandidateForElection.Parameters["@result_of_candidacy_id"].Value = candidateForElection.resultOfCandidacyId;
sqlCommandUpdateCandidateForElection.Parameters["@record_last_updated_date_time"].Value = processingDateTime;
sqlCommandUpdateCandidateForElection.Parameters["@candidate_for_election_id"].Value = candidateForElection.candidateForElectionId;
await sqlCommandUpdateCandidateForElection.ExecuteNonQueryAsync();
returnValue.candidateForElectionId = candidateForElection.candidateForElectionId;
returnValue.personId = candidateForElection.personId;
returnValue.distinctElectedOfficeForTerritoryId = candidateForElection.distinctElectedOfficeForTerritoryId;
returnValue.electionForTerritoryId = candidateForElection.electionForTerritoryId;
returnValue.politicalPartyId = candidateForElection.politicalPartyId;
returnValue.resultOfCandidacyId = candidateForElection.resultOfCandidacyId;
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in CandidateForElectionWSController::UpdateCandidateForElection(). 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));
}
} // UpdateCandidateForElection()
public async Task <ActionResult <Models.CandidateForElection> > GetCandidateForElection(int candidateForElectionId)
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandGetCandidateForElection;
NpgsqlDataReader sqlDataReaderGetCandidateForElection;
try
{
Models.CandidateForElection returnValue = new Models.CandidateForElection();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("SELECT tl.territory_level_id, tl.reference_name, t.territory_id, t.full_name, eo.reference_name, deot.distinct_elected_office_for_territory_id, ");
sqlStatement.Append(" deot.distinct_office_designator, elft.election_date, p.political_party_id, p.reference_name, roc.result_of_candidacy_id, roc.description ");
sqlStatement.Append(" FROM candidate_for_election cfe INNER JOIN distinct_elected_office_for_territory deot ");
sqlStatement.Append(" ON cfe.distinct_elected_office_for_territory_id = deot.distinct_elected_office_for_territory_id ");
sqlStatement.Append(" INNER JOIN elected_office_for_territory eot ");
sqlStatement.Append(" ON deot.elected_office_for_territory_id = eot.elected_office_for_territory_id ");
sqlStatement.Append(" INNER JOIN elected_office eo ON eot.elected_office_id = eo.elected_office_id ");
sqlStatement.Append(" INNER JOIN territory t ON eot.territory_id = t.territory_id ");
sqlStatement.Append(" INNER JOIN territory_level tl ON t.territory_level_id = t.territory_level_id ");
sqlStatement.Append(" INNER JOIN political_party p ON cfe.political_party_id = p.political_party_id ");
sqlStatement.Append(" INNER JOIN result_of_candidacy roc ON cfe.result_of_candidacy_id = roc.result_of_candidacy_id ");
sqlStatement.Append(" INNER JOIN election_for_territory elft on cfe.election_for_territory_id = elft.election_for_territory_id ");
sqlStatement.Append(" WHERE cfe.candidate_for_election_id = @candidate_for_election_id ");
sqlCommandGetCandidateForElection = sqlConnection.CreateCommand();
sqlCommandGetCandidateForElection.CommandText = sqlStatement.ToString();
sqlCommandGetCandidateForElection.CommandTimeout = 600;
sqlCommandGetCandidateForElection.Parameters.Add(new NpgsqlParameter("@candidate_for_election_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandGetCandidateForElection.Parameters["@candidate_for_election_id"].Value = 0;
await sqlCommandGetCandidateForElection.PrepareAsync();
sqlCommandGetCandidateForElection.Parameters["@candidate_for_election_id"].Value = candidateForElectionId;
using (sqlDataReaderGetCandidateForElection = await sqlCommandGetCandidateForElection.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
{
if (await sqlDataReaderGetCandidateForElection.ReadAsync())
{
returnValue.candidateForElectionId = candidateForElectionId;
returnValue.territoryLevelId = sqlDataReaderGetCandidateForElection.GetInt32(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_TERRITORY_LEVEL_ID);
returnValue.territoryLevelDescription = sqlDataReaderGetCandidateForElection.GetString(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_TERRITORY_LEVEL_DESCRIPTION);
returnValue.territoryId = sqlDataReaderGetCandidateForElection.GetInt32(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_TERRITORY_ID);
returnValue.territoryFullName = sqlDataReaderGetCandidateForElection.GetString(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_TERRITORY_FULL_NAME);
returnValue.electedOfficeDescription = sqlDataReaderGetCandidateForElection.GetString(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_ELECTED_OFFICE_REFERENCE_NAME);
returnValue.distinctElectedOfficeForTerritoryId = sqlDataReaderGetCandidateForElection.GetInt32(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_DISTINCT_ELECTED_OFFICE_FOR_TERRITORY_ID);
returnValue.distinctOfficeDesignator = sqlDataReaderGetCandidateForElection.GetString(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_DISTINCT_OFFICE_DESIGNATOR);
returnValue.electionDate = sqlDataReaderGetCandidateForElection.GetDateTime(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_ELECTION_DATE);
returnValue.politicalPartyId = sqlDataReaderGetCandidateForElection.GetInt32(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_POLITICAL_PARTY_ID);
returnValue.politicalPartyReferenceName = sqlDataReaderGetCandidateForElection.GetString(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_POLITICAL_PARTY_REFERENCE_NAME);
returnValue.resultOfCandidacyId = sqlDataReaderGetCandidateForElection.GetInt32(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_RESULT_OF_CANDIDACY_ID);
returnValue.resultOfCandidacyDescription = sqlDataReaderGetCandidateForElection.GetString(ApplicationValues.CANDIDATE_FOR_ELECTION_QUERY_RESULT_COLUMN_OFFSET_RESULT_OF_CANDIDACY_DESCRIPTION);
}
;
await sqlDataReaderGetCandidateForElection.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 CandidateForElectionWSController::GetCandidateForElection(). 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));
}
} // GetCandidateForElection()
