public int CreateBeerInDB(Beer b)
{
//initialize connection
SqlConnection conn = new SqlConnection(connectionString);
try
{
//open connection
conn.Open();
//setup DB command and execute it
string cmdText = "INSERT INTO Beer VALUES ('" + b.Name + "'," + b.Volume + "," + b.Price + ")";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
return 1; //all went good :)
}
catch (Exception err)
{
MessageBox.Show(err.ToString()); //display message box with error
return -1;
}
finally
{ conn.Close(); }
}
private void btnCreateBeer_Click(object sender, EventArgs e)
{
//create new beer object
Beer newBeer = new Beer(txtName.Text, Convert.ToInt32(txtVolume.Text), Convert.ToInt32(txtPrice.Text));
//tell pub object to add beer
pub.Add(newBeer);
RefreshList();
}
public List<Beer> RetrieveBeersFromDB()
{
//initialize connection
SqlConnection conn = new SqlConnection(connectionString);
try
{
//open connection
conn.Open();
//setup DB command and execute it
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Beer", conn);
DataTable personTable = new DataTable();
da.Fill(personTable);
List<Beer> beerList = new List<Beer>();
//run through all rows in result set, map each row to a person object and add it to list
foreach (DataRow dr in personTable.Rows)
{
Beer b = new Beer();
#region Map DataRow to Person object
b.Name = dr["name"].ToString();
b.Volume = Convert.ToInt32(dr["volume"].ToString());
b.Price = Convert.ToInt32(dr["price"].ToString());
#endregion
beerList.Add(b);
}
return beerList;
}
catch (Exception err)
{
MessageBox.Show(err.ToString()); //display message box with error
return null;
}
finally { conn.Close(); }
}
public int UpdateBeerInDB(Beer b)
{
//initialize connection
SqlConnection conn = new SqlConnection(connectionString);
try
{
//open connection
conn.Open();
//setup DB command and execute it
string cmdText = "UPDATE Beer SET volume =" + b.Volume + ",price=" + b.Price + " WHERE name='" + b.Name + "'";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
return 1; //all went good :)
}
catch (Exception err)
{
MessageBox.Show(err.ToString()); //display message box with error
return -1; //something went wrong :(
}
finally { conn.Close(); }
}
public void Add(Beer b)
{
beers.Add(b); //add beer to cached list
beerDAO.CreateBeerInDB(b); //add beer to database table
}
public void Update(Beer b)
{
beerDAO.UpdateBeerInDB(b);
beers = beerDAO.RetrieveBeersFromDB(); //update cached list
}
public void Remove(Beer b)
{
beers.Remove(b); //remove beer in cached list
beerDAO.DeleteBeerInDB(b.Name); //remove beer in database table
}
