public async Task <IEnumerable <Affiliation> > RefreshAffiliations()
{
List <Affiliation> affiliations = new List <Affiliation>();
string getAffiliationsQuery = "SELECT * FROM " + Constants.AFFILIATION + " WHERE active = 1;";
using (var connection1 = new MySqlConnection(Constants.CONNECTION_STRING))
{
await connection1.OpenAsync();
using (var cmd = new MySqlCommand(getAffiliationsQuery, connection1))
using (var reader = await cmd.ExecuteReaderAsync())
while (await reader.ReadAsync())
{
Affiliation temp = new Affiliation();
temp.Id = reader.GetInt32(0);
temp.Name = reader.GetString(1);
temp.Abbreviation = reader.GetString(2);
affiliations.Add(temp);
}
}
return(affiliations);
}
public async Task <Affiliation> AddNewAffiliationAsync(Affiliation affiliation)
{
var affiliations = new ObservableCollection <Affiliation>(await RefreshAffiliations());
string saveAffiliationStatement = "INSERT INTO " + Constants.AFFILIATION + " ("
+ "name, "
+ "abbreviation, "
+ "active"
+ ") VALUES ("
+ "@name, "
+ "@abbreviation, "
+ "@active"
+ ");"
+ " SELECT last_insert_id();";
using (var connection1 = new MySqlConnection(Constants.CONNECTION_STRING))
{
await connection1.OpenAsync();
using (var cmd = new MySqlCommand())
{
cmd.Connection = connection1;
cmd.CommandText = saveAffiliationStatement;
cmd.Prepare();
cmd.Parameters.AddWithValue("@name", affiliation.Name);
cmd.Parameters.AddWithValue("@abbreviation", affiliation.Abbreviation);
cmd.Parameters.AddWithValue("@active", 1);
await cmd.ExecuteNonQueryAsync();
#pragma warning disable CS0472 // The result of the expression is always the same since a value of this type is never equal to 'null'
if (cmd.LastInsertedId != null)
#pragma warning restore CS0472 // The result of the expression is always the same since a value of this type is never equal to 'null'
{
cmd.Parameters.Add(new MySqlParameter("newId", cmd.LastInsertedId));
}
var affiliationId = Convert.ToInt32(cmd.Parameters["@newId"].Value);
affiliation.Id = affiliationId;
}
#pragma warning disable CS0472 // The result of the expression is always the same since a value of this type is never equal to 'null'
if (affiliation.Id == 0 || affiliation.Id == null)
#pragma warning restore CS0472 // The result of the expression is always the same since a value of this type is never equal to 'null'
{
return(null);
}
else
{
foreach (Affiliation tempAffiliation in affiliations)
{
if (affiliation.Name == tempAffiliation.Name || affiliation.Abbreviation == tempAffiliation.Abbreviation)
{
affiliation.Id = -1;
}
}
return(affiliation);
}
}
}
