nested accordion in asp.net with values from Sql server database

See more:

ASP.NET

Javascript

CSS

SQL-Server

, +

Problem Definition

I have built an application which will generate accordion menu dynamically with the values from Sql Server database.The problem here is i have hard-coded 3-levels of nested "for" loop for 3-levels of iteration in the menu.If the menu has n-levels of iteration it is very hard to write n-levels of nested "for" loop manually So I need an optimized code which can iterate n-levels of sub-menus.

Eg: "Project Management" is root menu which has p_no as '0'(Database structure is given below).

Report and Inquire(11) and Code and Setup Maintenance(12) which has P_no as '2' are sub-menu of Project Management.

Vendor Certificate of Insurance Listing(27) which has p_no as '11' is sub-menu of Report and Inquire(11).

In above scenario there will be 3 iteration to display all root menu, sub-menu and leaf menu of Sub-menu .

If i add one more sub-menu as "CCL" under "Vendor Certificate of Insurance Listing(27)" then I have to write one more level of nested for loop to display "CCL"(Coding is given below). So I need an optimized code which can iterate n-levels of sub-menus.

please help me with this scenario. Thank you..


My Coding:

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["MenuStructureDB"].ToString());
        SqlCommand cmd = new SqlCommand("SELECT MenuNo,MenuName FROM SampleMenu WHERE MenuLinkNo=0", cn);
        ArrayList head_array = new ArrayList();//Array list to hold values of Root Menu Heading
        int i = 0;
        try
        {
            cn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();//this data reader contains MenuName which has MenuLinkNo as 0
            while (rdr.Read())
            {
                head_array.Insert(i, rdr[1].ToString());/*Inserting MenuNames to Array list head to access
                                                 the data values even after database connection was closed */
            }
        }
        catch (SqlException ae)
        {
            //Response.Write(ae);

        }
        finally
        {
            cn.Close();
        }
        head_array.Sort();
        int count_head = head_array.Count;
        Response.Write("<form id=\"form1\" runat=\"server\">");
        Response.Write(" <div id=\"content\">");
        for (int x = 0; x < count_head; x++)
        {
            ArrayList SubMenu_array = new ArrayList();
            Response.Write("<div class=\"panel\">");
            Response.Write("<p class=\"flip\" >" + head_array[x] + " </p>");//Root Menu values are under <p> tag
            Response.Write("<ul>");

            SqlCommand SubMenu = new SqlCommand("SELECT MenuName FROM SampleMenu WHERE MenuLinkNo=(select MenuNo from SampleMenu where MenuName='" + head_array[x] + "')", cn);
            try
            {
                cn.Open();
                SqlDataReader SubMenuRdr = SubMenu.ExecuteReader();
                int j = 0;

                while (SubMenuRdr.Read())
                {

                    SubMenu_array.Insert(j, SubMenuRdr[0].ToString());

                }

            }
            catch (SqlException ae)
            {
                //Response.Write(ae);

            }
            finally
            {
                cn.Close();
            }
            SubMenu_array.Sort();
            int count_SubMenu = SubMenu_array.Count;
            for (int y = 0; y < count_SubMenu; y++)
            {
                string SubMenu_temp = SubMenu_array[y].ToString();
                ArrayList SubMenuHeadDummy_array = new ArrayList();
            found:
                ArrayList SubMenuHead_array = new ArrayList();
                SqlCommand SubMenuHead = new SqlCommand("SELECT MenuName FROM SampleMenu WHERE MenuLinkNo=(select MenuNo from SampleMenu where MenuName='" + SubMenu_temp + "')", cn);
                try
                {
                    cn.Open();
                    SqlDataReader SubMenuHeadRdr = SubMenuHead.ExecuteReader();
                    int j = 0;

                    while (SubMenuHeadRdr.Read())
                    {

                        SubMenuHead_array.Insert(j, SubMenuHeadRdr[0].ToString());
                        SubMenuHeadDummy_array.Insert(j, SubMenuHeadRdr[0].ToString());

                    }

                }
                catch (SqlException ae)
                {
                   // Response.Write(ae);
                }
                finally
                {
                    cn.Close();
                }
                SubMenuHead_array.Sort();
                int count_SubMenuHead = SubMenuHead_array.Count;
                //int count_SubMenuHeadDummy = SubMenuHeadDummy_array.Count;
                // Response.Write("<li>" + count_SubMenuHead + "</li>");
                if (count_SubMenuHead > 0)
                {
                    Response.Write("<p class=\"flip\" >." + SubMenu_temp + " </p>");

                    //SubMenuHeadDummy_array.Remove(SubMenu_temp);
                    Response.Write("<ul>");
                    for (int z = 0; z < count_SubMenuHead; z++)
                    {
                        Response.Write("<li style=\"border-bottom:solid 0.5px #69162A\">" + SubMenuHead_array[z].ToString() + "</li>");
                    }
                    Response.Write("</ul>");
                }
                else
                {

                    //SubMenuHeadDummy_array.Remove(SubMenu_temp);
                    Response.Write("<li>" + SubMenu_temp + "</li>");
                    //if (SubMenuHeadDummy_array.Count > 0)
                    //{
                    //    for (int c = 0; c < SubMenuHeadDummy_array.Count; c++)
                    //    {
                    //        SubMenu_temp = SubMenuHeadDummy_array[c].ToString();
                    //        goto found;
                    //    }
                    //}
                }

            }
            Response.Write("</ul>");
            Response.Write("</div>");//---Close Div for Panel_1
        }

        Response.Write("</div>");//---Close Div for Content
        Response.Write("</form>");


    }
}

