public bool doesUserExists(string username) { _context = new MyFootballContext(); StringBuilder query = new StringBuilder(); query.AppendFormat("select * from webuser where webuser.username='******'", username); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); using (var reader = cmd.ExecuteReader()) { reader.Read(); if (!reader.HasRows) { conn.Close(); return(false); } else { conn.Close(); return(true); } } } }
public List <Player> GetPlayers() { List <Player> list = new List <Player>(); _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); MySqlCommand cmd = new MySqlCommand("select * from player", conn); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { int id = Convert.ToInt32(reader["id"]); string first_name = reader["firstName"].ToString(); string last_name = reader["lastName"].ToString(); DateTime birth_date = Convert.ToDateTime(reader["birthDate"].ToString()); int age = Convert.ToInt32(reader["age"]); string possition = reader["possition"].ToString(); string nationality = reader["nationality"].ToString(); double weight = Convert.ToDouble(reader["weight"]); double height = Convert.ToDouble(reader["height"]); int goals = Convert.ToInt32(reader["goals"]); string league = reader["league"].ToString(); int injured = Convert.ToInt32(reader["injured"]); string season = reader["season"].ToString(); int fk_Teamid = Convert.ToInt32(reader["fk_Teamid"]); list.Add(new Player() { id = id, first_name = first_name, last_name = last_name, birth_date = birth_date, age = age, possition = possition, nationality = nationality, weight = weight, height = height, goals = goals, league = league, injured = injured, season = season, fk_TeamId = fk_Teamid }); } reader.Close(); } conn.Close(); return(list); } }
public async Task <Player> FindAsync(int id) { Player playerById; _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string query = "select * from player where id = " + id.ToString(); MySqlCommand cmd = new MySqlCommand(query, conn); using (var reader = cmd.ExecuteReader()) { reader.Read(); if (!reader.HasRows) { return(null); } string firstname = reader["firstName"].ToString(); string last_name = reader["lastName"].ToString(); DateTime birth_date = Convert.ToDateTime(reader["birthDate"]); int age = Convert.ToInt32(reader["age"]); string possition = reader["possition"].ToString(); string nationality = reader["nationality"].ToString(); double weight = Convert.ToDouble(reader["weight"]); double height = Convert.ToDouble(reader["height"]); int goals = Convert.ToInt32(reader["goals"]); string league = reader["league"].ToString(); int injured = Convert.ToInt32(reader["injured"]); string season = reader["season"].ToString(); int fk_Teamid = Convert.ToInt32(reader["fk_Teamid"]); playerById = new Player() { id = id, first_name = firstname, last_name = last_name, birth_date = birth_date, age = age, possition = possition, nationality = nationality, weight = weight, height = height, goals = goals, league = league, injured = injured, season = season, fk_TeamId = fk_Teamid }; reader.Close(); } conn.Close(); return(playerById); } }
/// <summary> /// Removes choosen user by id /// </summary> /// <param name="user">user to be deleted</param> public void Remove(User user) { _context = new MyFootballContext(); string query = "delete from webuser where webuser.id=" + user.id; using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); MySqlCommand cmd = new MySqlCommand(query, conn); cmd.ExecuteNonQuery(); conn.Close(); } }
public void RemoveLastPlayer() { _context = new MyFootballContext(); string query = "DELETE FROM player WHERE id=(SELECT MAX(id) FROM player)"; using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); MySqlCommand cmd = new MySqlCommand(query, conn); cmd.ExecuteNonQuery(); conn.Close(); } }
/// <summary> /// Adds new team in database /// </summary> /// <param name="team">new team</param> /// <returns>new team</returns> public async Task <Team> Add(Team team) { _context = new MyFootballContext(); StringBuilder query = new StringBuilder(); query.AppendFormat("insert into team (name, code, logo, country, founded, vanue_name, vanue_city, vanue_capacity)" + " values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7})", team.name, team.code, team.logo, team.country, team.founded.ToShortDateString(), team.vanue_name, team.vanue_city, team.vanue_capacity); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); cmd.ExecuteNonQuery(); conn.Close(); return(team); } }
/// <summary> /// get all teams info from database /// </summary> /// <returns>List of teams</returns> public List <Team> GetTeams() { List <Team> list = new List <Team>(); _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); MySqlCommand cmd = new MySqlCommand("select * from team", conn); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { int id = Convert.ToInt32(reader["id"]); string name = reader["name"].ToString(); string code = reader["code"].ToString(); string logo = reader["logo"].ToString(); string country = reader["country"].ToString(); DateTime founded = Convert.ToDateTime(reader["founded"]); string vanue_name = reader["vanue_name"].ToString(); string vanue_city = reader["vanue_city"].ToString(); int vanue_capacity = Convert.ToInt32(reader["vanue_capacity"]); list.Add(new Team() { id = id, name = name, code = code, logo = logo, country = country, founded = founded, vanue_name = vanue_name, vanue_city = vanue_city, vanue_capacity = vanue_capacity }); } reader.Close(); } conn.Close(); return(list); } }
/// <summary> /// Finds and returns team info by id /// </summary> /// <param name="id"></param> /// <returns>returns team info by id</returns> public async Task <Team> FindAsync(int id) { Team teamById; _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string query = "select * from team where id = " + id.ToString(); MySqlCommand cmd = new MySqlCommand(query, conn); using (var reader = cmd.ExecuteReader()) { reader.Read(); if (!reader.HasRows) { return(null); } string name = reader["name"].ToString(); string code = reader["code"].ToString(); string logo = reader["logo"].ToString(); string country = reader["country"].ToString(); DateTime founded = Convert.ToDateTime(reader["founded"].ToString()); string vanue_name = reader["vanue_name"].ToString(); string vanue_city = reader["vanue_city"].ToString(); int vanue_capacity = Convert.ToInt32(reader["vanue_capacity"]); teamById = new Team() { id = id, name = name, code = code, logo = logo, country = country, founded = founded, vanue_name = vanue_name, vanue_city = vanue_city, vanue_capacity = vanue_capacity }; reader.Close(); } conn.Close(); return(teamById); } }
public async Task <Player> Add(Player player) { _context = new MyFootballContext(); StringBuilder query = new StringBuilder(); query.AppendFormat("insert into player (firstName, lastName, birthDate, age, possition, nationality, weight, height, goals, league, injured, season, fk_Teamid)" + " values ('{0}','{1}','{2}','{3}','{4}','{5}',{6},{7},{8},'{9}',{10},'{11}',{12})", player.first_name, player.last_name, player.birth_date.Date.ToShortDateString(), player.age, player.possition, player.nationality, player.weight, player.height, player.goals, player.league, player.injured, player.season, player.fk_TeamId); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); cmd.ExecuteNonQuery(); conn.Close(); } return(player); }
public async Task <Game> PutGame(int id, Game game) { _context = new MyFootballContext(); StringBuilder query = new StringBuilder(); query.AppendFormat("Update game Set eventDate = '{0}', goalsHomeTeam = {1}, goalsAwayTeam = {2}, venue = '{3}', elapsed = {4}, " + "fk_Teamid = {5}, fk_Teamid1 = {6} where id = {7}", game.eventDate.ToString(), game.goalsHomeTeam, game.goalsAwayTeam, game.venue, game.elapsed, game.fk_Teamid, game.fk_Teamid1, id); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); cmd.ExecuteNonQuery(); conn.Close(); return(game); } }
/// <summary> /// Updates choosen user /// </summary> /// <param name="id">user id</param> /// <param name="user">user update info</param> /// <returns>updated user</returns> public async Task <User> PutByUsername(string username, User user) { _context = new MyFootballContext(); string encPass = EncDecService.Encrypt(user.password); StringBuilder query = new StringBuilder(); query.AppendFormat("Update webuser Set password = '******', email = '{1}', favouriteTeam = {2}" + " where username = '******'", encPass, user.email, user.favouriteTeam, username); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); cmd.ExecuteNonQuery(); conn.Close(); user.password = encPass; return(user); } }
public int MaxId() { int maxId; _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string query = "SELECT id FROM player WHERE id = (SELECT max(id) FROM player)"; MySqlCommand cmd = new MySqlCommand(query, conn); using (var reader = cmd.ExecuteReader()) { reader.Read(); maxId = Convert.ToInt32(reader["id"]); reader.Close(); } conn.Close(); } return(maxId); }
/// <summary> /// Updates team info by id /// </summary> /// <param name="id">team id</param> /// <param name="team">team update fields</param> /// <returns></returns> public async Task <Team> PutTeam(int id, Team team) { _context = new MyFootballContext(); StringBuilder query = new StringBuilder(); query.AppendFormat("Update team Set name = '{0}', code = '{1}', logo = '{2}', country = '{3}', founded = '{4}', vanue_name = '{5}', " + "vanue_city = '{6}', vanue_capacity = {7} where id = {8}", team.name, team.code, team.logo, team.country, team.founded.ToShortDateString(), team.vanue_name, team.vanue_city, team.vanue_capacity, id); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); cmd.ExecuteNonQuery(); conn.Close(); return(team); } }
/// <summary> /// Adds new game in data base /// </summary> /// <param name="game">given game to be added to database</param> /// <returns>game</returns> public async Task <Game> Add(Game game) { _context = new MyFootballContext(); StringBuilder query = new StringBuilder(); query.AppendFormat("insert into game (eventDate, goalsHomeTeam, goalsAwayTeam, venue, elapsed, fk_Teamid, fk_Teamid1)" + " values ('{0}',{1},{2},'{3}',{4},{5},{6})", game.eventDate.ToString(), game.goalsHomeTeam, game.goalsAwayTeam, game.venue, game.elapsed, game.fk_Teamid, game.fk_Teamid1); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); cmd.ExecuteNonQuery(); conn.Close(); return(game); } }
/// <summary> /// returns user by choosen id /// </summary> /// <param name="id">searching user`s id</param> /// <returns>user</returns> public async Task <User> FindAsync(int id) { User userById; _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string query = "select * from webuser where id = " + id.ToString(); MySqlCommand cmd = new MySqlCommand(query, conn); using (var reader = cmd.ExecuteReader()) { reader.Read(); if (!reader.HasRows) { return(null); } string username = reader["username"].ToString(); string password = reader["password"].ToString(); string role = reader["role"].ToString(); string email = reader["email"].ToString(); int? favouriteTeam = null; if (reader["favouriteTeam"].ToString().Length > 0) { favouriteTeam = Convert.ToInt32(reader["favouriteTeam"]); } userById = new User() { id = id, username = username, password = password, role = role, email = email, favouriteTeam = favouriteTeam }; reader.Close(); } conn.Close(); return(userById); } }
/// <summary> /// Finds game by id /// </summary> /// <param name="id">searching game`s id</param> /// <returns>game</returns> public async Task <Game> FindAsync(int id) { Game gameById; _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string query = "select * from game where id = " + id.ToString(); MySqlCommand cmd = new MySqlCommand(query, conn); using (var reader = cmd.ExecuteReader()) { reader.Read(); if (!reader.HasRows) { return(null); } DateTime eventDate = Convert.ToDateTime(reader["eventDate"]); int goalsHomeTeam = Convert.ToInt32(reader["goalsHomeTeam"]); int goalsAwayTeam = Convert.ToInt32(reader["goalsAwayTeam"]); string venue = reader["venue"].ToString(); int elapsed = Convert.ToInt32(reader["elapsed"]); int fk_Teamid = Convert.ToInt32(reader["fk_Teamid"]); int fk_Teamid1 = Convert.ToInt32(reader["fk_Teamid1"]); gameById = new Game() { id = id, eventDate = eventDate, goalsHomeTeam = goalsHomeTeam, goalsAwayTeam = goalsAwayTeam, venue = venue, elapsed = elapsed, fk_Teamid = fk_Teamid, fk_Teamid1 = fk_Teamid1 }; reader.Close(); } conn.Close(); return(gameById); } }
/// <summary> /// Gets all games from database /// </summary> /// <returns>list of all games</returns> public List <Game> GetGames() { List <Game> list = new List <Game>(); _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); MySqlCommand cmd = new MySqlCommand("select * from game", conn); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { int id = Convert.ToInt32(reader["id"]); DateTime eventDate = Convert.ToDateTime(reader["eventDate"]); int goalsHomeTeam = Convert.ToInt32(reader["goalsHomeTeam"]); int goalsAwayTeam = Convert.ToInt32(reader["goalsAwayTeam"]); string venue = reader["venue"].ToString(); int elapsed = Convert.ToInt32(reader["elapsed"]); int fk_Teamid = Convert.ToInt32(reader["fk_Teamid"]); int fk_Teamid1 = Convert.ToInt32(reader["fk_Teamid1"]); list.Add(new Game() { id = id, eventDate = eventDate, goalsHomeTeam = goalsHomeTeam, goalsAwayTeam = goalsAwayTeam, venue = venue, elapsed = elapsed, fk_Teamid = fk_Teamid, fk_Teamid1 = fk_Teamid1 }); } reader.Close(); } conn.Close(); return(list); } }
public async Task <Player> PutPlayer(int id, Player player) { _context = new MyFootballContext(); StringBuilder query = new StringBuilder(); query.AppendFormat("Update player Set firstName = '{0}', lastName = '{1}', birthDate = '{2}', age = {3}, possition = '{4}', nationality = '{5}', " + "weight = {6}, height = {7}, goals = {8}, league = '{9}', injured = {10}, season = '{11}', fk_Teamid = {12} where id = {13}", player.first_name, player.last_name, player.birth_date.ToShortDateString(), player.age, player.possition, player.nationality, player.weight, player.height, player.goals, player.league, player.injured, player.season, player.fk_TeamId, id); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); cmd.ExecuteNonQuery(); conn.Close(); return(player); } }
/// <summary> /// Inserts new user in database /// </summary> /// <param name="user">new user</param> /// <returns>user</returns> public async Task <User> Add(User user) { _context = new MyFootballContext(); StringBuilder query = new StringBuilder(); string encPass = EncDecService.Encrypt(user.password); query.AppendFormat("insert into webuser (username, password, role, email, favouriteTeam)" + " values ('{0}','{1}','{2}','{3}','{4}')", user.username, encPass, "User", user.email, user.favouriteTeam); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); cmd.ExecuteNonQuery(); user.password = encPass; conn.Close(); } return(user); }
/// <summary> /// Returns list of all website users /// </summary> /// <returns>website users list</returns> public List <User> GetUsers() { List <User> list = new List <User>(); _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); MySqlCommand cmd = new MySqlCommand("select * from webuser", conn); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { int id = Convert.ToInt32(reader["id"]); string username = reader["username"].ToString(); string password = reader["password"].ToString(); string role = reader["role"].ToString(); string email = reader["email"].ToString(); int? favouriteTeam = null; if (reader["favouriteTeam"].ToString().Length > 0) { favouriteTeam = Convert.ToInt32(reader["favouriteTeam"]); } list.Add(new User() { id = id, username = username, password = password, role = role, email = email, favouriteTeam = favouriteTeam }); } reader.Close(); } conn.Close(); return(list); } }
public bool Exist(int id) { _context = new MyFootballContext(); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string query = "select * from player where id = " + id.ToString(); MySqlCommand cmd = new MySqlCommand(query, conn); using (var reader = cmd.ExecuteReader()) { reader.Read(); if (!reader.HasRows) { return(false); } reader.Close(); } conn.Close(); return(true); } }
private User getUserByUsername(string username) { User userFromDB; _context = new MyFootballContext(); StringBuilder query = new StringBuilder(); query.AppendFormat("select * from webuser where webuser.username = '******'", username); using (MySqlConnection conn = _context.GetConnection()) { conn.Open(); string q = query.ToString(); MySqlCommand cmd = new MySqlCommand(q, conn); using (var reader = cmd.ExecuteReader()) { reader.Read(); if (!reader.HasRows) { return(null); } string usernameDB = reader["username"].ToString(); string passwordDB = reader["password"].ToString(); string role = reader["role"].ToString(); userFromDB = new User() { username = usernameDB, password = passwordDB, role = role }; reader.Close(); } conn.Close(); return(userFromDB); } }