public void CreateRole(Role role) { var sqlQuery = $"Insert into Role (Id, Name) Values ({role.Id}, {role.Name}"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); DbQuery.ExecuteCommand(command); }
public List <Comment> GetComments(News news) { var comments = new List <Comment>(); var sqlQuery = $"Select * from Comments where NewsId = {news.NewsId}"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var comment = new Comment { CommentId = Convert.ToInt32(reader["CommentId"]), Text = reader["Text"].ToString(), Like = Convert.ToInt32(reader["[Like]"]), Dislike = Convert.ToInt32(reader["Dislike"]), PublicationTime = Convert.ToDateTime(reader["PublicationTime"]), NewsId = Convert.ToInt32(reader["NewsId"]), UserId = Convert.ToInt32(reader["UserId"]) }; comments.Add(comment); } command.Connection.Close(); return(comments); }
public List <Match> GetMatches(string query) { var matches = new List <Match>(); var sqlQuery = query; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var match = new Match { MatchId = Convert.ToInt32(reader["MatchId"]), HomeTeamId = Convert.ToInt32(reader["HomeTeamId"]), AwayTeamId = Convert.ToInt32(reader["AwayTeamId"]), HomeTeamScore = Convert.ToInt32(reader["HomeTeamScore"]), AwayTeamScore = Convert.ToInt32(reader["AwayTeamScore"]), Result = (TypeOfResult)reader["Result"], Date = Convert.ToDateTime(reader["Date"]) }; matches.Add(match); } command.Connection.Close(); return(matches); }
public void DeleteNews(int id) { var sqlQuery = $"Delete from News where NewsId = {id}"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); DbQuery.ExecuteCommand(command); }
public void EditUser(User user) { var sqlQuery = $"Update User Set FirstName = {user.FirstName}, Surname = {user.Surname}, Nickname = {user.Nickname}, Email = {user.Email}, Password = {user.Password}, RememberMe = {user.RememberMe}, Avatar = {user.Avatar}, City = {user.City}, DateOfBirth = {user.DateOfBirth} Where UserId = {user.UserId}"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); DbQuery.ExecuteCommand(command); }
public void CreateNews(News news) { var sqlQuery = $"Insert into News (Image, Title, MainText, PublicationTime, UserId) Values ({news.Image}, {news.Title}, {news.MainText}, {news.PublicationTime}, {news.UserId})"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); command.ExecuteNonQuery(); command.Connection.Close(); }
public void CreateStandings() { var currentSeason = GetCurrentSeason(); var teams = GetTeams(); var sqlQuery = string.Empty; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); foreach (var team in teams) { command.CommandText = $"Insert into Standings (TeamId, SeasonId) Values ('{team.TeamId}', '{currentSeason.SeasonId}');"; DbQuery.ExecuteCommand(command); } }
public List <string> DropDownListGeneration() { var sqlQuery = "Select Name from Teams"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); var collection = (from DbDataRecord item in reader select item.GetString(0)).ToList(); collection.Sort(); command.Connection.Close(); return(collection); }
private Season GetSeason(string sqlQuery) { var season = new Season(); var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { season = new Season(reader); } command.Connection.Close(); return(season); }
public List <string> GetAllBoards() { var boards = new List <string>(); var sqlQuery = "Select ImageName from Boards"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { boards.Add(reader["ImageName"].ToString()); } command.Connection.Close(); return(boards); }
public User GetUserByEmail(string email) { var user = new User(); var sqlQuery = $"Select * from Users where Email = {email}"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { user = new User(reader); } command.Connection.Close(); return(user); }
//var sqlQuery = // $"Insert into Standings (TeamId, SeasonId, GamesPlayed, Wins, Losses, OT, Points, ROW, GoalsFor, GoalsAgainst, GoalDifferential, HomeWins, HomeLosses, HomeOT, AwayWins, AwayLosses, AwayOT, ShootoutWins, ShootoutLosses, LastWins, LastLosses, LastOT, Streak) Values ({standings.TeamId}, {standings.SeasonId}, {standings.GamesPlayed}, {standings.Wins}, {standings.Losses}, {standings.OT}, {standings.Points}, {standings.ROW}, {standings.GoalsFor}, {standings.GoalsAgainst}, {standings.GoalDifferential}, {standings.HomeWins}, {standings.HomeLosses}, {standings.HomeOT}, {standings.AwayWins}, {standings.AwayLosses}, {standings.AwayOT}, {standings.ShootoutWins}, {standings.ShootoutLosses}, {standings.LastWins}, {standings.LastLosses}, {standings.LastOT}, {standings.Streak})"; public List <Standings> GetStandingsByTeamId(int id) { var standings = new List <Standings>(); var sqlQuery = $"Select * from Standings where TeamId = {id};"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var s = new Standings(reader); standings.Add(s); } command.Connection.Close(); return(standings); }
public News GetNewsById(int id) { var news = new News(); var sqlQuery = $"Select * from News where NewsId = {id}"; //var news = _db.News.Where(x => x.NewsId == id).Include(c => c.Bundle.Select(x => x.Tag)).ToList(); var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { news = new News(reader); } command.Connection.Close(); return(news); }
public Comment CreateComment(Comment comment) { UserRepository repository = new UserRepository(DbQuery.ConnectionString); comment.Author = repository.GetUserById(comment.UserId); comment.PublicationTime = DateTime.Now; var sqlQuery = $"Insert into Comments (Text, PublicationTime, NewsId, UserId) Values ({comment.Text}, {comment.PublicationTime}, {comment.NewsId}, {comment.UserId})"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); command.ExecuteNonQuery(); command.Connection.Close(); return(comment); }
public Role GetRole(int?id) //Check this method as soon as possible! { var role = new Role(); var sqlQuery = $"Select * from Role where RoleId = {id}"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { role.Id = Convert.ToInt32(reader["Id"]); role.Name = reader["Name"].ToString(); } command.Connection.Close(); return(role); }
public List <News> GetNewsByTagId(int id) { var news = new List <News>(); var sqlQuery = $"Select * from Tags where TagId = {id}"; //var coll = _db.News.SelectMany(b => b.Bundle.Where(t => t.TagId == tagId).Select(x => x.News)).Include(c => c.Bundle.Select(x => x.Tag)).ToList(); var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var n = new News(reader); news.Add(n); } command.Connection.Close(); return(news); }
public string GetShortName(string name) { var sqlQuery = $"Select ShortTeamName from Teams where Name = '{name}'"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); command.Prepare(); var reader = command.ExecuteReader(); string shortName = string.Empty; while (reader.Read()) { shortName = reader["ShortTeamName"].ToString(); } command.Connection.Close(); return(shortName); }
public void CreateUser(string nickname, string email, string password) { User user = new User { Nickname = nickname, Email = email, Password = HashingPassword(password), DateOfRegistration = DateTime.Now }; if (GetRole(2) != null) { user.RoleId = 2; } var sqlQuery = UserQuery(user); var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); DbQuery.ExecuteCommand(command); }
public string[] GetPlace(string s) { var sqlQuery = $"Select CityArea, Arena from Teams where Name = '{s}'"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); string[] result = new string[2]; while (reader.Read()) { result[0] = reader["CityArea"].ToString(); result[1] = reader["Arena"].ToString(); } command.Connection.Close(); return(result); }
public List <Team> GetTeams() { var teams = new List <Team>(); var sqlQuery = "Select * from Teams;"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var team = new Team(reader); teams.Add(team); } var sortedTeams = teams.OrderBy(x => x.Name).ToList(); command.Connection.Close(); return(sortedTeams); }
public List <News> GetNews() { var newsCollection = new List <News>(); var sqlQuery = "Select * from News"; //var news = _db.News.Include(c => c.Bundle.Select(x => x.Tag)).ToList(); var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); using (command) { command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var news = new News(reader); newsCollection.Add(news); } command.Connection.Close(); } return(newsCollection); }
public List <Tag> GetTags() { var tags = new List <Tag>(); var sqlQuery = "Select * from Tags"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { Tag tag = new Tag { Id = Convert.ToInt32(reader["Id"]), Title = reader["Title"].ToString() }; tags.Add(tag); } command.Connection.Close(); return(tags); }
//public IEnumerable<Standings> GetDivision() //{ // var currentSeason = GetCurrentSeason(); // var divisions = from s in _db.Standings orderby s.Team.Division /*orderby s.Points*/ select s; // return divisions.ToList(); //} public List <Standings> GetLeague() { var league = new List <Standings>(); var currentSeason = GetCurrentSeason(); var sqlQuery = $"Select * from Standings join Teams on Teams.TeamId = Standings.TeamId where SeasonId = {currentSeason.SeasonId};"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var team = new Team(reader); var standings = new Standings(reader) { Team = team }; league.Add(standings); } //var league = from s in _db.Standings /*where s.Seasons == currentSeason*/ orderby s.Points descending select s; return(league.ToList()); }
public Team GetTeamById(int id) { //Select * from Teams join Standings on Standings.TeamId = Teams.TeamId join Matches on Matches.SeasonId = Standings.SeasonId where Teams.TeamId = 8 AND (Matches.HomeTeamId = 8 OR Matches.AwayTeamId = 8) var team = new Team(); var s = new Standings(); var standings = new List <Standings>(); var matches = new List <Match>(); var sqlQuery = $"Select * from Teams left join Standings on Standings.TeamId = Teams.TeamId left join Matches on Matches.SeasonId = Standings.SeasonId left join Seasons on Seasons.SeasonId = Standings.SeasonId where Teams.TeamId = {id}"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { if (team.TeamId == 0) { team = new Team(reader); } if (s.StandingsId != Convert.ToInt32(reader["StandingsId"])) { s = new Standings(reader) { Season = new Season(reader) }; standings.Add(s); } var match = new Match(reader); matches.Add(match); } command.Connection.Close(); team.Standings = standings; team.MatchesHome = matches.Where(x => x.HomeTeamId == team.TeamId).ToList(); team.MatchesAway = matches.Where(x => x.AwayTeamId == team.TeamId).ToList(); return(team); }
public void NhlTableGeneration(Match match) { match.AwayTeam = GetTeamById(match.AwayTeamId); match.HomeTeam = GetTeamById(match.HomeTeamId); match.SeasonId = GetCurrentSeason().SeasonId; var sqlQuery = $"Insert into Matches (SeasonId, HomeTeamId, AwayTeamId, HomeTeamScore, AwayTeamScore, Result, Date) Values ({match.SeasonId}, {match.HomeTeamId}, {match.AwayTeamId}, {match.HomeTeamScore}, {match.AwayTeamScore}, {(int)match.Result}, '{match.Date}')"; var command = DbQuery.CreateCommand(sqlQuery, ConnectionString); DbQuery.ExecuteCommand(command); Team winningTeam; Team losingTeam; int winnersGoals; int losersGoals; if (match.AwayTeamScore > match.HomeTeamScore) { winningTeam = match.AwayTeam; winningTeam.MatchesAway.Add(match); winnersGoals = match.AwayTeamScore; losingTeam = match.HomeTeam; losingTeam.MatchesHome.Add(match); losersGoals = match.HomeTeamScore; } else { winningTeam = match.HomeTeam; winningTeam.MatchesHome.Add(match); winnersGoals = match.HomeTeamScore; losingTeam = match.AwayTeam; losingTeam.MatchesAway.Add(match); losersGoals = match.AwayTeamScore; } var currentSeason = GetCurrentSeason(); //if (currentSeason.Standings.Count == 0) //{ // CreateStandings(); //} var winStat = winningTeam.Standings.SingleOrDefault(x => x.Season.SeasonId == currentSeason.SeasonId); var loseStat = losingTeam.Standings.SingleOrDefault(x => x.Season.SeasonId == currentSeason.SeasonId); //var winStat = (from s in GetStandingsByTeamId(winningTeam.TeamId) where s.Season.IsCurrent == true select s).FirstOrDefault(); //var loseStat = (from s in GetStandingsByTeamId(losingTeam.TeamId) where s.Season.IsCurrent == true select s).FirstOrDefault(); winStat.Team = winningTeam; loseStat.Team = losingTeam; winStat.GamesPlayed += 1; loseStat.GamesPlayed += 1; switch (match.Result) { case TypeOfResult.FT: FulltimeCounter(match, winStat, loseStat, winnersGoals, losersGoals); break; case TypeOfResult.OT: OvertimeCounter(match, winStat, loseStat, winnersGoals, losersGoals); break; case TypeOfResult.SO: ShootoutCounter(match, winStat, loseStat, winnersGoals, losersGoals); break; } command.CommandText = StandingsQuery(winStat); DbQuery.ExecuteCommand(command); command.CommandText = StandingsQuery(loseStat); DbQuery.ExecuteCommand(command); }