public Team(int teamId)
{
string queryString = "SELECT * FROM [dbo].[Teams] WHERE TeamId = @teamId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("teamId", teamId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
this.TeamId = reader.GetInt32(0);
this.LeagueId = reader.GetInt32(1);
this.League = reader.GetString(2);
this.ConferenceId = reader.GetInt32(3);
this.Conference = reader.GetString(4);
this.Name = reader.GetString(5);
this.City = reader.GetString(6);
this.ScrapId = reader.GetString(7);
this.ScrapUrl = reader.GetString(8);
this.Scrap2Url = reader.GetString(9);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, teamId);
}
}
}
public Team(int teamId)
{
string queryString = "SELECT * FROM [dbo].[Teams] WHERE TeamId = @teamId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("teamId", teamId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
this.TeamId = reader.GetInt32(0);
this.LeagueId = reader.GetInt32(1);
this.League = reader.GetString(2);
this.ConferenceId = reader.GetInt32(3);
this.Conference = reader.GetString(4);
this.Name = reader.GetString(5);
this.City = reader.GetString(6);
this.ScrapId = reader.GetString(7);
this.ScrapUrl = reader.GetString(8);
this.Scrap2Url = reader.GetString(9);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, teamId);
}
}
}
public int[] NBABetGameIds()
{
List <int> gameIds = new List <int>();
string queryString = "Select * From NBABets Order By AGameDate";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
//Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
//command.CommandType = System.Data.CommandType.StoredProcedure;
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
gameIds.Add(reader.GetInt32(4));
gameIds.Add(reader.GetInt32(5));
gameIds.Add(reader.GetInt32(6));
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return(gameIds.Where(gId => gId != 0).Distinct().ToArray());
}
public bool InsertBetStatus()
{
string query = "INSERT INTO [dbo].[BetStatus] " +
"([BetId] " +
",[BetStatusTypeId]) " +
" VALUES " +
"(@BetId " +
",@BetStatusTypeId)";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@BetId", this.BetId);
command.Parameters.AddWithValue("@BetStatusTypeId", this.BetStatusTypeId);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return(true);
}
public bool InsertBetStatus()
{
string query = "INSERT INTO [dbo].[BetStatus] " +
"([BetId] " +
",[BetStatusTypeId]) " +
" VALUES " +
"(@BetId " +
",@BetStatusTypeId)";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@BetId", this.BetId);
command.Parameters.AddWithValue("@BetStatusTypeId", this.BetStatusTypeId);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return true;
}
public List <Game> AllSeasonGames(int seasonId, int leagueId)
{
List <Game> games = new List <Game>();
string queryString = "sp_FullSchedule";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@leagueId", leagueId);
command.Parameters.AddWithValue("@seasonId", seasonId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Game game = new Game();
game.GameId = reader.GetInt32(0);
game.GameDate = reader.GetDateTime(1);
game.GameStatusTypeId = reader.GetInt32(2);
game.HomeTeamId = reader.GetInt32(3);
game.HomeTeamConferenceId = reader.GetInt32(4);
game.HomeTeamName = reader.GetString(5);
game.HomeTeamScore = reader.GetInt32(6);
game.HomeTeamMoneyLine = reader.GetInt32(7);
game.HomeTeamSpread = reader.GetDecimal(8);
game.AwayTeamId = reader.GetInt32(9);
game.AwayTeamConferenceId = reader.GetInt32(10);
game.AwayTeamName = reader.GetString(11);
game.AwayTeamScore = reader.GetInt32(12);
game.AwayTeamMoneyLine = reader.GetInt32(13);
game.AwayTeamSpread = reader.GetDecimal(14);
game.WinningTeamId = reader.GetInt32(15);
game.WinningTeamName = reader.GetString(16);
game.GameLocationName = reader.GetString(17);
game.SeasonId = reader.GetInt32(18);
games.Add(game);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return(games);
}
public Game(int gameId)
{
string queryString = "SELECT * FROM [dbo].[Games] WHERE GameId = @gameId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@gameId", gameId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
this.GameId = reader.GetInt32(0);
this.GameDate = reader.GetDateTime(1);
this.GameStatusTypeId = reader.GetInt32(2);
this.HomeTeamId = reader.GetInt32(3);
this.HomeTeamName = reader.GetString(4);
this.HomeTeamScore = reader.GetInt32(5);
this.HomeTeamSpreadPay = reader.GetDecimalSafe(6);
this.HomeTeamSpreadPayOpen = reader.GetDecimalSafe(7);
this.HomeTeamSpread = reader.GetDecimalSafe(8);
this.HomeTeamSpreadOpen = reader.GetDecimalSafe(9);
this.HomeTeamMoneyLine = reader.GetInt32(10);
this.HomeTeamMoneyLineOpen = reader.GetInt32(11);
//this.HomeTeamLine = reader.GetDecimalSafe(6);
this.AwayTeamId = reader.GetInt32(13);
this.AwayTeamName = reader.GetString(14);
this.AwayTeamScore = reader.GetInt32(15);
this.AwayTeamSpreadPay = reader.GetDecimalSafe(16);
this.AwayTeamSpreadPayOpen = reader.GetDecimalSafe(17);
this.AwayTeamSpread = reader.GetDecimalSafe(18);
this.AwayTeamSpreadOpen = reader.GetDecimalSafe(19);
this.AwayTeamMoneyLine = reader.GetInt32(20);
this.AwayTeamMoneyLineOpen = reader.GetInt32(21);
//this.AwayTeamLine = reader.GetDecimalSafe(11);
this.WinningTeamId = reader.GetInt32(23);
this.WinningTeamName = reader.GetString(24);
this.SeasonId = reader.GetInt32(25);
this.GameLocationName = reader.GetString(26);
this.WonSpread = reader.GetBoolean(27);
this.WonMoneyLine = reader.GetBoolean(28);
}
reader.Close();
this.GameDateFormated = this.GameDate.ToString("yyyyMMdd", System.Globalization.CultureInfo.CreateSpecificCulture("en-US"));
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, gameId);
}
}
}
public List <Game> MLBBetPotentials(int seasonId)
{
List <Game> games = new List <Game>();
string queryString = "sp_MLBBetPotentials";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@seasonId", seasonId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Game game = new Game();
game.HomeTeamId = reader.GetInt32(0);
game.AwayTeamId = reader.GetInt32(1);
game.WinningTeamId = reader.GetInt32(2);
games.Add(game);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
int listLength = games.Count;
Game tempGame;
Game removeGame;
for (int i = 0; i <= listLength; i++)
{
if (i <= games.Count)
{
removeGame = null;
tempGame = games[i];
removeGame = games.Where(g => g.HomeTeamId == tempGame.AwayTeamId && g.AwayTeamId == tempGame.HomeTeamId).FirstOrDefault();
if (removeGame != null)
{
games.Remove(removeGame);
}
}
else
{
break;
}
}
return(games);
}
public List<Game> AllSeasonGames(int seasonId, int leagueId)
{
List<Game> games = new List<Game>();
string queryString = "sp_FullSchedule";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@leagueId", leagueId);
command.Parameters.AddWithValue("@seasonId", seasonId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Game game = new Game();
game.GameId = reader.GetInt32(0);
game.GameDate = reader.GetDateTime(1);
game.GameStatusTypeId = reader.GetInt32(2);
game.HomeTeamId = reader.GetInt32(3);
game.HomeTeamConferenceId = reader.GetInt32(4);
game.HomeTeamName = reader.GetString(5);
game.HomeTeamScore = reader.GetInt32(6);
game.HomeTeamMoneyLine = reader.GetInt32(7);
game.HomeTeamSpread = reader.GetDecimal(8);
game.AwayTeamId = reader.GetInt32(9);
game.AwayTeamConferenceId = reader.GetInt32(10);
game.AwayTeamName = reader.GetString(11);
game.AwayTeamScore = reader.GetInt32(12);
game.AwayTeamMoneyLine = reader.GetInt32(13);
game.AwayTeamSpread = reader.GetDecimal(14);
game.WinningTeamId = reader.GetInt32(15);
game.WinningTeamName = reader.GetString(16);
game.GameLocationName = reader.GetString(17);
game.SeasonId = reader.GetInt32(18);
games.Add(game);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return games;
}
public Game(int gameId)
{
string queryString = "SELECT * FROM [dbo].[Games] WHERE GameId = @gameId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@gameId", gameId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
this.GameId = reader.GetInt32(0);
this.GameDate = reader.GetDateTime(1);
this.GameStatusTypeId = reader.GetInt32(2);
this.HomeTeamId = reader.GetInt32(3);
this.HomeTeamName = reader.GetString(4);
this.HomeTeamScore = reader.GetInt32(5);
this.HomeTeamSpreadPay = reader.GetDecimalSafe(6);
this.HomeTeamSpreadPayOpen = reader.GetDecimalSafe(7);
this.HomeTeamSpread = reader.GetDecimalSafe(8);
this.HomeTeamSpreadOpen = reader.GetDecimalSafe(9);
this.HomeTeamMoneyLine = reader.GetInt32(10);
this.HomeTeamMoneyLineOpen = reader.GetInt32(11);
//this.HomeTeamLine = reader.GetDecimalSafe(6);
this.AwayTeamId = reader.GetInt32(13);
this.AwayTeamName = reader.GetString(14);
this.AwayTeamScore = reader.GetInt32(15);
this.AwayTeamSpreadPay = reader.GetDecimalSafe(16);
this.AwayTeamSpreadPayOpen = reader.GetDecimalSafe(17);
this.AwayTeamSpread = reader.GetDecimalSafe(18);
this.AwayTeamSpreadOpen = reader.GetDecimalSafe(19);
this.AwayTeamMoneyLine = reader.GetInt32(20);
this.AwayTeamMoneyLineOpen = reader.GetInt32(21);
//this.AwayTeamLine = reader.GetDecimalSafe(11);
this.WinningTeamId = reader.GetInt32(23);
this.WinningTeamName = reader.GetString(24);
this.SeasonId = reader.GetInt32(25);
this.GameLocationName = reader.GetString(26);
this.WonSpread = reader.GetBoolean(27);
this.WonMoneyLine = reader.GetBoolean(28);
}
reader.Close();
this.GameDateFormated = this.GameDate.ToString("yyyyMMdd", System.Globalization.CultureInfo.CreateSpecificCulture("en-US"));
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, gameId);
}
}
}
public bool TempUpdateBet()
{
Bet betCheck = new Bet();
if (betCheck.BetId != 0)
{
this.BetId = betCheck.BetId;
}
string queryString = "";
queryString = "UPDATE [dbo].[Bet]"
+ " SET [WinningBet] = @WinningBet"
+ " ,[BetNote] = @BetNote"
+ " WHERE BetId = " + this.BetId.ToString()
+ " SET @betId = " + this.BetId.ToString();
using (SqlConnection connection = new SqlConnection(Base.conn))
{
if (String.IsNullOrEmpty(this.BetNote))
{
this.BetNote = "";
}
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@WinningBet", this.WinningBet);
command.Parameters.AddWithValue("@BetNote", this.BetNote);
command.Parameters.Add("@betId", SqlDbType.Int, 0, "betId");
command.Parameters["@betId"].Direction = ParameterDirection.Output;
try
{
connection.Open();
command.ExecuteNonQuery();
this.BetId = (int)command.Parameters["@betId"].Value;
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, this.BetId);
}
}
return(true);
}
public bool InsertTeam()
{
string queryString = "INSERT INTO [dbo].[Team] "
+ "([LeagueId]"
+ ",[ConferenceId]"
+ ",[Name]"
+ ",[City]"
+ ",[ScrapId]"
+ ",[ScrapUrl]"
+ ",[Scrap2Url])"
+ " VALUES "
+ "(@LeagueId"
+ "(@ConferenceId"
+ ",@City"
+ ",@ScrapId"
+ ",@ScrapUrl"
+ ",@Scrap2Url)";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@LeagueId", this.LeagueId);
command.Parameters.AddWithValue("@ConferenceId", this.ConferenceId);
command.Parameters.AddWithValue("@Name", this.Name);
command.Parameters.AddWithValue("@City", this.City);
command.Parameters.AddWithValue("@ScrapId", this.ScrapId);
command.Parameters.AddWithValue("@ScrapUrl", this.ScrapUrl);
command.Parameters.AddWithValue("@Scrap2Url", this.Scrap2Url);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return(true);
}
public List <Team> AllTeams(int leagueId)
{
List <Team> teams = new List <Team>();
string queryString = "SELECT * FROM [dbo].[Teams] WHERE leagueId = @leagueId Order By City";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("leagueId", leagueId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Team team = new Team();
team.TeamId = reader.GetInt32(0);
team.LeagueId = reader.GetInt32(1);
team.League = reader.GetString(2);
team.ConferenceId = reader.GetInt32(3);
team.Conference = reader.GetString(4);
team.Name = reader.GetString(5);
team.City = reader.GetString(6);
team.ScrapId = reader.GetString(7);
team.ScrapUrl = reader.GetString(8);
team.Scrap2Url = reader.GetString(9);
teams.Add(team);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, leagueId);
}
}
return(teams);
}
public Bet(int betId)
{
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand("sp_BetDetails", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@betId", betId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
this.BetId = betId; //(0)
this.BetADate = reader.GetDateSafe(1);
this.BetBDate = reader.GetDateSafe(2);
this.BetCDate = reader.GetDateSafe(3);
this.BetDDate = reader.GetDateSafe(4);
this.BetStatusText = reader.GetString(5);
//this.StatusId = reader.GetInt32(5);
//this.StatusId = 0;
this.WinningBet = reader.GetStringSafe(6);
this.WinningBetTeamId = reader.GetInt32(7);
this.LosingBetTeamId = reader.GetInt32(8);
this.BetNote = reader.GetStringSafe(9);
this.WinningBetTeamRPI = reader.GetDecimalSafe(12);
this.LosingBetTeamRPI = reader.GetDecimalSafe(13);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
}
public Bet(int betId)
{
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand("sp_BetDetails", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@betId", betId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
this.BetId = betId; //(0)
this.BetADate = reader.GetDateSafe(1);
this.BetBDate = reader.GetDateSafe(2);
this.BetCDate = reader.GetDateSafe(3);
this.BetDDate = reader.GetDateSafe(4);
this.BetStatusText = reader.GetString(5);
//this.StatusId = reader.GetInt32(5);
//this.StatusId = 0;
this.WinningBet = reader.GetStringSafe(6);
this.WinningBetTeamId = reader.GetInt32(7);
this.LosingBetTeamId = reader.GetInt32(8);
this.BetNote = reader.GetStringSafe(9);
this.WinningBetTeamRPI = reader.GetDecimalSafe(12);
this.LosingBetTeamRPI = reader.GetDecimalSafe(13);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
}
public bool UpdateTeam()
{
if (this.TeamId != 0)
{
string queryString = "UPDATE [dbo].[Team] "
+ " SET [Name] = @Name"
+ ",[City] = @City"
+ ",[ScrapId] = @ScrapId"
+ ",[ScrapUrl] = @ScrapUrl"
+ ",[Scrap2Url] = @Scrap2Url"
+ " WHERE TeamId = @TeamId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@TeamId", this.TeamId);
command.Parameters.AddWithValue("@Name", this.Name);
command.Parameters.AddWithValue("@City", this.City);
command.Parameters.AddWithValue("@ScrapId", this.ScrapId);
command.Parameters.AddWithValue("@ScrapUrl", this.ScrapUrl);
command.Parameters.AddWithValue("@Scrap2Url", this.Scrap2Url);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, this.TeamId);
}
}
}
return(true);
}
public bool TempUpdateBet()
{
Bet betCheck = new Bet();
if (betCheck.BetId != 0)
{
this.BetId = betCheck.BetId;
}
string queryString = "";
queryString = "UPDATE [dbo].[Bet]"
+ " SET [WinningBet] = @WinningBet"
+ " ,[BetNote] = @BetNote"
+ " WHERE BetId = " + this.BetId.ToString()
+ " SET @betId = " + this.BetId.ToString();
using (SqlConnection connection = new SqlConnection(Base.conn))
{
if (String.IsNullOrEmpty(this.BetNote)) { this.BetNote = ""; }
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@WinningBet", this.WinningBet);
command.Parameters.AddWithValue("@BetNote", this.BetNote);
command.Parameters.Add("@betId", SqlDbType.Int, 0, "betId");
command.Parameters["@betId"].Direction = ParameterDirection.Output;
try
{
connection.Open();
command.ExecuteNonQuery();
this.BetId = (int)command.Parameters["@betId"].Value;
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, this.BetId);
}
}
return true;
}
public List<Game> MLBBetPotentials(int seasonId)
{
List<Game> games = new List<Game>();
string queryString = "sp_MLBBetPotentials";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@seasonId", seasonId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Game game = new Game();
game.HomeTeamId = reader.GetInt32(0);
game.AwayTeamId = reader.GetInt32(1);
game.WinningTeamId = reader.GetInt32(2);
games.Add(game);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
int listLength = games.Count;
Game tempGame;
Game removeGame;
for (int i = 0; i <= listLength; i++)
{
if (i <= games.Count)
{
removeGame = null;
tempGame = games[i];
removeGame = games.Where(g => g.HomeTeamId == tempGame.AwayTeamId && g.AwayTeamId == tempGame.HomeTeamId).FirstOrDefault();
if (removeGame != null)
{
games.Remove(removeGame);
}
}
else
{
break;
}
}
return games;
}
public bool UpdateGameLine()
{
string queryString = "UPDATE [dbo].[Game] "
+ "SET [HomeTeamSpreadPay] = @HomeTeamSpreadPay "
+ ",[HomeTeamSpreadPayOpen] = @HomeTeamSpreadPayOpen "
+ ",[HomeTeamScore] = @HomeTeamScore "
+ ",[HomeTeamSpread] = @HomeTeamSpread "
+ ",[HomeTeamSpreadOpen] = @HomeTeamSpreadOpen "
+ ",[HomeTeamMoneyLine] = @HomeTeamMoneyLine "
+ ",[HomeTeamMoneyLineOpen] = @HomeTeamMoneyLineOpen "
+ ",[AwayTeamSpreadPay] = @AwayTeamSpreadPay "
+ ",[AwayTeamScore] = @AwayTeamScore "
+ ",[AwayTeamSpreadPayOpen] = @AwayTeamSpreadPayOpen "
+ ",[AwayTeamSpread] = @AwayTeamSpread "
+ ",[AwayTeamSpreadOpen] = @AwayTeamSpreadOpen "
+ ",[AwayTeamMoneyLine] = @AwayTeamMoneyLine "
+ ",[AwayTeamMoneyLineOpen] = @AwayTeamMoneyLineOpen "
+ ",[WinSpread] = @WinSpread "
+ ",[WinMoneyLine] = @WinMoneyLine ";
if (this.AwayTeamScore > 0 && this.HomeTeamScore > 0)
{
queryString = queryString + ",[GameStatusTypeId] = @GameStatusTypeId ";
queryString = queryString + ",[WinningTeamId] = @WinningTeamId ";
if (this.AwayTeamScore > this.HomeTeamScore)
{
this.WinningTeamId = this.AwayTeamId;
}else{
this.WinningTeamId = this.HomeTeamId;
}
}
queryString = queryString +" WHERE GameId = @GameId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@GameId", this.GameId);
command.Parameters.AddWithValue("@GameStatusTypeId", 1);
command.Parameters.AddWithValue("@WinningTeamId", this.WinningTeamId);
command.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
command.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
command.Parameters.AddWithValue("@HomeTeamSpreadPay", this.HomeTeamSpreadPay);
command.Parameters.AddWithValue("@HomeTeamSpreadPayOpen", this.HomeTeamSpreadPayOpen);
command.Parameters.AddWithValue("@HomeTeamSpread", this.HomeTeamSpread);
command.Parameters.AddWithValue("@HomeTeamSpreadOpen", this.HomeTeamSpreadOpen);
command.Parameters.AddWithValue("@HomeTeamMoneyLine", this.HomeTeamMoneyLine);
command.Parameters.AddWithValue("@HomeTeamMoneyLineOpen", this.HomeTeamMoneyLineOpen);
command.Parameters.AddWithValue("@AwayTeamSpreadPay", this.AwayTeamSpreadPay);
command.Parameters.AddWithValue("@AwayTeamSpreadPayOpen", this.AwayTeamSpreadPayOpen);
command.Parameters.AddWithValue("@AwayTeamSpread", this.AwayTeamSpread);
command.Parameters.AddWithValue("@AwayTeamSpreadOpen", this.AwayTeamSpreadOpen);
command.Parameters.AddWithValue("@AwayTeamMoneyLine", this.AwayTeamMoneyLine);
command.Parameters.AddWithValue("@AwayTeamMoneyLineOpen", this.AwayTeamMoneyLineOpen);
command.Parameters.AddWithValue("@WinSpread", this.WonSpread);
command.Parameters.AddWithValue("@WinMoneyLine", this.WonMoneyLine);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, this.GameId);
}
}
return true;
}
public bool UpdateGame()
{
if (this.GameId != 0)
{
string queryString = "UPDATE [dbo].[Game] "
+ "SET [GameStatusTypeId] = @GameStatusTypeId "
+ " ,[HomeTeamScore] = @HomeTeamScore"
+ " ,[AwayTeamScore] = @AwayTeamScore"
+ " ,[WinningTeamId] = @WinningTeamId"
+ " WHERE GameId = @GameId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@GameId", this.GameId);
//command.Parameters.AddWithValue("@GameDate", this.GameDate);
command.Parameters.AddWithValue("@GameStatusTypeId", this.GameStatusTypeId);
command.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
command.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
command.Parameters.AddWithValue("@WinningTeamId", this.WinningTeamId);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, this.GameId);
}
}
Utiliy u = new Utiliy();
Bet bet = u.CheckGameBet(this.GameId);
if (bet.BetId != 0)
{
//This is a own bet
if (bet.WinningBetTeamId == this.WinningTeamId)
{
if (this.GameDate == bet.BetADate)
{
bet.WinningBet = "A";
}
if (this.GameDate == bet.BetBDate)
{
bet.WinningBet = "B";
}
if (this.GameDate == bet.BetCDate)
{
bet.WinningBet = "C";
}
if (this.GameDate == bet.BetDDate)
{
bet.WinningBet = "D";
}
bet.TempUpdateBet();
}
}
}
return true;
}
//private void _LoadGame() {
// string queryString = "SELECT * FROM [dbo].[Games] WHERE GameId = @gameId";
// using (SqlConnection connection = new SqlConnection(Base.conn))
// {
// // Create the Command and Parameter objects.
// SqlCommand command = new SqlCommand(queryString, connection);
// command.Parameters.AddWithValue("@gameId", this.GameId);
// try
// {
// connection.Open();
// SqlDataReader reader = command.ExecuteReader();
// while (reader.Read())
// {
// this.GameId = reader.GetInt32(0);
// this.GameDate = reader.GetDateTime(1);
// this.GameStatusTypeId = reader.GetInt32(2);
// this.HomeTeamId = reader.GetInt32(3);
// this.HomeTeamName = reader.GetString(4);
// this.HomeTeamScore = reader.GetInt32(5);
// this.HomeTeamSpreadPay = reader.GetDecimalSafe(6);
// this.HomeTeamSpreadPayOpen = reader.GetDecimalSafe(7);
// this.HomeTeamSpread = reader.GetDecimalSafe(8);
// this.HomeTeamSpreadOpen = reader.GetDecimalSafe(9);
// this.HomeTeamMoneyLine = reader.GetInt32(10);
// this.HomeTeamMoneyLineOpen = reader.GetInt32(11);
// //this.HomeTeamLine = reader.GetDecimalSafe(6);
// this.AwayTeamId = reader.GetInt32(13);
// this.AwayTeamName = reader.GetString(14);
// this.AwayTeamScore = reader.GetInt32(15);
// this.AwayTeamSpreadPay = reader.GetDecimalSafe(16);
// this.AwayTeamSpreadPayOpen = reader.GetDecimalSafe(17);
// this.AwayTeamSpread = reader.GetDecimalSafe(18);
// this.AwayTeamSpreadOpen = reader.GetDecimalSafe(19);
// this.AwayTeamMoneyLine = reader.GetInt32(20);
// this.AwayTeamMoneyLineOpen = reader.GetInt32(21);
// //this.AwayTeamLine = reader.GetDecimalSafe(11);
// this.WinningTeamId = reader.GetInt32(23);
// this.WinningTeamName = reader.GetString(24);
// this.SeasonId = reader.GetInt32(25);
// this.GameLocationName = reader.GetString(26);
// this.WonSpread = reader.GetBoolean(27);
// this.WonMoneyLine = reader.GetBoolean(28);
// }
// reader.Close();
// this.GameDateFormated = this.GameDate.ToString("yyyyMMdd", System.Globalization.CultureInfo.CreateSpecificCulture("en-US"));
// }
// catch (Exception ex)
// {
// SportsError sError = new SportsError(ex, gameId);
// }
// }
//}
public bool InsertGame()
{
Game gameCheck = new Game();
string queryCheckGame = "SELECT [GameId],[SeasonId],[GameDate],[GameStatusTypeId] " +
",[HomeTeamId],[HomeTeamScore] " +
",[AwayTeamId],[AwayTeamScore] " +
",[WinningTeamId] FROM [dbo].[Game] "
+ " WHERE HomeTeamId = @HomeTeamId "
+ " AND AwayTeamId = @AwayTeamId "
+ " AND HomeTeamScore = @HomeTeamScore "
+ " AND AwayTeamScore = @AwayTeamScore "
+ " AND SeasonId = @SeasonId AND GameDate = @GameDate";
//if (this.HomeTeamId == 31 && this.AwayTeamId == 39)
//{
// bool test = true;
//}
//if (this.HomeTeamId == 39 && this.AwayTeamId == 31)
//{
// bool test = true;
//}
using (SqlConnection connectionCheck = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand commandCheck = new SqlCommand(queryCheckGame, connectionCheck);
commandCheck.Parameters.AddWithValue("@HomeTeamId", this.HomeTeamId);
commandCheck.Parameters.AddWithValue("@AwayTeamId", this.AwayTeamId);
commandCheck.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
commandCheck.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
commandCheck.Parameters.AddWithValue("@GameDate", this.GameDate);
commandCheck.Parameters.AddWithValue("@SeasonId", this.SeasonId);
//TEMP COMMENT OUT TO INSERT TWO GAMES ON SAME DAY!
try
{
connectionCheck.Open();
SqlDataReader reader = commandCheck.ExecuteReader();
while (reader.Read())
{
gameCheck.GameId = reader.GetInt32(0);
gameCheck.SeasonId = reader.GetInt32(1);
gameCheck.GameDate = reader.GetDateTime(2);
gameCheck.GameStatusTypeId = reader.GetInt32(3);
gameCheck.HomeTeamId = reader.GetInt32(4);
gameCheck.HomeTeamScore = reader.GetInt32(5);
// gameCheck.HomeTeamLine = 0; //6
gameCheck.AwayTeamId = reader.GetInt32(6);
gameCheck.AwayTeamScore = reader.GetInt32(7);
// gameCheck.AwayTeamLine = 0; //9
gameCheck.WinningTeamId = reader.GetInt32(8);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
if (gameCheck.GameId != 0)
{
//if (gameCheck.GameStatusTypeId != this.GameStatusTypeId && gameCheck.WinningTeamId != this.WinningTeamId)
//{
// gameCheck.GameStatusTypeId = this.GameStatusTypeId;
// gameCheck.WinningTeamId = this.WinningTeamId;
// gameCheck.HomeTeamScore = this.HomeTeamScore;
// gameCheck.AwayTeamScore = this.AwayTeamScore;
// gameCheck.UpdateGame();
//}
}
else
{
string queryString = "INSERT INTO [dbo].[Game] "
+ "([SeasonId],[GameDate],[GameStatusTypeId],[HomeTeamId],[HomeTeamScore] "
+ ",[AwayTeamId],[AwayTeamScore],[WinningTeamId]) "
+ " VALUES "
+ "(@SeasonId,@GameDate,@GameStatusTypeId,@HomeTeamId,@HomeTeamScore "
+ ",@AwayTeamId,@AwayTeamScore,@WinningTeamId)";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@SeasonId", this.SeasonId);
command.Parameters.AddWithValue("@GameDate", this.GameDate);
command.Parameters.AddWithValue("@GameStatusTypeID", this.GameStatusTypeId);
command.Parameters.AddWithValue("@HomeTeamId", this.HomeTeamId);
command.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
//command.Parameters.AddWithValue("@HomeTeamLine", this.HomeTeamLine);
command.Parameters.AddWithValue("@AwayTeamId", this.AwayTeamId);
command.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
// command.Parameters.AddWithValue("@AwayTeamLine", this.AwayTeamLine);
command.Parameters.AddWithValue("@WinningTeamId", this.WinningTeamId);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
}
return true;
}
public bool UpdateGame()
{
if (this.GameId != 0)
{
string queryString = "UPDATE [dbo].[Game] "
+ "SET [GameStatusTypeId] = @GameStatusTypeId "
+ " ,[HomeTeamScore] = @HomeTeamScore"
+ " ,[AwayTeamScore] = @AwayTeamScore"
+ " ,[WinningTeamId] = @WinningTeamId"
+ " WHERE GameId = @GameId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@GameId", this.GameId);
//command.Parameters.AddWithValue("@GameDate", this.GameDate);
command.Parameters.AddWithValue("@GameStatusTypeId", this.GameStatusTypeId);
command.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
command.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
command.Parameters.AddWithValue("@WinningTeamId", this.WinningTeamId);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, this.GameId);
}
}
Utiliy u = new Utiliy();
Bet bet = u.CheckGameBet(this.GameId);
if (bet.BetId != 0)
{
//This is a own bet
if (bet.WinningBetTeamId == this.WinningTeamId)
{
if (this.GameDate == bet.BetADate)
{
bet.WinningBet = "A";
}
if (this.GameDate == bet.BetBDate)
{
bet.WinningBet = "B";
}
if (this.GameDate == bet.BetCDate)
{
bet.WinningBet = "C";
}
if (this.GameDate == bet.BetDDate)
{
bet.WinningBet = "D";
}
bet.TempUpdateBet();
}
}
}
return(true);
}
public List<Game> TeamSchedule(int seasonId, int teamId)
{
List<Game> games = new List<Game>();
string queryString = "sp_TeamSchedule";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@teamId", teamId);
command.Parameters.AddWithValue("@seasonId", seasonId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Game game = new Game();
game.GameId = reader.GetInt32(0);
game.GameDate = reader.GetDateTime(1);
game.GameStatusTypeId = reader.GetInt32(2);
game.HomeTeamId = reader.GetInt32(3);
// game.HomeTeamConferenceId = reader.GetInt32(4);
game.HomeTeamName = reader.GetString(4);
game.HomeTeamScore = reader.GetInt32(5);
game.AwayTeamId = reader.GetInt32(13);
game.AwayTeamName = reader.GetString(14);
game.AwayTeamScore = reader.GetInt32(15);
game.WinningTeamId = reader.GetInt32(23);
game.WinningTeamName = reader.GetString(24);
//game.GameLocationName = reader.GetString(17);
if (teamId == game.HomeTeamId)
{
game.ScrapUrl = reader.GetString(12);
}
else
{
game.ScrapUrl = reader.GetString(22);
}
game.GameDateFormated = game.GameDate.ToShortDateString();
games.Add(game);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return games;
}
public List <Bet> NBABets(int seasonId)
{
List <Bet> bets = new List <Bet>();
string queryString = "SELECT * FROM [dbo].[NBABets]";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (reader.GetInt32(3) == seasonId)
{
Bet bet = new Bet();
bet.BetId = reader.GetInt32(0);
bet.BetStatusText = reader.GetString(1);
bet.BetNote = reader.GetString(2);
bet.SeasonId = reader.GetInt32(3);
bet.BetAGameId = reader.GetInt32(4);
bet.BetBGameId = reader.GetInt32(5);
bet.BetCGameId = reader.GetInt32(6);
bet.BetDGameId = reader.GetInt32(60);
bet.BetADate = reader.GetDateSafe(7);
bet.BetGames = new List <Game>();
if (bet.BetAGameId != 0)
{
bet.BetGames.Add(new Game
{
GameId = bet.BetAGameId,
GameDate = reader.GetDateSafe(8),
HomeTeamName = reader.GetString(9),
HomeTeamScore = reader.GetInt32(10),
AwayTeamName = reader.GetString(11),
AwayTeamScore = reader.GetInt32(12),
WinningTeamName = reader.GetString(13),
HomeTeamMoneyLine = reader.GetInt32(14),
HomeTeamSpread = reader.GetDecimalSafe(15),
AwayTeamMoneyLine = reader.GetInt32(16),
AwayTeamSpread = reader.GetDecimalSafe(17),
WinningTeamId = reader.GetInt32(18),
HomeTeamId = reader.GetInt32(19),
AwayTeamId = reader.GetInt32(20),
WonSpread = reader.GetBoolean(61),
WonMoneyLine = reader.GetBoolean(62)
});
if (bet.BetGames.Last().WinningTeamId == bet.BetGames.Last().AwayTeamId)
{
bet.WinA = true;
}
if (bet.BetGames.Last().WonSpread)
{
bet.WinA = true;
}
if (!bet.BetGames.Last().WonSpread&& !bet.BetGames.Last().WonMoneyLine)
{
bet.WinA = false;
}
}
if (bet.BetBGameId != 0)
{
bet.BetGames.Add(new Game
{
GameId = bet.BetBGameId,
GameDate = reader.GetDateSafe(21),
HomeTeamName = reader.GetString(22),
HomeTeamScore = reader.GetInt32(23),
AwayTeamName = reader.GetString(24),
AwayTeamScore = reader.GetInt32(25),
WinningTeamName = reader.GetString(26),
HomeTeamMoneyLine = reader.GetInt32(27),
HomeTeamSpread = reader.GetDecimalSafe(28),
AwayTeamMoneyLine = reader.GetInt32(29),
AwayTeamSpread = reader.GetDecimalSafe(30),
WinningTeamId = reader.GetInt32(31),
HomeTeamId = reader.GetInt32(32),
AwayTeamId = reader.GetInt32(33),
WonSpread = reader.GetBoolean(63),
WonMoneyLine = reader.GetBoolean(64)
});
if (bet.BetGames.Last().WinningTeamId == bet.BetGames.Last().AwayTeamId)
{
bet.WinB = true;
}
if (bet.BetGames.Last().WonSpread)
{
bet.WinB = true;
}
if (!bet.BetGames.Last().WonSpread&& !bet.BetGames.Last().WonMoneyLine)
{
bet.WinB = false;
}
}
if (bet.BetCGameId != 0)
{
bet.BetGames.Add(new Game
{
GameId = bet.BetCGameId,
GameDate = reader.GetDateSafe(34),
HomeTeamName = reader.GetString(35),
HomeTeamScore = reader.GetInt32(36),
AwayTeamName = reader.GetString(37),
AwayTeamScore = reader.GetInt32(38),
WinningTeamName = reader.GetString(39),
HomeTeamMoneyLine = reader.GetInt32(40),
HomeTeamSpread = reader.GetDecimalSafe(41),
AwayTeamMoneyLine = reader.GetInt32(42),
AwayTeamSpread = reader.GetDecimalSafe(43),
WinningTeamId = reader.GetInt32(44),
HomeTeamId = reader.GetInt32(45),
AwayTeamId = reader.GetInt32(46),
WonSpread = reader.GetBoolean(65),
WonMoneyLine = reader.GetBoolean(66)
});
if (bet.BetGames.Last().WinningTeamId == bet.BetGames.Last().AwayTeamId)
{
bet.WinC = true;
}
if (bet.BetGames.Last().WonSpread)
{
bet.WinC = true;
}
if (!bet.BetGames.Last().WonSpread&& !bet.BetGames.Last().WonMoneyLine)
{
bet.WinC = false;
}
}
if (bet.BetDGameId != 0)
{
bet.BetGames.Add(new Game
{
GameId = bet.BetDGameId,
GameDate = reader.GetDateSafe(47),
HomeTeamName = reader.GetString(48),
HomeTeamScore = reader.GetInt32(49),
AwayTeamName = reader.GetString(50),
AwayTeamScore = reader.GetInt32(51),
WinningTeamName = reader.GetString(52),
HomeTeamMoneyLine = reader.GetInt32(53),
HomeTeamSpread = reader.GetDecimalSafe(54),
AwayTeamMoneyLine = reader.GetInt32(55),
AwayTeamSpread = reader.GetDecimalSafe(56),
WinningTeamId = reader.GetInt32(57),
HomeTeamId = reader.GetInt32(58),
AwayTeamId = reader.GetInt32(59),
WonSpread = reader.GetBoolean(67),
WonMoneyLine = reader.GetBoolean(68)
});
if (bet.BetGames.Last().WinningTeamId == bet.BetGames.Last().AwayTeamId)
{
bet.WinD = true;
}
if (bet.BetGames.Last().WonSpread)
{
bet.WinD = true;
}
if (!bet.BetGames.Last().WonSpread&& !bet.BetGames.Last().WonMoneyLine)
{
bet.WinD = false;
}
}
bets.Add(bet);
}
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return(bets.OrderBy(b => b.BetADate).ToList());
}
public List <Game> TeamSchedule(int seasonId, int teamId)
{
List <Game> games = new List <Game>();
string queryString = "sp_TeamSchedule";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@teamId", teamId);
command.Parameters.AddWithValue("@seasonId", seasonId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Game game = new Game();
game.GameId = reader.GetInt32(0);
game.GameDate = reader.GetDateTime(1);
game.GameStatusTypeId = reader.GetInt32(2);
game.HomeTeamId = reader.GetInt32(3);
// game.HomeTeamConferenceId = reader.GetInt32(4);
game.HomeTeamName = reader.GetString(4);
game.HomeTeamScore = reader.GetInt32(5);
game.AwayTeamId = reader.GetInt32(13);
game.AwayTeamName = reader.GetString(14);
game.AwayTeamScore = reader.GetInt32(15);
game.WinningTeamId = reader.GetInt32(23);
game.WinningTeamName = reader.GetString(24);
//game.GameLocationName = reader.GetString(17);
if (teamId == game.HomeTeamId)
{
game.ScrapUrl = reader.GetString(12);
}
else
{
game.ScrapUrl = reader.GetString(22);
}
game.GameDateFormated = game.GameDate.ToShortDateString();
games.Add(game);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return(games);
}
public bool UpdateTeam()
{
if (this.TeamId != 0)
{
string queryString = "UPDATE [dbo].[Team] "
+ " SET [Name] = @Name"
+ ",[City] = @City"
+ ",[ScrapId] = @ScrapId"
+ ",[ScrapUrl] = @ScrapUrl"
+ ",[Scrap2Url] = @Scrap2Url"
+ " WHERE TeamId = @TeamId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@TeamId", this.TeamId);
command.Parameters.AddWithValue("@Name", this.Name);
command.Parameters.AddWithValue("@City", this.City);
command.Parameters.AddWithValue("@ScrapId", this.ScrapId);
command.Parameters.AddWithValue("@ScrapUrl", this.ScrapUrl);
command.Parameters.AddWithValue("@Scrap2Url", this.Scrap2Url);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, this.TeamId);
}
}
}
return true;
}
public bool InsertBet()
{
//Bet betCheck = new Bet();
//string queryCheckGame = "SELECT [BetId]"
// + " FROM [dbo].[Bet]"
// + " WHERE WinningBetTeamId = @WinTeamId AND LosingBetTeamId = @LoseTeamId AND BetADate = @BetADate";
//using (SqlConnection connectionCheck = new SqlConnection(Base.conn))
//{
// // Create the Command and Parameter objects.
// SqlCommand commandCheck = new SqlCommand(queryCheckGame, connectionCheck);
// commandCheck.Parameters.AddWithValue("@LoseTeamId", this.LosingBetTeamId);
// commandCheck.Parameters.AddWithValue("@WinTeamId", this.WinningBetTeamId);
// commandCheck.Parameters.AddWithValue("@BetADate", this.BetADate);
// try
// {
// connectionCheck.Open();
// SqlDataReader reader = commandCheck.ExecuteReader();
// while (reader.Read())
// {
// betCheck.BetId = reader.GetInt32(0);
// }
// reader.Close();
// }
// catch (Exception ex)
// {
// throw (ex);
// }
//}
//if (betCheck.BetId != 0)
//{
// this.BetId = betCheck.BetId;
//}
string queryString = "";
if (this.BetId == 0)
{
queryString = "INSERT INTO [dbo].[Bet]"
+ "([BetADate]"
+ ",[BetBDate]"
+ ",[BetCDate]"
+ ",[BetDDate]"
+ ",[BetAGameId]"
+ ",[BetBGameId]"
+ ",[BetCGameId]"
+ ",[BetDGameId]"
+ ",[BetNote]"
+ ",[SeasonId])"
+ " VALUES "
+ "(@BetADate"
+ ",@BetBDate"
+ ",@BetCDate"
+ ",@BetDDate"
+ ",@BetAGameId"
+ ",@BetBGameId"
+ ",@BetCGameId"
+ ",@BetDGameId"
+ ",@BetNote"
+ ",@SeasonId)"
+ " SET @betId = @@Identity";
}
else
{
//queryString = "UPDATE [dbo].[Bet]"
// + " SET [BetADate] = @BetADate"
// + " ,[BetBDate] = @BetBDate"
// + " ,[BetCDate] = @BetCDate"
// + " ,[BetDDate] = @BetDDate"
// + " ,[WinningBet] = @WinningBet"
// + " ,[WinningBetTeamId] = @WinningBetTeamId"
// + " ,[LosingBetTeamId] = @LosingBetTeamId"
// + " ,[BetNote] = @BetNote"
// + " WHERE BetId = " + this.BetId.ToString()
// + " SET @betId = " + this.BetId.ToString();
// if (this.WinningBet != "Scheduled")
// {
queryString = "UPDATE [dbo].[Bet]"
+ " SET [WinningBet] = @WinningBet"
+ " ,[WinningBetTeamId] = @WinningBetTeamId"
+ " ,[LosingBetTeamId] = @LosingBetTeamId"
+ " WHERE BetId = " + this.BetId.ToString()
+ " SET @betId = " + this.BetId.ToString();
// }
//else
//{
// queryString = "UPDATE [dbo].[Bet]"
// + " SET [WinningBet] = @WinningBet"
// + " ,[WinningBetTeamId] = @WinningBetTeamId"
// + " ,[LosingBetTeamId] = @LosingBetTeamId"
// + " WHERE BetId = " + this.BetId.ToString()
// + " SET @betId = " + this.BetId.ToString();
//}
}
using (SqlConnection connection = new SqlConnection(Base.conn))
{
if (String.IsNullOrEmpty(this.BetNote))
{
this.BetNote = "";
}
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@BetADate", this.BetADate);
command.Parameters.AddWithValue("@BetBDate", this.BetBDate);
command.Parameters.AddWithValue("@BetCDate", this.BetCDate);
command.Parameters.AddWithValue("@BetDDate", this.BetDDate);
command.Parameters.AddWithValue("@BetAGameId", this.BetAGameId);
command.Parameters.AddWithValue("@BetBGameId", this.BetBGameId);
command.Parameters.AddWithValue("@BetCGameId", this.BetCGameId);
command.Parameters.AddWithValue("@BetDGameId", this.BetDGameId);
command.Parameters.AddWithValue("@BetNote", this.BetNote);
command.Parameters.AddWithValue("@SeasonId", this.SeasonId);
command.Parameters.Add("@betId", SqlDbType.Int, 0, "betId");
command.Parameters["@betId"].Direction = ParameterDirection.Output;
if (this.BetADate == DateTime.MinValue)
{
command.Parameters["@BetADate"].Value = SqlDateTime.Null;
}
if (this.BetBDate == DateTime.MinValue)
{
command.Parameters["@BetBDate"].Value = SqlDateTime.Null;
}
if (this.BetCDate == DateTime.MinValue)
{
command.Parameters["@BetCDate"].Value = SqlDateTime.Null;
}
if (this.BetDDate == DateTime.MinValue)
{
command.Parameters["@BetDDate"].Value = SqlDateTime.Null;
}
try
{
connection.Open();
command.ExecuteNonQuery();
this.BetId = (int)command.Parameters["@betId"].Value;
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
//throw (ex);
}
}
if (this.BetId != 0)
{
foreach (BetStatus status in this.BetStatus)
{
status.BetId = this.BetId;
status.InsertBetStatus();
}
}
return(true);
}
public bool InsertBet()
{
//Bet betCheck = new Bet();
//string queryCheckGame = "SELECT [BetId]"
// + " FROM [dbo].[Bet]"
// + " WHERE WinningBetTeamId = @WinTeamId AND LosingBetTeamId = @LoseTeamId AND BetADate = @BetADate";
//using (SqlConnection connectionCheck = new SqlConnection(Base.conn))
//{
// // Create the Command and Parameter objects.
// SqlCommand commandCheck = new SqlCommand(queryCheckGame, connectionCheck);
// commandCheck.Parameters.AddWithValue("@LoseTeamId", this.LosingBetTeamId);
// commandCheck.Parameters.AddWithValue("@WinTeamId", this.WinningBetTeamId);
// commandCheck.Parameters.AddWithValue("@BetADate", this.BetADate);
// try
// {
// connectionCheck.Open();
// SqlDataReader reader = commandCheck.ExecuteReader();
// while (reader.Read())
// {
// betCheck.BetId = reader.GetInt32(0);
// }
// reader.Close();
// }
// catch (Exception ex)
// {
// throw (ex);
// }
//}
//if (betCheck.BetId != 0)
//{
// this.BetId = betCheck.BetId;
//}
string queryString = "";
if (this.BetId == 0)
{
queryString = "INSERT INTO [dbo].[Bet]"
+ "([BetADate]"
+ ",[BetBDate]"
+ ",[BetCDate]"
+ ",[BetDDate]"
+ ",[BetAGameId]"
+ ",[BetBGameId]"
+ ",[BetCGameId]"
+ ",[BetDGameId]"
+ ",[BetNote]"
+ ",[SeasonId])"
+ " VALUES "
+ "(@BetADate"
+ ",@BetBDate"
+ ",@BetCDate"
+ ",@BetDDate"
+ ",@BetAGameId"
+ ",@BetBGameId"
+ ",@BetCGameId"
+ ",@BetDGameId"
+ ",@BetNote"
+ ",@SeasonId)"
+ " SET @betId = @@Identity";
}
else
{
//queryString = "UPDATE [dbo].[Bet]"
// + " SET [BetADate] = @BetADate"
// + " ,[BetBDate] = @BetBDate"
// + " ,[BetCDate] = @BetCDate"
// + " ,[BetDDate] = @BetDDate"
// + " ,[WinningBet] = @WinningBet"
// + " ,[WinningBetTeamId] = @WinningBetTeamId"
// + " ,[LosingBetTeamId] = @LosingBetTeamId"
// + " ,[BetNote] = @BetNote"
// + " WHERE BetId = " + this.BetId.ToString()
// + " SET @betId = " + this.BetId.ToString();
// if (this.WinningBet != "Scheduled")
// {
queryString = "UPDATE [dbo].[Bet]"
+ " SET [WinningBet] = @WinningBet"
+ " ,[WinningBetTeamId] = @WinningBetTeamId"
+ " ,[LosingBetTeamId] = @LosingBetTeamId"
+ " WHERE BetId = " + this.BetId.ToString()
+ " SET @betId = " + this.BetId.ToString();
// }
//else
//{
// queryString = "UPDATE [dbo].[Bet]"
// + " SET [WinningBet] = @WinningBet"
// + " ,[WinningBetTeamId] = @WinningBetTeamId"
// + " ,[LosingBetTeamId] = @LosingBetTeamId"
// + " WHERE BetId = " + this.BetId.ToString()
// + " SET @betId = " + this.BetId.ToString();
//}
}
using (SqlConnection connection = new SqlConnection(Base.conn))
{
if (String.IsNullOrEmpty(this.BetNote)) { this.BetNote = ""; }
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@BetADate", this.BetADate);
command.Parameters.AddWithValue("@BetBDate", this.BetBDate);
command.Parameters.AddWithValue("@BetCDate", this.BetCDate);
command.Parameters.AddWithValue("@BetDDate", this.BetDDate);
command.Parameters.AddWithValue("@BetAGameId", this.BetAGameId);
command.Parameters.AddWithValue("@BetBGameId", this.BetBGameId);
command.Parameters.AddWithValue("@BetCGameId", this.BetCGameId);
command.Parameters.AddWithValue("@BetDGameId", this.BetDGameId);
command.Parameters.AddWithValue("@BetNote", this.BetNote);
command.Parameters.AddWithValue("@SeasonId", this.SeasonId);
command.Parameters.Add("@betId", SqlDbType.Int, 0, "betId");
command.Parameters["@betId"].Direction = ParameterDirection.Output;
if (this.BetADate == DateTime.MinValue)
{ command.Parameters["@BetADate"].Value = SqlDateTime.Null; }
if (this.BetBDate == DateTime.MinValue)
{ command.Parameters["@BetBDate"].Value = SqlDateTime.Null; }
if (this.BetCDate == DateTime.MinValue)
{ command.Parameters["@BetCDate"].Value = SqlDateTime.Null; }
if (this.BetDDate == DateTime.MinValue)
{ command.Parameters["@BetDDate"].Value = SqlDateTime.Null; }
try
{
connection.Open();
command.ExecuteNonQuery();
this.BetId = (int)command.Parameters["@betId"].Value;
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
//throw (ex);
}
}
if (this.BetId != 0)
{
foreach (BetStatus status in this.BetStatus)
{
status.BetId = this.BetId;
status.InsertBetStatus();
}
}
return true;
}
public bool UpdateGameLine()
{
string queryString = "UPDATE [dbo].[Game] "
+ "SET [HomeTeamSpreadPay] = @HomeTeamSpreadPay "
+ ",[HomeTeamSpreadPayOpen] = @HomeTeamSpreadPayOpen "
+ ",[HomeTeamScore] = @HomeTeamScore "
+ ",[HomeTeamSpread] = @HomeTeamSpread "
+ ",[HomeTeamSpreadOpen] = @HomeTeamSpreadOpen "
+ ",[HomeTeamMoneyLine] = @HomeTeamMoneyLine "
+ ",[HomeTeamMoneyLineOpen] = @HomeTeamMoneyLineOpen "
+ ",[AwayTeamSpreadPay] = @AwayTeamSpreadPay "
+ ",[AwayTeamScore] = @AwayTeamScore "
+ ",[AwayTeamSpreadPayOpen] = @AwayTeamSpreadPayOpen "
+ ",[AwayTeamSpread] = @AwayTeamSpread "
+ ",[AwayTeamSpreadOpen] = @AwayTeamSpreadOpen "
+ ",[AwayTeamMoneyLine] = @AwayTeamMoneyLine "
+ ",[AwayTeamMoneyLineOpen] = @AwayTeamMoneyLineOpen "
+ ",[WinSpread] = @WinSpread "
+ ",[WinMoneyLine] = @WinMoneyLine ";
if (this.AwayTeamScore > 0 && this.HomeTeamScore > 0)
{
queryString = queryString + ",[GameStatusTypeId] = @GameStatusTypeId ";
queryString = queryString + ",[WinningTeamId] = @WinningTeamId ";
if (this.AwayTeamScore > this.HomeTeamScore)
{
this.WinningTeamId = this.AwayTeamId;
}
else
{
this.WinningTeamId = this.HomeTeamId;
}
}
queryString = queryString + " WHERE GameId = @GameId";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@GameId", this.GameId);
command.Parameters.AddWithValue("@GameStatusTypeId", 1);
command.Parameters.AddWithValue("@WinningTeamId", this.WinningTeamId);
command.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
command.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
command.Parameters.AddWithValue("@HomeTeamSpreadPay", this.HomeTeamSpreadPay);
command.Parameters.AddWithValue("@HomeTeamSpreadPayOpen", this.HomeTeamSpreadPayOpen);
command.Parameters.AddWithValue("@HomeTeamSpread", this.HomeTeamSpread);
command.Parameters.AddWithValue("@HomeTeamSpreadOpen", this.HomeTeamSpreadOpen);
command.Parameters.AddWithValue("@HomeTeamMoneyLine", this.HomeTeamMoneyLine);
command.Parameters.AddWithValue("@HomeTeamMoneyLineOpen", this.HomeTeamMoneyLineOpen);
command.Parameters.AddWithValue("@AwayTeamSpreadPay", this.AwayTeamSpreadPay);
command.Parameters.AddWithValue("@AwayTeamSpreadPayOpen", this.AwayTeamSpreadPayOpen);
command.Parameters.AddWithValue("@AwayTeamSpread", this.AwayTeamSpread);
command.Parameters.AddWithValue("@AwayTeamSpreadOpen", this.AwayTeamSpreadOpen);
command.Parameters.AddWithValue("@AwayTeamMoneyLine", this.AwayTeamMoneyLine);
command.Parameters.AddWithValue("@AwayTeamMoneyLineOpen", this.AwayTeamMoneyLineOpen);
command.Parameters.AddWithValue("@WinSpread", this.WonSpread);
command.Parameters.AddWithValue("@WinMoneyLine", this.WonMoneyLine);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, this.GameId);
}
}
return(true);
}
public int[] NBABetGameIds()
{
List<int> gameIds = new List<int>();
string queryString = "Select * From NBABets Order By AGameDate";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
//Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
//command.CommandType = System.Data.CommandType.StoredProcedure;
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
gameIds.Add(reader.GetInt32(4));
gameIds.Add(reader.GetInt32(5));
gameIds.Add(reader.GetInt32(6));
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return gameIds.Where(gId => gId != 0).Distinct().ToArray();
}
public List<Bet> NBABets(int seasonId)
{
List<Bet> bets = new List<Bet>();
string queryString = "SELECT * FROM [dbo].[NBABets]";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (reader.GetInt32(3) == seasonId)
{
Bet bet = new Bet();
bet.BetId = reader.GetInt32(0);
bet.BetStatusText = reader.GetString(1);
bet.BetNote = reader.GetString(2);
bet.SeasonId = reader.GetInt32(3);
bet.BetAGameId = reader.GetInt32(4);
bet.BetBGameId = reader.GetInt32(5);
bet.BetCGameId = reader.GetInt32(6);
bet.BetDGameId = reader.GetInt32(60);
bet.BetADate = reader.GetDateSafe(7);
bet.BetGames = new List<Game>();
if (bet.BetAGameId != 0)
{
bet.BetGames.Add(new Game
{
GameId = bet.BetAGameId,
GameDate = reader.GetDateSafe(8),
HomeTeamName = reader.GetString(9),
HomeTeamScore = reader.GetInt32(10),
AwayTeamName = reader.GetString(11),
AwayTeamScore = reader.GetInt32(12),
WinningTeamName = reader.GetString(13),
HomeTeamMoneyLine = reader.GetInt32(14),
HomeTeamSpread = reader.GetDecimalSafe(15),
AwayTeamMoneyLine = reader.GetInt32(16),
AwayTeamSpread = reader.GetDecimalSafe(17),
WinningTeamId = reader.GetInt32(18),
HomeTeamId = reader.GetInt32(19),
AwayTeamId = reader.GetInt32(20),
WonSpread = reader.GetBoolean(61),
WonMoneyLine = reader.GetBoolean(62)
});
if (bet.BetGames.Last().WinningTeamId == bet.BetGames.Last().AwayTeamId)
{
bet.WinA = true;
}
if (bet.BetGames.Last().WonSpread)
{
bet.WinA = true;
}
if (!bet.BetGames.Last().WonSpread && !bet.BetGames.Last().WonMoneyLine)
{
bet.WinA = false;
}
}
if (bet.BetBGameId != 0)
{
bet.BetGames.Add(new Game
{
GameId = bet.BetBGameId,
GameDate = reader.GetDateSafe(21),
HomeTeamName = reader.GetString(22),
HomeTeamScore = reader.GetInt32(23),
AwayTeamName = reader.GetString(24),
AwayTeamScore = reader.GetInt32(25),
WinningTeamName = reader.GetString(26),
HomeTeamMoneyLine = reader.GetInt32(27),
HomeTeamSpread = reader.GetDecimalSafe(28),
AwayTeamMoneyLine = reader.GetInt32(29),
AwayTeamSpread = reader.GetDecimalSafe(30),
WinningTeamId = reader.GetInt32(31),
HomeTeamId = reader.GetInt32(32),
AwayTeamId = reader.GetInt32(33),
WonSpread = reader.GetBoolean(63),
WonMoneyLine = reader.GetBoolean(64)
});
if (bet.BetGames.Last().WinningTeamId == bet.BetGames.Last().AwayTeamId)
{
bet.WinB = true;
}
if (bet.BetGames.Last().WonSpread)
{
bet.WinB = true;
}
if (!bet.BetGames.Last().WonSpread && !bet.BetGames.Last().WonMoneyLine)
{
bet.WinB = false;
}
}
if (bet.BetCGameId != 0)
{
bet.BetGames.Add(new Game
{
GameId = bet.BetCGameId,
GameDate = reader.GetDateSafe(34),
HomeTeamName = reader.GetString(35),
HomeTeamScore = reader.GetInt32(36),
AwayTeamName = reader.GetString(37),
AwayTeamScore = reader.GetInt32(38),
WinningTeamName = reader.GetString(39),
HomeTeamMoneyLine = reader.GetInt32(40),
HomeTeamSpread = reader.GetDecimalSafe(41),
AwayTeamMoneyLine = reader.GetInt32(42),
AwayTeamSpread = reader.GetDecimalSafe(43),
WinningTeamId = reader.GetInt32(44),
HomeTeamId = reader.GetInt32(45),
AwayTeamId = reader.GetInt32(46),
WonSpread = reader.GetBoolean(65),
WonMoneyLine = reader.GetBoolean(66)
});
if (bet.BetGames.Last().WinningTeamId == bet.BetGames.Last().AwayTeamId)
{
bet.WinC = true;
}
if (bet.BetGames.Last().WonSpread)
{
bet.WinC = true;
}
if (!bet.BetGames.Last().WonSpread && !bet.BetGames.Last().WonMoneyLine)
{
bet.WinC = false;
}
}
if (bet.BetDGameId != 0)
{
bet.BetGames.Add(new Game
{
GameId = bet.BetDGameId,
GameDate = reader.GetDateSafe(47),
HomeTeamName = reader.GetString(48),
HomeTeamScore = reader.GetInt32(49),
AwayTeamName = reader.GetString(50),
AwayTeamScore = reader.GetInt32(51),
WinningTeamName = reader.GetString(52),
HomeTeamMoneyLine = reader.GetInt32(53),
HomeTeamSpread = reader.GetDecimalSafe(54),
AwayTeamMoneyLine = reader.GetInt32(55),
AwayTeamSpread = reader.GetDecimalSafe(56),
WinningTeamId = reader.GetInt32(57),
HomeTeamId = reader.GetInt32(58),
AwayTeamId = reader.GetInt32(59),
WonSpread = reader.GetBoolean(67),
WonMoneyLine = reader.GetBoolean(68)
});
if (bet.BetGames.Last().WinningTeamId == bet.BetGames.Last().AwayTeamId)
{
bet.WinD = true;
}
if (bet.BetGames.Last().WonSpread)
{
bet.WinD = true;
}
if (!bet.BetGames.Last().WonSpread && !bet.BetGames.Last().WonMoneyLine)
{
bet.WinD = false;
}
}
bets.Add(bet);
}
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return bets.OrderBy(b => b.BetADate).ToList();
}
public bool InsertTeam()
{
string queryString = "INSERT INTO [dbo].[Team] "
+ "([LeagueId]"
+ ",[ConferenceId]"
+ ",[Name]"
+ ",[City]"
+ ",[ScrapId]"
+ ",[ScrapUrl]"
+ ",[Scrap2Url])"
+ " VALUES "
+ "(@LeagueId"
+ "(@ConferenceId"
+ ",@City"
+ ",@ScrapId"
+ ",@ScrapUrl"
+ ",@Scrap2Url)";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@LeagueId", this.LeagueId);
command.Parameters.AddWithValue("@ConferenceId", this.ConferenceId);
command.Parameters.AddWithValue("@Name", this.Name);
command.Parameters.AddWithValue("@City", this.City);
command.Parameters.AddWithValue("@ScrapId", this.ScrapId);
command.Parameters.AddWithValue("@ScrapUrl", this.ScrapUrl);
command.Parameters.AddWithValue("@Scrap2Url", this.Scrap2Url);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return true;
}
public List<Team> AllTeams(int leagueId)
{
List<Team> teams = new List<Team>();
string queryString = "SELECT * FROM [dbo].[Teams] WHERE leagueId = @leagueId Order By City";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("leagueId", leagueId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Team team = new Team();
team.TeamId = reader.GetInt32(0);
team.LeagueId = reader.GetInt32(1);
team.League = reader.GetString(2);
team.ConferenceId = reader.GetInt32(3);
team.Conference = reader.GetString(4);
team.Name = reader.GetString(5);
team.City = reader.GetString(6);
team.ScrapId = reader.GetString(7);
team.ScrapUrl = reader.GetString(8);
team.Scrap2Url = reader.GetString(9);
teams.Add(team);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex, leagueId);
}
}
return teams;
}
//private void _LoadGame() {
// string queryString = "SELECT * FROM [dbo].[Games] WHERE GameId = @gameId";
// using (SqlConnection connection = new SqlConnection(Base.conn))
// {
// // Create the Command and Parameter objects.
// SqlCommand command = new SqlCommand(queryString, connection);
// command.Parameters.AddWithValue("@gameId", this.GameId);
// try
// {
// connection.Open();
// SqlDataReader reader = command.ExecuteReader();
// while (reader.Read())
// {
// this.GameId = reader.GetInt32(0);
// this.GameDate = reader.GetDateTime(1);
// this.GameStatusTypeId = reader.GetInt32(2);
// this.HomeTeamId = reader.GetInt32(3);
// this.HomeTeamName = reader.GetString(4);
// this.HomeTeamScore = reader.GetInt32(5);
// this.HomeTeamSpreadPay = reader.GetDecimalSafe(6);
// this.HomeTeamSpreadPayOpen = reader.GetDecimalSafe(7);
// this.HomeTeamSpread = reader.GetDecimalSafe(8);
// this.HomeTeamSpreadOpen = reader.GetDecimalSafe(9);
// this.HomeTeamMoneyLine = reader.GetInt32(10);
// this.HomeTeamMoneyLineOpen = reader.GetInt32(11);
// //this.HomeTeamLine = reader.GetDecimalSafe(6);
// this.AwayTeamId = reader.GetInt32(13);
// this.AwayTeamName = reader.GetString(14);
// this.AwayTeamScore = reader.GetInt32(15);
// this.AwayTeamSpreadPay = reader.GetDecimalSafe(16);
// this.AwayTeamSpreadPayOpen = reader.GetDecimalSafe(17);
// this.AwayTeamSpread = reader.GetDecimalSafe(18);
// this.AwayTeamSpreadOpen = reader.GetDecimalSafe(19);
// this.AwayTeamMoneyLine = reader.GetInt32(20);
// this.AwayTeamMoneyLineOpen = reader.GetInt32(21);
// //this.AwayTeamLine = reader.GetDecimalSafe(11);
// this.WinningTeamId = reader.GetInt32(23);
// this.WinningTeamName = reader.GetString(24);
// this.SeasonId = reader.GetInt32(25);
// this.GameLocationName = reader.GetString(26);
// this.WonSpread = reader.GetBoolean(27);
// this.WonMoneyLine = reader.GetBoolean(28);
// }
// reader.Close();
// this.GameDateFormated = this.GameDate.ToString("yyyyMMdd", System.Globalization.CultureInfo.CreateSpecificCulture("en-US"));
// }
// catch (Exception ex)
// {
// SportsError sError = new SportsError(ex, gameId);
// }
// }
//}
public bool InsertGame()
{
Game gameCheck = new Game();
string queryCheckGame = "SELECT [GameId],[SeasonId],[GameDate],[GameStatusTypeId] " +
",[HomeTeamId],[HomeTeamScore] " +
",[AwayTeamId],[AwayTeamScore] " +
",[WinningTeamId] FROM [dbo].[Game] "
+ " WHERE HomeTeamId = @HomeTeamId "
+ " AND AwayTeamId = @AwayTeamId "
+ " AND HomeTeamScore = @HomeTeamScore "
+ " AND AwayTeamScore = @AwayTeamScore "
+ " AND SeasonId = @SeasonId AND GameDate = @GameDate";
//if (this.HomeTeamId == 31 && this.AwayTeamId == 39)
//{
// bool test = true;
//}
//if (this.HomeTeamId == 39 && this.AwayTeamId == 31)
//{
// bool test = true;
//}
using (SqlConnection connectionCheck = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand commandCheck = new SqlCommand(queryCheckGame, connectionCheck);
commandCheck.Parameters.AddWithValue("@HomeTeamId", this.HomeTeamId);
commandCheck.Parameters.AddWithValue("@AwayTeamId", this.AwayTeamId);
commandCheck.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
commandCheck.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
commandCheck.Parameters.AddWithValue("@GameDate", this.GameDate);
commandCheck.Parameters.AddWithValue("@SeasonId", this.SeasonId);
//TEMP COMMENT OUT TO INSERT TWO GAMES ON SAME DAY!
try
{
connectionCheck.Open();
SqlDataReader reader = commandCheck.ExecuteReader();
while (reader.Read())
{
gameCheck.GameId = reader.GetInt32(0);
gameCheck.SeasonId = reader.GetInt32(1);
gameCheck.GameDate = reader.GetDateTime(2);
gameCheck.GameStatusTypeId = reader.GetInt32(3);
gameCheck.HomeTeamId = reader.GetInt32(4);
gameCheck.HomeTeamScore = reader.GetInt32(5);
// gameCheck.HomeTeamLine = 0; //6
gameCheck.AwayTeamId = reader.GetInt32(6);
gameCheck.AwayTeamScore = reader.GetInt32(7);
// gameCheck.AwayTeamLine = 0; //9
gameCheck.WinningTeamId = reader.GetInt32(8);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
if (gameCheck.GameId != 0)
{
//if (gameCheck.GameStatusTypeId != this.GameStatusTypeId && gameCheck.WinningTeamId != this.WinningTeamId)
//{
// gameCheck.GameStatusTypeId = this.GameStatusTypeId;
// gameCheck.WinningTeamId = this.WinningTeamId;
// gameCheck.HomeTeamScore = this.HomeTeamScore;
// gameCheck.AwayTeamScore = this.AwayTeamScore;
// gameCheck.UpdateGame();
//}
}
else
{
string queryString = "INSERT INTO [dbo].[Game] "
+ "([SeasonId],[GameDate],[GameStatusTypeId],[HomeTeamId],[HomeTeamScore] "
+ ",[AwayTeamId],[AwayTeamScore],[WinningTeamId]) "
+ " VALUES "
+ "(@SeasonId,@GameDate,@GameStatusTypeId,@HomeTeamId,@HomeTeamScore "
+ ",@AwayTeamId,@AwayTeamScore,@WinningTeamId)";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@SeasonId", this.SeasonId);
command.Parameters.AddWithValue("@GameDate", this.GameDate);
command.Parameters.AddWithValue("@GameStatusTypeID", this.GameStatusTypeId);
command.Parameters.AddWithValue("@HomeTeamId", this.HomeTeamId);
command.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
//command.Parameters.AddWithValue("@HomeTeamLine", this.HomeTeamLine);
command.Parameters.AddWithValue("@AwayTeamId", this.AwayTeamId);
command.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
// command.Parameters.AddWithValue("@AwayTeamLine", this.AwayTeamLine);
command.Parameters.AddWithValue("@WinningTeamId", this.WinningTeamId);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
}
return(true);
}
