C#: SQLに存在するテーブルに基づいてリストを作成する

## SAMPLE
SQLに存在するテーブル情報に基づき、下記のリストを作成する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/51324/f94ee1eb-63ad-0bde-64fd-e2387fb27a2f.png)

## REFERENCE
[Database Driven Dynamic Menu Control](https://www.codeproject.com/Tips/526004/Database-driven-dynamic-Menu-Control)

## SQL
``` sql

CREATE TABLE [master_data]
	( id INT IDENTITY(1,1)
	, parent_menu NVARCHAR(100)
	, child_menu NVARCHAR(100)
	, menu_url NVARCHAR(600)
	, PRIMARY KEY (id) )

INSERT INTO [master_data]	
	( parent_menu, child_menu, menu_url )
VALUES ('PARENT_MENU1','CHILD_MENU1','https://qiita.com/kinoshita_yuri'),
 ('PARENT_MENU1','CHILD_MENU2','https://qiita.com/kinoshita_yuri'),
 ('PARENT_MENU1','CHILD_MENU3','https://qiita.com/kinoshita_yuri'),
 ('PARENT_MENU2','CHILD_MENU1','https://qiita.com/kinoshita_yuri'),
 ('PARENT_MENU2','CHILD_MENU2','https://qiita.com/kinoshita_yuri'),
 ('PARENT_MENU2','CHILD_MENU3','https://qiita.com/kinoshita_yuri'),
 ('PARENT_MENU3','CHILD_MENU1','https://qiita.com/kinoshita_yuri'),
 ('PARENT_MENU3','CHILD_MENU2','https://qiita.com/kinoshita_yuri'),
 ('PARENT_MENU3','CHILD_MENU3','https://qiita.com/kinoshita_yuri')
```

## ASP.NET DESIGNER
```HTML
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
        <asp:Panel ID="Panel1" runat="server">
        </asp:Panel>
    </form>
</body>
```

## CSS
```CSS
#menu{
  float:left;
  width: 40%;
}
```

## C# 
```c#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;

namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {

        SqlConnection con = new SqlConnection("Data Source=ServerName; Initial Catalog=DataNameBase; uid=UserName; pwd=Password");

        protected void Page_Load(object sender, EventArgs e)
        {
            HtmlGenericControl main = UList("menu");
            SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT parent_menu FROM [master_data]", con);
            DataTable dttc = new DataTable();
            da.Fill(dttc);

            foreach (DataRow row in dttc.Rows)
            {

                da = new SqlDataAdapter("SELECT child_menu,menu_url FROM [master_data] WHERE parent_menu ='" + row["parent_menu"].ToString() + "' ", con);
                DataTable dtDist = new DataTable();
                da.Fill(dtDist);
                HtmlGenericControl sub_menu = LIListParent(row["parent_menu"].ToString(), "menu");
                HtmlGenericControl ul = new HtmlGenericControl("ul");
                foreach (DataRow r in dtDist.Rows)
                {
                    ul.Controls.Add(LIListChild(r["child_menu"].ToString(), "menu", r["menu_url"].ToString()));
                }
                sub_menu.Controls.Add(ul);
                main.Controls.Add(sub_menu);
            }
            Panel1.Controls.Add(main);
        }

        private HtmlGenericControl UList(string cssClass)
        {
            HtmlGenericControl ul = new HtmlGenericControl("ul");
            ul.Attributes.Add("class", cssClass);
            return ul;
        }

        private HtmlGenericControl LIListParent(string innerHtml, string rel)
        {
            HtmlGenericControl li = new HtmlGenericControl("li");
            li.Attributes.Add("rel", rel);
            li.InnerHtml = "<a >" + innerHtml + "</a>";
            return li;
        }

        private HtmlGenericControl LIListChild(string innerHtml, string rel, string url)
        {
            HtmlGenericControl li = new HtmlGenericControl("li");
            li.Attributes.Add("rel", rel);
            li.InnerHtml = "<a href=" + url + "  target=\"_brank\" >" + innerHtml + "</a></li>";
            return li;
        }

    }
}
```