protected void AddHomeForm_ItemInserting(object sender, FormViewInsertEventArgs e)
{
XmlNode name = HouseDoc.SelectSingleNode("home/name");
name.InnerText = e.Values["name"].ToString();
XmlNode longitude = HouseDoc.SelectSingleNode("home/longitude");
longitude.InnerText = e.Values["longitude"].ToString();
XmlNode latitude = HouseDoc.SelectSingleNode("home/latitude");
latitude.InnerText = e.Values["latitude"].ToString();
MembershipUser myObject = Membership.GetUser();
object UserID = myObject.ProviderUserKey;
using (var db = new Models.HouseContext())
{
try
{
//var query = from b in db.House
// orderby b.HouseID
// select b;
//List<string> lst = new List<string>();
//XmlDocument HouseDoc = new XmlDocument();
//XmlElement root = HouseDoc.CreateElement("home");
//XmlElement nm = HouseDoc.CreateElement("name");
//root.AppendChild(nm);
//HouseDoc.AppendChild(root);
//XmlDataSource homeDatasource = new XmlDataSource();
//HouseDataSource.Data = HouseDoc.InnerXml;
//HouseDataSource.DataBind();
//AddHomeForm.DataBind();
#region SQL interface
#region Insert xml into localDB
Object returnValue;
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("InsertXmlDoc", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@theDoc", SqlDbType.Xml).Value = HouseDoc.OuterXml;
con.Open();
returnValue = cmd.ExecuteScalar();
con.Close();
//TextBox HouseNameInsertTextBox = (TextBox)AddHomeForm.FindControl("HouseNameInsertTextBox");
House newHouse = new House
{
HouseName = name.InnerText,
HouseDescription = "The best house in the world",
docID = (int)returnValue
};
House addedHouse = db.House.Add(newHouse);
db.SaveChanges();
int houseId = addedHouse.HouseID;
using (SqlCommand newcmd = new SqlCommand("INSERT INTO Houses_Users (HouseId, UserId) VALUES (@houseID, @userID)", con))
{
newcmd.CommandType = CommandType.Text;
newcmd.Parameters.Add("@houseID", SqlDbType.Int).Value = houseId;
newcmd.Parameters.Add("@userID", SqlDbType.UniqueIdentifier).Value = UserID;
con.Open();
returnValue = newcmd.ExecuteScalar();
con.Close();
}
}
}
#endregion
#region Select from View
// Table to store the query results
//DataTable table = new DataTable();
//// Creates a SQL connection
//using (var connection = new SqlConnection(connectionString))
//{
// connection.Open();
// // Creates a SQL command
// using (var command = new SqlCommand("SELECT * FROM Select_HouseName_XML", connection))
// {
// // Loads the query results into the table
// table.Load(command.ExecuteReader());
// }
// connection.Close();
//}
#endregion
SqlDataSource1.DataBind();
HousesRpter.DataBind();
AddHomeForm.Visible = false;
#endregion
e.Cancel = true;
}
catch (DbEntityValidationException dbEx)
{
Console.WriteLine(dbEx);
}
}
}
private int UpdateHouse(House house)
{
int docID;
try
{
using (var db = new Models.HouseContext())
{
var ho = db.House.FirstOrDefault(c => c.HouseID == house.HouseID);
ho.HouseName = house.HouseName;
ho.HouseDescription = house.HouseDescription;
docID = ho.docID;
db.SaveChanges();
return docID;
}
}
catch (DbEntityValidationException dbEx)
{
Console.WriteLine(dbEx);
return 0;
}
}
protected void HousesRpter_ItemCommand(object source, RepeaterCommandEventArgs e)
{
LinkButton lnkUpdate = (LinkButton)e.Item.FindControl("lnkUpdate");
LinkButton lnkCancel = (LinkButton)e.Item.FindControl("lnkCancel");
LinkButton lnkEdit = (LinkButton)e.Item.FindControl("lnkEdit");
LinkButton lnkDelete = (LinkButton)e.Item.FindControl("lnkDelete");
#region Edit
if (e.CommandName == "edit")
{
lnkCancel.Visible = true;
lnkUpdate.Visible = true;
lnkEdit.Visible = false;
int houseId = int.Parse(e.CommandArgument.ToString());
int doc = GetHomeDocID(houseId);
object returningValue;
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand newcmd = new SqlCommand("SELECT theDoc FROM XMLDoc WHERE id=" + doc, con))
{
newcmd.CommandType = CommandType.Text;
con.Open();
returningValue = newcmd.ExecuteScalar();
con.Close();
}
}
HouseDoc.InnerXml = (string)returningValue;
XmlDataSource homeDatasource = new XmlDataSource();
homeDatasource.Data = HouseDoc.InnerXml;
homeDatasource.EnableCaching = false;
homeDatasource.DataBind();
AddHomeForm.DataSourceID = null;
AddHomeForm.DataSource = homeDatasource;
AddHomeForm.ChangeMode(FormViewMode.Edit);
AddHomeForm.DataBind();
AddHomeForm.Visible = true;
}
#endregion
#region Update
if (e.CommandName == "update")
{
lnkCancel.Visible = false;
lnkUpdate.Visible = false;
lnkEdit.Visible = true;
EditHouse edit = (EditHouse)AddHomeForm.FindControl("EditHouse1");
string HouseName = ((TextBox)edit.FindControl("HouseNameTextBox")).Text;
string HouseLat = ((TextBox)edit.FindControl("HouseLatTextBox")).Text;
string HouseLong = ((TextBox)edit.FindControl("HouseLongTextBox")).Text;
int houseId = int.Parse(e.CommandArgument.ToString());
House house = new House();
house.HouseID = houseId;
house.HouseName = HouseName;
house.HouseDescription = "House Description";
int docID = UpdateHouse(house);
XmlNode name = HouseDoc.SelectSingleNode("home/name");
name.InnerText = HouseName;
XmlNode longitude = HouseDoc.SelectSingleNode("home/longitude");
longitude.InnerText = HouseLong;
XmlNode latitude = HouseDoc.SelectSingleNode("home/latitude");
latitude.InnerText = HouseLat;
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("UpdateHouseXML", con))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter userId = new SqlParameter("@Original_id", docID);
SqlParameter roleId = new SqlParameter("@thedoc", HouseDoc.InnerXml);
cmd.Parameters.Add(userId);
cmd.Parameters.Add(roleId);
con.Open();
cmd.ExecuteScalar();
con.Close();
}
}
SqlDataSource1.DataBind();
HousesRpter.DataBind();
AddHomeForm.Visible = false;
}
#endregion
#region Cancel
if (e.CommandName == "cancel")
{
lnkCancel.Visible = false;
lnkUpdate.Visible = false;
lnkEdit.Visible = true;
AddHomeForm.Visible = false;
}
#endregion
#region Delete
if (e.CommandName == "delete")
{
int houseId = int.Parse(e.CommandArgument.ToString());
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("DeleteHouse", con))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter userId = new SqlParameter("@houseID", houseId);
cmd.Parameters.Add(userId);
con.Open();
cmd.ExecuteScalar();
con.Close();
}
}
SqlDataSource1.DataBind();
HousesRpter.DataBind();
}
#endregion
}