// 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();
}
