static OleDbConnectionProvider()
{
Provider = new OleDbConnectionProvider();
}
private async void btnGenerateZipFile_Click(object sender, EventArgs e)
{
this.btnGenerateZipFile.Enabled = false;
const string DailyStandingsUri = "http://stats.nba.com/stats/scoreboard?DayOffset=0&LeagueID=00&gameDate=";
DateTime date = DateTime.Now;
try
{
string directoryWithReports = $"{this.ExeDirectory}Reports\\";
string zipPath = $"{this.ExeDirectory}reports.zip";
if (Directory.Exists(directoryWithReports))
{
Directory.Delete(directoryWithReports, true);
}
if (File.Exists(zipPath))
{
File.Delete(zipPath);
}
// If the directory already exists, this method does not create a new directory
DirectoryInfo di = Directory.CreateDirectory(directoryWithReports);
var options = new Dictionary <string, string>();
options["Referer"] = "http://stats.nba.com/scores/";
var tasks = new List <Task <DailyStandings> >();
int numberOfFiles = 10;
for (int i = 0; i < numberOfFiles; i++)
{
date = date.AddDays(-1);
string uriString = DailyStandingsUri + date.ToString("MM-dd-yyyy").Replace("-", "%2F");
// random delay to simulate human requests and prevent blocking of
// our IP address from server
int milisecondsToDelay = RandomProvider.Instance.Next(0, numberOfFiles * 50);
tasks.Add(GetJsonObjFromNetworkFileAsync <DailyStandings>(uriString, Encoding.UTF8, options, milisecondsToDelay));
}
await Task.Run(async() =>
{
foreach (var dailyStandings in await Task.WhenAll(tasks))
{
if (dailyStandings == null)
{
MessageBox.Show("Daily Standings url does not response with JSON file.");
return;
}
DateTime gameDate = DateTime.ParseExact(
dailyStandings.Parameters.GameDate,
"MM/dd/yyyy",
CultureInfo.InvariantCulture);
string directoryPath = directoryWithReports + gameDate.ToString("dd-MMM-yyyy");
Directory.CreateDirectory(directoryPath);
IEnumerable <string> reportsNames = new string[] {
"EastConfStandingsByDay",
"WestConfStandingsByDay"
};
foreach (var reportName in reportsNames)
{
var xlsPath = $"{directoryPath}\\{reportName}.xls";
var connectionString = OleDbConnectionProvider.GetConnectionString(xlsPath, false);
using (var oleDbConnection = new OleDbConnection(connectionString))
{
oleDbConnection.Open();
var sheetNames = GetSheetNames(oleDbConnection);
if (sheetNames.Count() != 2)
{
SetSheetNames(reportName, oleDbConnection);
}
foreach (var resultSet in dailyStandings.ResultSets)
{
foreach (var row in resultSet.RowSet)
{
if (resultSet.Name == reportName)
{
var teamId = (int)(long)row[0];
var leagueId = (string)row[1];
var seasonId = int.Parse((string)row[2]);
var standingsDate = DateTime.ParseExact((string)row[3], "MM/dd/yyyy", CultureInfo.InvariantCulture);
var conference = (string)row[4];
var team = (string)row[5];
var games = (byte)(long)row[6];
var wins = (byte)(long)row[7];
var losses = (byte)(long)row[8];
var winningsPercentage = (float)(double)row[9];
var homeRecord = (string)row[10];
var roadRecord = (string)row[11];
var cmd = new OleDbCommand(
$"INSERT INTO [{resultSet.Name}] VALUES (@TEAM_ID, @LEAGUE_ID, @SEASON_ID, @STANDINGSDATE, @CONFERENCE, @TEAM, @G, @W, @L, @W_PCT, @HOME_RECORD, @ROAD_RECORD)",
oleDbConnection);
cmd.Parameters.AddWithValue("@TEAM_ID", teamId);
cmd.Parameters.AddWithValue("@LEAGUE_ID", leagueId);
cmd.Parameters.AddWithValue("@SEASON_ID", seasonId);
cmd.Parameters.AddWithValue("@STANDINGSDATE", standingsDate);
cmd.Parameters.AddWithValue("@CONFERENCE", conference);
cmd.Parameters.AddWithValue("@TEAM", team);
cmd.Parameters.AddWithValue("@G", games);
cmd.Parameters.AddWithValue("@W", wins);
cmd.Parameters.AddWithValue("@L", losses);
cmd.Parameters.AddWithValue("@W_PCT", winningsPercentage);
cmd.Parameters.AddWithValue("@HOME_RECORD", homeRecord);
cmd.Parameters.AddWithValue("@ROAD_RECORD", roadRecord);
cmd.ExecuteNonQuery();
}
}
}
oleDbConnection.Close();
}
// Force clean up to release file handles
// source: http://stackoverflow.com/questions/2225087/the-process-cannot-access-the-file-because-it-is-being-used-by-another-process
GC.Collect();
}
}
});
ZipFile.CreateFromDirectory(directoryWithReports, zipPath);
Directory.Delete(directoryWithReports, true);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
this.btnGenerateZipFile.Enabled = true;
}
private async void btnGenerateZipFile_Click(object sender, EventArgs e)
{
const string dailyStandingsUri = "http://stats.nba.com/stats/scoreboard?DayOffset=0&LeagueID=00&gameDate=";
DateTime date = DateTime.Now;
try
{
string directoryWithReportsPath = $"{this.ExeDirectory}Reports";
string zipPath = $"{this.ExeDirectory}reports.zip";
if (Directory.Exists(directoryWithReportsPath))
{
Directory.Delete(directoryWithReportsPath, true);
}
if (File.Exists(zipPath))
{
File.Delete(zipPath);
}
// If the directory already exists, this method does not create a new directory
DirectoryInfo di = Directory.CreateDirectory(directoryWithReportsPath);
var options = new Dictionary <string, string>();
options["Referer"] = "http://stats.nba.com/scores/";
await Task.Run(async() =>
{
// get DailyStandings for last 10 days
for (int i = 0; i < 10; i++)
{
date = date.AddDays(-1);
string uriString = dailyStandingsUri + date.ToString("MM-dd-yyyy").Replace("-", "%2F");
await GetJsonObjFromNetworkFileAsync(uriString, Encoding.UTF8, options);
if (dailyStandings == null)
{
MessageBox.Show("Daily Standings url does not response with JSON file.");
return;
}
DateTime gameDate = DateTime.ParseExact(
dailyStandings.Parameters.GameDate,
"MM/dd/yyyy",
CultureInfo.InvariantCulture);
string directoryPath = this.ExeDirectory + "Reports\\" + gameDate.ToString("dd-MMM-yyyy");
Directory.CreateDirectory(directoryPath);
IEnumerable <string> reportsNames = new string[] {
"EastConfStandingsByDay",
"WestConfStandingsByDay"
};
foreach (var reportName in reportsNames)
{
var xlsPath = $"{directoryPath}\\{reportName}.xls";
var connectionString = OleDbConnectionProvider.GetConnectionString(xlsPath, false);
using (var oleDbConnection = new OleDbConnection(connectionString))
{
oleDbConnection.Open();
var sheetNames = GetSheetNames(oleDbConnection);
if (sheetNames.Count() != 2)
{
SetSheetNames(reportName, oleDbConnection);
}
foreach (var resultSet in dailyStandings.ResultSets)
{
if (resultSet.Name == reportName)
{
foreach (var row in resultSet.rowSet)
{
var teamId = (int)(long)row[0];
var leagueId = (string)row[1];
var seasonId = int.Parse((string)row[2]);
var standingsDate = DateTime.ParseExact((string)row[3], "MM/dd/yyyy", CultureInfo.InvariantCulture);
var conference = (string)row[4];
var team = (string)row[5];
var games = (byte)(long)row[6];
var wins = (byte)(long)row[7];
var losses = (byte)(long)row[8];
var winningsPercentage = (float)(double)row[9];
var homeRecord = (string)row[10]; //byte.Parse(((string)row[10]).Split(new char[] { '-' })[0]);
var roadRecord = (string)row[11]; //byte.Parse(((string)row[11]).Split(new char[] { '-' })[0]);
var cmd = new OleDbCommand(
$"INSERT INTO [{resultSet.Name}] VALUES (@TEAM_ID, @LEAGUE_ID, @SEASON_ID, @STANDINGSDATE, @CONFERENCE, @TEAM, @G, @W, @L, @W_PCT, @HOME_RECORD, @ROAD_RECORD)",
oleDbConnection);
cmd.Parameters.AddWithValue("@TEAM_ID", teamId);
cmd.Parameters.AddWithValue("@LEAGUE_ID", leagueId);
cmd.Parameters.AddWithValue("@SEASON_ID", seasonId);
cmd.Parameters.AddWithValue("@STANDINGSDATE", standingsDate);
cmd.Parameters.AddWithValue("@CONFERENCE", conference);
cmd.Parameters.AddWithValue("@TEAM", team);
cmd.Parameters.AddWithValue("@G", games);
cmd.Parameters.AddWithValue("@W", wins);
cmd.Parameters.AddWithValue("@L", losses);
cmd.Parameters.AddWithValue("@W_PCT", winningsPercentage);
cmd.Parameters.AddWithValue("@HOME_RECORD", homeRecord);
cmd.Parameters.AddWithValue("@ROAD_RECORD", roadRecord);
cmd.ExecuteNonQuery();
}
}
}
}
}
}
});
ZipFile.CreateFromDirectory(directoryWithReportsPath, zipPath);
// delete Reports directory
di.Delete(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}