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);
}
