public static void FilterCompanyHasSingleQuoteResult() { using (DbStockMonitor context = new DbStockMonitor()) { List <Company> allCompanyList = context.Companies.AsNoTracking().ToList(); foreach (var company in allCompanyList) { string response = RetrieveJsonDataHelper.GetQuoteStringBySymbol(company.Symbol).Result; if (response.Length < 20) { List <QuoteDaily> dailyQuoteList = context.QuoteDailies.AsNoTracking().Where(q => q.Symbol == company.Symbol).ToList(); Console.Out.WriteLine($"*** Found: {company.Symbol},will remove {dailyQuoteList.Count} records. response: {response}"); for (int i = 0; i < dailyQuoteList.Count; i++) { context.QuoteDailies.Remove(dailyQuoteList[i]); if (i % 50 == 0 && i != 0) { Console.Out.WriteLine($"{company.Symbol}: Deleted 50 records, left: {dailyQuoteList.Count - i}"); } context.SaveChanges(); } context.Companies.Remove(company); context.SaveChanges(); } } } }
public static void DeleteNoQuoteDataSybolsAndRecordsFromDb() { string filepath = "C:\\Users\\WW\\Desktop\\SourceTree\\IPD20-DotNetProject\\StockMonitor\\InvalidSymbols.txt"; List <string> list = File.ReadAllText(filepath).Split('\n').ToList(); foreach (var symbol in list) { using (DbStockMonitor context = new DbStockMonitor()) { var dailyQuoteList = context.QuoteDailies.Where(r => r.Symbol == symbol).ToList(); Console.Out.WriteLine($"Deleting records for {symbol}: {dailyQuoteList.Count}"); for (int i = 0; i < dailyQuoteList.Count; i++) { context.QuoteDailies.Remove(dailyQuoteList[i]); context.SaveChanges(); if (i % 50 == 0 && i != 0) { Console.Out.WriteLine($"Deleted 50 records, left: {dailyQuoteList.Count - i}"); } } context.SaveChanges(); Company company = context.Companies.FirstOrDefault(c => c.Symbol == symbol); context.Companies.Remove(company); context.SaveChanges(); Console.Out.WriteLine($"Delete {symbol}"); } } }
public static void InsertItemToWatchList(int userId, int companyId) { try { using (DbStockMonitor _dbContext = new DbStockMonitor()) { var result = _dbContext.WatchListItems .Include("Company").FirstOrDefault(w => w.UserId == userId && w.CompanyId == companyId); if (result != null) { throw new SystemException("Item EXISTS in database"); } else { WatchListItem item = new WatchListItem { UserId = userId, CompanyId = companyId }; _dbContext.WatchListItems.Add(item); _dbContext.SaveChanges(); Console.Out.WriteLine($"*** SUCCESSFULLY add item to watch list. {userId}, {companyId}"); } } } catch (SystemException ex) { throw new SystemException($"\n*** Add item to watchlist failed. {userId}:{companyId}. " + ex.Message); } }
public static void DeleteFromWatchListById(int userId, int companyId) { try { using (DbStockMonitor _dbContext = new DbStockMonitor()) { var result = _dbContext.WatchListItems.Include("Company") .FirstOrDefault(w => w.UserId == userId && w.CompanyId == companyId); if (result == null) { throw new SystemException($"*** No such item in watchlist db, {userId}, {companyId}"); } else { _dbContext.WatchListItems.Attach(result); _dbContext.WatchListItems.Remove(result); _dbContext.SaveChanges(); } } } catch (SystemException ex) { throw new SystemException( $"\n*** Delete item from watchlist fails. userid: {userId}, companyId: {companyId}. " + ex.Message); } }
private static void GetSubListDailyQuotes(List <string> subList, int index) { for (int i = 0; i < subList.Count; i++) { string info = $"T{index}=>{i}: "; List <QuoteDaily> dailyQuoteList = GUIDataHelper.GetQuoteDailyList(subList[i]); TimeSpan timeConsume = new TimeSpan(); using (DbStockMonitor dbctx = new DbStockMonitor()) { try { DateTime start = DateTime.Now; dailyQuoteList.ForEach(p => dbctx.QuoteDailies.Add(p)); dbctx.SaveChanges(); DateTime end = DateTime.Now; timeConsume = end - start; } catch (SystemException ex) { Console.Out.WriteLine(info + "!!!! DB save changes failure: " + subList[i] + ", " + ex.Message); } Console.Out.WriteLine( $"{info}Insert {subList[i]}, from {dailyQuoteList[0].Date} to {dailyQuoteList[dailyQuoteList.Count - 1].Date}, total: {dailyQuoteList.Count}, time: {timeConsume.TotalSeconds} sec"); } } }
public static void FirstImportStockListToDatabase() { int counter = 0; using (DbStockMonitor context = new DbStockMonitor()) { counter = context.Companies.Count(); Console.Out.WriteLine($"Counter start: {counter}"); } List <FmgStockListEntity> stockList = RetrieveJsonDataHelper.RetrieveStockList(); Console.Out.WriteLine("***Length of list: " + stockList.Count + "\n\n"); for (int i = 0; i < 5000; i++) { Console.Out.Write($"{i}: "); string symbol = stockList[i].Symbol; Company company = null; try { company = GUIDataHelper.GetCompanyBySymbol(symbol); } catch (Newtonsoft.Json.JsonSerializationException ex) { Console.Out.WriteLine("!!!!! Failed: " + ex.Message + $" <{symbol}> "); } catch (ArgumentException ex) { Console.Out.WriteLine("!!!!! Failed: " + ex.Message + $" <{symbol}> "); } catch (SystemException ex) { Console.Out.WriteLine("!!!!! Failed: " + ex.Message + $" <{symbol}> "); } if (company == null) { continue; } using (DbStockMonitor dbStockContext = new DbStockMonitor()) { dbStockContext.Companies.Add(company); try { dbStockContext.SaveChanges(); Console.Out.WriteLine($">>>>> {++counter}: Successfully insert record: <{company.Symbol}>"); } catch (SystemException ex) { Console.Out.WriteLine($"!!!!! Database save changes exception! <{company.Symbol}>, " + ex.Message); } } } }
// private static DbStockMonitor _dbContext = new DbStockMonitor(); public static void InsertCompanyToDb(string symbol) { Company company = GUIDataHelper.GetCompanyBySymbol(symbol); try { using (DbStockMonitor _dbContext = new DbStockMonitor()) { _dbContext.Companies.Add(company); _dbContext.SaveChanges(); Console.Out.WriteLine(company.ToString()); } } catch (Exception ex) { throw new SystemException($"InsertCompanyToDb exception: {symbol} > {ex.Message}"); } }