/* request the number of championships for each city in database for a client query */ public void getCitiesChampionshipsNum(bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); if (delay) { ManualResetEvent delayEvent = new ManualResetEvent(false); ThreadPool.QueueUserWorkItem(new WaitCallback((_) => { sleep(); delayEvent.Set(); })); delayEvent.WaitOne(); } using (var db = new TTTDataClassesDataContext()) { var x = db.Championships.GroupBy(c => c.City); CityChampionships[] citiesChmps = new CityChampionships[x.Count()]; int i = 0; foreach (var cc in x) { citiesChmps[i] = new CityChampionships(); citiesChmps[i].City = cc.Key; citiesChmps[i++].NumberOfChampionships = cc.Count(); } channel.sendCitiesChampionshipsNum(citiesChmps); } }
private ChampionshipData[] getAllChampionships(int playerId = -1) { using (var db = new TTTDataClassesDataContext()) { IEnumerable <Championship> x = null; if (playerId == -1) { x = db.Championships; } else { var y = db.PlayerChampionships.Where(pc => pc.PlayerId == playerId); x = db.Championships.Where(c => y.Any(pc => pc.ChampionshipId == c.Id)); } ChampionshipData[] chmps = new ChampionshipData[x.Count()]; int i = 0; foreach (var c in x) { chmps[i++] = getChampionshipData(c); } return(chmps); } }
/* request all players of a specific championship from database for a client query */ public void getChampionshipPlayers(ChampionshipData chmp, bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); if (delay) { ManualResetEvent delayEvent = new ManualResetEvent(false); ThreadPool.QueueUserWorkItem(new WaitCallback((_) => { sleep(); delayEvent.Set(); })); delayEvent.WaitOne(); } using (var db = new TTTDataClassesDataContext()) { var x = db.PlayerChampionships.Where(pc => pc.ChampionshipId == chmp.Id); PlayerData[] players = new PlayerData[x.Count()]; int i = 0; foreach (var pc in x) { players[i++] = getPlayerDataById(pc.PlayerId, db); } channel.sendPlayers(players, "Q"); } }
/* request to insert a game to database */ public void insertGameToDB(Game game) { using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { string sql = ""; if (game.Winner.HasValue) { sql = string.Format("Insert into Games(Player1, Player2, Winner, BoardSize, Moves, StartTime, EndTime) " + "values({0}, {1}, {2}, {3}, '{4}', '{5}', '{6}')", game.Player1, game.Player2, game.Winner, game.BoardSize, game.Moves, game.StartTime, game.EndTime); } else { sql = string.Format("Insert into Games(Player1, Player2, BoardSize, Moves, StartTime, EndTime) " + "values({0}, {1}, {2}, '{3}', '{4}', '{5}')", game.Player1, game.Player2, game.BoardSize, game.Moves, game.StartTime, game.EndTime); } SqlCommand cmd = new SqlCommand(sql, con); try { con.Open(); cmd.ExecuteNonQuery(); con.Close(); } catch (Exception) { } } } }
/* delete the specific championship from database */ public void deleteChampionship(ChampionshipData chmp) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { try { bool success = deleteChampionshipDependencies(chmp.Id, db, con); if (!success) { throw new Exception(); } string sql = string.Format("delete from Championships where Id={0}", chmp.Id);; SqlCommand cmd = new SqlCommand(sql, con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); channel.updateSuccess(); } catch (Exception) { channel.updateError("Some error occured while deleting from database"); } } } }
/* request the number of games for each player in database for a client query */ public void getPlayersGamesNum(bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); if (delay) { ManualResetEvent delayEvent = new ManualResetEvent(false); ThreadPool.QueueUserWorkItem(new WaitCallback((_) => { sleep(); delayEvent.Set(); })); delayEvent.WaitOne(); } using (var db = new TTTDataClassesDataContext()) { PlayerGames[] playersGames = new PlayerGames[db.Players.Count() - 1]; int i = 0; foreach (var p in db.Players) { if (p.Id != SERVER) { playersGames[i] = new PlayerGames(); playersGames[i].Name = p.Id + " : " + p.FirstName; var x = db.Games.Where(g => g.Player1 == p.Id || g.Player2 == p.Id); playersGames[i++].NumberOfGames = x.Count(); } } channel.sendPlayersGamesNum(playersGames); } }
private List <int> getChampionshipMatches(string title, string value) { try { IEnumerable <Championship> chmps = null; using (var db = new TTTDataClassesDataContext()) { switch (title) { case "Id": chmps = db.Championships.Where(c => c.Id == int.Parse(value.Replace(" ", string.Empty))); break; case "City": chmps = db.Championships.Where(c => c.City.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "StartDate": chmps = db.Championships.Where(c => c.StartDate.Equals(value)); break; case "EndDate": chmps = db.Championships.Where(c => c.EndDate.Equals(value)); break; case "Picture": chmps = db.Championships.Where(c => c.Picture.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; } if (chmps != null && chmps.Count() > 0) { List <int> lst = new List <int>(); foreach (var p in chmps) { lst.Add(p.Id); } return(lst); } else { return(null); } } } catch (Exception) { return(null); } }
/* update the championships database with the changes received in the array */ public void updateChampionships(ChampionshipData[] chmps) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); foreach (var c in chmps) { string sql = "Update Championships SET City='" + c.City + "', StartDate='" + c.StartDate + "'"; if (c.EndDate != null) { sql += ", EndDate='" + c.EndDate + "'"; } else { sql += ", EndDate=NULL"; } if (c.Picture != null && c.Picture.Replace(" ", String.Empty).Length > 0) { sql += ", Picture='" + c.Picture + "'"; } else { sql += ", Picture=NULL"; } sql += " where Id=" + c.Id; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } con.Close(); channel.updateSuccess(); } catch (Exception) { channel.updateError("Some error occured while updating the database"); } } } }
private bool deletePlayerDependencies(int playerId, TTTDataClassesDataContext db, SqlConnection con) { if (removePlayerAdvisors(playerId, db, con) && deletePlayerGames(playerId, db, con) && deletePlayerChampionships(playerId, db, con)) { return(true); } else { return(false); } }
///////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////// #region Private Methods private PlayerData[] getAllFreeAdvisors() { using (var db = new TTTDataClassesDataContext()) { var x = db.Players.Where(p => p.IsAdvisor == 1 && !p.AdviseTo.HasValue); PlayerData[] players = new PlayerData[x.Count()]; int i = 0; foreach (var p in x) { players[i++] = getPlayerData(p, db); } return(players); } }
/* delete the players from database that matches the specific value in the specific column */ public void deletePlayers(PlayerData player, string title, string value) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); List <int> playerIds = getPlayerMatches(player, title, value); if (playerIds == null || playerIds.Count() < 1) { channel.updateError("There's no players with value '" + value + "' in field '" + title + "'"); return; } using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); int loggedInCount = 0; foreach (var id in playerIds) { if (isUserLogged(id)) { channel.updateError("Can't delete an online user [id=" + id + "]"); loggedInCount++; } else { bool success = deletePlayerDependencies(id, db, con); if (success) { cmd.CommandText = string.Format("delete from Players where Id={0}", id); cmd.ExecuteNonQuery(); } } } con.Close(); channel.updateSuccess(playerIds.Count() - loggedInCount + " players deleted"); } catch (Exception) { channel.updateError("Some error occured while deleting from database"); } } } }
private PlayerData[] getAllPlayersFromDB() { using (var db = new TTTDataClassesDataContext()) { var x = db.Players.Where(p => p.Id != SERVER); PlayerData[] players = new PlayerData[x.Count()]; int i = 0; foreach (var p in x) { players[i++] = getPlayerData(p, db); } return(players); } }
private bool deleteChampionshipDependencies(int chmpId, TTTDataClassesDataContext db, SqlConnection con) { string sql = string.Format("delete from PlayerChampionships where ChampionshipId={0}", chmpId); SqlCommand cmd = new SqlCommand(sql, con); try { cmd.ExecuteNonQuery(); return(true); } catch (Exception) { return(false); } }
private bool deletePlayerGames(int playerId, TTTDataClassesDataContext db, SqlConnection con) { string sql = string.Format("delete from Games where Player1={0} or Player2={0} or Winner={0}", playerId); SqlCommand cmd = new SqlCommand(sql, con); try { cmd.ExecuteNonQuery(); return(true); } catch (Exception) { return(false); } }
private bool removePlayerAdvisors(int playerId, TTTDataClassesDataContext db, SqlConnection con) { String sql = string.Format("Update Players SET AdviseTo=NULL where AdviseTo={0}", playerId); SqlCommand cmd = new SqlCommand(sql, con); try { cmd.ExecuteNonQuery(); return(true); } catch (Exception) { return(false); } }
private PlayerData getPlayerDataById(System.Nullable <int> id, TTTDataClassesDataContext db) { if (!id.HasValue || id < 1) { return(null); } var x = db.Players.Where(p => p.Id == id.Value); if (x.Count() < 1) { return(null); } else { return(getPlayerData(x.First(), db)); } }
/* request all players of a specific game from database for a client query */ public async void getGamePlayers(GameData game, bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); if (delay) { await Task <int> .Factory.StartNew(sleep); } using (var db = new TTTDataClassesDataContext()) { PlayerData[] players = new PlayerData[2]; players[0] = getPlayerDataById(game.Player1, db); players[1] = getPlayerDataById(game.Player2, db); channel.sendPlayers(players, "Q"); } }
/* delete the championships from database that matches the specific value in the specific column */ public void deleteChampionships(string title, string value) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); List <int> chmpsIds = getChampionshipMatches(title, value); if (chmpsIds == null || chmpsIds.Count() < 1) { channel.updateError("There's no championships with value '" + value + "' in field '" + title + "'"); return; } using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); foreach (var id in chmpsIds) { bool success = deleteChampionshipDependencies(id, db, con); if (success) { cmd.CommandText = string.Format("delete from Championships where Id={0}", id); cmd.ExecuteNonQuery(); } } con.Close(); channel.updateSuccess(chmpsIds.Count() + " championships deleted"); } catch (Exception) { channel.updateError("Some error occured while deleting from database"); } } } }
private bool isAdviseToChanged(PlayerData player, TTTDataClassesDataContext db) { PlayerData adviseTo = getPlayerDataById(player.AdviseTo, db); if (adviseTo == null) { if (player.AdviseTo_Name != null && !player.AdviseTo_Name.Equals("")) { return(true); } } else { if (!player.AdviseTo_Name.Equals(adviseTo.Id + " : " + adviseTo.FirstName)) { return(true); } } return(false); }
private PlayerData getPlayerData(Player p, TTTDataClassesDataContext db) { PlayerData player = new PlayerData(); player.Id = p.Id; player.FirstName = p.FirstName; player.LastName = p.LastName; player.City = p.City; player.Country = p.Country; player.Phone = p.Phone; player.IsAdvisor = p.IsAdvisor; player.Is_Advisor = (p.IsAdvisor == 1) ? "Yes" : "No"; if (p.AdviseTo.HasValue) { player.AdviseTo = p.AdviseTo.Value; Player adviseTo = db.Players.Where(pl => pl.Id == player.AdviseTo).First(); player.AdviseTo_Name = adviseTo.Id + " : " + adviseTo.FirstName; } return(player); }
/* register new player to database */ public void registerNewPlayer(PlayerData player, int[] advisors) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { string sql = string.Format("Insert into Players(FirstName, LastName, City, Country, Phone, IsAdvisor) " + "values('{0}', '{1}', '{2}', '{3}', '{4}', {5})", player.FirstName, player.LastName, player.City, player.Country, player.Phone, player.IsAdvisor); SqlCommand cmd = new SqlCommand(sql, con); try { con.Open(); cmd.ExecuteNonQuery(); if (advisors != null) { player.Id = (from p in db.Players select p.Id).Max(); for (var i = 0; i < advisors.Length; i++) { cmd.CommandText = string.Format("update Players set AdviseTo = {0} where Id = {1}", player.Id, advisors[i]); cmd.ExecuteNonQuery(); } } con.Close(); channel.showPlayerRegisterSuccess(); } catch (Exception e) { channel.showException(e); } } } }
/* register new player to database */ public void registerNewPlayer(PlayerData player, int[] advisors) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { string sql = string.Format("Insert into Players(FirstName, LastName, City, Country, Phone, IsAdvisor) " + "values('{0}', '{1}', '{2}', '{3}', '{4}', {5})", player.FirstName, player.LastName, player.City, player.Country, player.Phone, player.IsAdvisor); SqlCommand cmd = new SqlCommand(sql, con); try { con.Open(); cmd.ExecuteNonQuery(); if (advisors != null) { player.Id = (from p in db.Players select p.Id).Max(); for (var i = 0; i < advisors.Length; i++) { cmd.CommandText = string.Format("update Players set AdviseTo = {0} where Id = {1}", player.Id, advisors[i]); cmd.ExecuteNonQuery(); } } con.Close(); channel.showPlayerRegisterSuccess(); } catch (Exception e) { channel.showException(e); } } } }
/* delete the specific player from database */ public void deletePlayer(PlayerData player) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); if (isUserLogged(player.Id)) { channel.updateError("Can't delete an online user"); return; } using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { try { bool success = deletePlayerDependencies(player.Id, db, con); if (!success) { throw new Exception(); } string sql = string.Format("delete from Players where Id={0}", player.Id); SqlCommand cmd = new SqlCommand(sql, con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); channel.updateSuccess(); } catch (Exception) { channel.updateError("Some error occured while deleting from database"); } } } }
/* register a player to championship(s) */ public void registerPlayerToChamp(PlayerData player, ChampionshipData[] chmps) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); if (player == null || chmps == null) { channel.registerPlayerToChampError("Error: data is corrupted"); return; } using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); for (var i = 0; i < chmps.Length; i++) { if (!isPlayerRegisteredToChamp(player, chmps[i], db)) { cmd.CommandText = string.Format("Insert into PlayerChampionships(PlayerId, ChampionshipId) " + "values({0}, {1})", player.Id, chmps[i].Id); cmd.ExecuteNonQuery(); } } con.Close(); channel.registerPlayerToChampSuccess(); } catch (Exception e) { channel.showException(e); } } } }
private GameData[] getAllGamesFromDB(bool withPlayersNames, int playerId = -1) { using (var db = new TTTDataClassesDataContext()) { IEnumerable <Game> x = null; if (playerId == -1) { x = db.Games; } else { x = db.Games.Where(g => g.Player1 == playerId || g.Player2 == playerId); } GameData[] games = new GameData[x.Count()]; int i = 0; foreach (var g in x) { games[i] = getGameData(g); if (withPlayersNames) { PlayerData p = getPlayerDataById(games[i].Player1, db); games[i].Player1_Name = p.Id + " : " + p.FirstName; p = getPlayerDataById(games[i].Player2, db); games[i].Player2_Name = p.Id + " : " + p.FirstName; if (g.Winner.HasValue) { p = getPlayerDataById((int)games[i].Winner, db); games[i].Winner_Name = p.Id + " : " + p.FirstName; } } i++; } return(games); } }
/* request all advisors of a specific game from database for a client query */ public void getGameAdvisors(GameData game, bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); if (delay) { ManualResetEvent delayEvent = new ManualResetEvent(false); ThreadPool.QueueUserWorkItem(new WaitCallback((_) => { sleep(); delayEvent.Set(); })); delayEvent.WaitOne(); } using (var db = new TTTDataClassesDataContext()) { PlayerData player1 = getPlayerDataById(game.Player1, db); PlayerData player2 = getPlayerDataById(game.Player2, db); var a1 = getPlayerAdvisors(player1.Id, db); var a2 = getPlayerAdvisors(player2.Id, db); PlayerData[] advisors = new PlayerData[a1.Count() + a2.Count()]; int i = 0; foreach (var a in a1) { advisors[i] = getPlayerData(a, db); advisors[i++].AdviseTo_Name = player1.Id + " : " + player1.FirstName; } foreach (var a in a2) { advisors[i] = getPlayerData(a, db); advisors[i++].AdviseTo_Name = player2.Id + " : " + player2.FirstName; } channel.sendGameAdvisors(advisors); } }
/* register new championship to database */ public void registerNewChampionship(ChampionshipData champ) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { string sql; if (champ.EndDate == null) { sql = string.Format("Insert into Championships(City, StartDate, Picture) " + "values('{0}', '{1}', '{2}')", champ.City, champ.StartDate, champ.Picture); } else { sql = string.Format("Insert into Championships(City, StartDate, EndDate, Picture) " + "values('{0}', '{1}', '{2}', '{3}')", champ.City, champ.StartDate, champ.EndDate, champ.Picture); } SqlCommand cmd = new SqlCommand(sql, con); try { con.Open(); cmd.ExecuteNonQuery(); con.Close(); channel.showNewChampSuccess(); } catch (Exception e) { channel.showException(e); } } } }
/* register new championship to database */ public void registerNewChampionship(ChampionshipData champ) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { string sql; if (champ.EndDate == null) { sql = string.Format("Insert into Championships(City, StartDate, Picture) " + "values('{0}', '{1}', '{2}')", champ.City, champ.StartDate, champ.Picture); } else { sql = string.Format("Insert into Championships(City, StartDate, EndDate, Picture) " + "values('{0}', '{1}', '{2}', '{3}')", champ.City, champ.StartDate, champ.EndDate, champ.Picture); } SqlCommand cmd = new SqlCommand(sql, con); try { con.Open(); cmd.ExecuteNonQuery(); con.Close(); channel.showNewChampSuccess(); } catch (Exception e) { channel.showException(e); } } } }
///////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////// #region Private Methods private PlayerData[] getAllFreeAdvisors() { using (var db = new TTTDataClassesDataContext()) { var x = db.Players.Where(p => p.IsAdvisor == 1 && !p.AdviseTo.HasValue); PlayerData[] players = new PlayerData[x.Count()]; int i = 0; foreach (var p in x) { players[i++] = getPlayerData(p, db); } return players; } }
/* delete the specific championship from database */ public void deleteChampionship(ChampionshipData chmp) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { try { bool success = deleteChampionshipDependencies(chmp.Id, db, con); if (!success) throw new Exception(); string sql = string.Format("delete from Championships where Id={0}", chmp.Id); ; SqlCommand cmd = new SqlCommand(sql, con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); channel.updateSuccess(); } catch (Exception) { channel.updateError("Some error occured while deleting from database"); } } } }
/* delete the specific player from database */ public void deletePlayer(PlayerData player) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); if (isUserLogged(player.Id)) { channel.updateError("Can't delete an online user"); return; } using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { try { bool success = deletePlayerDependencies(player.Id, db, con); if (!success) throw new Exception(); string sql = string.Format("delete from Players where Id={0}", player.Id); SqlCommand cmd = new SqlCommand(sql, con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); channel.updateSuccess(); } catch (Exception) { channel.updateError("Some error occured while deleting from database"); } } } }
/* update the players database with the changes received in the array */ public void updatePlayers(PlayerData[] players) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); bool allAdviseToChangesSuccess = true; bool userLoggedIn = false; using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); foreach (var p in players) { userLoggedIn = isUserLogged(p.Id); if (!userLoggedIn) { string sql = ""; p.IsAdvisor = (p.Is_Advisor.Equals("Yes")) ? (byte)1 : (byte)0; if (p.IsAdvisor == 1) { bool adviseToChanged = isAdviseToChanged(p, db); bool updateAdviseTo = adviseToChanged; if (adviseToChanged) { if (p.AdviseTo_Name.Equals("")) { sql = string.Format("Update Players SET FirstName='{0}', " + "LastName='{1}', City='{2}', Country='{3}', Phone='{4}', IsAdvisor={5}, AdviseTo=NULL " + "where Id={6}", p.FirstName, p.LastName, p.City, p.Country, p.Phone, p.IsAdvisor, p.Id); } else { try { int id = int.Parse(p.AdviseTo_Name); p.AdviseTo = id; } catch (Exception) { allAdviseToChangesSuccess = false; updateAdviseTo = false; } if (updateAdviseTo) { sql = string.Format("Update Players SET FirstName='{0}', " + "LastName='{1}', City='{2}', Country='{3}', Phone='{4}', IsAdvisor={5}, AdviseTo={6} " + "where Id={7}", p.FirstName, p.LastName, p.City, p.Country, p.Phone, p.IsAdvisor, p.AdviseTo, p.Id); } } } if (!adviseToChanged || sql.Equals("")) { sql = string.Format("Update Players SET FirstName='{0}', " + "LastName='{1}', City='{2}', Country='{3}', Phone='{4}', IsAdvisor={5} " + "where Id={6}", p.FirstName, p.LastName, p.City, p.Country, p.Phone, p.IsAdvisor, p.Id); } } else { sql = string.Format("Update Players SET FirstName='{0}', " + "LastName='{1}', City='{2}', Country='{3}', Phone='{4}', IsAdvisor={5}, AdviseTo=NULL " + "where Id={6}", p.FirstName, p.LastName, p.City, p.Country, p.Phone, p.IsAdvisor, p.Id); } cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } con.Close(); } catch (SqlException) { channel.updateError("Database updated partially with some errors\nPerhaps" + " one or more 'AdviseTo' references didn't match a Player Id"); } catch (Exception) { channel.updateError("Some error occured while updating the database"); } } } if (!allAdviseToChangesSuccess) channel.updateError("Database updated, but one or more 'AdviseTo' references that didn't match a Player Id"); if (userLoggedIn) channel.updateError("Can't update a logged in user"); if (!userLoggedIn && allAdviseToChangesSuccess) channel.updateSuccess(); }
/* request the number of games for each player in database for a client query */ public void getPlayersGamesNum(bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); if (delay) { ManualResetEvent delayEvent = new ManualResetEvent(false); ThreadPool.QueueUserWorkItem(new WaitCallback((_) => { sleep(); delayEvent.Set(); })); delayEvent.WaitOne(); } using (var db = new TTTDataClassesDataContext()) { PlayerGames[] playersGames = new PlayerGames[db.Players.Count()-1]; int i = 0; foreach (var p in db.Players) { if (p.Id != SERVER) { playersGames[i] = new PlayerGames(); playersGames[i].Name = p.Id + " : " + p.FirstName; var x = db.Games.Where(g => g.Player1 == p.Id || g.Player2 == p.Id); playersGames[i++].NumberOfGames = x.Count(); } } channel.sendPlayersGamesNum(playersGames); } }
/* request all players of a specific championship from database for a client query */ public void getChampionshipPlayers(ChampionshipData chmp, bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); if (delay) { ManualResetEvent delayEvent = new ManualResetEvent(false); ThreadPool.QueueUserWorkItem(new WaitCallback((_) => { sleep(); delayEvent.Set(); })); delayEvent.WaitOne(); } using (var db = new TTTDataClassesDataContext()) { var x = db.PlayerChampionships.Where(pc => pc.ChampionshipId == chmp.Id); PlayerData[] players = new PlayerData[x.Count()]; int i = 0; foreach (var pc in x) { players[i++] = getPlayerDataById(pc.PlayerId, db); } channel.sendPlayers(players, "Q"); } }
private bool deletePlayerDependencies(int playerId, TTTDataClassesDataContext db, SqlConnection con) { if (removePlayerAdvisors(playerId, db, con) && deletePlayerGames(playerId, db, con) && deletePlayerChampionships(playerId, db, con)) return true; else return false; }
private bool isAdviseToChanged(PlayerData player, TTTDataClassesDataContext db) { PlayerData adviseTo = getPlayerDataById(player.AdviseTo, db); if (adviseTo == null) { if (player.AdviseTo_Name != null && !player.AdviseTo_Name.Equals("")) return true; } else { if (!player.AdviseTo_Name.Equals(adviseTo.Id + " : " + adviseTo.FirstName)) return true; } return false; }
private List <int> getPlayerMatches(PlayerData player, string title, string value) { try { IEnumerable <Player> players = null; using (var db = new TTTDataClassesDataContext()) { switch (title) { case "Id": players = db.Players.Where(p => p.Id == int.Parse(value)); break; case "FirstName": players = db.Players.Where(p => p.FirstName.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "LastName": players = db.Players.Where(p => p.LastName.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "City": players = db.Players.Where(p => p.City.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "Country": players = db.Players.Where(p => p.Country.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "Phone": players = db.Players.Where(p => p.Phone.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "Is_Advisor": int IsAdvisor = -1; if (value.Equals("Yes")) { IsAdvisor = 1; } else if (value.Equals("No")) { IsAdvisor = 0; } if (IsAdvisor != -1) { players = db.Players.Where(p => p.IsAdvisor == IsAdvisor); } break; case "AdviseTo_Name": players = db.Players.Where(p => p.AdviseTo == player.AdviseTo); break; } if (players != null && players.Count() > 0) { List <int> lst = new List <int>(); foreach (var p in players) { lst.Add(p.Id); } return(lst); } else { return(null); } } } catch (Exception) { return(null); } }
private bool isPlayerRegisteredToChamp(PlayerData player, ChampionshipData champ, TTTDataClassesDataContext db) { var x = from pc in db.PlayerChampionships where pc.PlayerId == player.Id && pc.ChampionshipId == champ.Id select pc; return x.Count() > 0; }
private PlayerData[] getAllPlayersFromDB() { using (var db = new TTTDataClassesDataContext()) { var x = db.Players.Where(p => p.Id != SERVER); PlayerData[] players = new PlayerData[x.Count()]; int i = 0; foreach (var p in x) { players[i++] = getPlayerData(p, db); } return players; } }
/* update the players database with the changes received in the array */ public void updatePlayers(PlayerData[] players) { ICallBack channel = OperationContext.Current.GetCallbackChannel <ICallBack>(); bool allAdviseToChangesSuccess = true; bool userLoggedIn = false; using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); foreach (var p in players) { userLoggedIn = isUserLogged(p.Id); if (!userLoggedIn) { string sql = ""; p.IsAdvisor = (p.Is_Advisor.Equals("Yes")) ? (byte)1 : (byte)0; if (p.IsAdvisor == 1) { bool adviseToChanged = isAdviseToChanged(p, db); bool updateAdviseTo = adviseToChanged; if (adviseToChanged) { if (p.AdviseTo_Name.Equals("")) { sql = string.Format("Update Players SET FirstName='{0}', " + "LastName='{1}', City='{2}', Country='{3}', Phone='{4}', IsAdvisor={5}, AdviseTo=NULL " + "where Id={6}", p.FirstName, p.LastName, p.City, p.Country, p.Phone, p.IsAdvisor, p.Id); } else { try { int id = int.Parse(p.AdviseTo_Name); p.AdviseTo = id; } catch (Exception) { allAdviseToChangesSuccess = false; updateAdviseTo = false; } if (updateAdviseTo) { sql = string.Format("Update Players SET FirstName='{0}', " + "LastName='{1}', City='{2}', Country='{3}', Phone='{4}', IsAdvisor={5}, AdviseTo={6} " + "where Id={7}", p.FirstName, p.LastName, p.City, p.Country, p.Phone, p.IsAdvisor, p.AdviseTo, p.Id); } } } if (!adviseToChanged || sql.Equals("")) { sql = string.Format("Update Players SET FirstName='{0}', " + "LastName='{1}', City='{2}', Country='{3}', Phone='{4}', IsAdvisor={5} " + "where Id={6}", p.FirstName, p.LastName, p.City, p.Country, p.Phone, p.IsAdvisor, p.Id); } } else { sql = string.Format("Update Players SET FirstName='{0}', " + "LastName='{1}', City='{2}', Country='{3}', Phone='{4}', IsAdvisor={5}, AdviseTo=NULL " + "where Id={6}", p.FirstName, p.LastName, p.City, p.Country, p.Phone, p.IsAdvisor, p.Id); } cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } con.Close(); } catch (SqlException) { channel.updateError("Database updated partially with some errors\nPerhaps" + " one or more 'AdviseTo' references didn't match a Player Id"); } catch (Exception) { channel.updateError("Some error occured while updating the database"); } } } if (!allAdviseToChangesSuccess) { channel.updateError("Database updated, but one or more 'AdviseTo' references that didn't match a Player Id"); } if (userLoggedIn) { channel.updateError("Can't update a logged in user"); } if (!userLoggedIn && allAdviseToChangesSuccess) { channel.updateSuccess(); } }
/* request all advisors of a specific game from database for a client query */ public void getGameAdvisors(GameData game, bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); if (delay) { ManualResetEvent delayEvent = new ManualResetEvent(false); ThreadPool.QueueUserWorkItem(new WaitCallback((_) => { sleep(); delayEvent.Set(); })); delayEvent.WaitOne(); } using (var db = new TTTDataClassesDataContext()) { PlayerData player1 = getPlayerDataById(game.Player1, db); PlayerData player2 = getPlayerDataById(game.Player2, db); var a1 = getPlayerAdvisors(player1.Id, db); var a2 = getPlayerAdvisors(player2.Id, db); PlayerData[] advisors = new PlayerData[a1.Count() + a2.Count()]; int i = 0; foreach (var a in a1) { advisors[i] = getPlayerData(a, db); advisors[i++].AdviseTo_Name = player1.Id + " : " + player1.FirstName; } foreach (var a in a2) { advisors[i] = getPlayerData(a, db); advisors[i++].AdviseTo_Name = player2.Id + " : " + player2.FirstName; } channel.sendGameAdvisors(advisors); } }
private bool removePlayerAdvisors(int playerId, TTTDataClassesDataContext db, SqlConnection con) { String sql = string.Format("Update Players SET AdviseTo=NULL where AdviseTo={0}", playerId); SqlCommand cmd = new SqlCommand(sql, con); try { cmd.ExecuteNonQuery(); return true; } catch (Exception) { return false; } }
private IEnumerable <Player> getPlayerAdvisors(int id, TTTDataClassesDataContext db) { return(db.Players.Where(p => p.AdviseTo == id)); }
private bool deletePlayerGames(int playerId, TTTDataClassesDataContext db, SqlConnection con) { string sql = string.Format("delete from Games where Player1={0} or Player2={0} or Winner={0}", playerId); SqlCommand cmd = new SqlCommand(sql, con); try { cmd.ExecuteNonQuery(); return true; } catch (Exception) { return false; } }
/* request the number of championships for each city in database for a client query */ public void getCitiesChampionshipsNum(bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); if (delay) { ManualResetEvent delayEvent = new ManualResetEvent(false); ThreadPool.QueueUserWorkItem(new WaitCallback((_) => { sleep(); delayEvent.Set(); })); delayEvent.WaitOne(); } using (var db = new TTTDataClassesDataContext()) { var x = db.Championships.GroupBy(c => c.City); CityChampionships[] citiesChmps = new CityChampionships[x.Count()]; int i = 0; foreach (var cc in x) { citiesChmps[i] = new CityChampionships(); citiesChmps[i].City = cc.Key; citiesChmps[i++].NumberOfChampionships = cc.Count(); } channel.sendCitiesChampionshipsNum(citiesChmps); } }
private bool deleteChampionshipDependencies(int chmpId, TTTDataClassesDataContext db, SqlConnection con) { string sql = string.Format("delete from PlayerChampionships where ChampionshipId={0}", chmpId); SqlCommand cmd = new SqlCommand(sql, con); try { cmd.ExecuteNonQuery(); return true; } catch (Exception) { return false; } }
/* update the championships database with the changes received in the array */ public void updateChampionships(ChampionshipData[] chmps) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); foreach (var c in chmps) { string sql = "Update Championships SET City='" + c.City + "', StartDate='" + c.StartDate + "'"; if (c.EndDate != null) sql += ", EndDate='" + c.EndDate + "'"; else sql += ", EndDate=NULL"; if (c.Picture != null && c.Picture.Replace(" ", String.Empty).Length > 0) sql += ", Picture='" + c.Picture + "'"; else sql += ", Picture=NULL"; sql += " where Id=" + c.Id; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } con.Close(); channel.updateSuccess(); } catch (Exception) { channel.updateError("Some error occured while updating the database"); } } } }
private List<int> getPlayerMatches(PlayerData player, string title, string value) { try { IEnumerable<Player> players = null; using (var db = new TTTDataClassesDataContext()) { switch (title) { case "Id": players = db.Players.Where(p => p.Id == int.Parse(value)); break; case "FirstName": players = db.Players.Where(p => p.FirstName.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "LastName": players = db.Players.Where(p => p.LastName.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "City": players = db.Players.Where(p => p.City.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "Country": players = db.Players.Where(p => p.Country.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "Phone": players = db.Players.Where(p => p.Phone.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "Is_Advisor": int IsAdvisor = -1; if (value.Equals("Yes")) IsAdvisor = 1; else if (value.Equals("No")) IsAdvisor = 0; if (IsAdvisor != -1) players = db.Players.Where(p => p.IsAdvisor == IsAdvisor); break; case "AdviseTo_Name": players = db.Players.Where(p => p.AdviseTo == player.AdviseTo); break; } if (players != null && players.Count() > 0) { List<int> lst = new List<int>(); foreach (var p in players) lst.Add(p.Id); return lst; } else return null; } } catch (Exception) { return null; } }
/* delete the players from database that matches the specific value in the specific column */ public void deletePlayers(PlayerData player, string title, string value) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); List<int> playerIds = getPlayerMatches(player, title, value); if (playerIds == null || playerIds.Count() < 1) { channel.updateError("There's no players with value '" + value + "' in field '" + title + "'"); return; } using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); int loggedInCount = 0; foreach (var id in playerIds) { if (isUserLogged(id)) { channel.updateError("Can't delete an online user [id=" + id + "]"); loggedInCount++; } else { bool success = deletePlayerDependencies(id, db, con); if (success) { cmd.CommandText = string.Format("delete from Players where Id={0}", id); cmd.ExecuteNonQuery(); } } } con.Close(); channel.updateSuccess(playerIds.Count() - loggedInCount + " players deleted"); } catch (Exception) { channel.updateError("Some error occured while deleting from database"); } } } }
private List<int> getChampionshipMatches(string title, string value) { try { IEnumerable<Championship> chmps = null; using (var db = new TTTDataClassesDataContext()) { switch (title) { case "Id": chmps = db.Championships.Where(c => c.Id == int.Parse(value.Replace(" ", string.Empty))); break; case "City": chmps = db.Championships.Where(c => c.City.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; case "StartDate": chmps = db.Championships.Where(c => c.StartDate.Equals(value)); break; case "EndDate": chmps = db.Championships.Where(c => c.EndDate.Equals(value)); break; case "Picture": chmps = db.Championships.Where(c => c.Picture.Replace(" ", string.Empty).Equals(value.Replace(" ", string.Empty))); break; } if (chmps != null && chmps.Count() > 0) { List<int> lst = new List<int>(); foreach (var p in chmps) lst.Add(p.Id); return lst; } else return null; } } catch (Exception) { return null; } }
/* delete the championships from database that matches the specific value in the specific column */ public void deleteChampionships(string title, string value) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); List<int> chmpsIds = getChampionshipMatches(title, value); if (chmpsIds == null || chmpsIds.Count() < 1) { channel.updateError("There's no championships with value '" + value + "' in field '" + title + "'"); return; } using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); foreach (var id in chmpsIds) { bool success = deleteChampionshipDependencies(id, db, con); if (success) { cmd.CommandText = string.Format("delete from Championships where Id={0}", id); cmd.ExecuteNonQuery(); } } con.Close(); channel.updateSuccess(chmpsIds.Count() + " championships deleted"); } catch (Exception) { channel.updateError("Some error occured while deleting from database"); } } } }
/* register a player to championship(s) */ public void registerPlayerToChamp(PlayerData player, ChampionshipData[] chmps) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); if (player == null || chmps == null) { channel.registerPlayerToChampError("Error: data is corrupted"); return; } using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { SqlCommand cmd = new SqlCommand("", con); try { con.Open(); for (var i = 0; i < chmps.Length; i++) { if (!isPlayerRegisteredToChamp(player, chmps[i], db)) { cmd.CommandText = string.Format("Insert into PlayerChampionships(PlayerId, ChampionshipId) " + "values({0}, {1})", player.Id, chmps[i].Id); cmd.ExecuteNonQuery(); } } con.Close(); channel.registerPlayerToChampSuccess(); } catch (Exception e) { channel.showException(e); } } } }
private PlayerData getPlayerData(Player p, TTTDataClassesDataContext db) { PlayerData player = new PlayerData(); player.Id = p.Id; player.FirstName = p.FirstName; player.LastName = p.LastName; player.City = p.City; player.Country = p.Country; player.Phone = p.Phone; player.IsAdvisor = p.IsAdvisor; player.Is_Advisor = (p.IsAdvisor == 1) ? "Yes" : "No"; if (p.AdviseTo.HasValue) { player.AdviseTo = p.AdviseTo.Value; Player adviseTo = db.Players.Where(pl => pl.Id == player.AdviseTo).First(); player.AdviseTo_Name = adviseTo.Id + " : " + adviseTo.FirstName; } return player; }
/* request to insert a game to database */ public void insertGameToDB(Game game) { using (var db = new TTTDataClassesDataContext()) { using (SqlConnection con = new SqlConnection(db.Connection.ConnectionString)) { string sql = ""; if (game.Winner.HasValue) sql = string.Format("Insert into Games(Player1, Player2, Winner, BoardSize, Moves, StartTime, EndTime) " + "values({0}, {1}, {2}, {3}, '{4}', '{5}', '{6}')", game.Player1, game.Player2, game.Winner, game.BoardSize, game.Moves, game.StartTime, game.EndTime); else sql = string.Format("Insert into Games(Player1, Player2, BoardSize, Moves, StartTime, EndTime) " + "values({0}, {1}, {2}, '{3}', '{4}', '{5}')", game.Player1, game.Player2, game.BoardSize, game.Moves, game.StartTime, game.EndTime); SqlCommand cmd = new SqlCommand(sql, con); try { con.Open(); cmd.ExecuteNonQuery(); con.Close(); } catch (Exception) { } } } }
private ChampionshipData[] getAllChampionships(int playerId = -1) { using (var db = new TTTDataClassesDataContext()) { IEnumerable<Championship> x = null; if (playerId == -1) x = db.Championships; else { var y = db.PlayerChampionships.Where(pc => pc.PlayerId == playerId); x = db.Championships.Where(c => y.Any(pc => pc.ChampionshipId == c.Id)); } ChampionshipData[] chmps = new ChampionshipData[x.Count()]; int i = 0; foreach (var c in x) { chmps[i++] = getChampionshipData(c); } return chmps; } }
/* request all players of a specific game from database for a client query */ public async void getGamePlayers(GameData game, bool delay) { ICallBack channel = OperationContext.Current.GetCallbackChannel<ICallBack>(); if (delay) await Task<int>.Factory.StartNew(sleep); using (var db = new TTTDataClassesDataContext()) { PlayerData[] players = new PlayerData[2]; players[0] = getPlayerDataById(game.Player1, db); players[1] = getPlayerDataById(game.Player2, db); channel.sendPlayers(players, "Q"); } }
private GameData[] getAllGamesFromDB(bool withPlayersNames, int playerId = -1) { using (var db = new TTTDataClassesDataContext()) { IEnumerable<Game> x = null; if (playerId == -1) x = db.Games; else x = db.Games.Where(g => g.Player1 == playerId || g.Player2 == playerId); GameData[] games = new GameData[x.Count()]; int i = 0; foreach (var g in x) { games[i] = getGameData(g); if (withPlayersNames) { PlayerData p = getPlayerDataById(games[i].Player1, db); games[i].Player1_Name = p.Id + " : " + p.FirstName; p = getPlayerDataById(games[i].Player2, db); games[i].Player2_Name = p.Id + " : " + p.FirstName; if (g.Winner.HasValue) { p = getPlayerDataById((int)games[i].Winner, db); games[i].Winner_Name = p.Id + " : " + p.FirstName; } } i++; } return games; } }
private IEnumerable<Player> getPlayerAdvisors(int id, TTTDataClassesDataContext db) { return db.Players.Where(p => p.AdviseTo == id); }
private PlayerData getPlayerDataById(System.Nullable<int> id, TTTDataClassesDataContext db) { if (!id.HasValue || id < 1) return null; var x = db.Players.Where(p => p.Id == id.Value); if (x.Count() < 1) return null; else return getPlayerData(x.First(), db); }
private bool isPlayerRegisteredToChamp(PlayerData player, ChampionshipData champ, TTTDataClassesDataContext db) { var x = from pc in db.PlayerChampionships where pc.PlayerId == player.Id && pc.ChampionshipId == champ.Id select pc; return(x.Count() > 0); }