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);
}