public void SaveUsage() { string updateScript = "update AlphaVantageKeys set DateLastUsed='<date>', DailyCallCount=<callCount> where ApiKey='<ApiKey>'" .Replace("<date>", DateTime.Today.ToString()) .Replace("<ApiKey>", this.Key) .Replace("<callCount>", (this.CallsPerDay - this.dailyCallsLeft).ToString()); SQLUtilities.ExecuteScript(updateScript); }
internal static List <AlphaVantageAgent.KeyAgent> LoadKeys(AlphaVantageAgent parent) { List <AlphaVantageAgent.KeyAgent> keyAgents; string keyQuery = @"select ApiKey, coalesce(DateLastUsed, '1900-01-01') as DateLastUsed, coalesce(DailyCallCount, 0) as DailyCallCount, DailyCallMax as DailyCallMax, coalesce(CallsPerMinute, 0) as CallsPerMinute from AlphaVantageKeys;"; List <KeyDto> dtos = SQLUtilities.FromQuery <KeyDto>(keyQuery); keyAgents = dtos.Select(d => new AlphaVantageAgent.KeyAgent(parent, d.DailyCallMax, d.CallsPerMinute, d.ApiKey, d.DateLastUsed, d.DailyCallCount)) .ToList(); return(keyAgents); }
public void Run(string key) { string fileName; using (var client = new WebClient()) { fileName = AlphaVantageConfigs.BuildFilename(this.OutputSize, this.ApiFunction, this.TargetSymbol); string url = AlphaVantageConfigs.BuildUrl(this.OutputSize, this.ApiFunction, this.TargetSymbol, key); Console.WriteLine("key agent {2} requesting file {0} from {1}", fileName, url, key); client.DownloadFile(url, fileName); } List <StockDataPoint> data = new List <StockDataPoint>(); try { using (TextFieldParser csvParser = new TextFieldParser(fileName)) { csvParser.SetDelimiters(new string[] { "," }); csvParser.ReadLine(); int rowsLoaded = 0; while (!csvParser.EndOfData) { string[] fields = csvParser.ReadFields(); data.Add(new StockDataPoint(fields)); rowsLoaded += 1; } Console.WriteLine("key agent {0} loaded {1} rows", key, rowsLoaded); } } catch (Exception ex) { Console.WriteLine(ex.Message); } DataTable table = SQLUtilities.ToDataTable <StockDataPoint>(data); using (SqlConnection connection = new SqlConnection(AlphaVantageConfigs.ConnectionString)) { connection.Open(); string tempTableName = "#StockData"; StockDataPoint.SqlCreateTempTable(connection, tempTableName); SqlBulkCopy sb = new SqlBulkCopy(connection); sb.DestinationTableName = tempTableName; sb.WriteToServer(table); string updateScript = ""; string insertScript = ""; if (this.ApiFunction == ApiFunction.Daily && this.OutputSize == OutputSize.Full) { updateScript = $@"update s set s.HistoricalLoadStatus = 1, s.MinDate = t.MinDate from StockSymbol s inner join (select Symbol, min(PriceDate) as MinDate from StockPriceDaily ss where ss.Symbol = '{this.TargetSymbol}' group by Symbol) t on s.Symbol = t.Symbol where s.Symbol = '{this.TargetSymbol}';"; } else if (this.ApiFunction == ApiFunction.ThirtyMin && this.OutputSize == OutputSize.Full) { updateScript = $@"update s set s.HalfHourlyFullStatus = 1, s.MinDateTime = t.MinDateTime from StockSymbol s inner join (select Symbol, min(PriceDate) as MinDateTime from StockPrice30Min ss where ss.Symbol = '{this.TargetSymbol}' group by Symbol) t on s.Symbol = t.Symbol where s.Symbol = '{this.TargetSymbol}';"; } insertScript = $@"insert into <Table> with (tablock) (Symbol, PriceDate, [Open], High, Low, [Close], Volume) select '{this.TargetSymbol}', [Open], [High], [Low], [Close], Volume from #StockData t where not exists (select 1 from StockPrice s where s.Symbol = '{this.TargetSymbol}' and cast([TimeStamp] as date) = s.PriceDate and cast([TimeStamp] as time(0)) = s.PriceTime);" .Replace("<Table>", this.ApiFunction == ApiFunction.Daily ? "StockPriceDaily" : "StockPrice30Min"); string script = $@" begin try begin transaction; {insertScript} {updateScript} commit transaction; end try begin catch --do some stuff or whatever... end catch "; SqlCommand cmd = new SqlCommand(insertScript, connection); cmd.ExecuteNonQuery(); connection.Close(); } Console.WriteLine("key agent {0} saved {1} to database", key, this.Descriptor); }
private void pollDatabase() { Console.WriteLine("Agent poll database start"); string symbolQuery = @"select s.Symbol,CompanyName,Industry,HistoricalLoadStatus,HourlyFullStatus,MaxTimestamp from StockSymbol s left join (select Symbol, cast(max(PriceDate) as datetime) + cast(max(PriceTime) as datetime) as MaxTimestamp from StockPrice sp group by Symbol) sp on s.Symbol = sp.Symbol where (HistoricalLoadStatus = 0 or HourlyFullStatus = 0 or exists (select 1 from Util_DateTimes dt where MaxTimestamp < dt.DateTimePoint and dt.DateTimePoint < getdate() ) ) and IsActive = 1 order by s.LoadPriority, s.Symbol;"; List <StockSymbol> symbols = SQLUtilities.FromQuery <StockSymbol>(symbolQuery); foreach (StockSymbol s in symbols) { if (!s.HistoricalLoadStatus) { lock (this.tasks) { ILoadTask newTask = new LoadSymbolPriceData(stockSymbol: s.Symbol, outputSize: OutputSize.Full, apiFunction: ApiFunction.Daily); this.tasks.Add(newTask); } } if (!s.HourlyFullStatus) { lock (this.tasks) { ILoadTask newTask = new LoadSymbolPriceData(stockSymbol: s.Symbol, outputSize: OutputSize.Full, apiFunction: ApiFunction.ThirtyMin); this.tasks.Add(newTask); } } //if ((s.UpdateTimestamp - DateTime.Now).TotalMinutes > 30) //{ // lock (this.tasks) // { // ILoadTask newTask = new LoadSymbolPriceData(stockSymbol: s.Symbol, // outputSize: OutputSize.Compact, // apiFunction: ApiFunction.ThirtyMin); // this.tasks.Add(new LoadSymbolPriceData(stockSymbol: s.Symbol, // outputSize: OutputSize.Compact, // apiFunction: ApiFunction.ThirtyMin)); // } //} } }