public IEnumerable <ISecurityStaticDataModel> GetAllExistingStaticData() { _logger.Information($"PersistenceManager: retrieving All existing security static data."); string commandString = "select ed.Name ExchangeName, sd.Name SecurityName, sd.Symbol, sd.ExchangeId from SecurityDetails sd\n" + "join ExchangeDetails ed on ed.Id = sd.ExchangeId"; var securityStaticDataCollection = new List <ISecurityStaticDataModel>(); using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper)) { lock (_lockObject) { using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader()) { while (dataReader.ReadNext()) { string exchangeName = dataReader.GetField <string>("ExchangeName"); var exchangeData = new ExchangeStaticDataModel(exchangeName); string securityName = dataReader.GetField <string>("SecurityName"); string symbol = dataReader.GetField <string>("Symbol"); var securityData = new SecurityStaticDataModel(exchangeData, symbol, securityName); securityStaticDataCollection.Add(securityData); } } } } return(securityStaticDataCollection); }
public DateTime GetLatestStockDateStamp(string symbol) { _logger.Information($"PersistenceManager: retrieving latest stock data timestamp for symbol: {symbol}"); int?securityId = GetSecurityIdBySymbol(symbol); if (!securityId.HasValue) { throw new ArgumentException($"Security not found for symbol: {symbol}"); } string commandString = "SELECT DateStamp FROM EndOfDayData WHERE SecurityId = @SecurityId ORDER BY DateStamp DESC LIMIT 1;"; using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper)) { commandWrapper.AddParameter("@SecurityId", securityId.Value); lock (_lockObject) { using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader()) { if (!dataReader.HasRows || !dataReader.ReadNext()) { return(new DateTime(1950, 01, 01)); } return(dataReader.GetField <DateTime>("DateStamp")); } } } }
public IEnumerable <IStockDataModel> GetSecurityData(IEnumerable <string> symbolCollection, DateTime startDateStamp, DateTime endDateStamp) { StringBuilder stringBuilder = new StringBuilder("WHERE sd.symbol IN ("); foreach (string symbol in symbolCollection) { stringBuilder.Append($"'{symbol}',"); } stringBuilder.Length--; stringBuilder.Append(")"); string symolFilter = stringBuilder.ToString(); string command = @"select sd.symbol, eod.Open, eod.High, eod.Low, eod.Close, eod.AdjustedClose, eod.Volume, eod.DayChange, eod.DayPercentageChange, eod.StandardChange, eod.StandardPercentageChange, eod.DateStamp from endofdaydata eod join securitydetails sd on sd.Id = eod.SecurityId " + symolFilter + Environment.NewLine + $"and eod.DateStamp >= '{startDateStamp.ToString("yyyy-MM-dd")}' and eod.DateStamp <= '{endDateStamp.ToString("yyyy-MM-dd")}'" + Environment.NewLine + "order by eod.DateStamp ASC;"; var stockDataCollection = new List <IStockDataModel>(); using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(command, _connectionWrapper)) { lock (_lockObject) { using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader()) { if (!dataReader.HasRows) { return(stockDataCollection); } while (dataReader.ReadNext()) { IStockDataModel stockData = _securityDataSqlFactory.GetStockData(dataReader); stockDataCollection.Add(stockData); } } } } return(stockDataCollection); }
private void ExecuteNoReadQuery(string commandString, int expectedAffectedRows, Dictionary <string, object> parameters) { lock (_lockObject) { using (IMySqlCommandWrapper command = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper)) { parameters.ToList().ForEach(x => command.AddParameter(x.Key, x.Value)); int affactedRows = command.ExecuteNonQuery(); if (expectedAffectedRows > -1 && affactedRows != expectedAffectedRows) { throw new InvalidOperationException($"Query execution failure, expected row count of 1, received {affactedRows}"); } } } }
public bool DoesSecurityStaticDataExist(ISecurityStaticDataModel securityStaticData) { _logger.Information($"PersistenceManager: Checking for the existence of security data: {securityStaticData.Symbol}"); string commandString = "SELECT * FROM SecurityDetails WHERE Symbol = @Symbol;"; using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper)) { commandWrapper.AddParameter("@Symbol", securityStaticData.Symbol); lock (_lockObject) { using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader()) { string existenceLog = dataReader.HasRows ? "exists" : "does not exist"; _logger.Information($"PersistenceManager: Security static data {securityStaticData.Symbol} {existenceLog}"); return(dataReader.HasRows); } } } }
private int?GetSecurityIdBySymbol(string symbol) { string commandString = "SELECT Id FROM SecurityDetails WHERE Symbol = @Symbol;"; using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper)) { commandWrapper.AddParameter("@Symbol", symbol); lock (_lockObject) { using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader()) { if (!dataReader.HasRows || !dataReader.ReadNext()) { return(null); } return(dataReader.GetField <int?>("Id")); } } } }
private int?GetExchangeIdByName(string name) { string commandString = "SELECT Id FROM ExchangeDetails WHERE Name = @ExchangeName;"; using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper)) { commandWrapper.AddParameter("@ExchangeName", name); lock (_lockObject) { using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader()) { if (!dataReader.HasRows || !dataReader.ReadNext()) { return(null); } return(dataReader.GetField <int?>("Id")); } } } }