Application Logic
I wrote a web scraper that store some football data inside my database. Those data need to be update if 7 days are passed since the last update. I have three types of record:
- Team
- Season
- Player
The table structure of those records is essentially this:
id | season_id | update_at
only the second column name change for the specific type, eg for Team
:
id | team_id | | update_at
Structure
The web scraper run 24h on a server, and check steadily if the records need to be updated. For doing that I wrote this method:
public List<Season> GetSeasonsToAddUpdate(List<Season> seasons)
{
//Store the seasons that need to update
List<Season> newSeasons = new List<Season>();
using (MySqlConnection connection = new DBConnection().Connect)
{
using (MySqlCommand command = new MySqlCommand())
{
connection.Open();
command.Connection = connection;
//Get all the ids of the seasons to add or update
command.CommandText = "SELECT cs.season_id " +
"FROM (SELECT {textToReplace}) cs " +
"LEFT JOIN competition_seasons s on s.id = cs.season_id " +
"WHERE s.id IS NULL OR " +
"s.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) OR s.update_at IS NULL";
//Join all the available parameters.
StringBuilder unions = new StringBuilder();
//List of available parameters.
List<MySqlParameter> prms = new List<MySqlParameter>();
//Add the first parameter.
MySqlParameter pr = new MySqlParameter("@first", MySqlDbType.UInt32) { Value = seasons.First().Id };
prms.Add(pr);
unions.Append($" @first as season_id ");
//Start from one 'cause first param already defined in query.
int prmCounter = 1;
//Create the parameter for the derived table based on the available seasons
foreach (Season t in seasons.Skip(1))
{
string placeholder = "@p" + prmCounter;
unions.Append($" UNION ALL SELECT {placeholder}");
pr = new MySqlParameter(placeholder, MySqlDbType.Int32) { Value = t.Id };
prms.Add(pr);
prmCounter++;
}
command.Parameters.AddRange(prms.ToArray());
command.CommandText = command.CommandText.Replace("{textToReplace}", unions.ToString());
using (MySqlDataReader reader = command.ExecuteReader())
{
//Remove all the seasons that doesn't need update.
while (reader.Read())
{
newSeasons.Add(seasons.FirstOrDefault(x => x.Id == Convert.ToInt32(reader["season_id"])));
}
}
return newSeasons;
}
}
}
Basically, the method above only works for the Season
object, and perform the following query:
SELECT cs.season_id
FROM (SELECT 67 as season_id UNION ALL SELECT 68 UNION ALL
SELECT 69 UNION ALL SELECT 70
) cs LEFT JOIN
competition_season s
on s.id = cs.season_id AND
update_at >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
WHERE s.id IS NULL;
The query structure is essentially the same for the other two type: Team
and Players
(only the table name change).
The goal
My goal is remove the code redundancy, 'cause I have three method of the code above which are essentially the same but change only for the table name and the type of object, eg:
public List<Team> GetTeamsToAddUpdate(List<Team> teams)
{
//Store the teams that need to update
List<Team> newTeams = new List<Team>();
using (MySqlConnection connection = new DBConnection().Connect)
{
using (MySqlCommand command = new MySqlCommand())
{
connection.Open();
command.Connection = connection;
//Get all the ids of the teams to add or update
command.CommandText = "SELECT tt.team_id " +
"FROM (SELECT {textToReplace}) tt " +
"LEFT JOIN team t on t.id = tt.team_id " +
"WHERE t.id IS NULL OR " +
"t.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) OR t.update_at IS NULL";
I though to implement something like a switch statement for create 1 method that handle multiple types, but I don't know if there are better ways to handle this.
Could someone tell me if is possible optimize that and remove this redundancy?
The other two methods:
Team:
public List<Team> GetTeamsToAddUpdate(List<Team> teams)
{
List<Team> newTeams = new List<Team>();
using (MySqlConnection connection = new DBConnection().Connect)
{
using (MySqlCommand command = new MySqlCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = "SELECT tt.team_id " +
"FROM (SELECT {textToReplace}) tt " +
"LEFT JOIN team t on t.id = tt.team_id " +
"WHERE t.id IS NULL OR " +
"t.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) OR t.update_at IS NULL";
StringBuilder unions = new StringBuilder();
List<MySqlParameter> prms = new List<MySqlParameter>();
MySqlParameter pr = new MySqlParameter("@first", MySqlDbType.UInt32) { Value = teams.First().Id };
prms.Add(pr);
unions.Append($" @first as team_id ");
int prmCounter = 1;
foreach (Team t in teams.Skip(1))
{
string placeholder = "@p" + prmCounter;
unions.Append($" UNION ALL SELECT {placeholder}");
pr = new MySqlParameter(placeholder, MySqlDbType.Int32) { Value = t.Id };
prms.Add(pr);
prmCounter++;
}
command.Parameters.AddRange(prms.ToArray());
command.CommandText = command.CommandText.Replace("{textToReplace}", unions.ToString());
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
newTeams.Add(teams.FirstOrDefault(x => x.Id == Convert.ToInt32(reader["team_id"])));
}
}
return newTeams;
}
}
}
Player:
public List<Player> GetPlayersToAddUpdate(List<Player> players)
{
List<Player> newPlayers = new List<Player>();
using (MySqlConnection connection = new DBConnection().Connect)
{
using (MySqlCommand command = new MySqlCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = "SELECT pp.player_id " +
"FROM (SELECT {textToReplace}) pp " +
"LEFT JOIN player p on p.id = pp.player_id " +
"WHERE p.id IS NULL OR " +
"p.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) OR p.update_at IS NULL";
StringBuilder unions = new StringBuilder();
List<MySqlParameter> prms = new List<MySqlParameter>();
MySqlParameter pr = new MySqlParameter("@first", MySqlDbType.UInt32) { Value = players.First().Id };
prms.Add(pr);
unions.Append($" @first as player_id ");
int prmCounter = 1;
foreach(Player p in players.Skip(1))
{
string placeholder = "@p" + prmCounter;
unions.Append($" UNION ALL SELECT {placeholder}");
pr = new MySqlParameter(placeholder, MySqlDbType.Int32) { Value = p.Id };
prms.Add(pr);
prmCounter++;
}
command.Parameters.AddRange(prms.ToArray());
command.CommandText = command.CommandText.Replace("{textToReplace}", unions.ToString());
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
newPlayers.Add(players.FirstOrDefault(x => x.Id == Convert.ToInt32(reader["player_id"])));
}
}
return newPlayers;
}
}
}