public static void AddPowerRates()
{
//Method to deserialize CSV file, to populate the properties of the PowerRate model
//and to persist each instance to the EF code-first table PowerRates in the Rates database.
using (var context = new ElectricityRatesContext())
{
if (context.PowerRates.Any())
{
return;
}
Console.WriteLine("Since this is the first run of the app, we are adding electricty rate information to the database. This process should take a few moments.");
var directory = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
string fileName = Path.Combine(directory, "iouzipcodes2016.csv");
int i = 1;
//Changed adding each instance of the model class to the context to adding it to a List
//of PowerRate since Add on a DbContext uses a lot of processing power.
List <PowerRate> powerRatesList = new List <PowerRate>();
using (FileStream fs = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var bs = new BufferedStream(fs))
using (var reader = new StreamReader(bs))
{
using (var csv = new CsvReader(reader))
{
csv.Read();
csv.ReadHeader();
while (csv.Read())
{
string zipCode;
if (csv[0].Length < 5)
{
zipCode = "0" + csv[0];
}
else
{
zipCode = csv[0];
}
string utilityName = csv[2];
double residentialRate;
if (double.TryParse(csv[8], out residentialRate))
{
;
}
PowerRate rate = new PowerRate(zipCode, utilityName, residentialRate);
powerRatesList.Add(rate);
if (i % 5000 == 0)
{
Console.WriteLine("Adding more rates.");
}
i++;
}
}
context.PowerRates.AddRange(powerRatesList);
context.SaveChanges();
}
Console.WriteLine("Electrity rate information added! Proceeding to the Main Menu.");
}
}
// Helper method to get the name of the Utility provider.
private static string GetUtilityProviderName(string zipCode)
{
using (var context = new ElectricityRatesContext())
{
return(context.PowerRates.Where(pr => pr.ZipCode == zipCode)
.Select(pr => pr.UtilityName)
.FirstOrDefault());
}
}
// Implementation of the Save abstract method.
protected override void Save(UtilitySearchResult utilitySearch)
{
utilitySearch.Time = DateTime.Now;
using (var context = new ElectricityRatesContext())
{
context.UtilitySearchResults.Add(utilitySearch);
context.SaveChanges();
}
}
// Implementation of the Save abstract method.
protected override void Save(ResidentialChargeResult chargeResult)
{
chargeResult.Time = DateTime.Now;
using (var context = new ElectricityRatesContext())
{
context.ResidentialChargeResults.Add(chargeResult);
context.SaveChanges();
}
}
//Implementation of the Save abstract method.
protected override void Save(RateComparisonResult rateComparison)
{
rateComparison.Time = DateTime.Now;
using (var context = new ElectricityRatesContext())
{
context.RateComparisonResults.Add(rateComparison);
context.SaveChanges();
}
}
// Implemented method to get a rate from PowerRates as all RateGetters need to have
// same implmentation.
// Protected access modifier as the method should only be called on classes that inherit RateGetters<T>.
protected double GetRate(string zipCode)
{
using (var context = new ElectricityRatesContext())
{
return(context.PowerRates.Where(pr => pr.ZipCode == zipCode)
.Select(pr => pr.ResidentialRate)
.DefaultIfEmpty(0)
.Sum());
}
}
// Implementation of the GetHistory abstract method.
// Method to get a user-specified length of IQueryable<UtilitySearchResult> and displays them
// to the console using the ConsoleTables NuGet extension.
public override void GetHistory()
{
if (!NumberOfResults(out int numberOfResults, "utility provider searches"))
{
return;
}
var table = new ConsoleTable("Time", "City", "State", "Provider");
using (var context = new ElectricityRatesContext())
{
var results = context.UtilitySearchResults.OrderByDescending(r => r.Id)
.Take(numberOfResults);
foreach (var result in results)
{
table.AddRow(result.Time.ToString(), result.City, result.StateAbbreviation, result.UtilityName);
}
Console.WriteLine(string.Format("Here are the last {0} result(s):", results.Count()));
}
table.Write();
Console.WriteLine();
}
// Implementation of the GetHistory abstract method.
// Method to get a user-specified length of IQueryable<ResidentialChargeResult> and displays them
// to the console using the ConsoleTables NuGet extension.
public override void GetHistory()
{
if (!NumberOfResults(out int numberOfResults, "residential charge estimates"))
{
return;
}
var table = new ConsoleTable("Time", "City", "State", "Rate", "Charge", "Usage(kWh)");
using (var context = new ElectricityRatesContext())
{
var results = context.ResidentialChargeResults.OrderByDescending(r => r.Id)
.Take(numberOfResults);
foreach (var result in results)
{
table.AddRow(result.Time.ToString(), result.City, result.StateAbbreviation,
string.Format("{0:C}", result.Rate), string.Format("{0:C}", result.Charge), result.Usage);
}
Console.WriteLine(string.Format("Here are the last {0} result(s):", results.Count()));
}
table.Write();
Console.WriteLine();
}
// Implementation of the GetHistory abstract method.
// Method to get a user-specified length IQueryable<RateComparisonResult> and displays them
// to the console using the ConsoleTables NuGet extension.
public override void GetHistory()
{
if (!NumberOfResults(out int numberOfResults, "rate comparisons"))
{
return;
}
var table = new ConsoleTable("Time", "City 1", "State 1", "Rate 1", "City 2", "State 2", "Rate 2", "Difference");
using (var context = new ElectricityRatesContext())
{
var results = context.RateComparisonResults.OrderByDescending(r => r.Id)
.Take(numberOfResults);
foreach (var result in results)
{
table.AddRow(result.Time.ToString(), result.City1, result.StateAbbreviation1, string.Format("{0:C}", result.Rate1),
result.City2, result.StateAbbreviation2, string.Format("{0:C}", result.Rate2), string.Format("{0:P2}", result.Difference));
}
Console.WriteLine(string.Format("Here are the last {0} result(s):", results.Count()));
Console.WriteLine("A negative percentage in the Difference Column means the first city's rate is less.");
}
table.Write();
Console.WriteLine();
}
