public int UpdateRewardsPoints(Customer value) { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = @"UPDATE Customer SET `Rewards Points` = @RewardsPoints WHERE `Customer Email` = @CustomerEmail"; MySqlCommand cmd = new MySqlCommand(stm, con); cmd.Parameters.AddWithValue("@CustomerEmail", value.Email); cmd.Parameters.AddWithValue("@RewardsPoints", value.RewardsPoints); cmd.Prepare(); cmd.ExecuteNonQuery(); db.CloseConnection(); return(value.RewardsPoints); } //-1 is returned if something goes wrong; -1 is a recognizable sentinel value across the system return(-1); }
public void DeleteItem(int id) { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); //defining the string string stm = @"DELETE FROM Item WHERE `Item ID` = @id"; //making new command MySqlCommand cmd = new MySqlCommand(stm, con); //preparing the command and executing it; this deletes the record with the right id cmd.Parameters.AddWithValue("@id", id); cmd.Prepare(); cmd.ExecuteNonQuery(); db.CloseConnection(); } }
public void SaveEmployee(Employee value) { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = @"INSERT INTO Employee(`Employee First Name`, `Employee Last Name`, `Employee Address`, `Employee SSN`, `Employee Birth Date`, `Username`, `Password`) VALUES(@EmployeeFirstName, @EmployeeLastName, @EmployeeAddress, @EmployeeSSN, @EmployeeBirthDate, @Username, @Password)"; MySqlCommand cmd = new MySqlCommand(stm, con); cmd.Parameters.AddWithValue("@EmployeeFirstName", value.FirstName); cmd.Parameters.AddWithValue("@EmployeeLastName", value.LastName); cmd.Parameters.AddWithValue("@EmployeeAddress", value.Address); cmd.Parameters.AddWithValue("@EmployeeSSN", value.SSN); cmd.Parameters.AddWithValue("@EmployeeBirthDate", value.BirthDate); cmd.Parameters.AddWithValue("@Username", value.Username); cmd.Parameters.AddWithValue("@Password", value.Password); cmd.Prepare(); cmd.ExecuteNonQuery(); db.CloseConnection(); } }
public void SaveCustomer(string FirstName, string LastName, string Email, string Password, string PhoneNumber, int RewardsPoints) { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = @"INSERT INTO Customer(`Customer Email`, `Customer First Name`, `Customer Last Name`, `Customer Phone Number`, `Rewards Points`, `Password`) VALUES(@CustomerEmail, @CustomerFirstName, @CustomerLastName, @CustomerPhoneNumber, @RewardsPoints, @Password)"; MySqlCommand cmd = new MySqlCommand(stm, con); cmd.Parameters.AddWithValue("@CustomerEmail", Email); cmd.Parameters.AddWithValue("@CustomerFirstName", FirstName); cmd.Parameters.AddWithValue("@CustomerLastName", LastName); cmd.Parameters.AddWithValue("@CustomerPhoneNumber", PhoneNumber); cmd.Parameters.AddWithValue("@RewardsPoints", RewardsPoints); cmd.Parameters.AddWithValue("@Password", Password); cmd.Prepare(); cmd.ExecuteNonQuery(); db.CloseConnection(); } }
public List <Item> GetTransactionItems(int id) { List <Item> transItems = new List <Item>(); //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = "SELECT * FROM Item WHERE `Transaction ID` = @transID"; MySqlCommand cmd = new MySqlCommand(stm, con); cmd.Parameters.AddWithValue("@transID", id); cmd.Prepare(); using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { transItems.Add(ParseItemFromRdr(rdr)); } } db.CloseConnection(); return(transItems); } else { return(new List <Item>()); } }
public Item GetItem(int id) { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { Item temp = new Item(); //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = "SELECT * FROM Item WHERE `Item ID` = @id;"; MySqlCommand cmd = new MySqlCommand(stm, con); cmd.Parameters.AddWithValue("@id", id); cmd.Prepare(); using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { temp = ParseItemFromRdr(rdr); } } db.CloseConnection(); return(temp); } else { return(new Item()); } }
public List <Item> GetAllItems() { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = "SELECT * FROM Item"; MySqlCommand cmd = new MySqlCommand(stm, con); List <Item> items = new List <Item>(); using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { items.Add(ParseItemFromRdr(rdr)); } } db.CloseConnection(); return(items); } else { //if something goes wrong, we just return an empty list return(new List <Item>()); } }
public void AddItem(Item value) { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = @"INSERT INTO Item (`Item Name`, `Item Price`, `Item Year`, `Item Cost`, `Is Purchased`, `Card Condition`, `Card Sport`, `Card Team`, `Memorabilia Description`) VALUES(@ItemName, @ItemPrice, @ItemYear, @ItemCost, @IsPurchased, @CardCondition, @CardSport, @CardTeam, @MemorabiliaDescription)"; //making a command with the connection MySqlCommand cmd = new MySqlCommand(stm, con); //adding sample data cmd.Parameters.AddWithValue("@ItemName", value.ItemName); cmd.Parameters.AddWithValue("@ItemPrice", value.ItemPrice); cmd.Parameters.AddWithValue("@ItemYear", value.ItemYear); cmd.Parameters.AddWithValue("@ItemCost", value.ItemCost); cmd.Parameters.AddWithValue("@IsPurchased", "false"); cmd.Parameters.AddWithValue("@CardCondition", value.ItemCardCondition); cmd.Parameters.AddWithValue("@CardSport", value.ItemCardSport); cmd.Parameters.AddWithValue("@CardTeam", value.ItemCardTeam); cmd.Parameters.AddWithValue("@MemorabiliaDescription", value.ItemMemorabiliaDescription); //preparing the command string before touching the database cmd.Prepare(); //actually executing the command to insert a new item cmd.ExecuteNonQuery(); db.CloseConnection(); } }
public void UpdateItem(Item updatedItem) { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); //defining the string string stm = @"UPDATE Item SET `Item Name` = @ItemName, `Item Price` = @ItemPrice, `Item Year` = @ItemYear, `Card Condition` = @CardCondition, `Card Sport` = @CardSport, `Card Team` = @CardTeam, `Memorabilia Description` = @MemorabiliaDescription WHERE `Item ID` = @ItemID"; //making new command MySqlCommand cmd = new MySqlCommand(stm, con); //preparing the command and executing it cmd.Parameters.AddWithValue("@ItemID", updatedItem.ItemID); cmd.Parameters.AddWithValue("@ItemName", updatedItem.ItemName); cmd.Parameters.AddWithValue("@ItemPrice", updatedItem.ItemPrice); cmd.Parameters.AddWithValue("@ItemYear", updatedItem.ItemYear); cmd.Parameters.AddWithValue("@CardCondition", updatedItem.ItemCardCondition); cmd.Parameters.AddWithValue("@CardSport", updatedItem.ItemCardSport); cmd.Parameters.AddWithValue("@CardTeam", updatedItem.ItemCardTeam); cmd.Parameters.AddWithValue("@MemorabiliaDescription", updatedItem.ItemMemorabiliaDescription); cmd.Prepare(); cmd.ExecuteNonQuery(); db.CloseConnection(); } //if the open fails, the api should just do nothing; hopefully this helps prevent some crashes }
public List <Transaction> GetAllTransactions() { //making an item selector up front IGetTransactionItems readObj = new ReadItemData(); //making an empty list of transactions List <Transaction> transactions = new List <Transaction>(); //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = "SELECT * FROM Transact"; MySqlCommand cmd = new MySqlCommand(stm, con); using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Transaction newTrans = new Transaction() { TransactionID = rdr.GetInt32(0), TransactionDate = DateTime.Parse(rdr.GetString(1)), AmtDiscounted = rdr.GetDouble(2), PaymentType = rdr.GetString(3), EmployeeID = rdr.GetInt32(4), CustomerEmail = rdr.GetString(5) }; List <Item> transItems = readObj.GetTransactionItems(newTrans.TransactionID); newTrans.ItemIDs = GetItemIDs(transItems); newTrans.Subtotal = GetSubtotal(transItems); transactions.Add(newTrans); } } db.CloseConnection(); return(transactions); } return(transactions); }
public void SaveTransaction(string PaymentType, int EmployeeID, string CustomerEmail, List <int> ItemIDs, double AmtDiscounted, DateTime TransactionDate) { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = @"INSERT INTO Transact(`Transaction Date`, `Amount Discounted`, `Payment Type`, `Employee ID`, `Customer Email`) VALUES(@TransactionDate, @AmountDiscounted, @PaymentType, @EmployeeID, @CustomerEmail)"; MySqlCommand cmd = new MySqlCommand(stm, con); //inserting the transaction cmd.Parameters.AddWithValue("@TransactionDate", TransactionDate); cmd.Parameters.AddWithValue("@AmountDiscounted", AmtDiscounted); cmd.Parameters.AddWithValue("@PaymentType", PaymentType); cmd.Parameters.AddWithValue("@EmployeeID", EmployeeID); cmd.Parameters.AddWithValue("@CustomerEmail", CustomerEmail); cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.CommandText = @"SELECT `Transaction ID` FROM Transact ORDER BY `Transaction Date` DESC LIMIT 1"; cmd.Prepare(); var transactionID = cmd.ExecuteScalar(); foreach (int itemId in ItemIDs) { cmd.CommandText = @"UPDATE Item SET `Transaction ID` = @TransactionID, `Is Purchased` = 'true' WHERE `Item ID` = @ItemID"; cmd.Parameters.AddWithValue("@TransactionID", transactionID); cmd.Parameters.AddWithValue("@ItemID", itemId); cmd.Prepare(); cmd.ExecuteNonQuery(); } db.CloseConnection(); } }
//the idea behind logging in a customer is to return their rewards points: -1 means the customer wasn't found, anything else means they're signed in public int FindCustomer(Customer value) { //connecting to and opening the database DBConnect db = new DBConnect(); bool isOpen = db.OpenConnection(); if (isOpen) { int temp = -1; //if the open succeeded, we proceed with the sql commands MySqlConnection con = db.GetCon(); string stm = @"SELECT `Customer Email`, `Rewards Points` FROM Customer WHERE `Customer Email` = @email AND `Password` = @password;"; MySqlCommand cmd = new MySqlCommand(stm, con); cmd.Parameters.AddWithValue("@email", value.Email); cmd.Parameters.AddWithValue("@password", value.Password); cmd.Prepare(); using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { //try to return the rewards points from the customer try { temp = Convert.ToInt32(rdr[1]); } catch { db.CloseConnection(); //if the customer doesn't exist, this will throw an exception; if the customer doesn't exist, return -1 as rewards points return(temp); } } } db.CloseConnection(); return(temp); } //this sentinel value will indicate that the connection failed, which might be helpful on the front-end return(-2); }