private void RemovePendingFriendRequests(user user) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var frdReqQuery = from frdReq in db.friendRequests where (frdReq.from_user == user.ID || frdReq.to_user == user.ID) select frdReq; foreach (var frdReq in frdReqQuery) { db.friendRequests.DeleteOnSubmit(frdReq); } try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.RemovePendingFriendRequests!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } }
public List <user> GetPendingFriendRequests(string username) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var curUser = GetUserFromEmailAddress(username); if (null == curUser) { return(null); } IQueryable <friendRequest> requestQuery = from request in db.friendRequests where request.to_user == curUser.ID select request; if (null == requestQuery.FirstOrDefault()) { return(null); } return(requestQuery.Select(request => ( from u in db.users where u.ID == request.from_user select u) ) .Select(userQuery => userQuery.First()).ToList()); } } }
public List <user_game> GetUserGameHistory(user user) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from g in db.user_games where g.userID == user.ID select g; return(userQuery.ToList()); } } }
private game GetGameID(int winnerId, DateTime gameStartTime) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from u in db.games where ((u.winner == winnerId) && (u.gameStartTime == gameStartTime)) select u; return(userQuery.FirstOrDefault()); } } }
private user GetUserFromEmailAddress(string emailAddress) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from u in db.users where u.email == emailAddress select u; return(userQuery.Count() == 1 ? userQuery.First() : null); } } }
public List <user> GetFriends(string userName) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var curUser = GetUserFromEmailAddress(userName); if (null == curUser) { return(null); } var friendshipQueryOne = from frd in db.friendships where frd.user1 == curUser.ID select frd.user2; var friendshipQueryTwo = from frd in db.friendships where frd.user2 == curUser.ID select frd.user1; var userList = friendshipQueryOne.Select(id => ( from u in db.users where u.ID == id select u) ) .Select(userQuery => userQuery.FirstOrDefault()).ToList(); userList.AddRange(friendshipQueryTwo.Select(id => ( from u in db.users where u.ID == id select u) ).Select(userQuery => userQuery.FirstOrDefault())); return(userList.Count == 0 ? null : userList); } } }
public List <user> GetMatchingUsersByAlias(string alias) { if (string.IsNullOrEmpty(alias)) { return(null); } using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from u in db.users where u.alias.StartsWith(alias) select u; return(userQuery.ToList()); } } }
public user GetUserByAlias(string alias) { if (string.IsNullOrEmpty(alias)) { return(null); } using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from u in db.users where u.alias == alias select u; return(userQuery.FirstOrDefault()); } } }
public void AddNewUser(string newEmail, string newAlias) { if (string.IsNullOrEmpty(newEmail)) { return; } if (string.IsNullOrEmpty(newAlias)) { return; } using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var newUser = new user { email = newEmail, alias = newAlias, creation_time = DateTime.UtcNow, last_seen_time = DateTime.UtcNow }; db.users.InsertOnSubmit(newUser); try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.AddNewUser!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } }
private void RemoveFriendRequest(string fromUser, string toUser) { var _fromUser = GetUserFromEmailAddress(fromUser); var _toUser = GetUserFromEmailAddress(toUser); using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { if ((_fromUser != null) && (_toUser != null)) { var requestQuery = from request in db.friendRequests where (request.from_user == _fromUser.ID) && (request.to_user == _toUser.ID) select request; foreach (var request in requestQuery) { db.friendRequests.DeleteOnSubmit(request); } try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.RemoveFriendRequest!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } } }
// // This method is private because friendships can only be created when a row in the // friendRequest table has been modified. // private void CreateFriendship(string user1, string user2) { var _user1 = GetUserFromEmailAddress(user1); var _user2 = GetUserFromEmailAddress(user2); if ((_user1 != null) && (_user2 != null)) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var friendship = new friendship { user1 = _user1.ID, user2 = _user2.ID, creation_time = DateTime.UtcNow }; db.friendships.InsertOnSubmit(friendship); try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.CreateFriendship!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } } }
public void RemoveFriendship(string user1, string user2) { var _user1 = GetUserFromEmailAddress(user1); var _user2 = GetUserFromEmailAddress(user2); if ((_user1 != null) && (_user2 != null)) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { IQueryable <friendship> friendshipQuery = from frd in db.friendships where (frd.user1 == _user1.ID && frd.user2 == _user2.ID) || (frd.user1 == _user2.ID && frd.user2 == _user1.ID) select frd; foreach (var frd in friendshipQuery) { db.friendships.DeleteOnSubmit(frd); } try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.RemoveFriendship!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } } }
/// <summary> /// Test Hook Method for Unit Tests. Not to be used IRL. /// </summary> public void RemoveUser(string emailAddr) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var curUser = GetUserFromEmailAddress(emailAddr); if (curUser != null) { IQueryable <user> userQuery = from u in db.users where (u.email == emailAddr) select u; foreach (var user in userQuery) { RemovePendingFriendRequests(user); RemoveAllFriends(user); db.users.DeleteOnSubmit(user); } try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.RemoveUser!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } } }
public void SendFriendRequest(string fromUserString, string toUserString) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var fromUser = GetUserFromEmailAddress(fromUserString); var toUser = GetUserFromEmailAddress(toUserString); if ((fromUser != null) && (toUser != null)) { var request = new friendRequest { from_user = fromUser.ID, to_user = toUser.ID, creation_time = DateTime.UtcNow }; db.friendRequests.InsertOnSubmit(request); try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.SendFriendRequest!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } } }
public user GetUserByEmail(string email) { if (string.IsNullOrEmpty(email)) { return null; } using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from u in db.users where u.email == email select u; return userQuery.FirstOrDefault(); } } }
public List<user_game> GetUserGameHistory(user user) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from g in db.user_games where g.userID == user.ID select g; return userQuery.ToList(); } } }
public void AddNewGameData(GameData gameData) { if (null == gameData) { Trace.WriteLine(string.Format( "Invalid Game Data in Database.AddNewGameData!\n GameData is null.")); return; } var winnerUser = GetUserFromEmailAddress(gameData.Winner); if (null == winnerUser) { Trace.WriteLine(string.Format( "Invalid Game Data in Database.AddNewGameData!\n Winner {0} not recognized.", gameData.Winner)); return; } foreach (var kvp in gameData.PlayerDictionary) { var player = GetUserFromEmailAddress(kvp.Key); if (null == player) { Trace.WriteLine(string.Format( "Invalid Game Data in Database.AddNewGameData!\n Player {0} not recognized.", kvp.Key)); return; } } if (gameData.GameDuration < 0) { Trace.WriteLine(string.Format( "Invalid Game Data in Database.AddNewGameData!\n GameDuration of value {0} is invalid.", gameData.GameDuration)); return; } using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var newData = new game { winner = winnerUser.ID, gameStartTime = gameData.GameStartTime, gameDuration = gameData.GameDuration }; // ToDo: newData.gameDataFile = ?? db.games.InsertOnSubmit(newData); try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.AddNewGameData!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } var gameInstance = GetGameID(GetUserFromEmailAddress(gameData.Winner).ID, gameData.GameStartTime); if (gameInstance == null) { return; } foreach (var kvp in gameData.PlayerDictionary) { var userGame = new user_game(); userGame.avgTimeBetweenDumps = kvp.Value.AvgTimeBetweenDumps; userGame.avgTimeBetweenPeels = kvp.Value.AvgTimeBetweenPeels; userGame.isWinner = kvp.Value.IsWinner; userGame.numberOfDumps = kvp.Value.NumberOfDumps; userGame.numberOfPeels = kvp.Value.NumberOfPeels; userGame.performedFirstPeel = kvp.Value.PerformedFirstPeel; userGame.userID = GetUserFromEmailAddress(kvp.Key).ID; userGame.gameID = gameInstance.gameID; db.user_games.InsertOnSubmit(userGame); } try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.AddNewGameData!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } }
public void RemoveFriendship(string user1, string user2) { var _user1 = GetUserFromEmailAddress(user1); var _user2 = GetUserFromEmailAddress(user2); if ((_user1 != null) && (_user2 != null)) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { IQueryable<friendship> friendshipQuery = from frd in db.friendships where (frd.user1 == _user1.ID && frd.user2 == _user2.ID) || (frd.user1 == _user2.ID && frd.user2 == _user1.ID) select frd; foreach (var frd in friendshipQuery) { db.friendships.DeleteOnSubmit(frd); } try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.RemoveFriendship!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } } }
/// <summary> /// Test Hook Method for Unit Tests. Not to be used IRL. /// </summary> public void RemoveUser(string emailAddr) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var curUser = GetUserFromEmailAddress(emailAddr); if (curUser != null) { IQueryable<user> userQuery = from u in db.users where (u.email == emailAddr) select u; foreach (var user in userQuery) { RemovePendingFriendRequests(user); RemoveAllFriends(user); db.users.DeleteOnSubmit(user); } try { db.SubmitChanges(); } catch (Exception e) { Trace.WriteLine(string.Format( "Exception in Database.RemoveUser!\n Exception: {0} \n Callstack: {1}", e.Message, e.StackTrace)); } } } } }
private user GetUserFromEmailAddress(string emailAddress) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from u in db.users where u.email == emailAddress select u; return userQuery.Count() == 1 ? userQuery.First() : null; } } }
/// <summary> /// Returns the list of users whose email addresses match the given sequence of characters /// passed in. I.E. passing in "jo" will return jon, john, jonathan, jo*. This is /// intended to be used when the end user is searching for new friends in the database. /// </summary> /// <param name="email"></param> /// <returns></returns> public List<user> GetMatchingUsersByEmail(string email) { if (string.IsNullOrEmpty(email)) { return null; } using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from u in db.users where u.email.StartsWith(email) select u; return userQuery.ToList(); } } }
public List<user> GetFriends(string userName) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var curUser = GetUserFromEmailAddress(userName); if (null == curUser) { return null; } var friendshipQueryOne = from frd in db.friendships where frd.user1 == curUser.ID select frd.user2; var friendshipQueryTwo = from frd in db.friendships where frd.user2 == curUser.ID select frd.user1; var userList = friendshipQueryOne.Select(id => ( from u in db.users where u.ID == id select u) ) .Select(userQuery => userQuery.FirstOrDefault()).ToList(); userList.AddRange(friendshipQueryTwo.Select(id => ( from u in db.users where u.ID == id select u) ).Select(userQuery => userQuery.FirstOrDefault())); return userList.Count == 0 ? null : userList; } } }
public List<user> GetPendingFriendRequests(string username) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var curUser = GetUserFromEmailAddress(username); if (null == curUser) { return null; } IQueryable<friendRequest> requestQuery = from request in db.friendRequests where request.to_user == curUser.ID select request; if (null == requestQuery.FirstOrDefault()) { return null; } return requestQuery.Select(request => ( from u in db.users where u.ID == request.from_user select u) ) .Select(userQuery => userQuery.First()).ToList(); } } }
private game GetGameID(int winnerId, DateTime gameStartTime) { using (var connection = new SqlConnection(ConnectionString)) { using (var db = new OzwegoDataClassesDataContext(connection)) { var userQuery = from u in db.games where ((u.winner == winnerId) && (u.gameStartTime == gameStartTime)) select u; return userQuery.FirstOrDefault(); } } }