public void DeleteAllPicks(string symbol)
{
using (StockDataEntities context = new StockDataEntities())
{
context.Database.ExecuteSqlCommand("Delete from StockPick Where Symbol= {0}", symbol);
}
}
public List<StockCountry> GetCountryList()
{
using (StockDataEntities context = new StockDataEntities())
{
return context.StockCountries.OrderBy(c=>c.Code).ToList();
}
}
public void UpdateSymbols(List<StockSymbol> symbols)
{
using (StockDataEntities context = new StockDataEntities())
{
foreach (StockSymbol symbol in symbols)
{
StockSymbol exsitingSymbol = context.StockSymbols
.Where(s => String.Compare(s.Symbol, symbol.Symbol, true) == 0).SingleOrDefault();
if (exsitingSymbol != null)
{
exsitingSymbol.StockName = symbol.StockName;
exsitingSymbol.Sector = string.IsNullOrEmpty(symbol.Sector) ? exsitingSymbol.Sector : symbol.Sector;
exsitingSymbol.ETF = symbol.ETF.HasValue ? symbol.ETF : exsitingSymbol.ETF;
exsitingSymbol.Country = string.IsNullOrEmpty(symbol.Country) ? exsitingSymbol.Country : symbol.Country;
exsitingSymbol.HasFuture = symbol.HasFuture.HasValue ? symbol.HasFuture : exsitingSymbol.HasFuture;
exsitingSymbol.StartDate = symbol.StartDate.HasValue ? symbol.StartDate : exsitingSymbol.StartDate;
exsitingSymbol.EndDate = symbol.EndDate.HasValue ? symbol.EndDate : exsitingSymbol.EndDate;
}
else
{
context.StockSymbols.Add(symbol);
}
}
context.SaveChanges();
}
}
public void RemoveComponentfromIndex(string index, StockSymbol symbol)
{
using (StockDataEntities context = new StockDataEntities())
{
StockIndex stockIndex = context.StockIndexes
.Where(i => string.Compare(i.IndexName, index, true) == 0).FirstOrDefault();
if (stockIndex != null)
{
var existingSymbol = context.StockSymbols
.Where(s => string.Compare(s.Symbol, symbol.Symbol, true) == 0).SingleOrDefault();
if (existingSymbol != null)
{
stockIndex.StockSymbols.Remove(existingSymbol);
}
else
{
throw new ApplicationException(string.Format("Cannot find symbol: {0}", symbol.Symbol));
}
context.SaveChanges();
}
}
}
public List<StockQuote> GetQuotes(string symbol, short timeFrame, DateTime startDate, DateTime endDate)
{
using (StockDataEntities context = new StockDataEntities())
{
return context.StockQuotes.Where(q => (string.Compare(q.Symbol, symbol, true) == 0
&& q.TimeFrame == timeFrame && q.QuoteDate>=startDate && q.QuoteDate<=endDate))
.OrderBy(q => q.QuoteDate).ToList();
}
}
public StockSymbol GetSymbol(string symbol)
{
using (StockDataEntities context = new StockDataEntities())
{
StockSymbol stockSymbol = context.StockSymbols
.Where(s => string.Compare(s.Symbol, symbol, true) == 0).SingleOrDefault();
return stockSymbol;
}
}
public List<StockPick> GetAllPicks(string symbol)
{
using (StockDataEntities context = new StockDataEntities())
{
List<StockPick> picks = context.StockPicks
.Where(s => (string.Compare(s.Symbol, symbol, true) == 0))
.OrderBy(p => p.PickDate).ToList();
return picks;
}
}
public List<StockIndex> GetAllIndexes()
{
List<StockIndex> indexList = new List<StockIndex>();
using (StockDataEntities context = new StockDataEntities())
{
indexList = context.StockIndexes.OrderBy(i => i.IndexName).ToList();
}
return indexList;
}
public void DeleteCountry(StockCountry country)
{
using (StockDataEntities context = new StockDataEntities())
{
StockCountry sCountry = context.StockCountries
.Where(c => (string.Compare(c.Code, country.Code, true) == 0)).SingleOrDefault();
if (sCountry != null)
context.StockCountries.Remove(sCountry);
context.SaveChanges();
}
}
public void UpdateCountry(StockCountry country)
{
using (StockDataEntities context = new StockDataEntities())
{
StockCountry sCountry = context.StockCountries
.Where(c => (string.Compare(c.Code, country.Code, true) == 0)).SingleOrDefault();
if (sCountry != null)
sCountry.FullName = country.FullName;
else
context.StockCountries.Add(country);
context.SaveChanges();
}
}
public List<StockSymbol> GetIndexComponents(string indexName)
{
List<StockSymbol> symbols = new List<StockSymbol>();
using (StockDataEntities context = new StockDataEntities())
{
StockIndex index = context.StockIndexes
.Where(i => string.Compare(i.IndexName, indexName, true) == 0).SingleOrDefault();
if (index != null)
symbols = index.StockSymbols.ToList();
}
return symbols;
}
public void DeleteIndex(StockIndex stockIndex)
{
using (StockDataEntities context = new StockDataEntities())
{
StockIndex existingIndex = context.StockIndexes
.Where(i => String.Compare(i.IndexName, stockIndex.IndexName, true) == 0).SingleOrDefault();
if (existingIndex != null)
{
context.StockIndexes.Remove(existingIndex);
context.SaveChanges();
}
}
}
public void UpdateStockIndex(StockIndex stockIndex)
{
using (StockDataEntities context = new StockDataEntities())
{
StockIndex existingIndex = context.StockIndexes
.Where(i => String.Compare(i.IndexName, stockIndex.IndexName, true) == 0).SingleOrDefault();
if (existingIndex != null)
{
existingIndex.Description = stockIndex.Description;
}
else
{
context.StockIndexes.Add(stockIndex);
}
context.SaveChanges();
}
}