private async Task <int> GetGameIdAsync(Team homeTeam, Team awayTeam, DateTime?date)
{
return(await _sqlConnection.GetIdAsync(GamesTable,
ColumnValuePair.Create(new ForegnKeyTableColumn(GamesTable, TeamsTable, "HomeTeamId").ColumnName, homeTeam.Id),
ColumnValuePair.Create(new ForegnKeyTableColumn(GamesTable, TeamsTable, "AwayTeamId").ColumnName, awayTeam.Id),
ColumnValuePair.Create(nameof(Game.Date), date)));
}
public async Task <IEnumerable <League> > GetLeaguesAsync(Sport sport, Country country)
{
return(await _sqlConnection.GetAllAsync(LeaguesTable,
new[]
{
ColumnValuePair.Create(new ForegnKeyTableColumn(LeaguesTable, SportsTable, "Id").ColumnName, sport.Id),
ColumnValuePair.Create(new ForegnKeyTableColumn(LeaguesTable, CountriesTable, "Id").ColumnName, country.Id)
},
CreateLeagueAsync));
}
public static ColumnValuePair[] CreateColumnValuePairs(this GameOdds odd, int gameId)
{
return(new[]
{
ColumnValuePair.Create(new ForegnKeyTableColumn(DbRepository.GameOddsTable, DbRepository.GamesTable, "Id").ColumnName, gameId),
ColumnValuePair.Create(new ForegnKeyTableColumn(DbRepository.GameOddsTable, DbRepository.BookersTable, "Id").ColumnName, odd.Bookkeeper.Id),
ColumnValuePair.Create(nameof(GameOdds.HomeOdd), odd.HomeOdd),
ColumnValuePair.Create(nameof(GameOdds.DrawOdd), odd.DrawOdd),
ColumnValuePair.Create(nameof(GameOdds.AwayOdd), odd.AwayOdd),
ColumnValuePair.Create(nameof(GameOdds.IsValid), odd.IsValid)
});
}
private async Task <Team> CreateTeamAsync(string name, Sport sport)
{
var id = await _sqlConnection.InsertAsync(TeamsTable,
ColumnValuePair.CreateName(name),
ColumnValuePair.Create(new ForegnKeyTableColumn(TeamsTable, SportsTable, "Id").ColumnName, sport.Id));
return(new Team()
{
Id = id,
Name = name,
Sport = sport
});
}
public static ColumnValuePair[] CreateColumnValuePairs(this Game game)
{
return(new[]
{
ColumnValuePair.Create(new ForegnKeyTableColumn(DbRepository.GamesTable, DbRepository.LeaguesTable, "Id").ColumnName, game.League.Id),
ColumnValuePair.Create(new ForegnKeyTableColumn(DbRepository.GamesTable, DbRepository.TeamsTable, "HomeTeamId").ColumnName, game.HomeTeam.Id),
ColumnValuePair.Create(new ForegnKeyTableColumn(DbRepository.GamesTable, DbRepository.TeamsTable, "AwayTeamId").ColumnName, game.AwayTeam.Id),
ColumnValuePair.Create(nameof(Game.Date), game.Date),
ColumnValuePair.Create(nameof(Game.HomeTeamScore), game.HomeTeamScore),
ColumnValuePair.Create(nameof(Game.AwayTeamScore), game.AwayTeamScore),
ColumnValuePair.Create(nameof(Game.IsPlayoffs), game.IsPlayoffs),
ColumnValuePair.Create(nameof(Game.IsOvertime), game.IsOvertime),
ColumnValuePair.Create(nameof(Game.Season), game.Season),
ColumnValuePair.Create(nameof(Game.GameLink), game.GameLink)
});
}
private async Task <League> CreateLeagueAsync(string name, bool isFirst, Sport sport, Country country)
{
var id = await _sqlConnection.InsertAsync(LeaguesTable,
ColumnValuePair.CreateName(name),
ColumnValuePair.Create(new ForegnKeyTableColumn(LeaguesTable, SportsTable, "Id").ColumnName, sport.Id),
ColumnValuePair.Create(new ForegnKeyTableColumn(LeaguesTable, CountriesTable, "Id").ColumnName, country.Id),
ColumnValuePair.Create(nameof(League.IsFirst), isFirst));
return(new League()
{
Id = id,
Name = name,
Sport = sport,
Country = country,
IsFirst = isFirst
});
}
public async Task <Team> GetTeamAsync(string name, Sport sport)
{
var dbId = await _sqlConnection.GetIdAsync(TeamsTable,
ColumnValuePair.CreateName(name),
ColumnValuePair.Create(new ForegnKeyTableColumn(TeamsTable, SportsTable, "Id").ColumnName, sport.Id));
if (dbId > 0)
{
return(new Team
{
Id = dbId,
Name = name,
Sport = sport
});
}
return(null);
}
public async Task <League> GetLeagueAsync(string name, Sport sport, Country country)
{
var dbId = await _sqlConnection.GetIdAsync(LeaguesTable,
ColumnValuePair.CreateName(name),
ColumnValuePair.Create(new ForegnKeyTableColumn(LeaguesTable, SportsTable, "Id").ColumnName, sport.Id),
ColumnValuePair.Create(new ForegnKeyTableColumn(LeaguesTable, CountriesTable, "Id").ColumnName, country.Id));
if (dbId > 0)
{
return(new League
{
Id = dbId,
Name = name,
Sport = sport,
Country = country
});
}
return(null);
}
public async Task UpdateGameLeagueAsync(Game game)
{
await _sqlConnection.UpdateAsync(GamesTable, game.Id,
ColumnValuePair.Create(new ForegnKeyTableColumn(GamesTable, LeaguesTable, "Id").ColumnName, game.League.Id)
);
}
private IEnumerable <GameOdds> GetGameOdds(int gameId)
{
return(_sqlConnection.GetAll(GameOddsTable,
new[] { ColumnValuePair.Create(new ForegnKeyTableColumn(GameOddsTable, GamesTable, "Id").ColumnName, gameId) },
CreateGameOddFromReader));
}
public IEnumerable <Game> GetAllLeagueGames(League league)
{
return(_sqlConnection.GetAll(GamesTable,
new[] { ColumnValuePair.Create(new ForegnKeyTableColumn(GamesTable, LeaguesTable, "Id").ColumnName, league.Id) },
CreateGame));
}
public async Task <IEnumerable <Game> > GetAllLeagueGamesAsync(League league)
{
return(await _sqlConnection.GetAllAsync(GamesTable,
new[] { ColumnValuePair.Create(new ForegnKeyTableColumn(GamesTable, LeaguesTable, "Id").ColumnName, league.Id) },
CreateGameAsync));
}
private async Task <IEnumerable <GameOdds> > GetGameOddsAsync(int gameId)
{
return(await _sqlConnection.GetAllAsync(GameOddsTable,
new[] { ColumnValuePair.Create(new ForegnKeyTableColumn(GameOddsTable, GamesTable, "Id").ColumnName, gameId) },
CreateGameOddFromReaderAsync));
}
private async Task <int> DeleteGameOddsAsync(int gameId)
{
return(await _sqlConnection.DeleteAsync(GameOddsTable,
ColumnValuePair.Create(new ForegnKeyTableColumn(GameOddsTable, GamesTable, "Id").ColumnName, gameId)));
}
public async Task <bool> GameExistsAsync(string gameLink)
{
return(await _sqlConnection.GetIdAsync(GamesTable,
ColumnValuePair.Create(nameof(Game.GameLink), gameLink)) > 0);
}