public List <Entities.StockDb> GetAllStockPricesFromDatabase()
{
List <Entities.StockDb> result = new List <Entities.StockDb>();
using (SqlCommand command = new SqlCommand("SELECT * FROM StockQuotesTable", conn))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = (int)reader["StockId"];
string symbol = (string)reader["Symbol"];
string name = (string)reader["Name"];
decimal? bid = (decimal)reader["Bid"];
decimal? ask = (decimal)reader["Ask"];
decimal? open = (decimal)reader["Open"];
decimal? previousClose = (decimal)reader["PreviousClose"];
decimal? lastTrade = (decimal)reader["LastTrade"];
decimal? high = (decimal)reader["High"];
decimal? low = (decimal)reader["Low"];
int volume = (int)reader["Volume"];
decimal? high52 = (decimal)reader["High52"];
decimal? low52 = (decimal)reader["Low52"];
Entities.StockDb s = new Entities.StockDb(id, symbol, name, bid, ask, open, previousClose, lastTrade, high, low, volume, high52, low52);
result.Add(s);
}
}
return(result);
}
public void AddPortfolioStock(Entities.Portfolio p, Entities.StockDb s, int quantity)
{
string sqlAddToPortfolioStock = "INSERT INTO PortfolioStock (Symbol, GameID, NumberOfSharesOwned, AveragePurchasePrice)" +
"Values (@Symbol, @GameID, @NumberOfSharesOwned, @AveragePurchasePrice)";
SqlCommand cmdInsertStock = new SqlCommand(sqlAddToPortfolioStock, conn);
cmdInsertStock.Parameters.Add("@Symbol", SqlDbType.NChar).Value = s.Symbol;
cmdInsertStock.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID;
cmdInsertStock.Parameters.Add("@NumberOfSharesOwned", SqlDbType.Int).Value = quantity;
cmdInsertStock.Parameters.Add("@AveragePurchasePrice", SqlDbType.Money).Value = s.Ask;
cmdInsertStock.ExecuteNonQuery();
}
////Adding a transaction buy
public void AddBuyTransaction(Entities.Portfolio p, Entities.StockDb s, int quantity)
{
string sqlBuy = "INSERT INTO Transactions (PortfolioId, Type, Symbol, BuySellPrice, SharesBoughtSold, Date)"
+ "VALUES (@PortfolioId, @Type, @Symbol, @Ask, @SharesBought, @Date)";
SqlCommand cmd = new SqlCommand(sqlBuy, conn);
cmd.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID;
cmd.Parameters.Add("@Type", SqlDbType.NChar).Value = "Buy";
cmd.Parameters.Add("@Symbol", SqlDbType.NChar).Value = s.Symbol;
cmd.Parameters.Add("@Ask", SqlDbType.Money).Value = s.Ask;
cmd.Parameters.Add("@SharesBought", SqlDbType.Int).Value = quantity;
cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now;
cmd.ExecuteNonQuery();
string sqlUpdateCash = "Update Portfolio SET Cash=@Cash Where PortfolioId=@PortfolioId";
SqlCommand cmdUpdate = new SqlCommand(sqlUpdateCash, conn);
cmdUpdate.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID;
cmdUpdate.Parameters.Add("@Cash", SqlDbType.Money).Value = p.Cash - (s.Ask * quantity);
cmdUpdate.ExecuteNonQuery();
}
public void UpdatePortfolioStock(Entities.Portfolio p, Entities.StockDb s, int quantity)
{
int finalQty = 0;
decimal newAverage = 0;
string sqlGetVolumePrice = "SELECT NumberOfSharesOwned, AveragePurchasePrice FROM PortfolioStock WHERE Symbol=@Symbol AND GameID=@GameID";
SqlCommand cmdGetVolumePrice = new SqlCommand(sqlGetVolumePrice, conn);
cmdGetVolumePrice.Parameters.Add("@Symbol", SqlDbType.NVarChar).Value = s.Symbol;
cmdGetVolumePrice.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID;
int quantityDB;
decimal priceDB;
// this part is needed to convert decimal? to decimal
decimal askPrice = (decimal)s.Ask;
using (SqlDataReader rd = cmdGetVolumePrice.ExecuteReader())
{
while (rd.Read())
{
//rd.NextResult();
//var quantityDB = (int)rd["NumberOfSharesOwned"];
quantityDB = Convert.ToInt32(rd["NumberOfSharesOwned"]);
priceDB = (decimal)rd["AveragePurchasePrice"];
decimal DatabaseTotalPrice = priceDB * quantityDB;
decimal NewTotalPrice = quantity * askPrice;
finalQty = quantityDB + quantity;
newAverage = (DatabaseTotalPrice + NewTotalPrice) / finalQty;
finalQty = quantityDB + quantity;
newAverage = (DatabaseTotalPrice + NewTotalPrice) / finalQty;
}
}
string sqlAddToPortfolioStock = "UPDATE PortfolioStock SET NumberOfSHaresOWned=@NumberOfSHaresOWned, AveragePurchasePrice=@AveragePurchasePrice " +
"WHERE Symbol=@Symbol AND GameID=@GameID";
SqlCommand cmdUpdate = new SqlCommand(sqlAddToPortfolioStock, conn);
cmdUpdate.Parameters.Add("@Symbol", SqlDbType.NChar).Value = s.Symbol;
cmdUpdate.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID;
cmdUpdate.Parameters.Add("@NumberOfSharesOwned", SqlDbType.Int).Value = finalQty;
cmdUpdate.Parameters.Add("@AveragePurchasePrice", SqlDbType.Money).Value = newAverage;
cmdUpdate.ExecuteNonQuery();
Console.Write("END");
cmdUpdate.ExecuteNonQuery();
}
