public void SaveToDB(int matchNo, DateTime matchDay, Location location, Role role, MatchDayShort matchDayShort, char matchType, int round, string row, DrivingInfo drivingInfo, Team homeTeam, Team awayTeam)
{
using (var conn = new MySqlConnection(ConnectionString))
{
conn.Open();
var locationId = GetLocationId(location);
var homeTeamId = GetTeamId(homeTeam);
var awayTeamId = GetTeamId(awayTeam);
var cmd = new MySqlCommand(
"INSERT INTO DbuGrabber_Game " +
"(GameNo, Round, GameDayShort, GameDay, Row, LocationId, HomeTeamId, AwayTeamId, Role, DrivingHours, DrivingMinutes, MatchType) " +
"VALUES " +
"(@GameNo, @Round, @GameDayShort, @GameDay, @Row, @LocationId, @HomeTeamId, @AwayTeamId, @Role, @DrivingHours, @DrivingMinutes, @MatchType)" +
" ON DUPLICATE KEY UPDATE Round = @Round, GameDayShort = @GameDayShort, GameDay = @GameDay, Row = @Row, LocationId = @LocationId, HomeTeamId= @HomeTeamId, AwayTeamId = @AwayTeamId, Role = @Role, DrivingHours = CASE WHEN (DrivingHours <> @DrivingHours) THEN @DrivingHours ELSE DrivingHours END, DrivingMinutes = CASE WHEN (DrivingMinutes <> @DrivingMinutes) THEN @DrivingMinutes ELSE DrivingMinutes END, MatchType= @MatchType, Sent = CASE WHEN (DrivingHours <> @DrivingHours OR DrivingMinutes <> @DrivingMinutes) THEN 0 ELSE Sent END", conn);
cmd.Parameters.AddWithValue("@GameNo", matchNo);
cmd.Parameters.AddWithValue("@Round", round);
cmd.Parameters.AddWithValue("@GameDayShort", matchDayShort.ToString());
cmd.Parameters.AddWithValue("@GameDay", matchDay);
cmd.Parameters.AddWithValue("@Row", row);
cmd.Parameters.AddWithValue("@LocationId", locationId);
cmd.Parameters.AddWithValue("@HomeTeamId", homeTeamId);
cmd.Parameters.AddWithValue("@AwayTeamId", awayTeamId);
cmd.Parameters.AddWithValue("@Role", role.ToString());
cmd.Parameters.AddWithValue("@DrivingHours", drivingInfo.Hours);
cmd.Parameters.AddWithValue("@DrivingMinutes", drivingInfo.Minutes);
cmd.Parameters.AddWithValue("@MatchType", matchType);
cmd.ExecuteNonQuery();
}
}
//TODO: Add addtional team attributes on insert!
private int GetTeamId(Team team)
{
using (var conn = new MySqlConnection(ConnectionString))
{
conn.Open();
var cmd =
new MySqlCommand(
string.Format("SELECT TeamId FROM DbuGrabber_Team WHERE TeamName = '{0}'", team.Name), conn);
var teamId = cmd.ExecuteScalar();
if (teamId == null)
{
cmd = new MySqlCommand(string.Format("INSERT INTO DbuGrabber_Team (TeamName) VALUES ('{0}'); SELECT last_insert_id();", team.Name), conn);
teamId = cmd.ExecuteScalar();
}
return int.Parse(teamId.ToString());
}
}
