protected void btn_gs_show_Click(object sender, EventArgs e) { _5101BFinalProject.Models.GiftShopT giftshop = new _5101BFinalProject.Models.GiftShopT(); //giftshop.Gift_Id = Convert.ToInt32(txt_gift_id.Text); //giftshop.Gift_Desc = txt_gift_desc2.Text; _5101BFinalProject.Models.CalvinDb db = new _5101BFinalProject.Models.CalvinDb(); try { GiftShopT gs = new GiftShopT(); if (txt_gift_id.Text != "") { gs.Gift_Id = Convert.ToInt32(txt_gift_id.Text); } if (txt_gift_desc2.Text != "") { gs.Gift_Desc = txt_gift_desc2.Text; } List <GiftShopT> gstable = db.Get(gs); table_gifts.InnerHtml = "<table border='1px solid #000'><tr><th>Gift ID</th><th>Sender's Full Name</th><th>Recipient's Full Name</th><th>Gift Type</th><th>Gift Description</th><th>Gift Price</th><th>Gift Sender ID</th><th>Gift Recipient ID</th></tr>"; foreach (GiftShopT item in gstable) { table_gifts.InnerHtml += "<tr><td>" + item.Gift_Id.ToString() + "</td><td>" + item.Sender_Full_Name + "</td><td>" + item.Recipient_Full_Name + "</td><td>" + item.Gift_Type + "</td><td>" + item.Gift_Desc + "</td><td> $ " + Math.Round(item.Gift_Price, 2).ToString() + "</td><td>" + item.Gift_Sender_Id.ToString() + "</td><td>" + item.Gift_Recipient_Id.ToString() + "</td></tr>"; } table_gifts.InnerHtml += "</table>"; } catch (OracleException except) { lbl_search_err.Text = except.Message + ". "; } finally { //lbl_msg.Text += Convert.ToString(db.Rows) + " rows deleted."; } }
public void Delete(GiftShopT gshop) { string command = String.Format("DELETE FROM gift_shop WHERE gift_id = :gs_id", gshop.Gift_Id); conn.Open(); OracleCommand cmd = new OracleCommand(command, conn); cmd.Parameters.Add(new OracleParameter("gs_id", gshop.Gift_Id)); _rows = cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); }
protected void btn_gs_delete_Click(object sender, EventArgs e) { _5101BFinalProject.Models.GiftShopT giftshop = new _5101BFinalProject.Models.GiftShopT(); giftshop.Gift_Id = Convert.ToInt32(txt_gift_id3.Text); _5101BFinalProject.Models.CalvinDb db = new _5101BFinalProject.Models.CalvinDb(); try { db.Delete(giftshop); } catch (OracleException except) { if (except.Message.Contains("ORA-02290")) { gift_id3_err.Text = "Please provide Gift ID."; } else { gift_id3_err.Text = ""; } if (except.Message.Contains("ORA-01400")) { gift_id3_err.Text = "Please provide Gift ID."; } else { gift_id3_err.Text = ""; } if (except.Message.Contains("ORA-01722")) { gift_id3_err.Text = "Please input a valid number"; } else { gift_id3_err.Text = ""; } lbl_delete_err.Text = except.Message + ". "; } finally { lbl_delete_err.Text += Convert.ToString(db.Rows) + " rows deleted."; } }
public void Add(GiftShopT gshop) { string command = "INSERT INTO gift_shop (gift_id, gift_sender_first_name, gift_sender_last_name, gift_recipient_first_name, gift_recipient_last_name, gift_type, gift_desc, gift_price, gift_sender_id, gift_recipient_id) VALUES(:gs_id, :gs_sender_f_name, :g_sender_l_name, :g_recipient_f_name, :g_recipient_l_name, :gs_type, :gs_desc, :gs_price, :gs_sender_id, :gs_recipient_id)"; conn.Open(); OracleCommand cmd = new OracleCommand(command, conn); cmd.Parameters.Add(new OracleParameter("gs_id", gshop.Gift_Id)); cmd.Parameters.Add(new OracleParameter("gs_sender_f_name", gshop.Gift_Sender_First_Name)); cmd.Parameters.Add(new OracleParameter("gs_sender_l_name", gshop.Gift_Sender_Last_Name)); cmd.Parameters.Add(new OracleParameter("gs_recipient_f_name", gshop.Gift_Recipient_First_Name)); cmd.Parameters.Add(new OracleParameter("gs_recipient_l_name", gshop.Gift_Recipient_Last_Name)); cmd.Parameters.Add(new OracleParameter("gs_type", gshop.Gift_Type)); cmd.Parameters.Add(new OracleParameter("gs_desc", gshop.Gift_Desc)); cmd.Parameters.Add(new OracleParameter("gs_price", gshop.Gift_Price)); cmd.Parameters.Add(new OracleParameter("gs_sender_id", gshop.Gift_Sender_Id)); cmd.Parameters.Add(new OracleParameter("gs_recipient_id", gshop.Gift_Recipient_Id)); _rows = cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); }
public void Update(GiftShopT gshop) { string command = "UPDATE gift_shop SET gift_sender_first_name = :gs_sender_f_name, gift_sender_last_name = :gs_sender_l_name, gift_recipient_first_name = :gs_recipient_f_name, gift_recipient_last_name = :gs_recipient_l_name, gift_type = :gs_type, gift_desc = :gs_desc, gift_price = :gs_price, gift_sender_id = :gs_sender_id, gift_recipient_id = :gs_recipient_id WHERE gift_id = :g_id"; conn.Open(); OracleCommand cmd = new OracleCommand(command, conn); cmd.Parameters.Add(new OracleParameter("gs_sender_f_name", gshop.Gift_Sender_First_Name)); cmd.Parameters.Add(new OracleParameter("gs_sender_l_name", gshop.Gift_Sender_Last_Name)); cmd.Parameters.Add(new OracleParameter("gs_recipient_f_name", gshop.Gift_Recipient_First_Name)); cmd.Parameters.Add(new OracleParameter("gs_recipient_l_name", gshop.Gift_Recipient_Last_Name)); cmd.Parameters.Add(new OracleParameter("gs_type", gshop.Gift_Type)); cmd.Parameters.Add(new OracleParameter("gs_desc", gshop.Gift_Desc)); cmd.Parameters.Add(new OracleParameter("gs_price", gshop.Gift_Price)); cmd.Parameters.Add(new OracleParameter("gs_sender_id", gshop.Gift_Sender_Id)); cmd.Parameters.Add(new OracleParameter("gs_recipient_id", gshop.Gift_Recipient_Id)); cmd.Parameters.Add(new OracleParameter("gs_id", gshop.Gift_Id)); _rows = cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); }
// METHODS public List <GiftShopT> Get(GiftShopT gshop) { List <GiftShopT> _giftshop = new List <GiftShopT>(); OracleCommand cmd; OracleDataReader reader; string query; if (gshop.Gift_Id != 0) { query = "SELECT * FROM gift_shop WHERE gift_id = :gshop_id"; conn.Open(); cmd = new OracleCommand(query, conn); cmd.Parameters.Add(new OracleParameter("gshop_id", gshop.Gift_Id)); reader = cmd.ExecuteReader(); while (reader.Read()) { GiftShopT giftshop = new GiftShopT(); { giftshop.Gift_Id = Convert.ToInt32(reader["gift_id"]); giftshop.Gift_Sender_First_Name = reader["gift_sender_first_name"].ToString(); giftshop.Gift_Sender_Last_Name = reader["gift_sender_last_name"].ToString(); giftshop.Gift_Recipient_First_Name = reader["gift_recipient_first_name"].ToString(); giftshop.Gift_Recipient_Last_Name = reader["gift_recipient_last_name"].ToString(); giftshop.Gift_Type = reader["gift_type"].ToString(); giftshop.Gift_Desc = reader["gift_desc"].ToString(); giftshop.Gift_Price = Convert.ToDouble(reader["gift_price"]); giftshop.Gift_Sender_Id = Convert.ToInt32(reader["gift_sender_id"]); giftshop.Gift_Recipient_Id = Convert.ToInt32(reader["gift_recipient_id"]); } _giftshop.Add(giftshop); } reader.Close(); cmd.Dispose(); conn.Close(); return(_giftshop); } if (!String.IsNullOrWhiteSpace(gshop.Gift_Desc)) { query = "SELECT * FROM gift_shop WHERE gift_desc LIKE '%' || :gshop_desc || '%'"; conn.Open(); cmd = new OracleCommand(query, conn); cmd.Parameters.Add(new OracleParameter("gshop_desc", gshop.Gift_Desc)); reader = cmd.ExecuteReader(); while (reader.Read()) { GiftShopT gs_temp = new GiftShopT(); { gs_temp.Gift_Id = Convert.ToInt32(reader["gift_id"]); gs_temp.Gift_Sender_First_Name = reader["gift_sender_first_name"].ToString(); gs_temp.Gift_Sender_Last_Name = reader["gift_sender_last_name"].ToString(); gs_temp.Gift_Recipient_First_Name = reader["gift_recipient_first_name"].ToString(); gs_temp.Gift_Recipient_Last_Name = reader["gift_recipient_last_name"].ToString(); gs_temp.Gift_Type = reader["gift_type"].ToString(); gs_temp.Gift_Desc = reader["gift_desc"].ToString(); gs_temp.Gift_Price = Convert.ToDouble(reader["gift_price"]); gs_temp.Gift_Sender_Id = Convert.ToInt32(reader["gift_sender_id"]); gs_temp.Gift_Recipient_Id = Convert.ToInt32(reader["gift_recipient_id"]); } _giftshop.Add(gs_temp); } reader.Close(); cmd.Dispose(); conn.Close(); return(_giftshop); } query = "SELECT * FROM gift_shop ORDER BY gift_id"; conn.Open(); cmd = new OracleCommand(query, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { GiftShopT giftShop = new GiftShopT(); { giftShop.Gift_Id = Convert.ToInt32(reader["gift_id"]); giftShop.Gift_Sender_First_Name = reader["gift_sender_first_name"].ToString(); giftShop.Gift_Sender_Last_Name = reader["gift_sender_last_name"].ToString(); giftShop.Gift_Recipient_First_Name = reader["gift_recipient_first_name"].ToString(); giftShop.Gift_Recipient_Last_Name = reader["gift_recipient_last_name"].ToString(); giftShop.Gift_Type = reader["gift_type"].ToString(); giftShop.Gift_Desc = reader["gift_desc"].ToString(); giftShop.Gift_Price = Convert.ToDouble(reader["gift_price"]); giftShop.Gift_Sender_Id = Convert.ToInt32(reader["gift_sender_id"]); giftShop.Gift_Recipient_Id = Convert.ToInt32(reader["gift_recipient_id"]); } _giftshop.Add(giftShop); } reader.Close(); cmd.Dispose(); conn.Close(); return(_giftshop); }
protected void btn_gs_create_Click(object sender, EventArgs e) { _5101BFinalProject.Models.GiftShopT giftshop = new _5101BFinalProject.Models.GiftShopT(); //giftshop.Gift_Id = Convert.ToInt32(txt_gift_id2.Text); // how to put into validation giftshop.Gift_Sender_First_Name = txt_sender_f_name.Text; giftshop.Gift_Sender_Last_Name = txt_sender_l_name.Text; giftshop.Gift_Recipient_First_Name = txt_recipient_f_name.Text; giftshop.Gift_Recipient_Last_Name = txt_recipient_l_name.Text; giftshop.Gift_Type = ddl_gift_type.SelectedItem.Value; giftshop.Gift_Desc = txt_gift_desc.Text; _5101BFinalProject.Models.CalvinDb db = new _5101BFinalProject.Models.CalvinDb(); //if (Convert.ToInt32(txt_gift_id2.Text) == 0) if (txt_gift_id2.Text == "") { gift_id2_err.Text = "Please enter a valid number"; } else if (txt_gift_price.Text == "") { gift_price_err.Text = "Please enter a valid number"; } else if (txt_gift_sender_id.Text == "") { gift_sender_id_err.Text = "Please enter a valid number"; } else if (txt_gift_recipient_id.Text == "") { gift_recipient_id_err.Text = "Please enter a valid number"; } else { giftshop.Gift_Id = Convert.ToInt32(txt_gift_id2.Text); double giftprice = Convert.ToDouble(txt_gift_price.Text); giftshop.Gift_Price = Math.Round(giftprice, 2); giftshop.Gift_Sender_Id = Convert.ToInt32(txt_gift_sender_id.Text); giftshop.Gift_Recipient_Id = Convert.ToInt32(txt_gift_recipient_id.Text); gift_id2_err.Text = ""; gift_price_err.Text = ""; gift_sender_id_err.Text = ""; gift_recipient_id_err.Text = ""; try { db.Add(giftshop); } catch (OracleException except) { if (txt_sender_f_name.Text == "") { if (except.Message.Contains("ORA-01400")) { gift_send_f_name_err.Text = "Please provide Sender's first name."; } } else { gift_send_f_name_err.Text = ""; } if (txt_sender_l_name.Text == "") { if (except.Message.Contains("ORA-01400")) { gift_send_l_name_err.Text = "Please provide Sender's last name."; } } else { gift_send_l_name_err.Text = ""; } if (txt_recipient_f_name.Text == "") { if (except.Message.Contains("ORA-01400")) { gift_rec_f_name_err.Text = "Please provide Recipient's first name."; } } else { gift_rec_f_name_err.Text = ""; } if (txt_recipient_l_name.Text == "") { if (except.Message.Contains("ORA-01400")) { gift_rec_l_name_err.Text = "Please provide Recipient's last name."; } } else { gift_rec_l_name_err.Text = ""; } // Recipient ID if (except.Message.Contains("ORA-01400")) { gift_recipient_id_err.Text = "Please provide Recipient ID."; } else { gift_recipient_id_err.Text = ""; } if (except.Message.Contains("ORA-01722")) { gift_recipient_id_err.Text = "Please input a valid number"; } else { gift_recipient_id_err.Text = ""; } if (except.Message.Contains("ORA-02291")) { gift_recipient_id_err.Text = "Recipient ID doesn't exist"; } else { gift_recipient_id_err.Text = ""; } // Sender ID if (except.Message.Contains("ORA-01400")) { gift_sender_id_err.Text = "Please provide Sender ID."; } else { gift_sender_id_err.Text = ""; } if (except.Message.Contains("ORA-01722")) { gift_sender_id_err.Text = "Please input a valid number"; } else { gift_sender_id_err.Text = ""; } if (except.Message.Contains("ORA-02291")) { gift_sender_id_err.Text = "Sender ID doesn't exist"; } else { gift_sender_id_err.Text = ""; } // Gift Price if (except.Message.Contains("ORA-01400")) { gift_price_err.Text = "Please put price for the item."; } else { gift_price_err.Text = ""; } if (except.Message.Contains("ORA-01722")) { gift_price_err.Text = "Please input a valid number"; } else { gift_price_err.Text = ""; } // Gift type if (except.Message.Contains("ORA-02290")) { gift_type_err.Text = "Please select proper gift type."; } else { gift_type_err.Text = ""; } // Gift Description if (txt_gift_desc.Text == "") { if (except.Message.Contains("ORA-01400")) { gift_desc_err.Text = "Please provide gift description."; } } else { gift_desc_err.Text = ""; } if (except.Message.Contains("ORA-01400")) { gift_id2_err.Text = "Please provide Gift ID."; } else { gift_id2_err.Text = ""; } if (except.Message.Contains("ORA-00001")) { gift_id2_err.Text = "Gift ID already in use."; } else { gift_id2_err.Text = ""; } if (except.Message.Contains("ORA-01722")) { gift_id2_err.Text = "Please input a valid number"; } else { gift_id2_err.Text = ""; } lbl_insert_err.Text = except.Message + ". "; } finally { lbl_insert_err.Text += Convert.ToString(db.Rows) + " rows inserted."; } } }