/// <summary>
/// Saves a new team to the database.
/// </summary>
/// <param name="team">The team model.</param>
/// <returns>The team model.</returns>
public TeamModel CreateTeam(TeamModel team)
{
using (IDbConnection connection =
new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("@TeamName", team.TeamName);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spTeams_Insert", p, commandType: CommandType.StoredProcedure);
team.Id = p.Get <int>("@id");
foreach (PersonModel person in team.TeamMembers)
{
p = new DynamicParameters();
p.Add("@TeamId", team.Id);
p.Add("@PersonId", person.Id);
connection.Execute("dbo.spTeamMembers_Insert", p, commandType: CommandType.StoredProcedure);
}
return(team);
}
}
public void UpdateMatchup(MatchupModel model)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
var p = new DynamicParameters();
if (model.Winner != null)
{
p.Add("matchup_id", model.Id);
p.Add("winner", model.Winner.Id);
connection.Execute("matchups_update", p, commandType: CommandType.StoredProcedure);
}
foreach (MatchupEntryModel me in model.Entries)
{
if (me.TeamCompeting != null)
{
p = new DynamicParameters();
p.Add("matchup_entry_id", me.Id);
p.Add("team", me.TeamCompeting.Id);
p.Add("entry_score", me.Score);
connection.Execute("matchup_entries_update", p, commandType: CommandType.StoredProcedure);
}
}
}
}
public List <PersonModel> GetPerson_All()
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
List <PersonModel> output;
output = connection.Query <PersonModel>("tournaments.spPeople_GetAll").ToList();
return(output);
}
}
public void CreateTournament(TournamentModel tournament)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
SaveTournament(connection, tournament);
SaveTournamentEntries(connection, tournament);
SaveTournamentPrizes(connection, tournament);
}
}
public List <PersonModel> GetPeople()
{
List <PersonModel> output = new List <PersonModel>();
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
output = connection.Query <PersonModel>("people_all").ToList();
}
return(output);
}
public void CreateTournament(TournamentModel model)
{
using (IDbConnection connection =
new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(db)))
{
SaveTournament(model, connection);
SaveTournamentPrizes(model, connection);
SaveTournamentEntries(model, connection);
}
}
public void CompleteTournament(TournamentModel model)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("tournament_id", model.Id);
// mark the tournament entry as not active
connection.Execute("tournaments_complete", p, commandType: CommandType.StoredProcedure);
}
}
/// <summary>
/// Returns all people in the database.
/// </summary
/// <returns>The list of people.</returns>
public List <PersonModel> GetPerson_All()
{
List <PersonModel> output;
using (IDbConnection connection =
new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(db)))
{
output = connection.Query <PersonModel>("dbo.spPeople_GetAll").ToList();
}
return(output);
}
public void CreateTournament(TournamentModel model)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
SaveTournament(model, connection);
SaveTournamentPrizes(model);
SaveTournamentEntries(model, connection);
SaveTournamentRounds(model, connection);
}
}
/// <summary>
/// Sets the id of a person model and saves the person to the database.
/// </summary>
/// <param name="model">The person model with the data to be inserted.</param>
public void CreatePerson(PersonModel model)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("f_name", model.FirstName);
p.Add("l_name", model.LastName);
p.Add("email_address", model.Email);
p.Add("@id", dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("people_insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@id");
}
}
public List <TeamModel> GetTeam_All()
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
List <TeamModel> teams = connection.Query <TeamModel>("tournaments.spTeam_GetAll").ToList();
foreach (TeamModel team in teams)
{
var parameters = new DynamicParameters();
parameters.Add("requested_team_id", team.Id);
team.Members = connection.Query <PersonModel>("tournaments.spTeamMembers_GetByTeam", parameters, commandType: CommandType.StoredProcedure).ToList();
}
return(teams);
}
}
public void CreatePrize(PrizeModel prize)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
var parameters = new DynamicParameters();
parameters.Add("argPlaceNumber", prize.Number);
parameters.Add("argPlaceName", prize.Name);
parameters.Add("argPlaceAmount", prize.Amount);
parameters.Add("argPrizePercentage", prize.Percentage);
parameters.Add("id", 0, DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("tournaments.spPrizes_Insert", parameters, commandType: CommandType.StoredProcedure);
prize.Id = parameters.Get <int>("id");
}
}
public void CreatePerson(PersonModel person)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
var parameters = new DynamicParameters();
parameters.Add("argFirstName", person.Firstname);
parameters.Add("argLastName", person.Lastname);
parameters.Add("argEmailAddress", person.EmailAddress);
parameters.Add("argPhoneNumber", person.PhoneNumber);
parameters.Add("id", 0, DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("tournaments.spPeople_Insert", parameters, commandType: CommandType.StoredProcedure);
person.Id = parameters.Get <int>("id");
}
}
/// <summary>
/// Sets the id of a prize model and saves the prize to the database.
/// </summary>
/// <param name="model">The prize model with the data to be inserted.</param>
public void CreatePrize(PrizeModel model)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("p_place_number", model.PlaceNumber);
p.Add("p_place_name", model.PlaceName);
p.Add("p_amount", model.PrizeAmount);
p.Add("p_percentage", model.PrizePercentage);
p.Add("p_tournament_id", model.TournamentId);
p.Add("@id", dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("prizes_insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@id");
}
}
/// <summary>
/// Saves a new prize to the database.
/// </summary>
/// <param name="prize">The prize model.</param>
/// <returns>The prize model.</returns>
public PrizeModel CreatePrize(PrizeModel prize)
{
using (IDbConnection connection =
new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("@PlaceNumber", prize.PlaceNumber);
p.Add("@PlaceName", prize.PlaceName);
p.Add("@PrizeAmount", prize.PrizeAmount);
p.Add("@PrizePercentage", prize.PrizePercentage);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spPrizes_Insert", p, commandType: CommandType.StoredProcedure);
prize.Id = p.Get <int>("@id");
return(prize);
}
}
/// <summary>
/// Saves a new person to the database.
/// </summary>
/// <param name="person">The person model.</param>
/// <returns>The personmodel.</returns>
public PersonModel CreatePerson(PersonModel person)
{
using (IDbConnection connection =
new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("@FirstName", person.FirstName);
p.Add("@LastName", person.LastName);
p.Add("@EmailAddress", person.EmailAddress);
p.Add("@CellphoneNumber", person.CellphoneNumber);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spPeople_Insert", p, commandType: CommandType.StoredProcedure);
person.Id = p.Get <int>("@id");
return(person);
}
}
/// <summary>
/// Returns all teams in the database.
/// </summary
/// <returns>The list of teams.</returns>
public List <TeamModel> GetTeam_All()
{
List <TeamModel> output;
using (IDbConnection connection =
new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(db)))
{
output = connection.Query <TeamModel>("dbo.spTeam_GetAll").ToList();
foreach (TeamModel team in output)
{
var p = new DynamicParameters();
p.Add("@TeamId", team.Id);
team.TeamMembers = connection.Query <PersonModel>("dbo.spTeamMembers_GetByTeam", p, commandType: CommandType.StoredProcedure).ToList();
}
}
return(output);
}
public TournamentModel CreateTournament(TournamentModel tournament)
{
using (IDbConnection connection =
new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("@TournamentName", tournament.TournamentName);
p.Add("@EntryFee", tournament.EntryFee);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spTournaments_Insert", p, commandType: CommandType.StoredProcedure);
tournament.Id = p.Get <int>("@id");
InsertTournamentPrizes(connection, tournament);
InsertTournamentEntries(connection, tournament);
InsertTournamentRounds(connection, tournament);
return(tournament);
}
}
public List <TeamModel> GetTeams()
{
List <TeamModel> output = new List <TeamModel>();
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
output = connection.Query <TeamModel>("teams_all").ToList();
// Fill the team members of each team
Dapper.DynamicParameters p;
foreach (TeamModel t in output)
{
p = new DynamicParameters();
p.Add("selected_team", t.Id);
t.TeamMembers = connection.Query <PersonModel>(
"team_members_by_team", p, commandType: CommandType.StoredProcedure).ToList();
}
}
return(output);
}
public void CreateTeam(TeamModel team)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
var parameters = new DynamicParameters();
parameters.Add("argTeamName", team.Name);
parameters.Add("id", 0, DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("tournaments.spTeams_Insert", parameters, commandType: CommandType.StoredProcedure);
team.Id = parameters.Get <int>("id");
foreach (PersonModel person in team.Members)
{
parameters = new DynamicParameters();
parameters.Add("argTeamId", team.Id);
parameters.Add("argPersonId", person.Id);
parameters.Add("id", 0, DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("tournaments.spTeamMembers_Insert", parameters, commandType: CommandType.StoredProcedure);
}
}
}
/// <summary>
/// Sets the id of a team model and saves the team to the database,
/// connecting the team members to their team in the database.
/// </summary>
/// <param name="model">The team with team members to the inserted.</param>
public void CreateTeam(TeamModel model)
{
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
// Insert team name and set the team id
var p = new DynamicParameters();
p.Add("p_team_name", model.TeamName);
p.Add("@id", dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("teams_insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@id");
// Insert each team member with the team id that was just set
foreach (PersonModel member in model.TeamMembers)
{
p = new DynamicParameters(); // overwrite p
p.Add("p_team_id", model.Id);
p.Add("p_person_id", member.Id);
connection.Execute("team_members_insert", p, commandType: CommandType.StoredProcedure);
}
}
}
public void UpdateMatchup(MatchupModel model)
{
using (IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(ConnectionDatabase)))
{
var p = new DynamicParameters();
p.Add("@id", model.Id);
p.Add("@WinnerId", model.Winner?.Id);
dbConnection.Execute("dbo.spMatchups_Update", p, commandType: CommandType.StoredProcedure);
}
model.Entries.ForEach(UpdateMatchupEntry);
}
public void UpdateMatchupEntry(MatchupEntryModel model)
{
using (IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(ConnectionDatabase)))
{
if (model.TeamCompeting != null)
{
var p = new DynamicParameters();
p.Add("@id", model.Id);
p.Add("@TeamCompetingId", model.TeamCompeting.Id);
if (model.Score.HasValue)
{
p.Add("@Score", model.Score);
}
dbConnection.Execute("dbo.spMatchupEntries_Update", p, commandType: CommandType.StoredProcedure);
}
}
}
public void DeactivateTournament(TournamentModel model)
{
using (IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(ConnectionDatabase)))
{
var p = new DynamicParameters();
p.Add("@id", model.Id);
dbConnection.Execute("dbo.spTournament_Deactivate", p, commandType: CommandType.StoredProcedure);
}
}
public void UpdateRound(RoundModel model)
{
using (IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(ConnectionDatabase)))
{
var p = new DynamicParameters();
p.Add("@id", model.Id);
p.Add("@Active", model.Active ?? null);
dbConnection.Execute("dbo.spRounds_Update", p, commandType: CommandType.StoredProcedure);
}
}
public List <TournamentModel> GetTournament_All()
{
List <TournamentModel> output;
using (IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(ConnectionDatabase)))
{
output = dbConnection.Query <TournamentModel>("dbo.spTournaments_GetAll").ToList();
foreach (TournamentModel tournament in output)
{
// Populate the prizes
var p = new DynamicParameters();
p.Add("@TournamentId", tournament.Id);
tournament.Prizes = dbConnection.Query <PrizeModel>("dbo.spPrizes_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
// Populate the teams
tournament.EnteredTeams = dbConnection.Query <TeamModel>("dbo.spTeam_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
// Populate the rounds
tournament.Rounds = dbConnection.Query <RoundModel>("dbo.spRounds_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
foreach (TeamModel team in tournament.EnteredTeams)
{
p = new DynamicParameters();
p.Add("@TeamId", team.Id);
team.TeamMembers = dbConnection.Query <PersonModel>("dbo.spTeamMembers_GetByTeam", p, commandType: CommandType.StoredProcedure).ToList();
}
RoundModel previousRound = tournament.Rounds.FirstOrDefault();
foreach (RoundModel currentRound in tournament.Rounds)
{
p = new DynamicParameters();
p.Add("@RoundId", currentRound.Id);
currentRound.Matchups = dbConnection.Query <MatchupModel>("dbo.spMatchups_GetByRound", p, commandType: CommandType.StoredProcedure).ToList();
foreach (MatchupModel matchup in currentRound.Matchups)
{
matchup.MatchupRound = currentRound.Number;
if (matchup.WinnerId.HasValue)
{
matchup.Winner = tournament.EnteredTeams.Find(t => t.Id == matchup.WinnerId);
}
p = new DynamicParameters();
p.Add("@MatchupId", matchup.Id);
matchup.Entries = dbConnection.Query <MatchupEntryModel>("dbo.spMatchupEntries_GetByMatchup", p, commandType: CommandType.StoredProcedure).ToList();
foreach (var entry in matchup.Entries)
{
if (entry.TeamCompetingId.HasValue)
{
entry.TeamCompeting = tournament.EnteredTeams.First(t => t.Id == entry.TeamCompetingId);
}
if (entry.ParentMatchupId.HasValue)
{
entry.ParentMatchup = previousRound?.Matchups.First(m => m.Id == entry.ParentMatchupId);
}
}
}
previousRound = currentRound;
}
}
}
return(output);
}
public void CreateTeam(TeamModel model)
{
using (IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(ConnectionDatabase)))
{
var p = new DynamicParameters();
p.Add("@TeamName", model.TeamName);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
dbConnection.Execute("dbo.spTeams_Insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@id");
foreach (PersonModel member in model.TeamMembers)
{
p = new DynamicParameters();
p.Add("@TeamId", model.Id);
p.Add("@PersonId", member.Id);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
dbConnection.Execute("dbo.spTeamMembers_Insert", p, commandType: CommandType.StoredProcedure);
}
}
}
public void CreateTournament(TournamentModel model)
{
using (IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(ConnectionDatabase)))
{
SaveTournament(dbConnection);
SaveTournamentPrizes(dbConnection);
SaveTournamentEntries(dbConnection);
SaveTournamentRounds(dbConnection);
}
void SaveTournament(IDbConnection dbConnection)
{
var p = new DynamicParameters();
p.Add("@TournamentName", model.TournamentName);
p.Add("@EntryFee", model.EntryFee);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
dbConnection.Execute("dbo.spTournaments_Insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@id");
}
void SaveTournamentPrizes(IDbConnection dbConnection)
{
foreach (PrizeModel prize in model.Prizes)
{
var p = new DynamicParameters();
p.Add("@TournamentId", model.Id);
p.Add("@PrizeId", prize.Id);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
dbConnection.Execute("dbo.spTournamentPrizes_Insert", p, commandType: CommandType.StoredProcedure);
}
}
void SaveTournamentEntries(IDbConnection dbConnection)
{
foreach (TeamModel team in model.EnteredTeams)
{
var p = new DynamicParameters();
p.Add("@TournamentId", model.Id);
p.Add("@TeamId", team.Id);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
dbConnection.Execute("dbo.spTournamentEntries_Insert", p, commandType: CommandType.StoredProcedure);
}
}
void SaveTournamentRounds(IDbConnection dbConnection)
{
foreach (RoundModel round in model.Rounds)
{
var p = new DynamicParameters();
p.Add("@TournamentId", model.Id);
p.Add("@Number", round.Number);
p.Add("@Active", round.Active);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
dbConnection.Execute("dbo.spRounds_Insert", p, commandType: CommandType.StoredProcedure);
round.Id = p.Get <int>("@id");
foreach (MatchupModel matchup in round.Matchups)
{
p = new DynamicParameters();
p.Add("@RoundId", round.Id);
p.Add("@WinnerId", matchup.Winner?.Id);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
dbConnection.Execute("dbo.spMatchups_Insert", p, commandType: CommandType.StoredProcedure);
matchup.Id = p.Get <int>("@id");
foreach (MatchupEntryModel matchupEntry in matchup.Entries)
{
p = new DynamicParameters();
p.Add("@MatchupId", matchup.Id);
p.Add("@ParentMatchupId", matchupEntry.ParentMatchup?.Id);
p.Add("@TeamCompetingId", matchupEntry.TeamCompeting?.Id);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
dbConnection.Execute("dbo.spMatchupEntries_Insert", p, commandType: CommandType.StoredProcedure);
}
}
}
}
}
public List <TournamentModel> GetTournaments()
{
List <TournamentModel> output = new List <TournamentModel>();
using (IDbConnection connection = new MySqlConnection(GlobalConfig.GetConnectionString(db)))
{
output = connection.Query <TournamentModel>("tournaments_all_active").ToList();
DynamicParameters p;
foreach (TournamentModel tournament in output)
{
p = new DynamicParameters();
p.Add("id_filter", tournament.Id);
// Populate Teams
tournament.EnteredTeams = connection.Query <TeamModel>(
"teams_by_tournament", p, commandType: CommandType.StoredProcedure).ToList();
DynamicParameters tp;
foreach (TeamModel team in tournament.EnteredTeams)
{
tp = new DynamicParameters();
tp.Add("selected_team", team.Id);
team.TeamMembers = connection.Query <PersonModel>(
"team_members_by_team", tp, commandType: CommandType.StoredProcedure).ToList();
}
// Populate Prizes
tournament.Prizes = connection.Query <PrizeModel>(
"prizes_by_tournament", p, commandType: CommandType.StoredProcedure).ToList();
// Populate Rounds
List <MatchupModel> matchups = connection.Query <MatchupModel>(
"matchups_by_tournament", p, commandType: CommandType.StoredProcedure).ToList();
foreach (MatchupModel m in matchups)
{
List <TeamModel> allTeams = GetTeams();
if (m.WinnerId > 0)
{
// Get the winning team by id from allTeams
foreach (TeamModel team in allTeams)
{
if (team.Id == m.WinnerId)
{
m.Winner = team;
break;
}
}
}
// Fill out the entries
p = new DynamicParameters();
p.Add("matchup", m.Id);
m.Entries = connection.Query <MatchupEntryModel>(
"matchup_entries_by_matchup", p, commandType: CommandType.StoredProcedure).ToList();
foreach (MatchupEntryModel me in m.Entries)
{
if (me.TeamCompetingId > 0)
{
// Get the competing team by id from allTeam
foreach (TeamModel team in allTeams)
{
if (team.Id == me.TeamCompetingId)
{
me.TeamCompeting = team;
break;
}
}
}
if (me.ParentMatchupId > 0)
{
// Get the parent matchup by id from matchups
foreach (MatchupModel matchup in matchups)
{
if (matchup.Id == me.ParentMatchupId)
{
me.ParentMatchup = matchup;
break;
}
}
}
}
}
List <MatchupModel> round = new List <MatchupModel>();
int currRound = 1;
foreach (MatchupModel m in matchups)
{
if (m.MatchupRound > currRound)
{
// Store the current round and start on the next round of matchups
tournament.Rounds.Add(round);
round = new List <MatchupModel>();
currRound++;
}
round.Add(m);
}
tournament.Rounds.Add(round); // store the last round of matchups
}
}
return(output);
}