public double GetPrice(string ticker, DateTime date) { int year = date.Year; int mon = date.Month; int day = date.Day; object price = null; using (SqlDataReader rd = new SqlCommandReaderHelper(logger, connection, $@"SELECT price FROM Currencies JOIN Prices ON Currencies.id_cur = Prices.id_cur WHERE (Currencies.ticker = '{ticker}' AND Prices.daydate = DATEFROMPARTS({year}, {mon}, {day}))").TryExecute() as SqlDataReader) { if (rd != null && rd.Read()) { price = rd.GetValue(0); } else { throw new EntryNotFoundException($"entry for date ${date.Date} not found"); } } return(Convert.ToDouble(price)); }
public void UpdateDbForDate(DateTime date) { try { string dateTime = date.ToString("dd.MM.yyyy"); string url = $"http://www.cbr.ru/scripts/xml_daily.asp?date_req={dateTime}"; var model = ParseDocument(url); int year = date.Year; int mon = date.Month; int day = date.Day; foreach (var currency in model.Currencies) { new SqlCommandNonqueryHelper(logger, connection, $@"IF(EXISTS (SELECT * FROM Currencies WHERE ticker = '{currency.Ticker}')) BEGIN UPDATE Currencies SET name = '{currency.Fullname}', ticker = '{currency.Ticker}' WHERE ticker = '{currency.Ticker}' END ELSE BEGIN INSERT INTO Currencies (name, ticker) VALUES ('{currency.Fullname}', '{currency.Ticker}') END").TryExecute(); object id_cur = null; using (SqlDataReader rd = new SqlCommandReaderHelper(logger, connection, $"SELECT * FROM Currencies WHERE Ticker = '{currency.Ticker}'").TryExecute() as SqlDataReader) { if (rd != null && rd.Read()) { id_cur = rd.GetValue(0); string ticker = rd.GetValue(2).ToString(); if (!hashTickers.ContainsKey(ticker)) { hashTickers.Add(ticker, ticker); lbTickers.Items.Add(ticker); } } } bool exists = false; using (SqlDataReader rd = new SqlCommandReaderHelper(logger, connection, $"SELECT * FROM Currencies JOIN Prices ON Currencies.id_cur = Prices.id_cur").TryExecute() as SqlDataReader) { exists = rd != null && rd.HasRows && rd.Read(); } if (exists) { new SqlCommandNonqueryHelper(logger, connection, $@"IF(EXISTS (SELECT * FROM Prices WHERE id_cur = {id_cur} AND daydate = DATEFROMPARTS({year}, {mon}, {day}))) BEGIN UPDATE Prices SET price = '{currency.Price.ToString().Replace(',', '.')}', daydate = DATEFROMPARTS({year}, {mon}, {day}), id_cur = {id_cur} WHERE id_cur = {id_cur} AND daydate = DATEFROMPARTS({year}, {mon}, {day}) END ELSE BEGIN INSERT INTO Prices (price, daydate, id_cur) VALUES ({currency.Price.ToString().Replace(',', '.')}, DATEFROMPARTS({year}, {mon}, {day}), {id_cur}) END").TryExecute(); } else { new SqlCommandNonqueryHelper(logger, connection, $@"INSERT INTO Prices (price, daydate, id_cur) VALUES ({currency.Price.ToString().Replace(',', '.')}, DATEFROMPARTS({year}, {mon}, {day}), {id_cur})").TryExecute(); } } if (lbTickers.Items.Count > 0 && lbTickers.SelectedIndex < 0) { lbTickers.SelectedIndex = 0; } } catch { logger.Log("Ошибка при загрузке данных", true); } }