ITブログ時々なんでもブログ

１．データベースの構造

CREATE TABLE [dbo].[T_Category_L](
    [LargeCode] [int] NOT NULL,
    [LargeName] [nvarchar](50) NULL,
    [Deleted]   [bit] NOT NULL,
    [Updated]   [datetime] NOT NULL,
    [Created]   [datetime] NOT NULL,
 CONSTRAINT [PK_T_Category_L] PRIMARY KEY CLUSTERED 
(
    [LargeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[T_Category_L] ADD  CONSTRAINT [DF_T_Category_L_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

CREATE TABLE [dbo].[T_Category_M](
    [MiddleCode] [int] NOT NULL,
    [MiddleName] [nvarchar](50) NULL,
    [LargeCode]  [int] NOT NULL,
    [Deleted]    [bit] NOT NULL,
    [Updated]    [datetime] NOT NULL,
    [Created]    [datetime] NOT NULL,
 CONSTRAINT [PK_T_Category_M] PRIMARY KEY CLUSTERED 
(
    [MiddleCode] ASC,
    [LargeCode]  ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[T_Category_M] ADD  CONSTRAINT [DF_T_Category_M_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

CREATE TABLE [dbo].[T_Category_S](
    [SmallCode]  [int] NOT NULL,
    [SmallName]  [nvarchar](50) NULL,

    [LargeCode]  [int] NOT NULL,
    [MiddleCode] [int] NOT NULL,
    [Deleted]    [bit] NOT NULL,
    [Updated]    [datetime] NOT NULL,
    [Created]    [datetime] NOT NULL,
 CONSTRAINT [PK_T_Category_S] PRIMARY KEY CLUSTERED 
(
    [SmallCode]  ASC,
    [LargeCode]  ASC,
    [MiddleCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[T_Category_S] ADD  CONSTRAINT [DF_T_Category_S_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

２．データセットの作成方法

Public Class DataSetTest
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then

            Dim dbUtil As New DbUtility()
            Dim ds1 As DataSet = Nothing
            Dim ds2 As DataSet = Nothing
            Dim ds3 As DataSet = Nothing
            Dim sql1 As New StringBuilder
            Dim sql2 As New StringBuilder
            Dim sql3 As New StringBuilder

            '大カテゴリ取得
            sql1.AppendLine(" SELECT  LargeCode     ")
            sql1.AppendLine("        ,LargeName     ")
            sql1.AppendLine(" FROM    T_Category_L  ")
            sql1.AppendLine(" ; ")

            '中カテゴリ取得
            sql2.AppendLine(" SELECT  MiddleCode    ")
            sql2.AppendLine("        ,MiddleName    ")
            sql2.AppendLine("        ,LargeCode     ")
            sql2.AppendLine(" FROM    T_Category_M  ")
            sql2.AppendLine(" ; ")

            '小カテゴリ取得
            sql3.AppendLine(" SELECT  SmallCode     ")
            sql3.AppendLine("        ,SmallName     ")
            sql3.AppendLine("        ,LargeCode     ")
            sql3.AppendLine("        ,MiddleCode    ")
            sql3.AppendLine(" FROM    T_Category_S  ")
            sql3.AppendLine(" ; ")

            dbUtil.Connect()
            dbUtil.ClearParams()

            'データを取得する
            ds1 = dbUtil.ExecuteFill(sql1.ToString, "CategoryLarge")
            ds2 = dbUtil.ExecuteFill(sql2.ToString, "CategoryMiddle")
            ds3 = dbUtil.ExecuteFill(sql3.ToString, "CategorySmall")

            '既存のデータセットから削除する
            Dim dt1 As DataTable = ds1.Tables("CategoryLarge")
            Dim dt2 As DataTable = ds2.Tables("CategoryMiddle")
            Dim dt3 As DataTable = ds3.Tables("CategorySmall")
            ds2.Tables.Remove(ds2.Tables("CategoryMiddle"))
            ds3.Tables.Remove(ds3.Tables("CategorySmall"))

            'まとめ用のデータセットに追加する
            ds1.Tables.Add(dt2)
            ds1.Tables.Add(dt3)

            'リレーションを追加する
            'リレーション（親→子）（単数列）
            ds1.Relations.Add("Category_Large-Middle",
                              dt1.Columns("LargeCode"),
                              dt2.Columns("LargeCode"))
            ds1.Relations(0).Nested = True

            'リレーション（子→孫）（複数列）
            ds1.Relations.Add("Category_Middle-Small",
                              New DataColumn() {dt2.Columns("LargeCode"), dt2.Columns("MiddleCode")},
                              New DataColumn() {dt3.Columns("LargeCode"), dt3.Columns("MiddleCode")}
                              )
            ds1.Relations(1).Nested = True

            ds1.AcceptChanges()

            'データバインド
            Me.rptLargeCategory.DataSource = dt1
            Me.rptLargeCategory.DataBind()

        End If

    End Sub

End Class