/// <summary> /// Edits information about the event. /// </summary> /// <param name="eventToUpdate">The event to update.</param> public void EditEvent(Event eventToUpdate) { _DbMatch = new DBMatch(); 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 = "UPDATE Event SET Name = @Name, GameName = @GameName, Type = @Type WHERE id = @id"; command.Parameters.AddWithValue("id", eventToUpdate.Id); command.Parameters.AddWithValue("Name", eventToUpdate.Name); command.Parameters.AddWithValue("GameName", eventToUpdate.GameName); command.Parameters.AddWithValue("Type", eventToUpdate.Type); command.ExecuteNonQuery(); if (eventToUpdate != null) { foreach (Match m in eventToUpdate.Matches) { _DbMatch.EditMatch(m); } } } connection.Close(); } scope.Complete(); } }
/// <summary> /// Deletes the event given by the parameter. /// </summary> /// <param name="eventToDelete">The event to delete.</param> public void DeleteEvent(Event eventToDelete) { _DbMatch = new DBMatch(); 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 = "DELETE FROM Event WHERE Id=@id; DBCC CHECKIDENT (Event, RESEED);"; command.Parameters.AddWithValue("id", eventToDelete.Id); command.ExecuteNonQuery(); if (eventToDelete != null) { foreach (Match m in eventToDelete.Matches) { _DbMatch.DeleteMatch(m); } } } connection.Close(); } scope.Complete(); } }
/// <summary> /// Gets All events. /// </summary> /// <returns>List of Events</returns> public IEnumerable <Event> GetEvents() { List <Event> events = new List <Event>(); List <int> matchIdList = new List <int>(); _DbMatch = new DBMatch(); Event eventToAdd = null; 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, GameName, Type FROM Event"; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { eventToAdd = new Event { Id = reader.GetInt32(reader.GetOrdinal("Id")), Name = reader.GetString(reader.GetOrdinal("Name")), GameName = reader.GetString(reader.GetOrdinal("GameName")), Type = reader.GetString(reader.GetOrdinal("Type")) }; events.Add(eventToAdd); } reader.Close(); command.Parameters.Clear(); foreach (Event e in events) { if (e != null) { e.Matches = _DbMatch.GetMatchesByEvent(e); } } } connection.Close(); } scope.Complete(); } return(events); }
/// <summary> /// Gets an event by unique identifier. /// </summary> /// <param name="id">The identifier.</param> /// <returns>The found event</returns> public Event GetEvent(int id) { _DbMatch = new DBMatch(); Event foundEvent = null; 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, GameName, Type FROM Event WHERE Id=@id"; command.Parameters.AddWithValue("Id", id); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { foundEvent = new Event { Id = reader.GetInt32(reader.GetOrdinal("Id")), Name = reader.GetString(reader.GetOrdinal("Name")), GameName = reader.GetString(reader.GetOrdinal("GameName")), Type = reader.GetString(reader.GetOrdinal("Type")) }; } reader.Close(); if (foundEvent != null) { foundEvent.Matches = _DbMatch.GetMatchesByEvent(foundEvent); } } connection.Close(); } scope.Complete(); } return(foundEvent); }
/// <summary> /// Persists Event object /// </summary> /// <param name="eventToCreate">The event to create.</param> /// <returns>EventId given to the persisted event</returns> public int CreateEvent(Event eventToCreate) { _DbMatch = new DBMatch(); int eventId = 0; 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 Event (Name, GameName, Type) OUTPUT INSERTED.ID VALUES(@Name, @GameName, @Type)"; command.Parameters.AddWithValue("Name", eventToCreate.Name); command.Parameters.AddWithValue("GameName", eventToCreate.GameName); command.Parameters.AddWithValue("Type", eventToCreate.Type); eventId = (int)command.ExecuteScalar(); eventToCreate.Id = eventId; if (eventToCreate != null) { if (eventToCreate.Matches != null) { foreach (Match m in eventToCreate.Matches) { m.Id = eventToCreate.Id; _DbMatch.CreateMatch(m); } } } } connection.Close(); } scope.Complete(); } return(eventId); }
/// <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); } }