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