private void ReadEspn(PickemPoolContext context, IList <Team> teams)
{
using (var client = new HttpClient())
{
client.Timeout = new TimeSpan(0, 0, 0, 30);
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
for (var weekNumber = 1; weekNumber <= this.CurrentWeek; weekNumber++)
{
dynamic espnData = HttpContext.Cache.Get($"EspnData{weekNumber}");
if (espnData != null)
{
ParseEspnData(context, teams, weekNumber, espnData);
continue;
}
var espnUrl = $"http://site.api.espn.com/apis/site/v2/sports/football/nfl/scoreboard?lang=en®ion=us&calendartype=blacklist&limit=100&dates=2016&seasontype=2&week={weekNumber}";
var response = client.GetAsync(espnUrl).Result;
if (response.IsSuccessStatusCode)
{
espnData = JsonConvert.DeserializeObject(response.Content.ReadAsStringAsync().Result);
if (!ParseEspnData(context, teams, weekNumber, espnData))
{
break;
}
HttpContext.Cache.Add($"EspnData{weekNumber}", espnData, null, DateTime.Now.AddMinutes(30), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
}
else
{
var message = $"There was a problem connecting to the endpoint using address: {espnUrl}. {response.ReasonPhrase}";
throw new DataException(message);
}
}
}
context.SaveChanges();
}
private IList <Participant> GetParticipants(PickemPoolContext context)
{
// if we already read the spreadsheet, return the data from the db
if (context.Participants.Any())
{
this.ReadEspn(context, context.Teams.Include(o => o.HomeGames).Include(o => o.AwayGames).ToList());
return(context.Participants.Include(o => o.Teams).ToList());
}
var file = Server.MapPath(@"~/App_Data/pickem.xlsx");
var stream = System.IO.File.Open(file, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = null;
if (file.EndsWith(".xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (file.EndsWith(".xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
throw new ArgumentOutOfRangeException(nameof(file), "Only .xlsx, .xls and .csv files are supported");
}
excelReader.IsFirstRowAsColumnNames = true;
var dataSet = excelReader.AsDataSet();
var dataTable = dataSet.Tables[0];
var teams = new List <Team>();
for (var i = 1; i <= 32; i++)
{
teams.Add(new Team
{
Name = dataTable.Columns[i].ColumnName
});
}
var participants = new List <Participant>();
foreach (DataRow row in dataTable.Rows)
{
if (string.IsNullOrWhiteSpace(row[0].ToString()))
{
break;
}
var participant = new Participant
{
Name = row[0].ToString()
};
for (var i = 1; i <= 32; i++)
{
if (!string.IsNullOrWhiteSpace(row[i].ToString()))
{
participant.Teams.Add(teams[i - 1]);
}
}
participants.Add(participant);
}
context.Teams.AddRange(teams);
context.SaveChanges();
context.Participants.AddRange(participants);
context.SaveChanges();
this.ReadEspn(context, teams);
return(participants);
}
