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); }