protected void btnAddCate_Click(object sender, EventArgs e)
{
DropDownList newSeleCat = (DropDownList)DetailsViewDetail.FindControl("categoryList");
String value = newSeleCat.SelectedValue;
TextBox newCate = (TextBox)DetailsViewDetail.FindControl("newCate");
if (newCate.Text.Length > 0 && null == newSeleCat.Items.FindByText(newCate.Text))
{
string connectionString =
ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connectionString;
OracleCommand comm = conn.CreateCommand();
comm.CommandText = "insert into categories (categoryid, type, typedesc) values (category_id_seq.nextval, '" + newCate.Text + "', '" + newCate.Text + "')";
comm.CommandType = CommandType.Text;
DataTable table;
table = new DataTable();
try
{
comm.Connection.Open();
OracleDataReader reader = comm.ExecuteReader();
table.Load(reader);
newSeleCat.DataBind();
newSeleCat.Items.FindByText(newCate.Text).Selected = true;
}
catch (SqlException ex)
{
exception.Text = ex.Message;
}
catch (Exception ex)
{
exception.Text = ex.Message;
}
finally
{
newCate.Text = "";
comm.Connection.Close();
}
}
else
{
newCate.Text = "";
}
}
protected void DetailsViewDetail_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{
TextBox newRecipeName = (TextBox)DetailsViewDetail.FindControl("editRecipename");
TextBox newDesc = (TextBox)DetailsViewDetail.FindControl("editDesc");
TextBox newNum = (TextBox)DetailsViewDetail.FindControl("editNum");
TextBox neweditMinute = (TextBox)DetailsViewDetail.FindControl("editMinute");
DropDownList newSeleCat = (DropDownList)DetailsViewDetail.FindControl("categoryList");
string newReName = newRecipeName.Text;
string newDes = newDesc.Text;
int servingNum = int.Parse(newNum.Text);
int minute = int.Parse(neweditMinute.Text);
string categ = newSeleCat.SelectedValue;
string connectionString =
ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connectionString;
OracleCommand comm = conn.CreateCommand();
try
{
comm.Connection.Open();
comm.CommandType = CommandType.Text;
comm.CommandText = "update recipes set recipename='" + newReName + "' , description='" + newDes + "', servingnum=" + servingNum + ", cookingminutes=" + minute + ", categoryid='" + categ + "' where recipeid = " + Request.QueryString["key"];
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
exception.Text = ex.Message;
}
finally
{
comm.Connection.Close();
}
DetailsViewDetail.ChangeMode(DetailsViewMode.ReadOnly);
BindList();
}
protected void btnDelete_Click(object sender, EventArgs e)
{
string connectionString =
ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connectionString;
OracleCommand comm = conn.CreateCommand();
comm.CommandText = "delete from recipes where recipeid=" + Request.QueryString["key"];
comm.CommandType = CommandType.Text;
DataTable table;
table = new DataTable();
try
{
comm.Connection.Open();
OracleDataReader reader = comm.ExecuteReader();
table.Load(reader);
}
catch (SqlException ex)
{
exception.Text = ex.Message;
}
catch (Exception ex)
{
exception.Text = ex.Message;
}
finally
{
comm.Connection.Close();
}
DetailsViewDetail.DataSource = table;
DetailsViewDetail.DataBind();
}
private void BindList()
{
string connectionString =
ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connectionString;
OracleCommand comm = conn.CreateCommand();
OracleDataReader reader;
DataTable recipeDetailTable = new DataTable();
DataTable categoryTable = new DataTable();
DataTable ingredientsTable = new DataTable();
DataTable listTable = new DataTable();
try
{
// load recipe data
comm.CommandText = "select u.name as Submity_by, c.type as category, r.recipeid, r.categoryid, r.recipename,r.userID, r.description, r.servingnum, r.cookingminutes from recipes r right outer join users u on r.userid = u.userid join categories c on c.categoryid = r.categoryid join recipesLinkIngredients on r.recipeid = recipesLinkIngredients.recipeid right outer join ingredients i on recipesLinkIngredients.ingredientID = i.ingredientID where r.recipeid=" + Request.QueryString["key"];
comm.CommandType = CommandType.Text;
comm.Connection.Open();
reader = comm.ExecuteReader();
recipeDetailTable.Load(reader);
// bind recipe detail view
DetailsViewDetail.DataSource = recipeDetailTable;
DetailsViewDetail.DataBind();
// load selected category data
comm.Parameters.Clear();
comm.CommandText = "select r.categoryid, c.type from recipes r left join categories c on c.categoryid = r.categoryid right outer join recipesLinkIngredients on r.recipeid = recipesLinkIngredients.recipeid right outer join ingredients i on recipesLinkIngredients.ingredientID = i.ingredientID where r.recipeid=" + Request.QueryString["key"];
reader = comm.ExecuteReader();
categoryTable.Load(reader);
// load category list data
comm.Parameters.Clear();
comm.CommandText = "select categoryid, type from categories";
reader = comm.ExecuteReader();
listTable.Load(reader);
// bind category label (normal mode) and category droplist (edit mode)
Label category = (Label)DetailsViewDetail.FindControl("catgoryLabel");
if (category != null)
{
category.Text = categoryTable.Rows[0]["type"].ToString();
}
DropDownList categoryDropList = (DropDownList)DetailsViewDetail.FindControl("categoryList");
if (categoryDropList != null)
{
categoryDropList.DataSource = listTable;
categoryDropList.DataTextField = "Type";
categoryDropList.DataValueField = "categoryid";
categoryDropList.DataBind();
ListItem selectedListItem = categoryDropList.Items.FindByValue(categoryTable.Rows[0]["categoryid"].ToString());
if (selectedListItem != null)
{
selectedListItem.Selected = true;
}
}
// load ingredients data
comm.Parameters.Clear();
comm.CommandText = "select ingredients.ingredientid,ingredients.name,ingredients.quantity,ingredients.unitofmeasure from ingredients join recipesLinkIngredients on ingredients.INGREDIENTID = recipesLinkIngredients.INGREDIENTID where recipesLinkIngredients .recipeid = " + Request.QueryString["key"];
reader = comm.ExecuteReader();
ingredientsTable.Load(reader);
// bind ingredientView
ingredientView.DataSource = ingredientsTable;
ingredientView.DataBind();
}
catch (SqlException ex)
{
exception.Text = ex.Message;
}
catch (Exception ex)
{
exception.Text = ex.Message;
}
finally
{
comm.Connection.Close();
}
}
protected void DetailsViewDetail_ModeChanging(object sender, DetailsViewModeEventArgs e)
{
DetailsViewDetail.ChangeMode(e.NewMode);
BindList();
}