public int GetUserTeamRole(string UserID) { List <string[]> param = new List <string[]>() { new string[] { "@UserID", UserID }, }; var Role = SQLConnection.ExecuteSearchQueryParameters("SELECT Role FROM UserTeams Where UserID = @UserID", param); return(Convert.ToInt32(Role[0][0])); }
public TeamRoles GetRole(string userID, string teamID) { List <string[]> param = new List <string[]> { new string[] { "@UserID", userID }, new string[] { "@TeamID", teamID } }; var result = SQLConnection.ExecuteSearchQueryParameters("SELECT Role FROM UserTeams WHERE UserID = @UserID AND TeamID = @TeamID", param); var role = result.Count > 0 ? (TeamRoles)Convert.ToInt32(result[0]) : TeamRoles.NonMember; return(role); }
public TeamDTO FindTeamByName(string Name) { List <string[]> param = new List <string[]>() { new string[] { "@TeamName", Name }, }; var result = SQLConnection.ExecuteSearchQueryParameters($"SELECT * FROM Teams WHERE `TeamName` = @TeamName", param); if (result.Count == 0) { return(null); } return(GenerateDTOFromRow(result[0])); }
public TeamDTO FindTeamByID(string ID) { List <string[]> param = new List <string[]>() { new string[] { "@TeamID", ID }, }; var result = SQLConnection.ExecuteSearchQueryParameters($"SELECT * FROM Teams WHERE TeamID = @TeamID", param); if (result.Count != 0) { return(GenerateDTOFromRow(result[0])); } else { return(null); } }
public TeamDTO FindTeamByUser(string userID) { List <string[]> param = new List <string[]>() { new string[] { "@UserID", userID }, }; var TeamID = SQLConnection.ExecuteSearchQueryParameters("SELECT TeamID FROM UserTeams WHERE UserID = @UserID", param); if (TeamID.Count == 0) { return(null); } param = new List <string[]>() { new string[] { "@TeamID", TeamID[0][0] }, }; var Team = SQLConnection.ExecuteSearchQueryParameters("SELECT * FROM Teams Where TeamID = @TeamID", param); return(GenerateDTOFromRow(Team[0])); }
public List <string> GetMembers(string teamID) { List <string[]> param = new List <string[]>() { new string[] { "@TeamID", teamID }, }; List <string[]> Members = SQLConnection.ExecuteSearchQueryParameters( @"SELECT Users.UserName FROM UserTeams UT INNER JOIN AspNetUsers Users ON UT.UserID = Users.Id WHERE UT.TeamID = @TeamID", param); List <string> returnResult = new List <string>(); foreach (string[] row in Members) { returnResult.Add(row[0]); } return(returnResult); }
public List <TeamMatchResultDTO> GetPreviousResults(string teamID) { List <string[]> param = new List <string[]> { new string[] { "@TeamID", teamID } }; //SELECT T.TeamName FROM TeamMatch TM INNER JOIN Teams T ON T.TeamID = TM.TeamID WHERE MatchID IN (SELECT MatchID FROM TeamMatch TM WHERE TeamID = @TeamID) AND T.TeamID <> @TeamID var result = SQLConnection.ExecuteSearchQueryParameters( @"SELECT T.TeamName AS Enemy, TM.Score AS EnemyScore, (SELECT Score FROM TeamMatch WHERE TeamID = @TeamID) AS OwnScore FROM (TeamMatch TM INNER JOIN Teams T ON T.TeamID = TM.TeamID) INNER JOIN `Match` M ON M.ID = TM.MatchID WHERE MatchID IN ( SELECT MatchID FROM TeamMatch TM WHERE TeamID = @TeamID ) AND T.TeamID <> @TeamID ORDER BY M.PlayDate", param); List <TeamMatchResultDTO> returnList = new List <TeamMatchResultDTO>(); foreach (var row in result) { TeamMatchResultDTO resultDTO = new TeamMatchResultDTO() { OpponentName = row[0], ScoreOpponent = Convert.ToInt32(row[1]), ScoreSelf = Convert.ToInt32(row[2]), Won = Convert.ToInt32(row[1]) < Convert.ToInt32(row[2]), }; resultDTO.Won = resultDTO.ScoreSelf > resultDTO.ScoreOpponent; returnList.Add(resultDTO); } return(returnList); }