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);
}
}
