如何使用单个存储过程保存主从关系中的对象图
4.85/5 (42投票s)
一种使用 ADO.NET 和表值参数通过单个存储过程将 C# 对象图持久化到数据库的方法
这是本系列的第一篇文章。接下来的文章
引言
我有一个 Web 客户端，它以 JSON 格式将数据发送到我的 ASP.NET 应用程序。数据是主-明细关系中的对象图，甚至是一个对象图的集合。数据被反序列化为 Web API 控制器中的 C# 对象图。我想使用 ADO.NET 和一个存储过程来保存该 C# 对象图。并且我不想使用 GUID、XML 和 EF！
背景
假设 Web 客户端发送的对象图包含三个对象
GrandRecordRecordChildRecord
设它是一个 GrandRecords 的集合，其中
- 每个
GrandRecord都有一个Records的集合 - 每个
Record都有一个ChildRecords的集合 Id值是整数，由数据库自动生成- 并且当一个对象尚未保存在数据库中时，
Id的值为0
以下是对象图集合（或对象图）的示例
Id, Name
GrandRecord 1, (A)
Record |-- 2, (A)A
ChildRecord |-- 3, (A)Aa
ChildRecord |-- 0, (A)Ab
Record |-- 0, (A)B
ChildRecord |-- 0, (A)Ba
ChildRecord |-- 0, (A)Bb
GrandRecord 0, (B)
Record |-- 0, (B)A
或者相同的 JSON 格式
grandRecords: [
{
id: 1,
name: "(A)",
records: [
{
id: 2,
name: "(A)A",
childRecords: [
{
id: 3,
name: "(A)Aa",
},
{
id: 0,
name: "(A)b",
},
]
},
{
id: 0,
name: "(A)B",
childRecords: [
{
id: 0,
name: "(A)Ba",
},
{
id: 0,
name: "(A)Bb",
},
]
}
]
},
{
id: 0,
name: "(B)",
records: [
{
id: 0,
name: "(B)A",
childRecords: []
}
]
}
]
在 Web 服务器的 ASP.NET 控制器中，上面的 JSON 字符串被反序列化为三个类的对象图
public class GrandRecord
{
public Int32 Id { get; set; }
public String Name { get; set; }
public IList<Record> Records { get; set; }
}
public class Record
{
public Int32 Id { get; set; }
public Int32 GrandRecordId { get; set; }
public String Name { get; set; }
public IList<ChildRecord> ChildRecords { get; set; }
}
public class ChildRecord
{
public Int32 Id { get; set; }
public Int32 RecordId { get; set; }
public String Name { get; set; }
}
现在，必须使用一个存储过程将对象图保存到三个数据库表中
create table dbo.GrandRecords
(
Id int not null identity primary key clustered,
Name varchar(30) not null
);
create table dbo.Records
(
Id int not null identity primary key clustered,
GrandRecordId int not null ,
Name varchar(30) not null ,
foreign key (GrandRecordId) references dbo.GrandRecords (Id) on delete cascade
);
create table dbo.ChildRecords
(
Id int not null identity primary key clustered,
RecordId int not null ,
Name varchar(30) not null ,
foreign key (RecordId) references dbo.Records (Id) on delete cascade
);
问题是如何？
解决方案
当然，存储过程的表值参数是答案的一部分！
拥有这些用户定义表类型
create type dbo.GrandRecordTableType as table
(
Id int not null primary key clustered,
Name varchar(30) not null
);
create type dbo.RecordTableType as table
(
Id int not null primary key clustered,
GrandRecordId int not null ,
Name varchar(30) not null
);
create type dbo.ChildRecordTableType as table
(
Id int not null primary key clustered,
RecordId int not null ,
Name varchar(30) not null
);
用于保存上述对象图的存储过程以...开始
create procedure dbo.SaveGrandRecords
@GrandRecords dbo.GrandRecordTableType readonly,
@Records dbo.RecordTableType readonly,
@ChildRecords dbo.ChildRecordTableType readonly
as
因此，我们必须按类型（GrandRecord、Record 和 ChildRecord）收集所有数据，创建 ADO.NET DataTables 并将它们传递给存储过程。
但是！因为我们的数据库表通过外键 GrandRecordId 和 RecordId 链接，我们必须在将对象图转换为单独的 DataTables 的过程中以某种方式持久化该链接。
更重要的是，新对象的标识必须是唯一的！否则，我们将无法区分 GrandRecord (A) 的 Records 和 GrandRecord (B) 的 Records。
然而，正如我们记得的那样，新对象的 Id 为 0！
为了解决这个问题，如果对象 Ids 等于 0，我们为它们分配一个不断递增的负数标识
var id = int.MinValue;
foreach (var grandRecord in grandRecords)
{
if (grandRecord.Id == 0)
grandRecord.Id = id++;
foreach (var record in grandRecord.Records)
{
if (record.Id == 0)
record.Id = id++;
record.GrandRecordId = grandRecord.Id;
foreach (var childRecord in record.ChildRecords)
{
if (childRecord.Id == 0)
childRecord.Id = id++;
childRecord.RecordId = record.Id;
}
}
}
现在是填充 DataTables 的时候了。
例如，这是准备一个包含 Records 数据的 DataTable 的方法
var recordTable = new DataTable("RecordTableType");
recordTable.Columns.Add( "Id" , typeof( Int32 ));
recordTable.Columns.Add( "GrandRecordId" , typeof( Int32 ));
recordTable.Columns.Add( "Name" , typeof( String ));
var records = grandRecords.SelectMany(gr => gr.Records);
foreach(var record in records)
{
table.Rows.Add(new object[] {record.Id, record.GrandRecordId, record.Name});
}
因此，在 DataTables 准备好之后，存储过程将在表值参数中接收以下数据
@GrandRecords
| ID | 名称 |
| 1 | (A) |
| -2147483648 | (B) |
@Records
| ID | GrandRecordId | 名称 |
| 2 | 1 | (A)A |
| -2147483647 | 1 | (A)B |
| -2147483646 | -2147483648 | (B)A |
@ChildRecords
| ID | RecordId | 名称 |
| 3 | 2 | (A)Aa |
| -2147483645 | 2 | (A)Ab |
| -2147483644 | -2147483647 | (A)Ba |
| -2147483643 | -2147483647 | (A)Bb |
对象图保存技术
为了更新现有数据、插入新数据和删除旧数据，SQL Server 使用 MERGE 语句。
MERGE 语句具有 OUTPUT 子句。MERGE 语句中的 OUTPUT 可以收集刚插入的 Ids 以及源（参数）表中的 Ids。
因此，“保存所有三个表并具有正确的外键”的技术是收集第一个表中的 InsertedId - ParamId 对，并将这些值翻译给第二个表。然后对第二个和第三个表执行相同的操作。
- 如果一个记录存在于表中，
MERGE将执行UPDATE，inserted.Id和source.Id等于现有Id。 - 如果一个记录不存在于表中，
MERGE将执行INSERT，inserted.Id等于新的Id，而source.Id等于负数标识。 - 如果一个记录不存在于源（参数）表中，
MERGE将执行DELETE，inserted.Id和source.Id等于NULL，但deleted.Id具有已删除记录的Id。
这是保存我们对象图的存储过程
create procedure dbo.SaveGrandRecords
@GrandRecords dbo.GrandRecordTableType readonly,
@Records dbo.RecordTableType readonly,
@ChildRecords dbo.ChildRecordTableType readonly
as
begin
set nocount on;
declare @GrandRecordIds table ( -- translation table
InsertedId int primary key,
ParamId int unique
);
declare @RecordIds table ( -- translation table
InsertedId int primary key,
ParamId int unique,
[Action] nvarchar(10)
);
-- save GrandRecords
merge into dbo.GrandRecords as target
using
(
select Id, Name from @GrandRecords
)
as source on source.Id = target.Id
when matched then
update set
Name = source.Name
when not matched by target then
insert ( Name )
values ( source.Name )
output -- collecting translation Ids
inserted.Id,
source.Id
into @GrandRecordIds (
InsertedId ,
ParamId );
-- save Records
merge into dbo.Records as target
using
(
select
Id ,
GrandRecordId = ids.InsertedId, -- Id translation target
Name
from
@Records r
inner join @GrandRecordIds ids
on ids.ParamId = r.GrandRecordId -- Id translation source
)
as source on source.Id = target.Id
when matched then
update set
GrandRecordId = source.GrandRecordId,
Name = source.Name
when not matched by target then
insert (
GrandRecordId ,
Name )
values (
source.GrandRecordId ,
source.Name )
when not matched by source
and target.GrandRecordId in (select InsertedId from @GrandRecordIds) then
delete
output -- collecting translation Ids
isnull(inserted.Id, deleted.Id),
isnull(source.Id, deleted.Id),
$action
into @RecordIds (
InsertedId ,
ParamId ,
[Action] );
delete from @RecordIds where [Action] = 'DELETE';
-- save ChildRecords
merge into dbo.ChildRecords as target
using
(
select
Id ,
RecordId = ids.InsertedId, -- Id translation target
Name
from
@ChildRecords cr
inner join @RecordIds ids
on ids.ParamId = cr.RecordId -- Id translation source
)
as source on source.Id = target.Id
when matched then
update set
RecordId = source.RecordId ,
Name = source.Name
when not matched by target then
insert (
RecordId ,
Name )
values (
source.RecordId ,
source.Name )
when not matched by source
and target.RecordId in (select InsertedId from @RecordIds) then
delete;
end;
重要提示
在
MERGE 语句中，源表和目标表必须在其 join 列上具有聚集索引！这可以防止死锁并保证插入顺序。join 列位于 MERGE 语句的 as source on source.Id = target.Id 行。
这就是为什么用户定义表类型在其定义中具有主键聚集的原因。
这就是为什么负数标识是不断递增的并且从 MinValue 开始的原因。
同时请注意永久表的外键定义。它们包含 on delete cascade 子句，这有助于在 MERGE 语句中删除父记录时删除子记录。
关于源代码
附带的压缩文件包含一个在 Visual Studio 2015 中创建的解决方案，该解决方案包含三个项目
Database- 用于创建 SQL Server 2016 数据库的 SSDT 项目ObjectGraphs- 包含存储库类的 DLL 项目Tests- 用于调用存储库方法并查看结果的测试项目
该解决方案包含以下示例
- 如何通过 Id 获取一个上级对象及其所有后代
- 如何获取上级对象列表及其所有后代
- 如何保存上级对象列表及其所有后代
要安装数据库并运行测试，请更改文件 ObjectGraphs.publish.xml 和 Repository.cs 中的连接字符串以使用您的。
下一步？
以上方法的后续和发展是以下文章