コメント
コメントの投稿
※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。
入力されていないコメントには返信しませんのであらかじめご了承くださいませ。
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
※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。
入力されていないコメントには返信しませんのであらかじめご了承くださいませ。