private static void DataToTable(HAPStock stock)
{
using (SqlConnection db = new SqlConnection(_connection))
{
string insertToTable = "INSERT INTO dbo.HAPStockTable (Time_Scraped, Stock_Symbol, Last_Price, Change, Change_Percent) VALUES (@time_scraped, @stock_symbol, @last_price, @change, @change_percent);";
{
db.Open();
Console.WriteLine("Database has been opened");
if (db.State == System.Data.ConnectionState.Open)
{
using (SqlCommand dataToTable = new SqlCommand(insertToTable, db))
{
dataToTable.Parameters.AddWithValue("@time_scraped", stock.TimeScraped);
dataToTable.Parameters.AddWithValue("@stock_symbol", stock.StockSymbol);
dataToTable.Parameters.AddWithValue("@last_price", stock.LastPrice);
dataToTable.Parameters.AddWithValue("@change", stock.Change);
dataToTable.Parameters.AddWithValue("@change_percent", stock.ChangePercent);
dataToTable.ExecuteNonQuery();
}
}
else
{
Console.WriteLine("No database found. Please check database connection.");
}
db.Close();
}
}
}
public void Scrape()
{
string nasdaqUrl = "https://www.nasdaq.com/market-activity/stocks";
HtmlWeb webNav = new HtmlWeb();
HtmlDocument document = webNav.Load(nasdaqUrl);
HtmlNodeCollection dataTable = document.DocumentNode.SelectNodes("/html/body/div[4]/div/main/div/article/div[2]/div/section[2]/div/div/div[2]/div[1]/table/tbody");
HAPStock stock;
List <HAPStock> stockData = new List <HAPStock>();
foreach (var tableRow in dataTable)
{
DateTime timeScraped = DateTime.Now;
string stockSymbol = tableRow.SelectSingleNode("td/h3/a").InnerText;
string lastPrice = tableRow.SelectSingleNode("td[4]").InnerText.Replace(" ", string.Empty);
string InitChange = tableRow.SelectSingleNode("td[5]/span").InnerText.Replace(" ", "").Replace(" ", "").Replace("▲", " ");
int changeLength = InitChange.Length;
int cutString = 4;
string change = InitChange.Substring(0, cutString).Trim();
string changePercent = InitChange.Substring(cutString).Trim();
stock = new HAPStock(timeScraped, stockSymbol, lastPrice, change, changePercent);
stockData.Add(stock);
InsertScrapeToDatabase(stock);
}
}
private static void LastScrapeToDatabase(HAPStock stock)
{
string lastScrape = @"IF EXISTS(SELECT* FROM HAPStockTable WHERE Stock Symbol = @stock_symbol)
UPDATE HAPStockTable
SET Time_Scraped=@time_scraped, Last_Price = @last_price, Change = @change, Change_Percent = @change_percent;
WHERE Stock_Symbol = @stock_symbol
ELSE
INSERT INTO HAPStockTable VALUES (@time_scraped, @stock_symbol, @last_price, @change, @change_percent);";
using (SqlConnection db = new SqlConnection(_connection))
{
db.Open();
Console.WriteLine("Database has been opened.");
if (db.State == System.Data.ConnectionState.Open)
{
using (SqlCommand command = new SqlCommand(lastScrape, db))
{
command.Parameters.AddWithValue("@time_scraped", stock.TimeScraped);
command.Parameters.AddWithValue("@stock_symbol", stock.StockSymbol);
command.Parameters.AddWithValue("@last_price", stock.LastPrice);
command.Parameters.AddWithValue("@change", stock.Change);
command.Parameters.AddWithValue("@change_percent", stock.ChangePercent);
command.ExecuteNonQuery();
}
}
else
{
Console.WriteLine("No database found. Please check database connection.");
}
db.Close();
}
}
public static void InsertScrapeToDatabase(HAPStock stock)
{
DataToTable(stock);
}
public static void LatestScrapeToDatabase(HAPStock stock)
{
LastScrapeToDatabase(stock);
}