/// <summary> /// Deprecated Method for adding a new object to the ExchangeRates EntitySet. Consider using the .Add method of the associated ObjectSet<T> property instead. /// </summary> public void AddToExchangeRates(ExchangeRate exchangeRate) { base.AddObject("ExchangeRates", exchangeRate); }
/// <summary> /// Create a new ExchangeRate object. /// </summary> /// <param name="date">Initial value of the Date property.</param> /// <param name="baseCurrencyCode">Initial value of the BaseCurrencyCode property.</param> /// <param name="variableCurrencyCode">Initial value of the VariableCurrencyCode property.</param> /// <param name="bidPrice">Initial value of the BidPrice property.</param> /// <param name="askPrice">Initial value of the AskPrice property.</param> public static ExchangeRate CreateExchangeRate(global::System.DateTime date, global::System.String baseCurrencyCode, global::System.String variableCurrencyCode, global::System.Decimal bidPrice, global::System.Decimal askPrice) { ExchangeRate exchangeRate = new ExchangeRate(); exchangeRate.Date = date; exchangeRate.BaseCurrencyCode = baseCurrencyCode; exchangeRate.VariableCurrencyCode = variableCurrencyCode; exchangeRate.BidPrice = bidPrice; exchangeRate.AskPrice = askPrice; return exchangeRate; }
public static void UploadExchangeBidAskRateExcel(string filePath) { FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read); //1. Reading from a binary Excel file ('97-2003 format; *.xls) //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream); //... //2. Reading from a OpenXml Excel file (2007 format; *.xlsx) using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream)) { //... //3. DataSet - The result of each spreadsheet will be created in the result.Tables // DataSet result = excelReader.AsDataSet(); //... //4. DataSet - Create column names from first row const int TOTAL_NUM_CURRENCY = 9; excelReader.IsFirstRowAsColumnNames = false; DataSet result = excelReader.AsDataSet(); DataTable dt = result.Tables[0]; DateTime datetime; string baseCurrency; string variableCurrency; int targetFirstColumn; List<ExchangeRate> exchangeRateList = new List<ExchangeRate>(); for(int i = 0; i < TOTAL_NUM_CURRENCY; i++){ targetFirstColumn = i * 4; string currencyPair = dt.Rows[0][targetFirstColumn].ToString().Split(' ')[0]; baseCurrency = currencyPair.Substring(0, 3); variableCurrency = currencyPair.Substring(3, 3); foreach (DataRow dr in dt.Rows) { if (dt.Rows.IndexOf(dr) == 0 || dt.Rows.IndexOf(dr) == 1) continue; datetime = FromExcelSerialDate(Convert.ToInt32(dr[targetFirstColumn])); exchangeRateList.Add(new FXEntities.ExchangeRate() { BaseCurrencyCode = baseCurrency, VariableCurrencyCode = variableCurrency, Date = datetime, BidPrice = Convert.ToDecimal(Convert.ToDouble(dr[targetFirstColumn + 1])), AskPrice = Convert.ToDecimal(Convert.ToDouble(dr[targetFirstColumn + 2])) }); } } DateTime minDate = exchangeRateList.Select(e => e.Date).Min(); DateTime maxDate = exchangeRateList.Select(e => e.Date).Max(); var availableDates = Util.DateTimeHelper.GetWeekdaysDate( minDate, maxDate ); foreach (var currency in exchangeRateList.Select(e => e.VariableCurrencyCode).Distinct().Where(c => c != "EUR").ToList()) { var insertedDates = exchangeRateList .Where(e => e.VariableCurrencyCode == currency).Select(e => e.Date); var leftDays = availableDates.Except(insertedDates); foreach (var leftDay in leftDays) { if (leftDay == minDate) continue; var lastRecordBeforeLeftDay = exchangeRateList .Where(e => e.VariableCurrencyCode == currency && e.Date == insertedDates.Where(d => d < leftDay).Max()).FirstOrDefault(); if (lastRecordBeforeLeftDay == null) throw new Exception("Cannot find record to replace"); else { ExchangeRate newExchangeRate = new ExchangeRate() { BaseCurrencyCode = lastRecordBeforeLeftDay.BaseCurrencyCode, VariableCurrencyCode = lastRecordBeforeLeftDay.VariableCurrencyCode, BidPrice = lastRecordBeforeLeftDay.BidPrice, AskPrice = lastRecordBeforeLeftDay.AskPrice, Date = leftDay }; exchangeRateList.Add(newExchangeRate); } } } using (FXEntities.FXEntities fxEntities = new FXEntities.FXEntities()) { exchangeRateList.ForEach(e => fxEntities.AddToExchangeRates(e)); fxEntities.SaveChanges(); } } stream.Close(); }