/// <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); }