public HelpLevel3 GetHelpLevel3ById(int id)
{
string query = "SELECT * FROM HelpOnlineLevel3 WHERE Id = " + id;
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];
HelpLevel3 obj = new HelpLevel3();
obj.Id = Convert.ToInt32(row["Id"]);
obj.Title = row["Title"].ToString();
obj.URL = row["URL"].ToString();
obj.ParentId = Convert.ToInt32(row["ParentId"]);
obj.Index = Convert.ToInt32(row["IndexTopic"]);
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 <HelpLevel3> ListAll()
{
string query = "SELECT * FROM HelpOnlineLevel3 order by parentId, IndexTopic";
List <HelpLevel3> list = new List <HelpLevel3>();
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];
HelpLevel3 obj = new HelpLevel3();
obj.Id = Convert.ToInt32(row["Id"]);
obj.Title = row["Title"].ToString();
obj.URL = row["URL"].ToString();
obj.ParentId = Convert.ToInt32(row["ParentId"]);
obj.Index = Convert.ToInt32(row["IndexTopic"]);
list.Add(obj);
}
}
}
}
return(list);
}
public List <HelpLevel3> LoadHelpLevel3ByParentId(int parentId)
{
string query = "SELECT * FROM HelpOnlineLevel3 WHERE parentID =" + parentId + " order by IndexTopic";
List <HelpLevel3> list = new List <HelpLevel3>();
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];
HelpLevel3 level3 = new HelpLevel3();
level3.Id = Convert.ToInt32(row["Id"]);
level3.Title = row["Title"].ToString();
level3.URL = row["URL"].ToString();
level3.ParentId = Convert.ToInt32(row["parentId"]);
HelpLevel2DB helpLevel2DB = new HelpLevel2DB();
level3.ParentTopic = helpLevel2DB.GetHelpLevel2ById(level3.ParentId);
level3.Index = 1;
list.Add(level3);
}
}
}
}
return(list);
}
public void UpdateIndexTopicLevel3(HelpLevel3 level3)
{
string query = "UPDATE HelpOnlineLevel3 SET IndexTopic=@indexTopic WHERE Id=@Id";
using (SqlConnection con = SQLConnect())
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.AddWithValue("@indexTopic", level3.Index);
cmd.Parameters.AddWithValue("@Id", level3.Id);
cmd.Connection = con;
con.Open();
//int insertedID = Convert.ToInt32(cmd.ExecuteScalar());
cmd.ExecuteNonQuery();
con.Close();
}
}
}
public void InsertLevel3(HelpLevel3 level3)
{
string query = "INSERT INTO HelpOnlineLevel3 (Title, URL, ParentId, IndexTopic) VALUES (@title,@url, @parentId, @indexTopic);";
using (SqlConnection con = SQLConnect())
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.AddWithValue("@title", level3.Title);
cmd.Parameters.AddWithValue("@url", level3.URL);
cmd.Parameters.AddWithValue("@parentId", level3.ParentId);
cmd.Parameters.AddWithValue("@indexTopic", level3.Index);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
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);
}