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 static DbParameter ToParameter(this ColumnValuePair columnValuePair)
{
return(new SQLiteParameter(columnValuePair.ValueType, columnValuePair.Value)
{
SourceColumn = columnValuePair.Column
});
}
private async Task <Bookkeeper> CreateBookerAsync(string name)
{
var id = await _sqlConnection.InsertAsync(BookersTable, ColumnValuePair.CreateName(name));
return(new Bookkeeper()
{
Id = id,
Name = name
});
}
private async Task <Country> CreateCountryAsync(string name)
{
var id = await _sqlConnection.InsertAsync(CountriesTable, ColumnValuePair.CreateName(name));
return(new Country()
{
Id = id,
Name = name
});
}
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));
}
private async Task <Sport> CreateSportAsync(string name)
{
var id = await _sqlConnection.InsertAsync(SportsTable, ColumnValuePair.CreateName(name));
return(new Sport()
{
Id = id,
Name = name
});
}
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
});
}
private async Task <Bookkeeper> GetBookerAsync(string name)
{
var dbId = await _sqlConnection.GetIdAsync(BookersTable, ColumnValuePair.CreateName(name));
if (dbId > 0)
{
return(new Bookkeeper
{
Id = dbId,
Name = name
});
}
return(null);
}
public async Task <Country> GetCountryAsync(string name)
{
var dbId = await _sqlConnection.GetIdAsync(CountriesTable, ColumnValuePair.CreateName(name));
if (dbId > 0)
{
return(new Country
{
Id = dbId,
Name = name
});
}
return(null);
}
public async Task <Sport> GetSportAsync(string name)
{
var dbId = await _sqlConnection.GetIdAsync(SportsTable, ColumnValuePair.CreateName(name));
if (dbId > 0)
{
return(new Sport
{
Id = dbId,
Name = name
});
}
return(null);
}
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);
}
/// <summary>
/// Sets a value for the specified column
/// </summary>
/// <param name="column">The column or attribute name</param>
/// <param name="value">The new value to update</param>
/// <param name="mode">Sets how the update value will be applied to the existing field value</param>
internal UpdateQueryBuilder Set(string column, object value, ValueMode mode)
{
// Check parameter
if (String.IsNullOrWhiteSpace(column))
{
throw new ArgumentNullException("column");
}
// Add column to list
if (Columns.ContainsKey(column))
{
Columns[column] = new ColumnValuePair(column, value, mode);
}
else
{
Columns.Add(column, new ColumnValuePair(column, value, mode));
}
// Return this instance for chaining
return(this);
}
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 static async Task <T> GetByIdAsync <T>(this DbConnection connection, string tableName, int id, Func <DbDataReader, Task <T> > dataCreator)
{
return(await connection.GetSingleAsync(tableName, new[] { ColumnValuePair.CreateId(id) }, dataCreator));
}
public async Task <bool> GameExistsAsync(string gameLink)
{
return(await _sqlConnection.GetIdAsync(GamesTable,
ColumnValuePair.Create(nameof(Game.GameLink), gameLink)) > 0);
}
public static string BuildUpdateQuery(string Table, List <ColumnValuePair> Values, ColumnValuePair condition)
{
string _query = "UPDATE " + Table + " SET ";
foreach (var pair in Values)
{
_query += pair.Culumn + "=" + pair.Value.ToDBObject() + ", ";
}
_query = _query.Remove(_query.LastIndexOf(','), 1);
_query += " WHERE " + condition.Culumn + "=" + condition.Value;
return(_query);
}
private async Task <int> DeleteGameOddsAsync(int gameId)
{
return(await _sqlConnection.DeleteAsync(GameOddsTable,
ColumnValuePair.Create(new ForegnKeyTableColumn(GameOddsTable, GamesTable, "Id").ColumnName, gameId)));
}
public static T GetById <T>(this DbConnection connection, string tableName, int id, Func <DbDataReader, T> dataCreator)
{
return(connection.GetSingle(tableName, new[] { ColumnValuePair.CreateId(id) }, dataCreator));
}
private async Task <IEnumerable <GameOdds> > GetGameOddsAsync(int gameId)
{
return(await _sqlConnection.GetAllAsync(GameOddsTable,
new[] { ColumnValuePair.Create(new ForegnKeyTableColumn(GameOddsTable, GamesTable, "Id").ColumnName, gameId) },
CreateGameOddFromReaderAsync));
}
public Int32 GetIdentity(params Object[] argKey)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT " + this.Column + " FROM " + this.Table + " ");
sql.Append("WHERE ");
foreach (Object key in argKey)
{
switch (key.GetType().ToString())
{
case "OSN.ColumnValuePair`1[System.String]":
{
ColumnValuePair <string> k = (ColumnValuePair <string>)key;
sql.Append(k.Column + " = ");
sql.Append("'" + k.Value + "'");
break;
}
case "OSN.ColumnValuePair`1[System.Int32]":
{
ColumnValuePair <Int32> k = (ColumnValuePair <Int32>)key;
sql.Append(k.Column + " = ");
sql.Append(k.Value);
break;
}
case "OSN.ColumnValuePair`1[System.Float]":
{
ColumnValuePair <float> k = (ColumnValuePair <float>)key;
sql.Append(k.Column + " = ");
sql.Append(k.Value);
break;
}
case "OSN.ColumnValuePair`1[System.Double]":
{
ColumnValuePair <double> k = (ColumnValuePair <double>)key;
sql.Append(k.Column + " = ");
sql.Append(k.Value);
break;
}
case "OSN.ColumnValuePair`1[System.DateTime]":
{
ColumnValuePair <DateTime> k = (ColumnValuePair <DateTime>)key;
sql.Append(k.Column + " = ");
sql.Append("'" + k.Value.Year.ToString() +
k.Value.Month.ToString() + k.Value.Day.ToString() + "'");
break;
}
}
sql.Append(" AND ");
}
if (argKey.Length > 0)
{
sql.Remove(sql.Length - 5, 5);
}
Int32 id = (Int32)SqlHelper.ExecuteScalar(this.Connection, System.Data.CommandType.Text, sql.ToString());
return(id);
}
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));
}
