// Method to get the whiskey info with a given whiskey ID. Returns an object from the class whiskey with the received information public Whiskey getWhiskey(int ID) { SqlConnection con = databaseConnect(); string cmd = "Select * FROM Whiskeys WHERE whiskeyId = @ID"; SqlDataAdapter ad = new SqlDataAdapter(cmd, con); ad.SelectCommand.Parameters.AddWithValue("@ID", ID); DataTable dt = new DataTable(); ad.Fill(dt); List <string> objectList = new List <string>(); foreach (DataColumn col in dt.Columns) { foreach (DataRow row in dt.Rows) { objectList.Add(row[col.ColumnName].ToString()); } } Whiskey newWhiskey = new Whiskey(Convert.ToInt32(objectList[0]), objectList[1], objectList[2], Convert.ToInt32(objectList[3]), Convert.ToDouble(objectList[4]), Convert.ToDouble(objectList[5]), objectList[6], Convert.ToInt32(objectList[7]), objectList[8], objectList[9], objectList[10], Convert.ToInt32(objectList[11])); return(newWhiskey); }
// Method to update a whiskey to the databased with a given object public void updateWhiskey(Whiskey whiskey) { SqlConnection con = databaseConnect(); SqlCommand cmd = new SqlCommand("UPDATE Whiskeys SET name = @name, age = @age, categoryId = @categoryId," + "currentMarketValue = @currentMarketValue, purchasePrice = @purchasePrice, description = @description, " + "brandId = @brandId, alcoholPercentage = @alcoholPercentage, sealed = @sealed, baseingredient = @baseingredient WHERE whiskeyId = @ID", con); cmd.Parameters.AddWithValue("@ID", whiskey.getId()); cmd.Parameters.AddWithValue("@name", whiskey.getName()); cmd.Parameters.AddWithValue("@age", whiskey.getAge()); cmd.Parameters.AddWithValue("@categoryId", whiskey.getCategoryId()); cmd.Parameters.AddWithValue("@currentMarketValue", whiskey.getCurrentMarketValue()); cmd.Parameters.AddWithValue("@purchasePrice", whiskey.getPurchasePrice()); cmd.Parameters.AddWithValue("@description", whiskey.getDescription()); cmd.Parameters.AddWithValue("@brandId", whiskey.getBrandId()); cmd.Parameters.AddWithValue("@alcoholPercentage", whiskey.getAlcoholPercentage()); cmd.Parameters.AddWithValue("@sealed", whiskey.getIsSealed()); cmd.Parameters.AddWithValue("@baseingredient", whiskey.getBaseIngredient()); cmd.ExecuteNonQuery(); con.Close(); }
// Method to add a whiskey to the databased with a given object public void addWhiskey(Whiskey whiskey) { SqlConnection con = databaseConnect(); SqlCommand cmd = new SqlCommand("insert into Whiskeys(name,age,categoryId,currentMarketValue," + "purchasePrice,description,brandId,alcoholPercentage,sealed,baseIngredient,collectionId) values (@name," + "@age,@categoryId,@currentMarketValue,@purchasePrice,@description,@brandId,@alcoholPercentage," + "@sealed,@baseingredient,@collectionId)", con); cmd.Parameters.AddWithValue("@name", whiskey.getName()); cmd.Parameters.AddWithValue("@age", whiskey.getAge()); cmd.Parameters.AddWithValue("@categoryId", whiskey.getCategoryId()); cmd.Parameters.AddWithValue("@currentMarketValue", whiskey.getCurrentMarketValue()); cmd.Parameters.AddWithValue("@purchasePrice", whiskey.getPurchasePrice()); cmd.Parameters.AddWithValue("@description", whiskey.getDescription()); cmd.Parameters.AddWithValue("@brandId", whiskey.getBrandId()); cmd.Parameters.AddWithValue("@alcoholPercentage", whiskey.getAlcoholPercentage()); cmd.Parameters.AddWithValue("@sealed", whiskey.getIsSealed()); cmd.Parameters.AddWithValue("@baseingredient", whiskey.getBaseIngredient()); cmd.Parameters.AddWithValue("@collectionId", whiskey.getCollectionId()); cmd.ExecuteNonQuery(); con.Close(); }