/// <summary>
/// Gets the player via a unique identifier.
/// </summary>
/// <param name="id">The identifier.</param>
/// <returns>The found player</returns>
public Player GetPlayer(int id)
{
Player player = null;
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()) {
command.CommandText = "SELECT Id, NickName, Age, Role, TeamId FROM Player WHERE Id=@id";
command.Parameters.AddWithValue("Id", id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
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"))
};
}
reader.Close();
command.Parameters.Clear();
}
connection.Close();
}
scope.Complete();
}
return(player);
}
/// <summary>
/// Gets the matches by event.
/// </summary>
/// <param name="eventt">The eventt.</param>
/// <returns>List of matches</returns>
public List <Match> GetMatchesByEvent(Event eventt)
{
List <Match> matches = new List <Match>();
Match match = null;
DBTeam dBTeam = new DBTeam();
SqlDataReader reader;
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, Format, WinnerId, EventId FROM Match WHERE EventId = @EventId";
command.Parameters.AddWithValue("EventId", eventt.Id);
reader = command.ExecuteReader();
while (reader.Read())
{
match = new Match {
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Format = reader.GetString(reader.GetOrdinal("Format")),
EventId = reader.GetInt32(reader.GetOrdinal("EventId"))
};
matches.Add(match);
}
reader.Close();
command.Parameters.Clear();
foreach (Match m in matches)
{
if (m != null)
{
GetMapsByMatch(m);
m.Teams = dBTeam.GetTeamsByMatch(m);
m.Winner = dBTeam.GetWinnerTeamByMatch(m);
if (m.Teams.Count != 0)
{
m.GenerateName(m.Teams[0], m.Teams[1]);
}
}
}
}
connection.Close();
}
scope.Complete();
}
return(matches);
}
/// <summary>
/// Gets the match by unique identifier.
/// </summary>
/// <param name="id">The identifier.</param>
/// <returns>The found match</returns>
public Match GetMatch(int id)
{
DBTeam dBTeam = new DBTeam();
DBEvent dBEvent = new DBEvent();
Match match = null;
int eventId = 0;
List <Map> mapList = new List <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 = "SELECT Id, Format, WinnerId, EventId FROM Match WHERE Id=@id";
command.Parameters.AddWithValue("Id", id);
int winnerId = 0;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
match = new Match {
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Format = reader.GetString(reader.GetOrdinal("Format")),
EventId = reader.GetInt32(reader.GetOrdinal("EventId"))
};
winnerId = reader.GetInt32(reader.GetOrdinal("WinnerId"));
eventId = reader.GetInt32(reader.GetOrdinal("EventId"));
}
command.Parameters.Clear();
reader.Close();
if (match != null)
{
GetMapsByMatch(match);
match.Teams = dBTeam.GetTeamsByMatch(match);
match.Winner = dBTeam.GetTeam(winnerId);
match.GenerateName(match.Teams[0], match.Teams[1]);
}
}
connection.Close();
}
scope.Complete();
}
return(match);
}
/// <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>
/// 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);
}
}
