private static void InsertIntoScrapeHistory(StockModel stock)
{
string scrapeHistory = "INSERT INTO StockHistory VALUES (@Symbol, @Price, @ChangePercent, @Volume, @MarketCap);";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
using (SqlCommand command = new SqlCommand(scrapeHistory, connection))
{
command.Parameters.Add(new SqlParameter("@Symbol", stock.Symbol));
command.Parameters.Add(new SqlParameter("@Price", stock.LastPrice));
command.Parameters.Add(new SqlParameter("@ChangePercent", stock.ChangePercent));
command.Parameters.Add(new SqlParameter("@Volume", stock.Volume));
command.Parameters.Add(new SqlParameter("@MarketCap", stock.MarketCap));
command.ExecuteNonQuery();
Console.WriteLine("{0} added to StockHistory table...", stock.Symbol);
}
}
else
{
Console.WriteLine("No connection...");
}
connection.Close();
}
}
public static void InsertIntoLatestScrape(StockModel stock)
{
string latestScrape = @"IF EXISTS(SELECT * FROM Stocks WHERE Symbol = @Symbol)
UPDATE Stocks
SET LastPrice = @Price, ChangePercent = @ChangePercent,
Volume = @Volume, MarketCap = @MarketCap
WHERE Symbol = @Symbol
ELSE
INSERT INTO Stocks VALUES(@Symbol, @Price, @ChangePercent, @Volume, @MarketCap)";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
using (SqlCommand command = new SqlCommand(latestScrape, connection))
{
command.Parameters.Add(new SqlParameter("@Symbol", stock.Symbol));
command.Parameters.Add(new SqlParameter("@Price", stock.LastPrice));
command.Parameters.Add(new SqlParameter("@ChangePercent", stock.ChangePercent));
command.Parameters.Add(new SqlParameter("@Volume", stock.Volume));
command.Parameters.Add(new SqlParameter("@MarketCap", stock.MarketCap));
command.ExecuteNonQuery();
Console.WriteLine("{0} added to Stocks table...", stock.Symbol);
}
}
}
}
private static void ParseScrapedData(ScrapedData extractedData)
{
Console.WriteLine("Begin ParseScrapedData Method");
int stockTotal = extractedData.StockSymbols.Count;
Console.WriteLine("stocktotal {0}", stockTotal);
List <string> symbols = new List <string>();
List <double> lastPrice = new List <double>();
List <double> changePercent = new List <double>();
List <string> volume = new List <string>();
List <string> marketCap = new List <string>();
StockModel stock = new StockModel();
for (int i = 0; i < stockTotal; i++)
{
symbols.Insert(i, Convert.ToString(extractedData.StockSymbols[i].Text));
lastPrice.Insert(i, Convert.ToDouble(extractedData.StockLastPrices[i].Text));
char trim = '%';
changePercent.Insert(i, Convert.ToDouble(extractedData.StockChangePercents[i].Text.TrimEnd(trim)));
volume.Insert(i, Convert.ToString(extractedData.StockVolumes[i].Text));
marketCap.Insert(i, Convert.ToString(extractedData.StockMarketCaps[i].Text));
stock = new StockModel(symbols[i],
lastPrice[i],
changePercent[i],
volume[i],
marketCap[i]);
Console.WriteLine("{0} stock created", symbols[i]);
InsertStockHistory(stock);
InsertCurrentStock(stock);
}
}
public static void InsertCurrentStock(StockModel stock)
{
InsertIntoLatestScrape(stock);
}
public static void InsertStockHistory(StockModel stock)
{
InsertIntoScrapeHistory(stock);
}
