/// <summary> /// Gets all needed add STK code. /// </summary> /// <returns></returns> private async Task <List <string> > GetAllNeededAddStkCode() { using (var se = new StockEntities()) { // 取得category = 1所有項目的列表 var stkCodeListInCat1 = se.ListedStock.Where(l => l.StkCategory == 1).Select(l => l.StkCode).ToList(); // 取得股票價格所有項目的列表 var stockPriceList = se.StockHistoricalPrice.Select(p => p.StkCode).Distinct().ToList(); var exceptList = stkCodeListInCat1.Except(stockPriceList).ToList(); // 取得stkCodeListInCat1除了stockPriceList的差集, 結果為沒有任何價格紀錄需要被新增的集合 this.totalStkAmount = exceptList.Count(); return(exceptList); } }
public List <ListedStock> ReadStkCodeAndNameWithCat(int Category) { //取得category X 股票代碼分類 StockData.StkCategory //Stock = 1 //ListedWarrant = 2 //TaiwanDepositaryReceipt = 3 //RealEstateInvestmentTrust = 4 //ManageStocks = 5 //AssetBasedSecurities = 6 //ETF = 7 //SpecialStock = 8 using (StockEntities se = new StockEntities()) { var stkCodeAndNameList = (from stkCodeName in se.ListedStock where stkCodeName.StkCategory == Category select stkCodeName).ToList(); return(stkCodeAndNameList); } }
public List <StockHistoricalPrice> ReadHistoricalPrice(string stkId) { var startDate = StockTypeSettings.StartDate; var endDate = StockTypeSettings.EndDate; if (startDate == DateTime.Parse("0001/1/1")) { startDate = DateTime.Now.AddYears(-1); } if (endDate == DateTime.Parse("0001/1/1")) { endDate = DateTime.Now; } using (StockEntities se = new StockEntities()) { //取得listbox被選中的股票的歷史價格 var oneOfStkHistoryPrice = (from oneOfStkPrice in se.StockHistoricalPrice where oneOfStkPrice.StkCode == stkId && oneOfStkPrice.Date >= startDate && oneOfStkPrice.Date <= endDate select oneOfStkPrice).ToList(); return(oneOfStkHistoryPrice); } }
public async Task UpdatePriceToDB(IProgress <RptStructure> progress) { // using (var se = new StockEntities()) // { // // 刪除所有價格資料 // se.StockHistoricalPrice.Where(s => true).DeleteFromQuery(); // se.BulkSaveChanges(operation => operation.BatchTimeout = 200); // } var neededUpdateList = await this.GetAllNeededAddStkCode(); // 取得stkcode差集列表 var allStkPrice = await this.GetAllStkPrice(neededUpdateList, progress); // 取得所有需要新增的股票價格列表 using (var se = new StockEntities()) { // 新增所有缺少記錄的價格資料 var index = 0; RptStructure RptProgress; foreach (var priceOfList in allStkPrice) { RptProgress = new RptStructure(); await se.BulkInsertAsync(priceOfList); await se.BulkSaveChangesAsync(); RptProgress.WrittingDBProgress = (float)++index / allStkPrice.Count * 100; progress.Report(RptProgress); } RptProgress = new RptStructure() { CompletedMsg = "更新歷史價格完成!" }; progress.Report(RptProgress); } }
/// <summary>Writes to database.</summary> public void WriteToDb() { using (var se = new StockEntities()) { var deleteData = from ls in se.ListedStock select ls; bulkRemoveStopwatchTime.Start(); se.BulkDelete(deleteData); bulkRemoveStopwatchTime.Stop(); dataTransformStopwatchTime.Start(); var stockList = new List <ListedStock>(); // 將自訂類別轉換成ListedStock類別 foreach (var sd in this.stockDataCollection) { var stock = new ListedStock { StkCategory = sd.IntStkCategory, StkCode = sd.StrStkCode, StkName = sd.StrStkName, ISIN_code = sd.StrISIN_code, SubmitDate = sd.StrSubmitDate, MarketNo = sd.StrMarketNo, BelongClass = sd.StrBelongClass, CFI_code = sd.StrCFI_code }; stockList.Add(stock); } // 如果找不到重複的主鍵,便加入到儲存列表中 var allStkInDb = (from stk in se.ListedStock orderby stk.StkCategory, stk.StkCode select stk).ToList(); var neededWritingStockList = new List <ListedStock>(); // 剔除已經在資料庫內的StkCode foreach (var incomeStk in stockList) { var match = false; foreach (var dbStk in allStkInDb) { if (incomeStk.StkCode == dbStk.StkCode) { match = true; } } if (!match) { neededWritingStockList.Add(incomeStk); } } dataTransformStopwatchTime.Stop(); bulkAddStopwatchTime.Start(); se.BulkInsert(neededWritingStockList); se.BulkSaveChanges(); bulkAddStopwatchTime.Stop(); } ; }