如何使用一个存储过程以主从关系保存对象图

最新推荐文章于 2024-05-19 20:09:45 发布

寒冰屋

最新推荐文章于 2024-05-19 20:09:45 发布

阅读量23

点赞数

分类专栏： CSharp.NET 数据库文章标签：数据库 .net

原文链接：https://www.codeproject.com/Articles/1153556/How-to-Save-Object-Graph-in-Master-Detail-Relation

版权

CSharp.NET 同时被 2 个专栏收录

1024 篇文章 46 订阅

订阅专栏

数据库

214 篇文章 12 订阅

订阅专栏

下载源代码 - 14.2 KB

这是该系列的第一篇文章。下一篇文章：

介绍

我有一个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;