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 url = "https://www.marketwatch.com/investing/index/comp?mod=us-markets";
HtmlWeb webNav = new HtmlWeb();
HtmlDocument document = webNav.Load(url);
HtmlNodeCollection stockTable = document.DocumentNode.SelectNodes("/html/body/div[1]/div[5]/div[3]/div[1]/div/table/tbody");
Console.WriteLine(stockTable);
HAPStock stock;
List <HAPStock> stockData = new List <HAPStock>();
foreach (var tableRow in stockTable)
{
DateTime timeScraped = DateTime.Now;
string stockSymbol = tableRow.SelectSingleNode("/html/body/div[1]/div[5]/div[3]/div[1]/div/table/tbody/tr[1]/td[1]").InnerText;
Console.WriteLine(stockSymbol);
string lastPrice = tableRow.SelectSingleNode("/html/body/div[1]/div[5]/div[3]/div[1]/div/table/tbody/tr[1]/td[2]").InnerText.Replace(" ", string.Empty);
Console.WriteLine(lastPrice);
string change = tableRow.SelectSingleNode("/html/body/div[1]/div[5]/div[3]/div[1]/div/table/tbody/tr[1]/td[3]").InnerText.Replace(" ", "").Replace(" ", "").Replace("▲", " ");
Console.WriteLine(change);
string changePercent = tableRow.SelectSingleNode("/html/body/div[1]/div[5]/div[3]/div[1]/div/table/tbody/tr[1]/td[4]").InnerText;
Console.WriteLine(changePercent);
/*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);
}