/// <summary>
/// Edits team specific information.
/// </summary>
/// <param name="team">The team.</param>
public void EditTeam(Team team)
{
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "UPDATE Team SET Name=@name WHERE Id=@id";
command.Parameters.AddWithValue("name", team.Name);
command.Parameters.AddWithValue("id", team.Id);
command.ExecuteNonQuery();
}
connection.Close();
}
scope.Complete();
}
}
/// <summary>
/// Deletes a persisted user however the users bets is not deleted. The userId on the user's bets is set to 1 for statistical purposes.
/// </summary>
/// <param name="user">The user.</param>
public void DeleteUser(User user)
{
//Set transaction options with isolation level
TransactionOptions options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
//Create transaction scope with options
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) {
//Create sqlconnection and open it
using (SqlConnection conn = DBConnection.GetSqlConnection()) {
conn.Open();
//Create sqlcommand
using (SqlCommand cmd = conn.CreateCommand()) {
//Delete users bets in database
cmd.CommandText = "UPDATE Bet SET UserId = @UserId WHERE UserId = @id";
cmd.Parameters.AddWithValue("UserId", 1);
cmd.Parameters.AddWithValue("id", user.Id);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//Delete user data in database
cmd.CommandText = "DELETE FROM [User] WHERE id = @id; DBCC CHECKIDENT (@tableName, RESEED);";
cmd.Parameters.AddWithValue("id", user.Id);
cmd.Parameters.AddWithValue("tableName", "[User]");
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//Delete users account data in database
cmd.CommandText = "DELETE FROM Account WHERE Id = @id; DBCC CHECKIDENT (@tableName, RESEED);";
cmd.Parameters.AddWithValue("id", user.Account.Id);
cmd.Parameters.AddWithValue("tableName", "Account");
cmd.ExecuteNonQuery();
}
//Close connection for good measure
conn.Close();
}
//Close scope for good measure
scope.Complete();
}
}
/// <summary>
/// Edits information about the map.
/// </summary>
/// <param name="map">The map.</param>
public void EditMap(Map map)
{
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "UPDATE Map SET Name = @Name, IsActive = @IsActive WHERE Id = @Id";
command.Parameters.AddWithValue("@Name", map.Name);
command.Parameters.AddWithValue("@IsActive", map.IsActive);
command.Parameters.AddWithValue("@Id", map.Id);
command.ExecuteNonQuery();
}
connection.Close();
}
scope.Complete();
}
}
/// <summary>
/// Deletes the persisted map.
/// </summary>
/// <param name="map">The map.</param>
public void DeleteMap(Map map)
{
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "DELETE FROM Map WHERE Id=@id; DBCC CHECKIDENT (@tableName, RESEED);";
command.Parameters.AddWithValue("tableName", "Map");
command.Parameters.AddWithValue("id", map.Id);
command.ExecuteNonQuery();
command.Parameters.Clear();
}
connection.Close();
}
scope.Complete();
}
}
/// <summary>
/// Persists Map object
/// </summary>
/// <param name="map">The map.</param>
/// <returns>MapId given to the persisted map</returns>
public int CreateMap(Map map)
{
int mapId;
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "INSERT INTO Map (Name, IsActive) OUTPUT INSERTED.ID VALUES(@Name, @IsActive)";
command.Parameters.AddWithValue("@Name", map.Name);
command.Parameters.AddWithValue("@IsActive", map.IsActive);
mapId = (int)command.ExecuteScalar();
}
connection.Close();
}
scope.Complete();
}
return(mapId);
}
/// <summary>
/// Gets the winner team by match.
/// </summary>
/// <param name="match">The match.</param>
/// <returns>Team</returns>
public Team GetWinnerTeamByMatch(Match match)
{
_DbPlayer = new DBPlayer();
Team team = null;
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "SELECT t.Id, t.Name FROM Team t INNER JOIN Match m ON t.Id = m.WinnerId WHERE m.Id = @MatchId";
command.Parameters.AddWithValue("MatchId", match.Id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
team = new Team {
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name"))
};
}
reader.Close();
if (team != null)
{
team.Players = _DbPlayer.GetPlayersByTeam(team);
foreach (Player p in team.Players)
{
p.TeamId = team.Id;
}
}
}
connection.Close();
}
scope.Complete();
}
return(team);
}
/// <summary>
/// Gets an event by unique identifier.
/// </summary>
/// <param name="id">The identifier.</param>
/// <returns>The found event</returns>
public Event GetEvent(int id)
{
_DbMatch = new DBMatch();
Event foundEvent = null;
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "SELECT Id, Name, GameName, Type FROM Event WHERE Id=@id";
command.Parameters.AddWithValue("Id", id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
foundEvent = new Event {
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
GameName = reader.GetString(reader.GetOrdinal("GameName")),
Type = reader.GetString(reader.GetOrdinal("Type"))
};
}
reader.Close();
if (foundEvent != null)
{
foundEvent.Matches = _DbMatch.GetMatchesByEvent(foundEvent);
}
}
connection.Close();
}
scope.Complete();
}
return(foundEvent);
}
/// <summary>
/// Persists Player object
/// </summary>
/// <param name="player">The player.</param>
/// <returns>PlayerId given to the persisted player</returns>
public int CreatePlayer(Player player)
{
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "INSERT INTO Player (NickName, Age, Role, TeamId) OUTPUT INSERTED.ID VALUES(@NickName, @Age, @Role, @TeamId)";
command.Parameters.AddWithValue("NickName", player.NickName);
command.Parameters.AddWithValue("Age", player.Age);
command.Parameters.AddWithValue("Role", player.Role);
command.Parameters.AddWithValue("TeamId", player.TeamId);
player.Id = (int)command.ExecuteScalar();
}
connection.Close();
}
scope.Complete();
}
return(player.Id);
}
/// <summary>
/// Edits information on a persisted player.
/// </summary>
/// <param name="player">The player.</param>
public void EditPlayer(Player player)
{
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "UPDATE Player SET NickName=@NickName, Age=@Age, Role=@Role, TeamId=@TeamId WHERE @Id=id";
command.Parameters.AddWithValue("NickName", player.NickName);
command.Parameters.AddWithValue("Age", player.Age);
command.Parameters.AddWithValue("Role", player.Role);
command.Parameters.AddWithValue("TeamId", player.TeamId);
command.Parameters.AddWithValue("id", player.Id);
command.ExecuteNonQuery();
}
connection.Close();
}
scope.Complete();
}
}
/// <summary>
/// Persists Event object
/// </summary>
/// <param name="eventToCreate">The event to create.</param>
/// <returns>EventId given to the persisted event</returns>
public int CreateEvent(Event eventToCreate)
{
_DbMatch = new DBMatch();
int eventId = 0;
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "INSERT INTO Event (Name, GameName, Type) OUTPUT INSERTED.ID VALUES(@Name, @GameName, @Type)";
command.Parameters.AddWithValue("Name", eventToCreate.Name);
command.Parameters.AddWithValue("GameName", eventToCreate.GameName);
command.Parameters.AddWithValue("Type", eventToCreate.Type);
eventId = (int)command.ExecuteScalar();
eventToCreate.Id = eventId;
if (eventToCreate != null)
{
if (eventToCreate.Matches != null)
{
foreach (Match m in eventToCreate.Matches)
{
m.Id = eventToCreate.Id;
_DbMatch.CreateMatch(m);
}
}
}
}
connection.Close();
}
scope.Complete();
}
return(eventId);
}
/// <summary>
/// Gets the team via a unique identifier.
/// </summary>
/// <param name="id">The identifier.</param>
/// <returns>Found team with players</returns>
public Team GetTeam(int id)
{
Team team = null;
_DbPlayer = new DBPlayer();
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "SELECT Id, Name FROM Team WHERE Id=@id";
command.Parameters.AddWithValue("Id", id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
team = new Team {
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name"))
};
}
reader.Close();
if (team != null)
{
team.Players = _DbPlayer.GetPlayersByTeam(team);
}
}
connection.Close();
}
scope.Complete();
}
return(team);
}
/// <summary>
/// Persists User object.
/// </summary>
/// <param name="user">The user.</param>
public void CreateUser(User user)
{
List <int> ids = new List <int>();
//Set transaction options with isolation level
TransactionOptions options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
//Create transaction scope with options
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) {
//Create sqlconnection and open it
using (SqlConnection conn = DBConnection.GetSqlConnection()) {
conn.Open();
//Create sqlcommand
using (SqlCommand cmd = conn.CreateCommand()) {
//First persist account data in the database and get the id
cmd.CommandText = "INSERT INTO Account (Balance) OUTPUT INSERTED.ID VALUES (@balance)";
cmd.Parameters.AddWithValue("balance", user.Account.Balance);
user.Account.Id = (int)cmd.ExecuteScalar();
cmd.Parameters.Clear();
//Persist the user data in the database
cmd.CommandText = "INSERT INTO [User] (FirstName, LastName, Username, Email, PhoneNumber, AccountId) OUTPUT INSERTED.ID VALUES (@firstName, @lastName, @userName, @email, @phoneNumber, @accountId)";
cmd.Parameters.AddWithValue("firstName", user.FirstName);
cmd.Parameters.AddWithValue("lastName", user.LastName);
cmd.Parameters.AddWithValue("userName", user.UserName);
cmd.Parameters.AddWithValue("email", user.Email);
cmd.Parameters.AddWithValue("phoneNumber", user.PhoneNumber);
cmd.Parameters.AddWithValue("accountId", user.Account.Id);
user.Id = (int)cmd.ExecuteScalar();
}
//Close connection for good measure
conn.Close();
}
//Close scope for good measure
scope.Complete();
}
}
/// <summary>
/// Gets all players.
/// </summary>
/// <returns>List of Players</returns>
public IEnumerable <Player> GetPlayers()
{
List <Player> players = new List <Player>();
DBTeam dBTeam = new DBTeam();
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
Dictionary <Player, int> teamIdToPlayer = new Dictionary <Player, int>();
command.CommandText = "SELECT Id, NickName, Age, Role, TeamId FROM Player";
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Player player = new Player {
Id = reader.GetInt32(reader.GetOrdinal("Id")),
NickName = reader.GetString(reader.GetOrdinal("NickName")),
Age = reader.GetString(reader.GetOrdinal("Age")),
Role = reader.GetString(reader.GetOrdinal("Role")),
TeamId = reader.GetInt32(reader.GetOrdinal("TeamId"))
};
players.Add(player);
}
reader.Close();
command.Parameters.Clear();
}
connection.Close();
}
scope.Complete();
}
return(players);
}
/// <summary>
/// Adds a bet to user.
/// </summary>
/// <param name="user">The user.</param>
/// <param name="bet">The bet.</param>
public void AddBetToUser(User user, Bet bet)
{
try {
//Set transaction options with isolation level
TransactionOptions options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
//Create transaction scope with options
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) {
//Create sqlconnection and open it
using (SqlConnection conn = DBConnection.GetSqlConnection()) {
conn.Open();
//Create sqlcommand
using (SqlCommand cmd = conn.CreateCommand()) {
int BetId = 0;
//Add bets to user
cmd.CommandText = "INSERT INTO Bet (Amount, Odds, UserId) OUTPUT INSERTED.ID VALUES (@amount, @Odds, @UserId)";
cmd.Parameters.AddWithValue("amount", bet.Amount);
cmd.Parameters.AddWithValue("odds", bet.Odds);
cmd.Parameters.AddWithValue("UserId", user.Id);
BetId = (int)cmd.ExecuteScalar();
bet.Id = BetId;
cmd.Parameters.Clear();
if (bet.Type is Match)
{
cmd.CommandText = "INSERT INTO BetsOnMatch (MatchId, BetId) OUTPUT INSERTED.ID VALUES (@matchId, @betId)";
cmd.Parameters.AddWithValue("matchId", (bet.Type as Match).Id);
cmd.Parameters.AddWithValue("betId", bet.Id);
int BomId = (int)cmd.ExecuteScalar();
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO TeamsOnBetsOnMatch(TeamId, BomId) VALUES(@TeamId, @BomId)";
cmd.Parameters.AddWithValue("TeamId", (bet.WinCondition as Team).Id);
cmd.Parameters.AddWithValue("BomId", BomId);
cmd.ExecuteNonQuery();
}
if (bet.Type is Event)
{
cmd.CommandText = "INSERT INTO BetsOnEvent (EventId, BetId) VALUES (@eventId, @betId)";
cmd.Parameters.AddWithValue("eventId", (bet.Type as Event).Id);
cmd.Parameters.AddWithValue("betId", bet.Id);
cmd.ExecuteNonQuery();
}
if (bet.Type is Team)
{
cmd.CommandText = "INSERT INTO BetsOnTeam (TeamId, BetId) VALUES (@teamId, @betId)";
cmd.Parameters.AddWithValue("TeamId", (bet.Type as Team).Id);
cmd.Parameters.AddWithValue("betId", bet.Id);
cmd.ExecuteNonQuery();
}
if (bet.Type is Player)
{
cmd.CommandText = "INSERT INTO BetsOnPlayer (PlayerId, BetId) VALUES (@playerId, @betId)";
cmd.Parameters.AddWithValue("uplayerId", (bet.Type as Player).Id);
cmd.Parameters.AddWithValue("betId", bet.Id);
cmd.ExecuteNonQuery();
}
}
//Close connection for good measure
conn.Close();
}
//Close scope for good measure
scope.Complete();
}
} catch (System.Exception) {
throw;
}
}
/// <summary>
/// Gets the bets from a given User.
/// </summary>
/// <param name="user">The user.</param>
/// <returns>List of Bets</returns>
public List <Bet> GetBets(User user)
{
List <Bet> betsList = new List <Bet>();
Bet bet = null;
int typeId = -1;
int bomId = -1;
int teamId = -1;
string typeString = "";
SqlDataReader reader;
//Set transaction options with isolation level
TransactionOptions options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
//Create transaction scope with options
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) {
//Create sqlconnection and open it
using (SqlConnection conn = DBConnection.GetSqlConnection()) {
conn.Open();
//Create sqlcommand
using (SqlCommand cmd = conn.CreateCommand()) {
//Get all bets on user from the database
cmd.CommandText = "SELECT b.id, b.amount, b.odds, b.Verified FROM Bet b, [User] u WHERE b.UserId = u.Id AND b.UserId = @userId";
cmd.Parameters.AddWithValue("userId", user.Id);
reader = cmd.ExecuteReader();
while (reader.Read())
{
bet = new Bet {
Id = int.Parse(reader["Id"].ToString()),
Amount = decimal.Parse(reader["Amount"].ToString()),
Odds = decimal.Parse(reader["Odds"].ToString()),
Verified = bool.Parse(reader["Verified"].ToString())
};
betsList.Add(bet);
}
reader.Close();
foreach (Bet b in betsList)
{
if (b != null)
{
typeString = "";
cmd.Parameters.Clear();
cmd.CommandText = "SELECT e.eventId AS eventId, m.matchId AS matchId, t.teamId AS teamId, p.playerId AS playerId, tobom.TeamId as team2Id FROM bet AS b LEFT JOIN betsonevent AS e ON e.betid = b.Id LEFT JOIN BetsOnMatch AS m ON m.betid = b.Id LEFT JOIN betsonteam AS t ON t.betid = b.Id LEFT JOIN TeamsOnBetsOnMatch as tobom on m.Id = tobom.BomId LEFT JOIN betsonplayer AS p ON p.betid = b.Id WHERE b.id = @betId";
cmd.Parameters.AddWithValue("betId", b.Id);
reader = cmd.ExecuteReader();
if (reader.Read())
{
if (!string.IsNullOrEmpty(reader["matchId"].ToString()))
{
typeId = int.Parse(reader["matchId"].ToString());
bomId = int.Parse(reader["team2Id"].ToString());
typeString = "matchId";
}
else if (!string.IsNullOrEmpty(reader["eventId"].ToString()))
{
typeId = int.Parse(reader["eventId"].ToString());
typeString = "eventId";
}
else if (!string.IsNullOrEmpty(reader["teamId"].ToString()))
{
typeId = int.Parse(reader["teamId"].ToString());
typeString = "teamId";
}
else if (!string.IsNullOrEmpty(reader["playerId"].ToString()))
{
typeId = int.Parse(reader["playerId"].ToString());
typeString = "playerId";
}
}
reader.Close();
if (!typeString.Equals(""))
{
if (typeString.Equals("matchId"))
{
reader.Close();
DBTeam dBTeam = new DBTeam();
Team team = dBTeam.GetTeam(bomId);
DBMatch dBMatch = new DBMatch();
Match match = dBMatch.GetMatch(typeId);
b.Type = match;
b.WinCondition = team;
}
else if (typeString.Equals("eventId"))
{
DBEvent dBEvent = new DBEvent();
Event betEvent = dBEvent.GetEvent(typeId);
b.Type = betEvent;
}
else if (typeString.Equals("teamId"))
{
DBTeam dBTeam = new DBTeam();
Team team = dBTeam.GetTeam(typeId);
b.Type = team;
}
else if (typeString.Equals("playerId"))
{
DBPlayer dBPlayer = new DBPlayer();
Player player = dBPlayer.GetPlayer(typeId);
b.Type = player;
}
}
}
}
//Close connection for good measure
conn.Close();
}
//Close scope for good measure
scope.Complete();
}
return(betsList);
}
}
/// <summary>
/// Edits information for the persisted match.
/// </summary>
/// <param name="match">The match.</param>
public void EditMatch(Match match)
{
TransactionOptions to = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, to)) {
using (SqlConnection connection = DBConnection.GetSqlConnection()) {
connection.Open();
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "UPDATE Match SET Format=@Format, WinnerId=@WinnerId, EventId=@EventId, Held=@Held WHERE Id=@id";
command.Parameters.AddWithValue("Format", match.Format);
if (match.Winner == null)
{
command.Parameters.AddWithValue("WinnerId", -1);
}
else
{
command.Parameters.AddWithValue("WinnerId", match.Winner.Id);
}
if (match.Held)
{
command.Parameters.AddWithValue("Held", 1);
}
else
{
command.Parameters.AddWithValue("Held", 0);
}
command.Parameters.AddWithValue("EventId", match.EventId);
command.Parameters.AddWithValue("id", match.Id);
command.ExecuteNonQuery();
foreach (Map map in match.Maps)
{
command.CommandText = "UPDATE MapsOnMatch SET MatchId = @MatchId, MapId = @MapId WHERE MatchId = @MatchId";
command.Parameters.AddWithValue("MatchId", match.Id);
command.Parameters.AddWithValue("MapId", map.Id);
command.ExecuteNonQuery();
command.Parameters.Clear();
}
SqlDataReader reader;
command.CommandText = "SELECT Id FROM TeamsInMatch WHERE MatchId = @MatchId";
command.Parameters.AddWithValue("@MatchId", match.Id);
reader = command.ExecuteReader();
int[] ids = new int[2];
int count = 0;
while (reader.Read())
{
ids[count] = reader.GetInt32(reader.GetOrdinal("Id"));
count++;
}
command.Parameters.Clear();
reader.Close();
for (int i = 0; i < match.Teams.Count; i++)
{
command.CommandText = "UPDATE TeamsInMatch SET TeamId = @TeamId WHERE Id = @id";
command.Parameters.AddWithValue("@TeamId", match.Teams[i].Id);
command.Parameters.AddWithValue("@id", ids[i]);
command.ExecuteNonQuery();
command.Parameters.Clear();
}
match.GenerateName(match.Teams[0], match.Teams[1]);
}
connection.Close();
}
scope.Complete();
}
}
/// <summary>
/// Gets all users with won bets for a given match.
/// </summary>
/// <param name="match">The match.</param>
/// <returns>List of Users</returns>
public List <User> GetBetsOnUserOnMatch(Match match)
{
List <Bet> betsList = new List <Bet>();
List <User> users = new List <User>();
Bet bet = null;
List <int> userIds = new List <int>();
List <int> betIds = new List <int>();
SqlDataReader reader;
//Set transaction options with isolation level
TransactionOptions options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
//Create transaction scope with options
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) {
//Create sqlconnection and open it
using (SqlConnection conn = DBConnection.GetSqlConnection()) {
conn.Open();
//Create sqlcommand
using (SqlCommand cmd = conn.CreateCommand()) {
//Get all bets on user from the database
cmd.CommandText = "SELECT b.id as BetId, b.Amount as Amount, b.Odds as Odds, b.Verified as Verified, b.UserId as UserId FROM Bet as b INNER JOIN BetsOnMatch as bom ON b.Id = bom.BetId INNER JOIN TeamsOnBetsOnMatch as tobom ON tobom.BomId = bom.Id WHERE bom.MatchId = @MatchId AND tobom.TeamId = @WinnerId; ";
cmd.Parameters.AddWithValue("matchId", match.Id);
cmd.Parameters.AddWithValue("winnerId", match.Winner.Id);
reader = cmd.ExecuteReader();
while (reader.Read())
{
bet = new Bet {
Id = int.Parse(reader["BetId"].ToString()),
Amount = decimal.Parse(reader["Amount"].ToString()),
Odds = decimal.Parse(reader["Odds"].ToString()),
Verified = bool.Parse(reader["Verified"].ToString()),
};
betIds.Add(int.Parse(reader["BetId"].ToString()));
userIds.Add(int.Parse(reader["UserId"].ToString()));
betsList.Add(bet);
}
reader.Close();
foreach (int abe in userIds)
{
if (abe != 1)
{
users.Add(GetUser(abe));
}
}
foreach (User u in users)
{
u.Bets.RemoveAll(item => !betsList.Contains(item));
u.Bets.RemoveAll(item => item.Verified == true);
}
foreach (Bet b in betsList)
{
if (b != null)
{
b.Type = match;
b.WinCondition = match.Winner;
}
}
//Close connection for good measure
conn.Close();
}
//Close scope for good measure
scope.Complete();
}
return(users);
}
}
/// <summary>
/// Gets a user on a unique identifier.
/// </summary>
/// <param name="id">The identifier.</param>
/// <returns>The found user</returns>
public User GetUser(int id)
{
User user = null;
int accountId = 0;
SqlDataReader reader;
//Set transaction options with isolation level
TransactionOptions options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
//Create transaction scope with options
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) {
//Create sqlconnection and open it
using (SqlConnection conn = DBConnection.GetSqlConnection()) {
conn.Open();
//Create sqlcommand
using (SqlCommand cmd = conn.CreateCommand()) {
//Get the user data from the database and instantiate user
cmd.CommandText = "SELECT Id, FirstName, LastName, Username, Email, phoneNumber, AccountId FROM [User] where Id = @id";
cmd.Parameters.AddWithValue("id", id);
reader = cmd.ExecuteReader();
while (reader.Read())
{
user = new User {
Id = int.Parse(reader["Id"].ToString()),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
UserName = reader["Username"].ToString(),
Email = reader["Email"].ToString(),
PhoneNumber = reader["phoneNumber"].ToString()
};
accountId = int.Parse(reader["AccountId"].ToString());
}
cmd.Parameters.Clear();
reader.Close();
//Get the account data from the database and set account on user
cmd.CommandText = "SELECT Id, Balance FROM Account where Id = @id";
cmd.Parameters.AddWithValue("id", accountId);
reader = cmd.ExecuteReader();
while (reader.Read())
{
Account account = new Account {
Id = int.Parse(reader["Id"].ToString()),
Balance = decimal.Parse(reader["Balance"].ToString())
};
user.Account = account;
}
reader.Close();
}
//Close connection for good measure
conn.Close();
}
//Close scope for good measure
scope.Complete();
}
//Add bets to user
if (user != null)
{
user.Bets = GetBets(user);
}
return(user);
}
/// <summary>
/// Gets all users.
/// </summary>
/// <returns>List of users</returns>
public List <User> GetAllUsers()
{
List <User> userList = new List <User>();
User user = null;
SqlDataReader reader;
//Set transaction options with isolation level
TransactionOptions options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
//Create transaction scope with options
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) {
//Create sqlconnection and open it
using (SqlConnection conn = DBConnection.GetSqlConnection()) {
conn.Open();
//Create sqlcommand
using (SqlCommand cmd = conn.CreateCommand()) {
//Get all user data from the database
cmd.CommandText = "SELECT Id, FirstName, LastName, Username, Email, phoneNumber, AccountId FROM [User]";
reader = cmd.ExecuteReader();
while (reader.Read())
{
user = new User {
Id = int.Parse(reader["Id"].ToString()),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
UserName = reader["Username"].ToString(),
Email = reader["Email"].ToString(),
PhoneNumber = reader["phoneNumber"].ToString()
};
if (user.Id > 1)
{
userList.Add(user);
}
}
cmd.Parameters.Clear();
reader.Close();
//Get account for each user and add it to user
foreach (User u in userList)
{
cmd.CommandText = "SELECT A.Id, A.Balance FROM Account A, [User] U WHERE A.Id = u.AccountId AND U.Id = @id";
cmd.Parameters.AddWithValue("id", u.Id);
reader = cmd.ExecuteReader();
reader.Read();
Account account = new Account {
Id = int.Parse(reader["Id"].ToString()),
Balance = decimal.Parse(reader["Balance"].ToString())
};
u.Account = account;
cmd.Parameters.Clear();
reader.Close();
}
}
//Add bets to user
foreach (User u in userList)
{
u.Bets = GetBets(u);
}
//Close connection for good measure
conn.Close();
}
//Close scope for good measure
scope.Complete();
}
return(userList);
}