public NBAStatsForm()
{
InitializeComponent();
this.ExeDirectory = AppDomain.CurrentDomain.BaseDirectory;
var dbContext = new NBAStatisticsDbContext();
if (dbContext.Conferences.Count() == 0)
{
dbContext.Conferences.Add(new NBAStatistics.Models.Conference
{
Name = "East"
});
dbContext.Conferences.Add(new NBAStatistics.Models.Conference
{
Name = "West"
});
}
if (dbContext.Seasons.Count() == 0)
{
dbContext.Seasons.Add(new NBAStatistics.Models.Season
{
SeasonId = "2016-17"
});
dbContext.Seasons.Add(new NBAStatistics.Models.Season
{
SeasonId = "2015-16"
});
}
dbContext.SaveChanges();
}
public static async Task ImportFromZipFile(string zipPath)
{
var dbContext = new NBAStatisticsDbContext();
await Task.Run(() =>
{
try
{
var directoryPath = zipPath.Substring(0, zipPath.LastIndexOf('\\'));
string directoryWithReports = $"{directoryPath}\\Reports\\";
if (Directory.Exists(directoryWithReports))
{
Directory.Delete(directoryWithReports, true);
}
// If the directory already exists, this method does not create a new directory
DirectoryInfo di = Directory.CreateDirectory(directoryWithReports);
using (ZipArchive archive = ZipFile.OpenRead(zipPath))
{
foreach (ZipArchiveEntry entry in archive.Entries)
{
var directoryName = entry.FullName.Substring(0, 11);
var dateOfTheReport = DateTime.ParseExact(
directoryName,
"dd-MMM-yyyy",
CultureInfo.InvariantCulture);
var xlsFileName = entry.FullName.Substring(12);
var xlsPath = $"{directoryWithReports}{directoryName}_{xlsFileName}";
entry.ExtractToFile(xlsPath);
var connectionString = GetConnectionString(xlsPath, false);
using (var oleDbConnection = new OleDbConnection(connectionString))
{
oleDbConnection.Open();
var sheetNames = GetSheetNames(oleDbConnection);
var oleDbCommand =
new OleDbCommand("SELECT * FROM [" + sheetNames.First() + "]", oleDbConnection);
using (var oleDbAdapter = new OleDbDataAdapter(oleDbCommand))
{
var dataSet = new DataSet();
oleDbAdapter.Fill(dataSet);
// Load into the dbContext
dbContext.StandingsByDays.Load();
dbContext.Teams.Load();
dbContext.Conferences.Load();
using (var reader = dataSet.CreateDataReader())
{
while (reader.Read())
{
var teamId = (int)(double)reader["TEAM_ID"]; // TEAM_ID
var leagueId = reader["LEAGUE_ID"]; // LEAGUE_ID
var seasonId = (int)(double)reader["SEASON_ID"]; // SEASON_ID
var standingsDate = (DateTime)reader["STANDINGSDATE"]; // STANDINGSDATE
var conference = (string)reader["CONFERENCE"]; // CONFERENCE
var team = (string)reader["TEAM"]; // TEAM
var games = (byte)(double)reader["G"]; // G
var wins = (byte)(double)reader["W"]; // W
var losses = (byte)(double)reader["L"]; // L
var winningsPercentage = (double)reader["W_PCT"]; // W_PCT
var homeRecord = (string)reader["HOME_RECORD"]; // HOME_RECORD
var roadRecord = (string)reader["ROAD_RECORD"]; // ROAD_RECORD
var standingsByDayInDb = dbContext.StandingsByDays.Local
.SingleOrDefault(sbd => (sbd.TeamId == teamId) && (sbd.Date == standingsDate)); // runs in memory
// for tests only
//var t1 = dbContext.Teams.Local
// .SingleOrDefault(t => t.TeamId == teamId)
// .Id;
//var t2 = seasonId;
//var t3 = standingsDate;
//var t4 = dbContext.Conferences.Local
// .SingleOrDefault(c => c.Name == conference)
// .Id;
//var t5 = games;
//var t6 = wins;
//var t7 = losses;
//var t8 = winningsPercentage;
//var t9 = (byte)homeRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
// .Select(int.Parse)
// .ToArray()[0];
//var t10 = (byte)roadRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
// .Select(int.Parse)
// .ToArray()[0];
if (standingsByDayInDb == null)
{
dbContext.StandingsByDays.Add(new StandingsByDay
{
TeamId = dbContext.Teams.Local
.SingleOrDefault(t => t.TeamId == teamId)
.Id,
SeasonId = seasonId,
Date = standingsDate,
ConferenceId = dbContext.Conferences.Local
.SingleOrDefault(c => c.Name == conference)
.Id,
Games = games,
Wins = wins,
Loses = losses,
SuccessRate = Math.Round(winningsPercentage, 2),
HomeRecordWins = (byte)homeRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
.Select(int.Parse)
.ToArray()[0],
HomeRecordLosses = (byte)homeRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
.Select(int.Parse)
.ToArray()[1],
RoadRecordWins = (byte)roadRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
.Select(int.Parse)
.ToArray()[0],
RoadRecordLosses = (byte)roadRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
.Select(int.Parse)
.ToArray()[1]
});
}
}
}
}
}
// 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();
}
}
dbContext.SaveChanges();
Directory.Delete(directoryWithReports, true);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
});
}
