/// <summary> /// Persists Team. /// </summary> /// <param name="team">The team.</param> /// <returns>TeamId given to the persisted team</returns> public int CreateTeam(Team team) { _DbPlayer = new DBPlayer(); TransactionOptions to = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) { using (SqlConnection connection = DBConnection.GetSqlConnection()) { connection.Open(); using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "INSERT INTO Team (Name) OUTPUT INSERTED.ID VALUES(@Name)"; command.Parameters.AddWithValue("Name", team.Name); team.Id = (int)command.ExecuteScalar(); if (team != null) { if (team.Players != null) { foreach (Player p in team.Players) { _DbPlayer.EditPlayer(p); } } } } connection.Close(); } scope.Complete(); } return(team.Id); }
/// <summary> /// Gets all teams except free agents. /// </summary> /// <returns>List of Teams</returns> public IEnumerable <Team> GetTeams() { List <Team> teams = new List <Team>(); _DbPlayer = new DBPlayer(); TransactionOptions to = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) { using (SqlConnection connection = DBConnection.GetSqlConnection()) { connection.Open(); using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT Id, Name FROM Team"; var reader = command.ExecuteReader(); while (reader.Read()) { Team team = new Team { Id = reader.GetInt32(reader.GetOrdinal("Id")), Name = reader.GetString(reader.GetOrdinal("Name")) }; if (team.Id > 1) { teams.Add(team); } } reader.Close(); foreach (Team t in teams) { if (t != null) { t.Players = _DbPlayer.GetPlayersByTeam(t); foreach (Player p in t.Players) { p.TeamId = t.Id; } } } } connection.Close(); } scope.Complete(); } return(teams); }
/// <summary> /// Gets the teams in a given match. /// </summary> /// <param name="match">The match.</param> /// <returns>List of teams</returns> public List <Team> GetTeamsByMatch(Match match) { List <Team> teams = new List <Team>(); _DbPlayer = new DBPlayer(); TransactionOptions to = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) { using (SqlConnection connection = DBConnection.GetSqlConnection()) { connection.Open(); using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT t.Id, t.Name FROM Team t INNER JOIN TeamsInMatch tim ON t.Id = tim.TeamId WHERE tim.MatchId = @MatchId"; command.Parameters.AddWithValue("MatchId", match.Id); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Team team = new Team { Id = reader.GetInt32(reader.GetOrdinal("Id")), Name = reader.GetString(reader.GetOrdinal("Name")) }; teams.Add(team); } reader.Close(); foreach (Team t in teams) { if (t != null) { t.Players = _DbPlayer.GetPlayersByTeam(t); foreach (Player p in t.Players) { p.TeamId = t.Id; } } } } connection.Close(); } scope.Complete(); } return(teams); }
/// <summary> /// Gets the team via a unique identifier. /// </summary> /// <param name="id">The identifier.</param> /// <returns>Found team with players</returns> public Team GetTeam(int id) { Team team = null; _DbPlayer = new DBPlayer(); TransactionOptions to = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) { using (SqlConnection connection = DBConnection.GetSqlConnection()) { connection.Open(); using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT Id, Name FROM Team WHERE Id=@id"; command.Parameters.AddWithValue("Id", id); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { team = new Team { Id = reader.GetInt32(reader.GetOrdinal("Id")), Name = reader.GetString(reader.GetOrdinal("Name")) }; } reader.Close(); if (team != null) { team.Players = _DbPlayer.GetPlayersByTeam(team); } } connection.Close(); } scope.Complete(); } return(team); }
/// <summary> /// Gets the bets from a given User. /// </summary> /// <param name="user">The user.</param> /// <returns>List of Bets</returns> public List <Bet> GetBets(User user) { List <Bet> betsList = new List <Bet>(); Bet bet = null; int typeId = -1; int bomId = -1; int teamId = -1; string typeString = ""; SqlDataReader reader; //Set transaction options with isolation level TransactionOptions options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }; //Create transaction scope with options using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { //Create sqlconnection and open it using (SqlConnection conn = DBConnection.GetSqlConnection()) { conn.Open(); //Create sqlcommand using (SqlCommand cmd = conn.CreateCommand()) { //Get all bets on user from the database cmd.CommandText = "SELECT b.id, b.amount, b.odds, b.Verified FROM Bet b, [User] u WHERE b.UserId = u.Id AND b.UserId = @userId"; cmd.Parameters.AddWithValue("userId", user.Id); reader = cmd.ExecuteReader(); while (reader.Read()) { bet = new Bet { Id = int.Parse(reader["Id"].ToString()), Amount = decimal.Parse(reader["Amount"].ToString()), Odds = decimal.Parse(reader["Odds"].ToString()), Verified = bool.Parse(reader["Verified"].ToString()) }; betsList.Add(bet); } reader.Close(); foreach (Bet b in betsList) { if (b != null) { typeString = ""; cmd.Parameters.Clear(); cmd.CommandText = "SELECT e.eventId AS eventId, m.matchId AS matchId, t.teamId AS teamId, p.playerId AS playerId, tobom.TeamId as team2Id FROM bet AS b LEFT JOIN betsonevent AS e ON e.betid = b.Id LEFT JOIN BetsOnMatch AS m ON m.betid = b.Id LEFT JOIN betsonteam AS t ON t.betid = b.Id LEFT JOIN TeamsOnBetsOnMatch as tobom on m.Id = tobom.BomId LEFT JOIN betsonplayer AS p ON p.betid = b.Id WHERE b.id = @betId"; cmd.Parameters.AddWithValue("betId", b.Id); reader = cmd.ExecuteReader(); if (reader.Read()) { if (!string.IsNullOrEmpty(reader["matchId"].ToString())) { typeId = int.Parse(reader["matchId"].ToString()); bomId = int.Parse(reader["team2Id"].ToString()); typeString = "matchId"; } else if (!string.IsNullOrEmpty(reader["eventId"].ToString())) { typeId = int.Parse(reader["eventId"].ToString()); typeString = "eventId"; } else if (!string.IsNullOrEmpty(reader["teamId"].ToString())) { typeId = int.Parse(reader["teamId"].ToString()); typeString = "teamId"; } else if (!string.IsNullOrEmpty(reader["playerId"].ToString())) { typeId = int.Parse(reader["playerId"].ToString()); typeString = "playerId"; } } reader.Close(); if (!typeString.Equals("")) { if (typeString.Equals("matchId")) { reader.Close(); DBTeam dBTeam = new DBTeam(); Team team = dBTeam.GetTeam(bomId); DBMatch dBMatch = new DBMatch(); Match match = dBMatch.GetMatch(typeId); b.Type = match; b.WinCondition = team; } else if (typeString.Equals("eventId")) { DBEvent dBEvent = new DBEvent(); Event betEvent = dBEvent.GetEvent(typeId); b.Type = betEvent; } else if (typeString.Equals("teamId")) { DBTeam dBTeam = new DBTeam(); Team team = dBTeam.GetTeam(typeId); b.Type = team; } else if (typeString.Equals("playerId")) { DBPlayer dBPlayer = new DBPlayer(); Player player = dBPlayer.GetPlayer(typeId); b.Type = player; } } } } //Close connection for good measure conn.Close(); } //Close scope for good measure scope.Complete(); } return(betsList); } }