public void AddHistoryEntry(StockHistoryModel historyModel)
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"INSERT INTO stock_history
(stock_symbol, trading_day, open_price, daily_high, daily_low, close_price, volume)
VALUES (@stockSymbol, @tradingDay, @openPrice, @dailyHigh, @dailyLow, @closePrice, @volume);", conn);
cmd.Parameters.AddWithValue("@stockSymbol", historyModel.StockSymbol);
cmd.Parameters.AddWithValue("@tradingDay", historyModel.TradingDay);
cmd.Parameters.AddWithValue("@openPrice", historyModel.OpenPrice);
cmd.Parameters.AddWithValue("@dailyHigh", historyModel.DailyHigh);
cmd.Parameters.AddWithValue("@dailyLow", historyModel.DailyLow);
cmd.Parameters.AddWithValue("@closePrice", historyModel.ClosePrice);
cmd.Parameters.AddWithValue("@volume", historyModel.Volume);
cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw ex;
}
}
private StockHistoryModel MapRowToStockHistory(SqlDataReader reader)
{
StockHistoryModel stock = new StockHistoryModel();
stock.StockSymbol = Convert.ToString(reader["stock_symbol"]);
stock.TradingDay = Convert.ToDateTime(reader["trading_day"]);
stock.OpenPrice = Convert.ToDouble(reader["open_price"]);
stock.DailyHigh = Convert.ToDouble(reader["daily_high"]);
stock.ClosePrice = Convert.ToDouble(reader["close_price"]);
stock.DailyLow = Convert.ToDouble(reader["daily_low"]);
stock.Volume = Convert.ToInt32(reader["volume"]);
return(stock);
}
/// <summary>
/// Gets just the past days stock history
/// </summary>
public IList <StockHistoryModel> GetLastCloseStockHistory()
{
// Create a new array to store randomized apiKeys and copy the apiKeys array into the new array
string[] randomApiKeys = new string[apiKeys.Length];
Array.ConstrainedCopy(apiKeys, 0, randomApiKeys, 0, apiKeys.Length);
// Randomize the order of the api keys so we hopefully never max any of them out
Random rng = new Random();
int n = randomApiKeys.Length;
while (n > 1)
{
int k = rng.Next(n--);
string temp = randomApiKeys[n];
randomApiKeys[n] = randomApiKeys[k];
randomApiKeys[k] = temp;
}
// Get a cached list of dates reflecting the next missing entry in the stock history table to be used for the api query
IDictionary <string, string> nextDate = stockDao.GetNextMissingHistoryEntryAllStocksFormatted();
// List of stock history models to store the results from the external API query
List <StockHistoryApiModel> historyResults = new List <StockHistoryApiModel>();
// Initialize counters and a new WebClient object to query the API
int currentStockIndex = 0;
int currentApiKeyIndex = 0;
WebClient client = new WebClient();
// Loop over the randomized apiKeys array and store our position in the counters so if a request fails we can pick back up where it failed
for (; currentApiKeyIndex < randomApiKeys.Length; currentApiKeyIndex++)
{
try
{
for (; currentStockIndex < stocks.Length; currentStockIndex++)
{
if (nextDate.ContainsKey(stocks[currentStockIndex]))
{
string apiRequest = $"https://marketdata.websol.barchart.com/getHistory.json?apikey={randomApiKeys[currentApiKeyIndex]}&type=daily&symbol={stocks[currentStockIndex]}&startDate={nextDate[stocks[currentStockIndex]]}";
string response = client.DownloadString(apiRequest);
historyResults.Add(JsonConvert.DeserializeObject <StockHistoryApiModel>(response));
}
}
break;
}
catch
{
continue;
}
}
List <StockHistoryModel> models = new List <StockHistoryModel>();
foreach (StockHistoryApiModel historyResult in historyResults)
{
Result[] results = null;
if (historyResult.results != null && historyResult.results.Length != 0)
{
results = historyResult.results;
}
else
{
results = new Result[1];
results[0] = null;
}
//Add the stock details required for stock to our stock model
foreach (Result result in results)
{
if (result != null)
{
StockHistoryModel newStock = new StockHistoryModel();
newStock.StockSymbol = result.symbol;
newStock.TradingDay = Convert.ToDateTime(result.tradingDay);
newStock.OpenPrice = result.open;
newStock.DailyHigh = result.high;
newStock.DailyLow = result.low;
newStock.ClosePrice = result.close;
newStock.Volume = result.volume;
models.Add(newStock);
}
}
}
return(models);
}
