public void NhlHtmlReport_ParseFrenchRegSeasonAndPersist()
{
string path = @"C:\Users\jordanf\Google Drive\Coding\Sportsdata\TestData\FrenchRegSeasonRoster_formatted.htm";
string html = File.ReadAllText(path);
Nhl_Games_Rtss_Roster model = NhlGamesRtssRoster.ParseHtmlBlob(-1, html);
using (SportsDataContext db = new SportsDataContext())
{
db.Nhl_Games_Rtss_Roster_DbSet.AddOrUpdate(
m => m.Id,
model);
db.SaveChanges();
model = db.Nhl_Games_Rtss_Roster_DbSet.FirstOrDefault(m => m.Id == model.Id);
model.VisitorRoster.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "visitor player 1" });
model.VisitorRoster.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "visitor player 2" });
model.VisitorScratches.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "visitor scratch 1" });
model.VisitorScratches.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "visitor scratch 2" });
model.VisitorHeadCoach.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "visitor head coach 1" });
model.HomeRoster.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "home player 1" });
model.HomeRoster.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "home player 2" });
model.HomeScratches.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "home scratch 1" });
model.HomeScratches.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "home scratch 2" });
model.HomeHeadCoach.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "home head coach 1" });
model.Referees.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "referee 1" });
model.Referees.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "referee 2" });
model.Linesman.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "linesman 1" });
model.Linesman.Add(new Nhl_Games_Rtss_RosterParticipantItem { Name = "linesman 2" });
db.Nhl_Games_Rtss_Roster_DbSet.AddOrUpdate(
m => m.Id,
model);
db.SaveChanges();
}
List<Nhl_Games_Rtss_Roster> models;
using (SportsDataContext db = new SportsDataContext())
{
models = (from m in db.Nhl_Games_Rtss_Roster_DbSet
.Include(x => x.VisitorRoster)
.Include(x => x.VisitorScratches)
.Include(x => x.VisitorHeadCoach)
.Include(x => x.HomeRoster)
.Include(x => x.HomeScratches)
.Include(x => x.HomeHeadCoach)
.Include(x => x.Referees)
.Include(x => x.Linesman)
select m).ToList();
}
}
public static List<TwitterSnapshot> UpdateSnapshotsInDb(List<TwitterAccount> accounts)
{
// Get latest results
List<TwitterSnapshot> snapshots = TwitterQuery.GetTwitterSnapshots(accounts);
// Remove existing results from DB from today and save new ones
using (SportsDataContext db = new SportsDataContext())
{
IEnumerable<TwitterSnapshot> snapshotsToRemove = from s in db.TwitterSnapshot_DbSet
where DbFunctions.TruncateTime(s.DateOfSnapshot) == DbFunctions.TruncateTime(DateTime.UtcNow)
select s;
foreach (TwitterSnapshot snapshotToRemove in snapshotsToRemove)
{
db.TwitterSnapshot_DbSet.Remove(snapshotToRemove);
}
//db.SaveChanges();
foreach (TwitterSnapshot snapshotToAdd in snapshots)
{
db.TwitterSnapshot_DbSet.Add(snapshotToAdd);
}
db.SaveChanges();
}
return snapshots;
}
public static List <TwitterSnapshot> UpdateSnapshotsInDb(List <TwitterAccount> accounts)
{
// Get latest results
List <TwitterSnapshot> snapshots = TwitterQuery.GetTwitterSnapshots(accounts);
// Remove existing results from DB from today and save new ones
using (SportsDataContext db = new SportsDataContext())
{
IEnumerable <TwitterSnapshot> snapshotsToRemove = from s in db.TwitterSnapshot_DbSet
where DbFunctions.TruncateTime(s.DateOfSnapshot) == DbFunctions.TruncateTime(DateTime.UtcNow)
select s;
foreach (TwitterSnapshot snapshotToRemove in snapshotsToRemove)
{
db.TwitterSnapshot_DbSet.Remove(snapshotToRemove);
}
//db.SaveChanges();
foreach (TwitterSnapshot snapshotToAdd in snapshots)
{
db.TwitterSnapshot_DbSet.Add(snapshotToAdd);
}
db.SaveChanges();
}
return(snapshots);
}
public static List <MlbGameSummaryModel> UpdateSeasonForTeam(MlbSeasonType mlbSeasonType, MlbTeamShortName mlbTeam, int seasonYear)
{
// Get latest results
List <MlbGameSummaryModel> gamesToAdd = MlbAttendanceQuery.GetSeasonForTeam(mlbSeasonType, mlbTeam, seasonYear);
// Remove existing results from DB and save new ones
using (SportsDataContext db = new SportsDataContext())
{
string mlbTeamString = mlbTeam.ToString(); // http://stackoverflow.com/questions/5899683/linq-to-entities-does-not-recognize-the-method-system-string-tostring-method
var gamesToRemove = from g in db.MlbGameSummaryModel_DbSet
where g.MlbSeasonType == mlbSeasonType &&
g.Year == seasonYear &&
g.Home.Equals(mlbTeamString, StringComparison.InvariantCultureIgnoreCase)
select g;
foreach (MlbGameSummaryModel gameToRemove in gamesToRemove)
{
db.MlbGameSummaryModel_DbSet.Remove(gameToRemove);
}
if (null != gamesToAdd)
{
foreach (MlbGameSummaryModel gameToAdd in gamesToAdd)
{
db.MlbGameSummaryModel_DbSet.Add(gameToAdd);
}
}
db.SaveChanges();
}
return(gamesToAdd);
}
public static List<MlbGameSummaryModel> UpdateSeasonForTeam(MlbSeasonType mlbSeasonType, MlbTeamShortName mlbTeam, int seasonYear)
{
// Get latest results
List<MlbGameSummaryModel> gamesToAdd = MlbAttendanceQuery.GetSeasonForTeam(mlbSeasonType, mlbTeam, seasonYear);
// Remove existing results from DB and save new ones
using (SportsDataContext db = new SportsDataContext())
{
string mlbTeamString = mlbTeam.ToString(); // http://stackoverflow.com/questions/5899683/linq-to-entities-does-not-recognize-the-method-system-string-tostring-method
var gamesToRemove = from g in db.MlbGameSummaryModel_DbSet
where g.MlbSeasonType == mlbSeasonType &&
g.Year == seasonYear &&
g.Home.Equals(mlbTeamString, StringComparison.InvariantCultureIgnoreCase)
select g;
foreach (MlbGameSummaryModel gameToRemove in gamesToRemove)
{
db.MlbGameSummaryModel_DbSet.Remove(gameToRemove);
}
if (null != gamesToAdd)
{
foreach (MlbGameSummaryModel gameToAdd in gamesToAdd)
{
db.MlbGameSummaryModel_DbSet.Add(gameToAdd);
}
}
db.SaveChanges();
}
return gamesToAdd;
}
public static List<MlbGameSummaryModel> UpdateSeason(MlbSeasonType mlbSeasonType, int seasonYear)
{
// Get latest results
List<MlbGameSummaryModel> gamesToAdd = MlbAttendanceQuery.GetSeason(mlbSeasonType, seasonYear);
// Remove existing results from DB and save new ones
using (SportsDataContext db = new SportsDataContext())
{
IEnumerable<MlbGameSummaryModel> gamesToRemove = from g in db.MlbGameSummaryModel_DbSet
where g.MlbSeasonType == mlbSeasonType && g.Year == seasonYear
select g;
foreach (MlbGameSummaryModel gameToRemove in gamesToRemove)
{
db.MlbGameSummaryModel_DbSet.Remove(gameToRemove);
}
foreach (MlbGameSummaryModel gameToAdd in gamesToAdd)
{
db.MlbGameSummaryModel_DbSet.Add(gameToAdd);
}
db.SaveChanges();
}
return gamesToAdd;
}
public static List <MlbGameSummaryModel> UpdateSeason(MlbSeasonType mlbSeasonType, int seasonYear)
{
// Get latest results
List <MlbGameSummaryModel> gamesToAdd = MlbAttendanceQuery.GetSeason(mlbSeasonType, seasonYear);
// Remove existing results from DB and save new ones
using (SportsDataContext db = new SportsDataContext())
{
IEnumerable <MlbGameSummaryModel> gamesToRemove = from g in db.MlbGameSummaryModel_DbSet
where g.MlbSeasonType == mlbSeasonType && g.Year == seasonYear
select g;
foreach (MlbGameSummaryModel gameToRemove in gamesToRemove)
{
db.MlbGameSummaryModel_DbSet.Remove(gameToRemove);
}
foreach (MlbGameSummaryModel gameToAdd in gamesToAdd)
{
db.MlbGameSummaryModel_DbSet.Add(gameToAdd);
}
db.SaveChanges();
}
return(gamesToAdd);
}
private static void AddOrUpdateDb(List<Nhl_Players_Bio_Goalie> models)
{
using (SportsDataContext db = new SportsDataContext())
{
db.Nhl_Players_Bio_Goalie_DbSet.AddOrUpdate<Nhl_Players_Bio_Goalie>(p => new { p.NhlSeasonType, p.Name, p.Year, p.Team }, models.ToArray());
db.SaveChanges();
}
}
private static void AddOrUpdateDb(List <Nhl_Players_Bio_Goalie> models)
{
using (SportsDataContext db = new SportsDataContext())
{
db.Nhl_Players_Bio_Goalie_DbSet.AddOrUpdate <Nhl_Players_Bio_Goalie>(p => new { p.NhlSeasonType, p.Name, p.Year, p.Team }, models.ToArray());
db.SaveChanges();
}
}
private static void AddOrUpdateDb(List <Nhl_Draftbook> models)
{
using (SportsDataContext db = new SportsDataContext())
{
db.Database.ExecuteSqlCommand("DELETE FROM NhlDraftbookModels");
db.Nhl_Draftbook_DbSet.AddRange(models);
db.SaveChanges();
}
}
private static void AddOrUpdateDb(List<Nhl_Draftbook> models)
{
using (SportsDataContext db = new SportsDataContext())
{
db.Database.ExecuteSqlCommand("DELETE FROM NhlDraftbookModels");
db.Nhl_Draftbook_DbSet.AddRange(models);
db.SaveChanges();
}
}
public static void UpdateDatabase(List<DemographicsModel> data)
{
Console.WriteLine("Saving {0} records", data.Count);
using (SportsDataContext db = new SportsDataContext())
{
db.Demographic_DbSet.AddOrUpdate(x => x.Zip, data.ToArray());
db.SaveChanges();
}
Console.WriteLine("Done saving {0} records", data.Count);
}
public static void UpdateDatabase(List <DemographicsModel> data)
{
Console.WriteLine("Saving {0} records", data.Count);
using (SportsDataContext db = new SportsDataContext())
{
db.Demographic_DbSet.AddOrUpdate(x => x.Zip, data.ToArray());
db.SaveChanges();
}
Console.WriteLine("Done saving {0} records", data.Count);
}
private static void AddOrUpdateDb(List <Nhl_Games_Summary> models)
{
// Note: downcast for models is not necessary but leave this here in anticipation of moving this method to a base class (and it will be necessary)
// Special case the FLA/NSH double header on 9/16/2013
IEnumerable <Nhl_Games_BaseModel> specialCaseModels = NhlGamesBaseClass.GetSpecialCaseModels(models);
IEnumerable <Nhl_Games_Summary> downcastSpecialCaseModels = specialCaseModels.ToList().ConvertAll <Nhl_Games_Summary>(m => (Nhl_Games_Summary)m);
IEnumerable <Nhl_Games_Summary> downcastModels = models.Except(specialCaseModels, new NhlGameStatsBaseModelComparer()).ToList().ConvertAll <Nhl_Games_Summary>(m => (Nhl_Games_Summary)m);
using (SportsDataContext db = new SportsDataContext())
{
db.Nhl_Games_Summary_DbSet.AddOrUpdate <Nhl_Games_Summary>(g => new { g.Date, g.Visitor, g.Home, g.VisitorScore, g.HomeScore }, downcastSpecialCaseModels.ToArray());
db.Nhl_Games_Summary_DbSet.AddOrUpdate <Nhl_Games_Summary>(g => new { g.Date, g.Visitor, g.Home }, downcastModels.ToArray());
db.SaveChanges();
}
}
private static void AddOrUpdateDb(List<Nhl_Games_Summary> models)
{
// Note: downcast for models is not necessary but leave this here in anticipation of moving this method to a base class (and it will be necessary)
// Special case the FLA/NSH double header on 9/16/2013
IEnumerable<Nhl_Games_BaseModel> specialCaseModels = NhlGamesBaseClass.GetSpecialCaseModels(models);
IEnumerable<Nhl_Games_Summary> downcastSpecialCaseModels = specialCaseModels.ToList().ConvertAll<Nhl_Games_Summary>(m => (Nhl_Games_Summary)m);
IEnumerable<Nhl_Games_Summary> downcastModels = models.Except(specialCaseModels, new NhlGameStatsBaseModelComparer()).ToList().ConvertAll<Nhl_Games_Summary>(m => (Nhl_Games_Summary)m);
using (SportsDataContext db = new SportsDataContext())
{
db.Nhl_Games_Summary_DbSet.AddOrUpdate<Nhl_Games_Summary>(g => new { g.Date, g.Visitor, g.Home, g.VisitorScore, g.HomeScore }, downcastSpecialCaseModels.ToArray());
db.Nhl_Games_Summary_DbSet.AddOrUpdate<Nhl_Games_Summary>(g => new { g.Date, g.Visitor, g.Home }, downcastModels.ToArray());
db.SaveChanges();
}
}
public static void UpdateSeason([Optional] int year, [Optional] DateTime fromDate, [Optional] bool forceOverwrite)
{
// Initialize the rtss reports that we are going to read and parse
List<Nhl_Games_Rtss> models = NhlHtmlReportBase.GetRtssReports(year, fromDate);
List<Nhl_Games_Rtss_Roster> existingModels = null;
if (forceOverwrite == false)
{
// Only query for existing if we are not going to force overwrite all
existingModels = NhlGamesRtssRoster.GetHtmlRosterReports(year, fromDate);
}
// For each report, get the html blob from blob storage and parse the blob to a report
List<Nhl_Games_Rtss_Roster> results = new List<Nhl_Games_Rtss_Roster>();
foreach (Nhl_Games_Rtss model in models)
{
if (forceOverwrite == false && existingModels.Exists(m => m.NhlRtssReportModelId == model.Id))
{
// In this case, only get data if it is not already populated
continue;
}
Nhl_Games_Rtss_Roster report = null;
if (!model.GameLink.Equals("#"))
{
string htmlBlob = HtmlBlob.RetrieveBlob(HtmlBlobType.NhlRoster, model.Id.ToString(), new Uri(model.RosterLink), true);
report = NhlGamesRtssRoster.ParseHtmlBlob(model.Id, htmlBlob);
}
if (null != report)
{
results.Add(report);
}
}
// Save the reports to the db 100 records at a time
using (SportsDataContext db = new SportsDataContext())
{
int counter = 0;
int totalCounter = 0;
int batchSize = 10;
foreach (Nhl_Games_Rtss_Roster model in results)
{
Console.WriteLine("Start saving {0} to {1}", results.Count, db.Database.Connection.ConnectionString);
db.Configuration.AutoDetectChangesEnabled = false;
db.Configuration.ValidateOnSaveEnabled = false;
counter++;
totalCounter++;
if (model.Id != 0)
{
db.Nhl_Games_Rtss_Roster_DbSet.Attach(model);
db.Entry(model).State = EntityState.Modified;
}
else
{
db.Entry(model).State = EntityState.Added;
}
if (counter >= batchSize)
{
db.SaveChanges();
counter = 0;
Console.WriteLine("Saved {0} of {1}", totalCounter, results.Count);
}
}
db.SaveChanges();
Console.WriteLine("Saved {0} of {1}", totalCounter, results.Count);
}
}
public static void UpdateSeason([Optional] int year, [Optional] DateTime fromDate, [Optional] bool forceOverwrite)
{
// Get the RtssReports for the specified year. Exclude recent games that may not be done (UtcNow - 1)
List <Nhl_Games_Rtss> models = NhlHtmlReportBase.GetRtssReports(year, fromDate).Where(m => m.Date < DateTime.UtcNow.AddDays(-1).Date).ToList();
List <Nhl_Games_Rtss_Summary> existingModels = null;
if (forceOverwrite == false)
{
// Only query for existing if we are not going to force overwrite all
existingModels = NhlGamesRtssSummary.GetHtmlSummaryReports(year, fromDate);
}
// For each report, get the html blob from blob storage and parse the blob to a report
List <Nhl_Games_Rtss_Summary> results = new List <Nhl_Games_Rtss_Summary>();
foreach (Nhl_Games_Rtss model in models)
{
if (forceOverwrite == false && existingModels.Exists(m => m.NhlRtssReportModelId == model.Id))
{
// In this case, only get data if it is not already populated
continue;
}
Nhl_Games_Rtss_Summary report = null;
if (!model.GameLink.Equals("#"))
{
string htmlBlob = HtmlBlob.RetrieveBlob(HtmlBlobType.NhlRoster, model.Id.ToString(), new Uri(model.GameLink), true);
report = NhlGamesRtssSummary.ParseHtmlBlob(model.Id, htmlBlob);
}
if (null != report)
{
results.Add(report);
}
}
// Save the reports to the db
using (SportsDataContext db = new SportsDataContext())
{
Console.WriteLine("Start saving {0} to {1}", results.Count, db.Database.Connection.ConnectionString);
db.Configuration.AutoDetectChangesEnabled = false;
db.Configuration.ValidateOnSaveEnabled = false;
int counter = 0;
int totalCounter = 0;
int batchSize = 10;
foreach (var model in results)
{
counter++;
totalCounter++;
if (model.Id != 0)
{
db.Nhl_Games_Rtss_Summary_DbSet.Attach(model);
db.Entry(model).State = EntityState.Modified;
}
else
{
db.Entry(model).State = EntityState.Added;
}
if (counter >= batchSize)
{
db.SaveChanges();
counter = 0;
Console.WriteLine("Saved {0} of {1}", totalCounter, results.Count);
}
}
db.SaveChanges();
Console.WriteLine("Saved {0} of {1}", totalCounter, results.Count);
}
}
public void NhlHtmlReport_ParseFrenchRegSeasonAndPersist()
{
string path = @"C:\Users\jordanf\Google Drive\Coding\Sportsdata\TestData\FrenchRegSeasonRoster_formatted.htm";
string html = File.ReadAllText(path);
Nhl_Games_Rtss_Roster model = NhlGamesRtssRoster.ParseHtmlBlob(-1, html);
using (SportsDataContext db = new SportsDataContext())
{
db.Nhl_Games_Rtss_Roster_DbSet.AddOrUpdate(
m => m.Id,
model);
db.SaveChanges();
model = db.Nhl_Games_Rtss_Roster_DbSet.FirstOrDefault(m => m.Id == model.Id);
model.VisitorRoster.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "visitor player 1"
});
model.VisitorRoster.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "visitor player 2"
});
model.VisitorScratches.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "visitor scratch 1"
});
model.VisitorScratches.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "visitor scratch 2"
});
model.VisitorHeadCoach.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "visitor head coach 1"
});
model.HomeRoster.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "home player 1"
});
model.HomeRoster.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "home player 2"
});
model.HomeScratches.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "home scratch 1"
});
model.HomeScratches.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "home scratch 2"
});
model.HomeHeadCoach.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "home head coach 1"
});
model.Referees.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "referee 1"
});
model.Referees.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "referee 2"
});
model.Linesman.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "linesman 1"
});
model.Linesman.Add(new Nhl_Games_Rtss_RosterParticipantItem {
Name = "linesman 2"
});
db.Nhl_Games_Rtss_Roster_DbSet.AddOrUpdate(
m => m.Id,
model);
db.SaveChanges();
}
List <Nhl_Games_Rtss_Roster> models;
using (SportsDataContext db = new SportsDataContext())
{
models = (from m in db.Nhl_Games_Rtss_Roster_DbSet
.Include(x => x.VisitorRoster)
.Include(x => x.VisitorScratches)
.Include(x => x.VisitorHeadCoach)
.Include(x => x.HomeRoster)
.Include(x => x.HomeScratches)
.Include(x => x.HomeHeadCoach)
.Include(x => x.Referees)
.Include(x => x.Linesman)
select m).ToList();
}
}