public static MySqlConnection getMySqlConnection()
{
if (instance == null)
{
instance = new MySqlConnection(GlobalConfig.ConnectionString(dbName));
return(instance);
}
return(instance);
}
// TODO - Make the CreatePrize Method actually save to the database
/// <summary>
/// Saves a new prize to the database
/// </summary>
/// <param name="model">
/// The prize information
/// </param>
/// <returns>
/// The prize information, including the unique identifier.
/// </returns>
public PrizeModel CreatePrize(PrizeModel model)
{
using (IDbConnection connection = new SqlConnection(GlobalConfig.ConnectionString("Tournaments")))
{
var p = new DynamicParameters();
p.Add("@PlaceNumber", model.PlaceNumber);
p.Add("@PlaceName", model.PlaceName);
p.Add("@PrizeAmount", model.PrizeAmount);
p.Add("@PrizePercentage", model.PrizePercentage);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spPrizes_Insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@id");
return(model);
}
}
public void CreateTournament(TournamentModel model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
SaveTournament(connection, model);
SaveTournamentPrizes(connection, model);
SaveTournamentEntries(connection, model);
SaveTournamentRounds(connection, model);
}
}
public TeamModel CreateTeam(TeamModel model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("@TeamName", model.TeamName);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spTeams_Insert", p, commandType: CommandType.StoredProcedure);
model.id = p.Get <int>("@id");
foreach (PersonModel person in model.TeamMembers)
{
p = new DynamicParameters();
p.Add("@TeamName", model.id);
p.Add("PersonID", person.id);
connection.Execute("dbo.spTeamMembers_Insert", p, commandType: CommandType.StoredProcedure);
}
return(model);
}
}
public List <TournamentModel> GetTournament_All()
{
List <TournamentModel> output;
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
output = connection.Query <TournamentModel>("dbo.spTournament_GetAll").ToList();
var p = new DynamicParameters();
foreach (TournamentModel tournament in output)
{
p = new DynamicParameters();
p.Add("@TournamentID", tournament.id);
tournament.Prizes = connection.Query <PrizeModel>("dbo.spPrizes_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
p = new DynamicParameters();
p.Add("@TournamentID", tournament.id);
tournament.EnteredTeams = connection.Query <TeamModel>("dbo.spTeam_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
foreach (TeamModel team in tournament.EnteredTeams)
{
p = new DynamicParameters();
p.Add("@TeamID", team.id);
team.TeamMembers = connection.Query <PersonModel>("dbo.spTeamMembers_GetByTeam", p, commandType: CommandType.StoredProcedure).ToList();
}
p = new DynamicParameters();
p.Add("@TournamentID", tournament.id);
List <MatchupModel> matchups = connection.Query <MatchupModel>("dbo.spMatchups_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
foreach (MatchupModel matchup in matchups)
{
p = new DynamicParameters();
p.Add("@MatchupID", matchup.id);
matchup.Entries = connection.Query <MatchupEntryModel>("dbo.spMatchupEntries_GetByMatchup", p, commandType: CommandType.StoredProcedure).ToList();
List <TeamModel> allTeams = GetTeam_All();
if (matchup.WinnerID > 0)
{
matchup.Winner = allTeams.Where(x => x.id == matchup.WinnerID).First();
}
foreach (var matchupEntry in matchup.Entries)
{
if (matchupEntry.TeamCompetingID > 0)
{
matchupEntry.TeamCompeting = allTeams.Where(x => x.id == matchupEntry.TeamCompetingID).First();
}
if (matchupEntry.ParentMatchupID > 0)
{
matchupEntry.ParentMatchup = matchups.Where(x => x.id == matchupEntry.ParentMatchupID).First();
}
}
}
List <MatchupModel> currentRow = new List <MatchupModel>();
int currentRound = 1;
foreach (MatchupModel matchup in matchups)
{
if (matchup.MatchupRound > currentRound)
{
tournament.Rounds.Add(currentRow);
currentRow = new List <MatchupModel>();
currentRound += 1;
}
currentRow.Add(matchup);
}
tournament.Rounds.Add(currentRow);
}
}
return(output);
}
public List <TeamModel> GetTeam_All()
{
List <TeamModel> output;
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(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 List <PersonModel> GetPerson_All()
{
List <PersonModel> output;
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
output = connection.Query <PersonModel>("dbo.spPeople_GetAll").ToList();
}
return(output);
}
public PersonModel CreatePerson(PersonModel model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("@FirstName", model.FirstName);
p.Add("@LastName", model.LastName);
p.Add("@Email", model.Email);
p.Add("@Cellphone", model.Cellphone);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spPeople_Insert", p, commandType: CommandType.StoredProcedure);
model.id = p.Get <int>("@id");
return(model);
}
}
public void UpdateMatchup(MatchupModel model)
{
// spMatchups_Update
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
var p = new DynamicParameters();
if (model.Winner != null)
{
p.Add("@id", model.Id);
p.Add("@WinnerId", model.Winner.Id);
connection.Execute("dbo.spMatchups_Update", p, commandType: CommandType.StoredProcedure);
}
foreach (MatchupEntryModel me in model.Entries)
{
if (me.TeamCompeting != null)
{
p = new DynamicParameters();
p.Add("@id", me.Id);
p.Add("@TeamCompetingId", me.TeamCompeting.Id);
p.Add("@Score", me.Score);
connection.Execute("dbo.spMatchupEntries_Update", p, commandType: CommandType.StoredProcedure);
}
}
}
}
public void CompleteTournament(TournamentModel model)
{
// spTournaments_Complete
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("@id", model.Id);
connection.Execute("dbo.spTournaments_Complete", p, commandType: CommandType.StoredProcedure);
}
}
public List <TournamentModel> GetTournament_All()
{
List <TournamentModel> output;
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
output = connection.Query <TournamentModel>("dbo.spTournaments_GetAll").ToList();
var p = new DynamicParameters();
foreach (TournamentModel tournament in output)
{
// Populate Prizes
p = new DynamicParameters();
p.Add("@TournamentId", tournament.Id);
tournament.Prizes = connection.Query <PrizeModel>("dbo.spPrizes_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
// Populate Teams
p = new DynamicParameters();
p.Add("@TournamentId", tournament.Id);
tournament.EnteredTeams = connection.Query <TeamModel>("dbo.spTeams_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
foreach (TeamModel team in tournament.EnteredTeams)
{
p = new DynamicParameters();
p.Add("@TeamId", team.Id);
team.TeamMembers = connection.Query <PersonModel>("dbo.spTeamMembers_GetByTeam", p, commandType: CommandType.StoredProcedure).ToList();
}
p = new DynamicParameters();
p.Add("@TournamentId", tournament.Id);
// Populate Rounds
List <MatchupModel> matchups = connection.Query <MatchupModel>("dbo.spMatchups_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
foreach (MatchupModel m in matchups)
{
p = new DynamicParameters();
p.Add("@MatchupId", m.Id);
m.Entries = connection.Query <MatchupEntryModel>("dbo.spMatchupEntries_GetByMatchup", p, commandType: CommandType.StoredProcedure).ToList();
// Populate each entry (2 models)
// Populate each matchup (1 model)
List <TeamModel> allTeams = GetTeam_All();
if (m.WinnerId > 0)
{
m.Winner = allTeams.Where(x => x.Id == m.WinnerId).First();
}
foreach (var me in m.Entries)
{
if (me.TeamCompetingId > 0)
{
me.TeamCompeting = allTeams.Where(x => x.Id == me.TeamCompetingId).First();
}
if (me.ParentMatchupId > 0)
{
me.ParentMatchup = matchups.Where(x => x.Id == me.ParentMatchupId).First();
}
}
}
List <MatchupModel> currRow = new List <MatchupModel>();
int currRound = 1;
foreach (MatchupModel m in matchups)
{
if (m.MatchupRound > currRound)
{
tournament.Rounds.Add(currRow);
currRow = new List <MatchupModel>();
currRound++;
}
currRow.Add(m);
}
tournament.Rounds.Add(currRow);
}
}
return(output);
}
/// <summary>
/// Create the Team Model - passing the TeamName and retrieving Id as output
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public TeamModel CreateTeam(TeamModel model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
// All the parameters are added in order to pass in later on
var q = new DynamicParameters();
q.Add("@TeamName", model.TeamName);
q.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
// The parameters will be passed in and the stored procedure will be executed
connection.Execute("dbo.spTeams_Insert", q, commandType: CommandType.StoredProcedure);
/* This will look into the dynamic parameter list p,
* / and find @id parameter of type int */
model.Id = q.Get <int>("@id");
foreach (PersonModel teamMember in model.TeamMembers)
{
var t = new DynamicParameters();
t.Add("@TeamId", model.Id);
t.Add("@PersonId", teamMember.Id);
connection.Execute("dbo.spTeamMembers_Insert", t, commandType: CommandType.StoredProcedure);
}
return(model);
}
}
/// <summary>
/// Saves a new prize to the SQL database
/// </summary>
/// <param name="model">The prize infromation</param>
/// <returns>The prize information, including the unique identifier</returns>
public PrizeModel CreatePrize(PrizeModel model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
// All the parameters are added in order to pass in later on
var p = new DynamicParameters();
p.Add("@PlaceNumber", model.PlaceNumber);
p.Add("@PlaceName", model.PlaceName);
p.Add("@PrizeAmount", model.PrizeAmount);
p.Add("@PrizePercentage", model.PrizePercentage);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
// The parameters will be passed in and the stored procedure will be executed
connection.Execute("dbo.spPrizes_Insert", p, commandType: CommandType.StoredProcedure);
/* This will look into the dynamic parameter list p,
* / and find @id parameter of type int */
model.Id = p.Get <int>("@id");
return(model);
}
}
public PersonModel Createperson(PersonModel model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
// All the parameters are added in order to pass in later on
var p = new DynamicParameters();
p.Add("@FirstName", model.FirstName);
p.Add("@LastName", model.LastName);
p.Add("@EmailAddress", model.EmailAddress);
p.Add("@CellPhoneNumber", model.CellPhoneNumber);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
// The parameters will be passed in and the stored procedure will be executed
connection.Execute("dbo.spPeople_Insert", p, commandType: CommandType.StoredProcedure);
/* This will look into the dynamic parameter list p,
* / and find @id parameter of type int */
model.Id = p.Get <int>("@id");
return(model);
}
}
//Make the CreatePrize method save to the database
/// <summary>
/// Saves a new prize to the database
/// </summary>
/// <param name="model"> The prize information</param>
/// <returns> The prize information with the unique identifier </returns>
public void CreatePrize(PrizeModel model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db)))
{
var p = new DynamicParameters();
p.Add("@Placenumber", model.PlaceNumber);
p.Add("@PlaceName", model.PlaceName);
p.Add("@PrizeAmount", model.PrizeAmount);
p.Add("@PrizePercentage", model.PrizePercentage);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output); //Return the id as an output
connection.Execute("dbo.spPrizes_Insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@id");
}
}