/// <summary>
/// Persists Team.
/// </summary>
/// <param name="team">The team.</param>
/// <returns>TeamId given to the persisted team</returns>
public int CreateTeam(Team team)
{
_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 = "INSERT INTO Team (Name) OUTPUT INSERTED.ID VALUES(@Name)";
command.Parameters.AddWithValue("Name", team.Name);
team.Id = (int)command.ExecuteScalar();
if (team != null)
{
if (team.Players != null)
{
foreach (Player p in team.Players)
{
_DbPlayer.EditPlayer(p);
}
}
}
}
connection.Close();
}
scope.Complete();
}
return(team.Id);
}
/// <summary>
/// Gets all teams except free agents.
/// </summary>
/// <returns>List of Teams</returns>
public IEnumerable <Team> GetTeams()
{
List <Team> teams = new List <Team>();
_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";
var reader = command.ExecuteReader();
while (reader.Read())
{
Team team = new Team {
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name"))
};
if (team.Id > 1)
{
teams.Add(team);
}
}
reader.Close();
foreach (Team t in teams)
{
if (t != null)
{
t.Players = _DbPlayer.GetPlayersByTeam(t);
foreach (Player p in t.Players)
{
p.TeamId = t.Id;
}
}
}
}
connection.Close();
}
scope.Complete();
}
return(teams);
}
/// <summary>
/// Gets the teams in a given match.
/// </summary>
/// <param name="match">The match.</param>
/// <returns>List of teams</returns>
public List <Team> GetTeamsByMatch(Match match)
{
List <Team> teams = new List <Team>();
_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 t.Id, t.Name FROM Team t INNER JOIN TeamsInMatch tim ON t.Id = tim.TeamId WHERE tim.MatchId = @MatchId";
command.Parameters.AddWithValue("MatchId", match.Id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Team team = new Team {
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name"))
};
teams.Add(team);
}
reader.Close();
foreach (Team t in teams)
{
if (t != null)
{
t.Players = _DbPlayer.GetPlayersByTeam(t);
foreach (Player p in t.Players)
{
p.TeamId = t.Id;
}
}
}
}
connection.Close();
}
scope.Complete();
}
return(teams);
}
/// <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>
/// 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);
}
}
