More than 5 years have passed since last update.

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

Last updated at 2019-10-01Posted at 2019-09-30

SAMPLE

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

REFERENCE

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

<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
        <asp:Panel ID="Panel1" runat="server">
        </asp:Panel>
    </form>
</body>

CSS

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

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

    }
}

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up