/// <summary>
/// Read the csv and populate the database
/// </summary>
private static void PopulateValuesFromCSV()
{
//The path of the csv file
string filePath = AppDomain.CurrentDomain.BaseDirectory + @"OnlineNewsPopularity.csv";
//Read the file line by line
var reader = new StreamReader(File.OpenRead(filePath));
//Discard the line containing the headers
if (!reader.EndOfStream)
{
reader.ReadLine();
}
//Create a MySQL repository to interact with the database
MySQLRepository myRepo = new MySQLRepository(password);
int count = 0;
//Iterate over each line in the file inserting values into the database
while (!reader.EndOfStream)
{
//Read one line
var line = reader.ReadLine();
//Split the attributes which are delimited by a comma
var values = line.Split(',');
//Add the article record
int article_id = (int)myRepo.AddArticle(values);
//Add the link record
myRepo.AddLink(values, article_id);
//Add the digital media record
myRepo.AddDigitalMedia(values, article_id);
//Add the nine shares records
myRepo.AddShares(values, article_id);
//Add the language record
int language_id = (int)myRepo.AddLanguage(values, article_id);
//Add the five lda records
myRepo.AddLDA(values, language_id);
//Add the six polarity records
myRepo.AddPolarity(values, language_id);
//Increment the count so that we know we are making progress
count++;
//The processing of lines takes a while, so provide some feedback
//so that we know the program is still working
if (count % 10 == 0)
{
Console.WriteLine("Record " + count + " added.");
}
if (count % 1000 == 0)
{
Console.WriteLine("Milestone count achieved: " + count);
}
}
myRepo.CloseConnection();
}
/// <summary>
/// Populate tables which contain static data. This was
/// designed to be idempotent, checking for the existance
/// or records before adding them anew.
/// </summary>
private static void PopulateLookupTables()
{
//Instantiate a sql repository through which we will interact with the database
MySQLRepository myRepo = new MySQLRepository(password);
//If we are able to open the connection
if (myRepo.OpenConnection() == true)
{
//Populate the table containing the days of the week
myRepo.CreateDays();
//Populate the categories table
myRepo.CreateCategories();
//Populate the ranks table
myRepo.CreateRanks();
//Populate the classifications table
myRepo.CreateClassifications();
//Close the connection
myRepo.CloseConnection();
}
else
{
Console.WriteLine("Connection not open");
}
}