目录
这是该系列的第一篇文章。下一篇文章:
介绍
我有一个Web客户端,它以JSON格式将数据发送到我的ASP.NET应用程序。数据是主从关系中的对象图,甚至是对象图的集合。使用Web API控制器将数据反序列化为C#对象图。我想保存带有ADO.NET和一个存储过程的C#对象图。我想在没有GUID、XML 和EF的情况下做到这一点!
背景
假设Web客户端发送由三个对象组成的对象图:
- GrandRecord
- Record
- ChildRecord
让它成为其中的GrandRecords集合:
- 每个都有GrandRecordRecords
- 每个都有RecordChildRecords
- 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: []
}
]
}
]
在ASP.NET控制器的Web服务器上,上述JSON string被反序列化为三个类的对象图:
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;
}
}
}
现在是时候填充数据表了。
例如,以下是如何使用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 | Name |
1 | (A) |
-2147483648 | (B) |
@Records
Id | GrandRecordId | Name |
2 | 1 | (A)A |
-2147483647 | 1 | (B)B |
-2147483646 | -2147483648 | (B)A |
@ChildRecords
Id | RecordId | Name |
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中的连接字符串更改为你的连接字符串。
https://www.codeproject.com/Articles/1153556/How-to-Save-Object-Graph-in-Master-Detail-Relation