public static List <Entities.PortfolioStock> GetAll(Entities.Portfolio p) { List <Entities.PortfolioStock> result = new List <Entities.PortfolioStock>(); string SelecetUserPortfolio = "SELECT * FROM PortfolioStock WHERE GameID=@GameID"; SqlCommand cmdGetPortfolioStock = new SqlCommand(SelecetUserPortfolio, Globals.Db.conn); cmdGetPortfolioStock.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID; using (SqlDataReader reader = cmdGetPortfolioStock.ExecuteReader()) { while (reader.Read()) { string symbol = (string)reader["Symbol"]; Console.Write(symbol); int id = Convert.ToInt32(reader["GameID"]); int numberOfSharesOwned = Convert.ToInt32(reader["NumberOfSharesOwned"]); Decimal averagePurchasePrice = Convert.ToDecimal(reader["averagePurchasePrice"]); Entities.PortfolioStock pn = new Entities.PortfolioStock(symbol, id, numberOfSharesOwned, averagePurchasePrice); result.Add(pn); } } return(result); }
public static List <Entities.Transaction> GetAll(Entities.Portfolio p) { List <Entities.Transaction> result = new List <Entities.Transaction>(); string sql = "SELECT * FROM Transactions WHERE PortfolioID=@PortfolioID"; SqlCommand cmdGetAllTransactions = new SqlCommand(sql, Globals.Db.conn); cmdGetAllTransactions.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID; using (SqlDataReader reader = cmdGetAllTransactions.ExecuteReader()) { while (reader.Read()) { int id = (int)reader["Id"]; int portfolioID = (int)reader["portfolioId"]; string type = (string)reader["Type"]; string symbol = (string)reader["Symbol"]; decimal price = (decimal)reader["BuySellPrice"]; int sharesBoughtSold = (int)reader["SharesBoughtSold"]; DateTime date = (DateTime)reader["date"]; Entities.Transaction t = new Entities.Transaction(id, portfolioID, type, symbol, price, sharesBoughtSold, date); result.Add(t); } } return(result); }
public static Entities.Portfolio GetUpdatedPortfolio(Entities.Portfolio p) { Entities.Portfolio updatedPortfolio = new Entities.Portfolio(1, "temp", "Temp", 0, 0, 0); String sql = "SELECT * FROM Portfolio WHERE PortfolioId=@PortfolioId"; SqlCommand cmdGetPortfolio = new SqlCommand(sql, Globals.Db.conn); cmdGetPortfolio.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID; using (SqlDataReader reader = cmdGetPortfolio.ExecuteReader()) { while (reader.Read()) { int id = (int)reader["portfolioId"]; string name = (string)reader["Name"]; string email = (string)reader["Email"]; decimal cash = (decimal)reader["Cash"]; decimal net = (decimal)reader["Net"]; decimal balance = (decimal)reader["Balance"]; updatedPortfolio = new Entities.Portfolio(id, name, email, cash, net, balance); } } return(updatedPortfolio); }
public static void AddNewPortfolioToTable(Entities.Portfolio p) { string sql = "INSERT INTO Portfolio (Name, Email) VALUES (@Name,@Email)"; SqlCommand cmd = new SqlCommand(sql, Globals.Db.conn); cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = p.Name; cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = p.Email; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); }
public static void UpdateNet(decimal net, Entities.Portfolio p) { string sql = "UPDATE Portfolio " + "SET Net=@Net " + "WHERE PortfolioId=@PortfolioId"; SqlCommand cmd = new SqlCommand(sql, Globals.Db.conn); cmd.Parameters.Add("@Net", SqlDbType.Money).Value = net; cmd.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioID; cmd.ExecuteNonQuery(); }
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(); }
public List <String> GetAllStockOwnedByUser(Entities.Portfolio p) { List <String> result = new List <String>(); String sqlGetAllStockOwnedByUser = "******"; SqlCommand cmdInsertStock = new SqlCommand(sqlGetAllStockOwnedByUser, conn); cmdInsertStock.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioID; using (SqlDataReader reader = cmdInsertStock.ExecuteReader()) { while (reader.Read()) { result.Add(reader.GetString(0)); } } return(result); }
public static List <Entities.Portfolio> GetAll() { List <Entities.Portfolio> result = new List <Entities.Portfolio>(); using (SqlCommand command = new SqlCommand("SELECT * FROM Portfolio", Globals.Db.conn)) using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { int id = (int)reader["portfolioId"]; string name = (string)reader["Name"]; string email = (string)reader["Email"]; decimal cash = (decimal)reader["Cash"]; decimal net = (decimal)reader["Net"]; decimal balance = (decimal)reader["Balance"]; Entities.Portfolio p = new Entities.Portfolio(id, name, email, cash, net, balance); result.Add(p); } } return(result); }
////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(); }
public void AddSellTransaction(String symbol, int qty, decimal sellPrice, Entities.PortfolioStock p, Entities.Portfolio up) { string sqlBuy = "INSERT INTO Transactions (PortfolioId, Type, Symbol, BuySellPrice, SharesBoughtSold, Date)" + "VALUES (@PortfolioId, @Type, @Symbol, @Bit, @SharesBought, @Date)"; SqlCommand cmd = new SqlCommand(sqlBuy, conn); cmd.Parameters.Add("@PortfolioId", SqlDbType.Int).Value = p.PortfolioId; cmd.Parameters.Add("@Type", SqlDbType.NChar).Value = "Sell"; cmd.Parameters.Add("@Symbol", SqlDbType.NChar).Value = symbol; cmd.Parameters.Add("@Bit", SqlDbType.Money).Value = sellPrice; cmd.Parameters.Add("@SharesBought", SqlDbType.Int).Value = qty; 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 = up.Cash + (sellPrice * qty); cmdUpdate.ExecuteNonQuery(); string sqlUpdatPortfolioStock = "Update PortfolioStock SET NumberOfSharesOwned=@NumberOfSharesOwned Where GameID=@GameID AND Symbol=@Symbol"; SqlCommand cmdUpdatePortfolioStock = new SqlCommand(sqlUpdatPortfolioStock, conn); cmdUpdatePortfolioStock.Parameters.Add("@GameID", SqlDbType.Int).Value = p.PortfolioId; cmdUpdatePortfolioStock.Parameters.Add("@Symbol", SqlDbType.NChar).Value = symbol; cmdUpdatePortfolioStock.Parameters.Add("@NumberOfSharesOwned", SqlDbType.Int).Value = p.SharesOwned - qty; cmdUpdatePortfolioStock.ExecuteNonQuery(); }