我们需要将"Setup"结构存储在SQL Server数据库中。"设置"有多个"通道"，这些"通道"可以有子通道。每个通道可以有多个"变量"，其中包含带有值的标准属性。
此结构在 C# 项目中定义。因此，有一个包含列表的 Setup 对象，有一个包含列表和一个列表的通道对象。
我实际上已经通过创建一个设置表、一个带有设置外键和自引用的通道表以及一个带有通道外键的变量表来完成该项目。
c# 程序需要一些功能来列出现有设置、加载和保存。
我创建了一个存储过程来保存一个接受 3 个表参数的设置：安装程序（应仅包含一行）、通道和变量。输入参数仅包含实体的自然键。然后，存储过程将它们插入或更新到数据库中。
为了加载一个设置，我创建了一个执行 3 个选择的存储过程。然后 c# 通过 SqlCommand.ExecuteReader（） 读取它们。
问：保存设置存储过程太复杂了。以下是简要的步骤。由于通道的层次结构，这一切都变得更加复杂，因为我需要逐步处理层次结构的每个级别：
Parameter validity check
Check if Setup exists
if yes, update, keep its id (SetupID)
if not, insert, keep its id (SetupID)
Update SetupID in parameter tables Channel,Variable
Fetch ChannelID for channels given in parameters that do exist in the database
Fetch VariableID for variables given in parameters that do exist in the database
Delete channels and variables that do exist in database for that setup, but were not provided in the parameters
Channels should be recursively deleted, first the ones without children, then their parents etc.
Insert new Channels (given in parameters, not existing in the database)
Do it recursively, first for the ones without children, then, their parents, etc.
Every time you do some insert, update the respective ChannelID in the parameter tables
Update Channels given in parameters that do exist in the database
Insert new Variables (given in parameters, not existing in the database)
Update Variables given in parameters that do exist in the database
它转换为大约 250 行 T-SQL 代码，我花了 2 天时间编写和错误检查。它现在工作正常，但我相信可以用更简单的方式完成。有什么想法/意见吗？
PS：以XML保存不是一种选择，因为该项目的关键思想是能够查看所有属性并对其进行查询，验证，制作报告和统计信息等。
提前感谢！
更新：表创建脚本
CREATE TABLE [dbo].[Setup](
[SetupID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Type] [nvarchar](4000) NULL,
[Info] [nvarchar](4000) NULL,
[FirstInserted] [datetime] NULL,
[LastUpdated] [datetime] NULL,
CONSTRAINT [PK$Setup] PRIMARY KEY CLUSTERED
([SetupID] 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
CREATE UNIQUE NONCLUSTERED INDEX [UQ$Setup$Name] ON [dbo].[Setup]
([Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Channel](
[ChannelID] [int] IDENTITY(1,1) NOT NULL,
[SetupID] [int] NOT NULL,
[Type] [nvarchar](50) NOT NULL,
[ParentChannelID] [int] NULL,
[Sequence] [int] NOT NULL,
[PanelIdx] [nvarchar](4000) NULL,
[Visible] [nvarchar](4000) NULL,
[FirstInserted] [datetime] NULL,
[LastUpdated] [datetime] NULL,
CONSTRAINT [PK$Channel] PRIMARY KEY CLUSTERED
([ChannelID] 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].[Channel] WITH CHECK ADD CONSTRAINT [FK$Channel$Channel] FOREIGN KEY([ParentChannelID])
REFERENCES [dbo].[Channel] ([ChannelID])
GO
ALTER TABLE [dbo].[Channel] CHECK CONSTRAINT [FK$Channel$Channel]
GO
ALTER TABLE [dbo].[Channel] WITH CHECK ADD CONSTRAINT [FK$Channel$Setup] FOREIGN KEY([SetupID])
REFERENCES [dbo].[Setup] ([SetupID])
GO
ALTER TABLE [dbo].[Channel] CHECK CONSTRAINT [FK$Channel$Setup]
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ$Channel$SetupID_Type] ON [dbo].[Channel]
([SetupID] ASC,[Type] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ$Channel$SetupID_ParentChannelID_Sequence] ON [dbo].[Channel]
([SetupID] ASC, [ParentChannelID] ASC, [Sequence] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Variable](
[VariableID] [int] IDENTITY(1,1) NOT NULL,
[ChannelID] [int] NOT NULL,
[Key] [nvarchar](50) NOT NULL,
[Sequence] [int] NOT NULL,
[DefaultText] [nvarchar](4000) NULL,
[IONumber] [nvarchar](4000) NULL,
[LinkType] [nvarchar](4000) NULL,
[DataType] [nvarchar](4000) NULL,
[ImageTrue] [nvarchar](4000) NULL,
[ImageFalse] [nvarchar](4000) NULL,
[FormatString] [nvarchar](4000) NULL,
[GroupBoxIdx] [nvarchar](4000) NULL,
[ControlIdx] [nvarchar](4000) NULL,
[PlcVar] [nvarchar](4000) NULL,
[Value] [nvarchar](4000) NULL,
[DefaultValue] [nvarchar](4000) NULL,
[MinValue] [nvarchar](4000) NULL,
[MaxValue] [nvarchar](4000) NULL,
[Measure] [nvarchar](4000) NULL,
[KeyIdx] [nvarchar](4000) NULL,
[Behavior] [nvarchar](4000) NULL,
[TrueEnter] [nvarchar](4000) NULL,
[ShowCheckDigit] [nvarchar](4000) NULL,
[ShowOverflow] [nvarchar](4000) NULL,
[Visible] [nvarchar](4000) NULL,
[ReadOnly] [char](1) NULL,
[Dynamic] [char](1) NULL,
[FirstInserted] [datetime] NULL,
[LastUpdated] [datetime] NULL,
CONSTRAINT [PK$Variable] PRIMARY KEY CLUSTERED
([VariableID] 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
CREATE UNIQUE NONCLUSTERED INDEX [UQ$Variable$ChannelID_Key] ON [dbo].[Variable]
([ChannelID] ASC, [Key] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ$Variable$ChannelID_Sequence] ON [dbo].[Variable]
([ChannelID] ASC, [Sequence] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Variable] WITH CHECK ADD CONSTRAINT [FK$Variable$Channel] FOREIGN KEY([ChannelID])
REFERENCES [dbo].[Channel] ([ChannelID])
GO
ALTER TABLE [dbo].[Variable] CHECK CONSTRAINT [FK$Variable$Channel]
GO
ALTER TABLE [dbo].[Variable] WITH CHECK ADD CONSTRAINT [CK$ChannelVariable$Dynamic_TF] CHECK (([Dynamic]='T' OR [Dynamic]='F'))
GO
ALTER TABLE [dbo].[Variable] CHECK CONSTRAINT [CK$ChannelVariable$Dynamic_TF]
GO
ALTER TABLE [dbo].[Variable] WITH CHECK ADD CONSTRAINT [CK$ChannelVariable$ReadOnly_TF] CHECK (([ReadOnly]='T' OR [ReadOnly]='F'))
GO
ALTER TABLE [dbo].[Variable] CHECK CONSTRAINT [CK$ChannelVariable$ReadOnly_TF]
GO
研究使用对象关系映射器。
.Net 中的一些示例是实体框架（来自 Microsoft）、NHibernate（开源）和 LLBLGen（商业）。
还有其他的，每个都有其优点和缺点。