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()); } }