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