public int InsertUserTeam(UserTeams userTeams, SqlConnection db)
{
try
{
int rowsAffected = 0;
using (IDataReader reader = userTeams.GetDataReader())
//using (var db = new SqlConnection(ConfigurationManager.ConnectionStrings["FantasyPremierLeagueUserTeam"].ConnectionString))
{
//db.Open();
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 1000;
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "UserTeam";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("id", "id");
bulkCopy.ColumnMappings.Add("player_first_name", "player_first_name");
bulkCopy.ColumnMappings.Add("player_last_name", "player_last_name");
bulkCopy.ColumnMappings.Add("player_region_id", "player_region_id");
bulkCopy.ColumnMappings.Add("player_region_name", "player_region_name");
bulkCopy.ColumnMappings.Add("player_region_iso_code_long", "player_region_iso_code");
bulkCopy.ColumnMappings.Add("summary_overall_points", "summary_overall_points");
bulkCopy.ColumnMappings.Add("summary_overall_rank", "summary_overall_rank");
bulkCopy.ColumnMappings.Add("summary_event_points", "summary_gameweek_points");
bulkCopy.ColumnMappings.Add("summary_event_rank", "summary_gameweek_rank");
bulkCopy.ColumnMappings.Add("current_event", "current_gameweekId");
bulkCopy.ColumnMappings.Add("joined_time", "joined_time");
bulkCopy.ColumnMappings.Add("name", "team_name");
bulkCopy.ColumnMappings.Add("last_deadline_bank", "team_bank");
bulkCopy.ColumnMappings.Add("last_deadline_value", "team_value");
bulkCopy.ColumnMappings.Add("last_deadline_total_transfers", "team_transfers");
bulkCopy.ColumnMappings.Add("kit", "kit");
bulkCopy.ColumnMappings.Add("favourite_team", "favourite_teamid");
bulkCopy.ColumnMappings.Add("started_event", "started_gameweekid");
//using (var dataReader = userTeamSeasons.ToDataReader())
//{
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
//}
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeam Repository (insert) error: " + ex.Message);
throw new Exception("UserTeam Repository (insert) error: " + ex.Message);
//throw ex;
}
}
public int UpdateUserTeam(UserTeams userTeams, SqlConnection db)
{
int rowsAffected = 0;
try
{
using (IDataReader reader = userTeams.GetDataReader())
{
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 1000;
bulkCopy.DestinationTableName = "UserTeamUpdateStaging";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("id", "id");
bulkCopy.ColumnMappings.Add("player_first_name", "player_first_name");
bulkCopy.ColumnMappings.Add("player_last_name", "player_last_name");
bulkCopy.ColumnMappings.Add("player_region_id", "player_region_id");
bulkCopy.ColumnMappings.Add("player_region_name", "player_region_name");
bulkCopy.ColumnMappings.Add("player_region_iso_code_long", "player_region_iso_code");
bulkCopy.ColumnMappings.Add("summary_overall_points", "summary_overall_points");
bulkCopy.ColumnMappings.Add("summary_overall_rank", "summary_overall_rank");
bulkCopy.ColumnMappings.Add("summary_event_points", "summary_gameweek_points");
bulkCopy.ColumnMappings.Add("summary_event_rank", "summary_gameweek_rank");
bulkCopy.ColumnMappings.Add("current_event", "current_gameweekId");
bulkCopy.ColumnMappings.Add("joined_time", "joined_time");
bulkCopy.ColumnMappings.Add("name", "team_name");
bulkCopy.ColumnMappings.Add("last_deadline_bank", "team_bank");
bulkCopy.ColumnMappings.Add("last_deadline_value", "team_value");
bulkCopy.ColumnMappings.Add("last_deadline_total_transfers", "team_transfers");
bulkCopy.ColumnMappings.Add("kit", "kit");
bulkCopy.ColumnMappings.Add("favourite_team", "favourite_teamid");
bulkCopy.ColumnMappings.Add("started_event", "started_gameweekid");
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeam Repository (update) error: " + ex.Message);
throw ex;
}
}