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 }