//Method for Adding HelpLevel1 public int InsertLevel1(HelpLevel1 level1) { string query = "sp_AddHelpOnlineLevel1"; // Stored procedure in DB int new_ID = -1; using (SqlConnection con = new SqlConnection(cs)) { using (SqlCommand cmd = new SqlCommand(query, con)) { cmd.CommandType = CommandType.StoredProcedure; //SqlParameter outPutVal = new SqlParameter("@NewId", SqlDbType.Int); cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = level1.Title; cmd.Parameters.Add("@imageFile", SqlDbType.VarChar).Value = (level1.ImageFile == null?"": level1.ImageFile); cmd.Parameters.Add("@indexTopic", SqlDbType.Int).Value = level1.Index; cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output; con.Open(); cmd.ExecuteNonQuery(); con.Close(); new_ID = Convert.ToInt32(cmd.Parameters["@NewId"].Value); return(new_ID); } } }
public List <HelpLevel1> ListAllDESC() { string query = "SELECT * FROM HelpOnlineLevel1 order by IndexTopic DESC"; List <HelpLevel1> list = new List <HelpLevel1>(); using (SqlConnection con = new SqlConnection(cs)) { 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]; HelpLevel1 obj = new HelpLevel1(); obj.Id = Convert.ToInt32(row["Id"]); obj.Title = row["Title"].ToString(); obj.ImageFile = row["ImageFile"].ToString(); obj.Index = Convert.ToInt32(row["IndexTopic"]); list.Add(obj); } } } } return(list); }
// Get Help Level1 by Title public HelpLevel1 GetHelpLevel1ByTitle(string title) { string query = "SELECT * FROM HelpOnlineLevel1 WHERE title = '" + title + "'"; using (SqlConnection con = new SqlConnection(cs)) { 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]; HelpLevel1 obj = new HelpLevel1(); obj.Id = Convert.ToInt32(row["Id"]); obj.Title = row["Title"].ToString(); obj.ImageFile = row["ImageFile"].ToString(); obj.Index = Convert.ToInt32(row["IndexTopic"]); return(obj); } else { return(null); } } } } }
//Method for Update Index of a topic help level 1 public void UpdateIndexTopicLevel1(HelpLevel1 level1) { string query = "UPDATE HelpOnlineLevel1 SET IndexTopic=@indexTopic WHERE Id=@Id"; using (SqlConnection con = new SqlConnection(cs)) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Parameters.AddWithValue("@indexTopic", level1.Index); cmd.Parameters.AddWithValue("@Id", level1.Id); cmd.Connection = con; con.Open(); //int insertedID = Convert.ToInt32(cmd.ExecuteScalar()); cmd.ExecuteNonQuery(); con.Close(); } } }
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 void UpdateDBWithXML(List <HelpLevel1> listOfLevel1FromXMLFile, string serverPathToSaveHTML, string serverPathOfHTMLLink) { foreach (HelpLevel1 level1 in listOfLevel1FromXMLFile) { HelpLevel1 level1DB = helpLevel1BD.GetHelpLevel1ByTitle(level1.Title); if (level1DB == null) { // add new this level1 level1.Id = helpLevel1BD.InsertLevel1(level1); } else { // this topic existed, just update index topic level1DB.Index = level1.Index; level1.Id = level1DB.Id; helpLevel1BD.UpdateIndexTopicLevel1(level1DB); } UpdateDBWithXMLLevel2(level1, serverPathToSaveHTML, serverPathOfHTMLLink); } DeleteIndexTopicZero(); }
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); }