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.";
}
}
}