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);
}
}
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 SecondInsertAllDailyQuote()
{
using (DbStockMonitor dbctx = new DbStockMonitor())
{
List <string> symbolList = dbctx.Companies.Select(p => p.Symbol).ToList();
const int threadsNum = 5;
int avgListLength = symbolList.Count / threadsNum;
int lengthCounter = 0, curStart = 0;
for (int i = 0; i < threadsNum; i++)
{
List <string> subList;
if (i != threadsNum - 1)
{
subList = symbolList.GetRange(lengthCounter, avgListLength);
curStart = lengthCounter;
lengthCounter = lengthCounter + avgListLength + 1;
}
else
{
subList = symbolList.GetRange(lengthCounter, symbolList.Count - lengthCounter);
}
try
{
Thread t = new Thread(() => GetSubListDailyQuotes(subList, i));
t.Start();
}
catch (ArgumentNullException ex)
{
Console.Out.WriteLine("Thread start failure: " + ex.Message);
}
}
}
}
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}");
}
}
}
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);
}
}
}
}
public static TradingRecord GetTradingRecordFromDb(int recordId)
{
try
{
using (DbStockMonitor _dbContext = new DbStockMonitor())
{
TradingRecord record =
_dbContext.TradingRecords.AsNoTracking().FirstOrDefault(r => r.Id == recordId);
return(record);
}
}
catch (SystemException ex)
{
throw new SystemException($"GetTradingRecordFromDb exception, id: {recordId} > {ex.Message}");
}
}
public static List <QuoteDaily> GetQuoteDailyListFromDb(string symbol)
{
try
{
using (DbStockMonitor _dbContext = new DbStockMonitor())
{
List <QuoteDaily> result = _dbContext.QuoteDailies.AsNoTracking().Where(p => p.Symbol == symbol)
.ToList();
return(result);
}
}
catch (SystemException ex)
{
throw new SystemException($"GetQuoteDailyListFromDb exception: {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}");
}
}
public static Company GetCompanyFromDb(string symbol)
{
try
{
using (DbStockMonitor _dbContext = new DbStockMonitor())
{
Stopwatch sw = Stopwatch.StartNew();
Company company =
_dbContext.Companies.AsNoTracking().FirstOrDefault(p => p.Symbol == symbol) as Company;
sw.Stop();
TimeSpan span = sw.Elapsed;
Console.Out.WriteLine($"Get company {symbol} from db: {span.TotalMilliseconds} mills");
return(company);
}
}
catch (SystemException ex)
{
throw new SystemException($"GetCompanyFromDb exception: {symbol} > {ex.Message}");
}
}
public static List <Company> SearchCompanyListBySymbol(string searchString)
{
try
{
using (DbStockMonitor _dbContext = new DbStockMonitor())
{
string[] splitStrings;
string titleString, contentString, limitString = "10";
if (searchString.Trim().StartsWith("@"))
{
splitStrings = Regex.Split(searchString, @"[\:\;]");
titleString = splitStrings[0].Substring(1, splitStrings[0].Length - 1);
contentString = splitStrings[1];
if (splitStrings.Length == 4)
{
limitString = splitStrings[2];
}
}
else
{
titleString = "Symbol";
contentString = searchString;
}
string searchCondition = "c=>" + GetSearchConditionString(titleString, contentString);
var searchItems = _dbContext.Companies.AsNoTracking().Where(searchCondition).OrderBy(c => c.Symbol).Take(int.Parse(limitString)).ToList();
if (searchItems.Count != 0)
{
return(searchItems.Select(item => item as Company).ToList());
}
}
}
catch (SystemException ex)
{
throw new SystemException($"Get company list by search failed: {searchString} " + ex.Message);
}
return(null);
}
public static void FilterSybomlNoQuoteData()
{
int counter = 0;
string filepath =
"C:\\Users\\WW\\Desktop\\SourceTree\\IPD20-DotNetProject\\StockMonitor\\InvalidSymbols.txt";
using (DbStockMonitor context = new DbStockMonitor())
{
List <string> symbolList = context.Companies.AsNoTracking().Select(p => p.Symbol).ToList();
for (int i = 0; i < symbolList.Count; i++)
{
string symbol = symbolList[i];
string response = RetrieveJsonDataHelper.GetQuoteStringBySymbol(symbol).Result;
if (response.Length < 10)
{
Console.Out.WriteLine($"<{i}>: find {++counter} - {symbol} single quote has NO data: <{response}>");
File.AppendAllText(filepath, $"{symbol}\n");
}
}
}
}
public static List <Company> GetWatchListCompaniesFromDb(int userId)
{
try
{
using (DbStockMonitor _dbContext = new DbStockMonitor())
{
Stopwatch sw = Stopwatch.StartNew();
List <int> companyIds = GetWatchCompanyIdsFromDb(userId);
List <Company> result = _dbContext.WatchListItems.Include("Company").AsNoTracking()
.Where(u => u.UserId == userId).Select(p => p.Company).ToList();
sw.Stop();
Console.Out.WriteLine(
$"\n----- Time to match all watch item with all companies for user: {userId} is {sw.Elapsed.TotalMilliseconds} mills");
return(result);
}
}
catch (SystemException ex)
{
throw new SystemException($"GetWatchListSymbolsFromDb exception, id: {userId} > {ex.Message}");
}
}
private static List <int> GetWatchCompanyIdsFromDb(int userId)
{
try
{
using (DbStockMonitor _dbContext = new DbStockMonitor())
{
Stopwatch sw = Stopwatch.StartNew();
List <int> result = _dbContext.WatchListItems.AsNoTracking().Where(i => i.UserId == userId)
.Select(i => i.CompanyId)
.ToList();
sw.Stop();
Console.Out.WriteLine(
$"\n>>> Time to get watchlistitems for user: {userId} is {sw.Elapsed.TotalMilliseconds} mills");
//result.ForEach(p=>Console.WriteLine(p.ToString()));
return(result);
}
}
catch (SystemException ex)
{
throw new SystemException($"GetWatListIdsFromDb exception, id: {userId} > {ex.Message}");
}
}
