public HelpLevel2 GetHelpLevel2ByTitle(string title) { string query = "SELECT * FROM HelpOnlineLevel2 WHERE title = '" + title + "'"; using (SqlConnection con = SQLConnect()) { using (SqlDataAdapter sda = new SqlDataAdapter(query, con)) { using (DataTable dt = new DataTable()) { con.Open(); sda.Fill(dt); if (dt.Rows.Count > 0) { DataRow row = dt.Rows[0]; HelpLevel2 obj = new HelpLevel2(); obj.Id = Convert.ToInt32(row["Id"]); obj.Title = row["Title"].ToString(); obj.ImageFile = row["ImageFile"].ToString(); obj.Index = Convert.ToInt32(row["IndexTopic"]); obj.ParentId = Convert.ToInt32(row["ParentId"]); return(obj); } else { return(null); } } } } }
public void UpdateDBWithXMLLevel3(HelpLevel2 level2, string serverPathToSaveHTML, string serverPathOfHTMLLink) { foreach (HelpLevel3 level3 in level2.Children) { level3.ParentId = level2.Id; HelpLevel3 level3DB = helpLevel3BD.GetHelpLevel3ByTitleAndParentId(level3.Title, level2.Id); if (level3DB == null) { // add new this level1 helpLevel3BD.InsertLevel3(level3); // upload htm file XMLHanlder.DownloadHTMLFile(level3.URL, serverPathToSaveHTML, serverPathOfHTMLLink); } else { // this topic existed, just update index topic level3DB.Index = level3.Index; level3.Id = level3DB.Id; if (!level3DB.URL.Equals(level3.URL)) { level3DB.URL = level3.URL; XMLHanlder.DownloadHTMLFile(level3.URL, serverPathToSaveHTML, serverPathOfHTMLLink); helpLevel3BD.UpdateIndexTopicAndURLLevel3(level3DB); } else { helpLevel3BD.UpdateIndexTopicLevel3(level3DB); } } } }
public List <HelpLevel2> LoadHelpLevel2ByParentId(int parentId) { if (parentId == 0) { return(ListAll()); } string query = "SELECT * FROM HelpOnlineLevel2 where parentId = " + parentId + " order by IndexTopic"; List <HelpLevel2> list = new List <HelpLevel2>(); using (SqlConnection con = SQLConnect()) { using (SqlDataAdapter sda = new SqlDataAdapter(query, con)) { using (DataTable dt = new DataTable()) { con.Open(); sda.Fill(dt); for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = dt.Rows[i]; HelpLevel2 obj = new HelpLevel2(); obj.Id = Convert.ToInt32(row["Id"]); obj.Title = row["Title"].ToString(); obj.ImageFile = row["ImageFile"].ToString(); obj.ParentId = Convert.ToInt32(row["ParentId"]); obj.Index = Convert.ToInt32(row["IndexTopic"]); list.Add(obj); } } } } return(list); }
public void UpdateIndexTopicLevel2(HelpLevel2 level2) { string query = "UPDATE HelpOnlineLevel2 SET IndexTopic=@indexTopic WHERE Id=@Id"; using (SqlConnection con = SQLConnect()) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Parameters.AddWithValue("@indexTopic", level2.Index); cmd.Parameters.AddWithValue("@Id", level2.Id); cmd.Connection = con; con.Open(); //int insertedID = Convert.ToInt32(cmd.ExecuteScalar()); cmd.ExecuteNonQuery(); con.Close(); } } }
public int InsertLevel2(HelpLevel2 level2) { string query = "INSERT INTO HelpOnlineLevel2 (Title,IndexTopic, ParentId) VALUES (@title, @indexTopic, @parentId);SELECT SCOPE_IDENTITY();"; using (SqlConnection con = SQLConnect()) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Parameters.AddWithValue("@title", level2.Title); //cmd.Parameters.AddWithValue("@imageFile", level2.ImageFile); cmd.Parameters.AddWithValue("@indexTopic", level2.Index); cmd.Parameters.AddWithValue("@parentId", level2.ParentId); cmd.Connection = con; con.Open(); int insertedID = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return(insertedID); } } }
public void UpdateDBWithXMLLevel2(HelpLevel1 level1, string serverPathToSaveHTML, string serverPathOfHTMLLink) { foreach (HelpLevel2 level2 in level1.Children) { level2.ParentId = level1.Id; HelpLevel2 level2DB = helpLevel2BD.GetHelpLevel2ByTitleAndParentId(level2.Title, level1.Id); if (level2DB == null) { // add new this level1 level2.Id = helpLevel2BD.InsertLevel2(level2); } else { // this topic existed, just update index topic level2DB.Index = level2.Index; level2.Id = level2DB.Id; helpLevel2BD.UpdateIndexTopicLevel2(level2DB); } UpdateDBWithXMLLevel3(level2, serverPathToSaveHTML, serverPathOfHTMLLink); } }
public static List <HelpLevel1> ReadXML(string fileXML, string serverPathToSaveHTML, string serverPathOfHTMLLink) { List <HelpLevel1> listOfLevel1 = new List <HelpLevel1>(); try { XmlReaderSettings settings = new XmlReaderSettings(); settings.IgnoreComments = true; settings.IgnoreProcessingInstructions = true; settings.IgnoreWhitespace = true; XmlReader r = XmlReader.Create(fileXML, settings); while (r.NodeType != XmlNodeType.Element) { r.Read(); } XElement e = XElement.Load(r); if (e.HasElements) { IEnumerable <XElement> level1Topics = e.Elements(); int indexLevel1 = 0; foreach (XElement el1 in level1Topics) { if (el1.Name.ToString().Equals("item", StringComparison.OrdinalIgnoreCase)) { XElement itemLevel1 = XElement.Parse(el1.ToString()); HelpLevel1 level1 = new HelpLevel1(); indexLevel1 += 1; level1.Title = itemLevel1.Attribute("name").Value; level1.Index = indexLevel1; level1.ImageFile = ""; level1.Children = new List <HelpLevel2>(); // Handling level 2 IEnumerable <XElement> level2Topics = el1.Elements(); int indexLevel2 = 0; foreach (XElement el2 in level2Topics) { if (el2.Name.ToString().Equals("item", StringComparison.OrdinalIgnoreCase)) { XElement itemLevel2 = XElement.Parse(el2.ToString()); HelpLevel2 level2 = new HelpLevel2(); indexLevel2 += 1; level2.Title = itemLevel2.Attribute("name").Value; level2.Index = indexLevel2; level2.ImageFile = ""; level2.Children = new List <HelpLevel3>(); // Hanlde level 3 if ((itemLevel2.Attribute("link") != null) && (itemLevel2.Attribute("link").Value != null) && (!itemLevel2.Attribute("link").Value.Equals(""))) { // this node does not have child so this node is also level 3 with url is link value HelpLevel3 level3 = new HelpLevel3(); level3.Title = level2.Title; level3.URL = itemLevel2.Attribute("link").Value; level3.Index = 1; level2.Children.Add(level3); // download html file here //DownloadHTMLFile(level3.URL,serverPathToSaveHTML,serverPathOfHTMLLink); } else { // has level 3 IEnumerable <XElement> level3Topics = el2.Elements(); foreach (XElement el3 in level3Topics) { if (el3.Name.ToString().Equals("item", StringComparison.OrdinalIgnoreCase)) { XElement itemLevel3 = XElement.Parse(el3.ToString()); HelpLevel3 level3 = new HelpLevel3(); level3.Title = itemLevel3.Attribute("name").Value; level3.URL = itemLevel3.Attribute("link").Value; level3.Index = 1; level2.Children.Add(level3); // download html file here // DownloadHTMLFile(level3.URL, serverPathToSaveHTML, serverPathOfHTMLLink); } } } level1.Children.Add(level2); } } //Add level 1 listOfLevel1.Add(level1); } } } } catch (FileNotFoundException ex) { Console.Write(ex.Message); } return(listOfLevel1); }