Database Structure:
Menu_no P_no Menu_name
1 0 CRM
2 0 Project Management
3 0 Estimating and Proposals
4 0 Sales Order Entry
5 0 Shipping
6 0 Invoicing
7 0 Product and Process
8 1 Contact Management
10 1 Service Call Status Report
11 2 Report and Inquires
12 2 Code and Setup Maintenance
13 3 Estimating Maintenance
14 3 Estimating Maintenance Limited
15 3 Reset Quote Expiration Date
17 3 Proposal Print
18 3 Proposal Reprint
19 3 Delete a Proposal
20 3 Quote Print
21 3 Copy Quote
22 3 Status of all Quotes
23 3 Quote Pipeline Report
24 3 Sign Template Maintenance
25 3 Reports and Inquires
26 3 Codes and Setup Maintenance
27 11 Vendor Certificate of Insurance Listing
28 12 Project Status Codes
29 12 Mall Location Maint
30 12 Landlord File Maint
31 12 Tenant Coordinator Maint
32 4 Sales Orders Entry
33 4 Credit Memo Entry
34 4 Invoice Only
35 4 Create Sales Order From Proposal
36 4 Sales Order Credit Approval
37 4 Change Customer on Sales Order
38 4 Print Sales Order
39 4 Sales Order Close
40 4 Report and Inquire
41 4 Sales Analysis
42 4 Inquiries
43 4 Sales Reports
44 4 Sales and Commission Reports
45 4 Codes and Setup Maintenance
46 5 Generate Shiplists
47 5 Record Shipping Information
48 5 Enter Shipment Tracking Information
49 5 View Status of Shipments
50 6 INACTIVE - View Uninvoiced Shipments
51 6 Invoice Only
52 6 Credit Memo Entries
53 6 Recurring Invoice Maintenance
54 6 One Step Invoicing
55 6 Recurring Invoice Release
56 6 Process Finance Charges
57 6 Print Invoices
58 6 Post Invoices (to AR)
59 6 Unpost Invoice (from AR)
60 6 Reports and Inquirie
61 7 Codes File Maintenance
62 7 Processes
64 7 Reports

here the menus which has P_no as 0 are parent menu