コード例 #1
0
        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);
                }
            }
        }
コード例 #2
0
        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;
            }
        }
コード例 #3
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
        }