Ajax - Dynamic Accordion control using xml and sql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Xml.Schema;
using System.Xml;
using AjaxControlToolkit;
using System.IO;
using System.Data.SqlClient;


public partial class HM28737 : System.Web.UI.UserControl
{

public static string _datasource;
public static string DS_type
{
get { return _datasource; }

set { _datasource = value; }
}

private void accordion_XML()
{
Accordion accordion = new Accordion();
accordion.ID = "AccordionID";
accordion.SelectedIndex = 0;
accordion.FadeTransitions = true;
accordion.FramesPerSecond = 50;
accordion.TransitionDuration = 50;
accordion.SuppressHeaderPostbacks = true;
accordion.HeaderCssClass = "accordionHeader";
accordion.HeaderSelectedCssClass = "accordionHeaderSelected";
accordion.ContentCssClass = "accordionContent";
accordion.RequireOpenedPane = false;


string _path = HttpContext.Current.Server.MapPath(_datasource.ToString());

System.IO.FileStream stream = new FileStream(_path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

XmlDocument document = new XmlDocument();

document.Load(stream);

XmlNodeList nodeList = document.GetElementsByTagName("menuitem");


for (int i = 0; i < nodeList.Count; i++)
{

AccordionPane accorPane = new AccordionPane();
Label lbl = new Label();
lbl.ID = "lblControl";
lbl.Text = nodeList[i].Attributes["name"].Value;
accorPane.HeaderContainer.Controls.Add(lbl);

for (int j = 0; j < nodeList[i].ChildNodes.Count; j++)
{



string name = nodeList[i].ChildNodes[j].Attributes["name"].Value;
string url = nodeList[i].ChildNodes[j].Attributes["url"].Value;

LinkButton lbtn_childname = new LinkButton();

lbtn_childname.ID = "lbtn_child";
lbtn_childname.Text = name;
lbtn_childname.PostBackUrl = url;
accorPane.ContentContainer.Controls.Add(lbtn_childname);
accorPane.ContentContainer.Controls.Add(new LiteralControl("
"));
}
accordion.Panes.Add(accorPane);


}
PlaceHolder1.Controls.Add(accordion);
stream.Close();


}

private void accordion_SQL()
{

Accordion accordion1 = new Accordion();
accordion1.ID = "AccordionID1";
accordion1.SelectedIndex = 0;
accordion1.FadeTransitions = true;
accordion1.FramesPerSecond = 50;
accordion1.TransitionDuration = 50;
accordion1.SuppressHeaderPostbacks = true;
accordion1.HeaderCssClass = "accordionHeader";
accordion1.HeaderSelectedCssClass = "accordionHeaderSelected";
accordion1.ContentCssClass = "accordionContent";
accordion1.RequireOpenedPane = false;


SqlConnection con = new SqlConnection("Data Source=rmsw41s0695;Initial Catalog=temp;Integrated Security=True");
con.Open();
SqlCommand cmd_sitetype = new SqlCommand("SELECT DISTINCT sitetype FROM HM28737", con);

cmd_sitetype.ExecuteNonQuery();

SqlDataAdapter da = new SqlDataAdapter(cmd_sitetype);
DataSet ds = new DataSet();
da.Fill(ds);


for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
AccordionPane accorPane1 = new AccordionPane();
Label lbl = new Label();
lbl.ID = "lblControl";
lbl.Text = ds.Tables[0].Rows[i].ItemArray[0].ToString();

accorPane1.HeaderContainer.Controls.Add(lbl);

SqlCommand cmd_sql = new SqlCommand("SELECT name, url FROM HM28737 WHERE (sitetype = '" + ds.Tables[0].Rows[i].ItemArray[0].ToString() + "')", con);

cmd_sql.ExecuteNonQuery();

SqlDataAdapter da_sql = new SqlDataAdapter(cmd_sql);
DataSet ds_sql = new DataSet();
da_sql.Fill(ds_sql);
cmd_sql.Dispose();

for (int j = 0; j < ds_sql.Tables[0].Rows.Count; j++)
{
string name = ds_sql.Tables[0].Rows[j].ItemArray[0].ToString();
string url = ds_sql.Tables[0].Rows[j].ItemArray[1].ToString();
LinkButton lbtn_childname = new LinkButton();
lbtn_childname.ID = "lbtn_child";
lbtn_childname.Text = name;
lbtn_childname.PostBackUrl = url;
accorPane1.ContentContainer.Controls.Add(lbtn_childname);
accorPane1.ContentContainer.Controls.Add(new LiteralControl("
"));
}
accordion1.Panes.Add(accorPane1);
ds_sql.Clear();
}
PlaceHolder1.Controls.Add(accordion1);
con.Close();
cmd_sitetype.Dispose();
ds.Clear();

}

protected void Page_Load(object sender, EventArgs e)

{

Response.Write("

");
if (_datasource.Contains(".xml"))
{
accordion_XML();
}
else
{
accordion_SQL();
}
Response.Write("

");

}


}