/// <summary>
/// Edits information about the event.
/// </summary>
/// <param name="eventToUpdate">The event to update.</param>
public void EditEvent(Event eventToUpdate)
{
_DbMatch = new DBMatch();
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 Event SET Name = @Name, GameName = @GameName, Type = @Type WHERE id = @id";
command.Parameters.AddWithValue("id", eventToUpdate.Id);
command.Parameters.AddWithValue("Name", eventToUpdate.Name);
command.Parameters.AddWithValue("GameName", eventToUpdate.GameName);
command.Parameters.AddWithValue("Type", eventToUpdate.Type);
command.ExecuteNonQuery();
if (eventToUpdate != null)
{
foreach (Match m in eventToUpdate.Matches)
{
_DbMatch.EditMatch(m);
}
}
}
connection.Close();
}
scope.Complete();
}
}
/// <summary>
/// Deletes the event given by the parameter.
/// </summary>
/// <param name="eventToDelete">The event to delete.</param>
public void DeleteEvent(Event eventToDelete)
{
_DbMatch = new DBMatch();
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 Event WHERE Id=@id; DBCC CHECKIDENT (Event, RESEED);";
command.Parameters.AddWithValue("id", eventToDelete.Id);
command.ExecuteNonQuery();
if (eventToDelete != null)
{
foreach (Match m in eventToDelete.Matches)
{
_DbMatch.DeleteMatch(m);
}
}
}
connection.Close();
}
scope.Complete();
}
}
/// <summary>
/// Gets All events.
/// </summary>
/// <returns>List of Events</returns>
public IEnumerable <Event> GetEvents()
{
List <Event> events = new List <Event>();
List <int> matchIdList = new List <int>();
_DbMatch = new DBMatch();
Event eventToAdd = 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";
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
eventToAdd = 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"))
};
events.Add(eventToAdd);
}
reader.Close();
command.Parameters.Clear();
foreach (Event e in events)
{
if (e != null)
{
e.Matches = _DbMatch.GetMatchesByEvent(e);
}
}
}
connection.Close();
}
scope.Complete();
}
return(events);
}
/// <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 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 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);
}
}