How to Save Object Graph in Master-Detail Relationship with One Stored Procedure

• Download source code - 14.2 KB

This is the first article in the series. The next articles:

• Artisan.Orm or How To Reinvent the Wheel
• Artisan Way of Data Reply

Introduction

I have a web client which sends data to my ASP.NET application in JSON format. The data is an object graph in master-detail relationship or even a collection of object graphs. The data is deserialized into C# object graphs with Web API controller. I want to save that C# object graph with ADO.NET and one stored procedure. And I want to do this without GUID, XML and EF!

Background

Let's suppose that a web client sends the object graphs consisting of three objects:

• GrandRecord
• Record
• ChildRecord

Let it be a collection of GrandRecords where:

• each GrandRecord has a collection of Records
• and each Record has a collection of ChildRecords
• Id value is integer and autogenerated by database
• and while an object is not saved in database, Id has value = 0

Here is the sample of object graph collection (or object graphs):

                      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

Or the same in JSON format:

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: []
            }
        ]
    }
]

On the web server in ASP.NET controller, the above JSON string is deserialized into object graphs of three classes:

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; }
}

Now the object graphs has to be saved with one stored procedure into three database tables:

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
);

The question is how?

Solution

Of course, the table valued parameters for stored procedure is a part of the answer!

Having these user-defined table types:

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    
);

The stored procedure for saving the above object graphs starts with:

create procedure dbo.SaveGrandRecords
    @GrandRecords  dbo.GrandRecordTableType  readonly,
    @Records       dbo.RecordTableType       readonly,
    @ChildRecords  dbo.ChildRecordTableType  readonly
as

So we have to collect all data by types (GrandRecord, Record and ChildRecord), create ADO.NET DataTables and pass them to the stored procedure.

But! Because our tables in database are linked by foreign keys GrandRecordId and RecordId, we have somehow to persist that link while converting the object graph into the separate DataTables.

What is more, the identities of new objects have to be unique! Otherwise, we cannot distinguish the Records of GrandRecord (A) from the Records of GrandRecord (B).

However, as we remember, new objects have Id = 0!

To fix this, let's assign ever increasing negative identities to object Ids, if they are equal to 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;
        }
    }
}

Now it's time to populate the Data tables.

For example, here is how to prepare a DataTable with Records data:

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});
}

So after the DataTables are prepared, the stored procedure will receive the following data in table valued parameters:

@GrandRecords

@Records

@ChildRecords

Object Graph Saving Technique

In order to update existing, insert new and delete old data, SQL Server uses the MERGE statement.

The MERGE statement has the OUTPUT clause. The OUTPUT in MERGE statement can collect just inserted Ids as well as the Ids from the source (parameter) table.

So the technique "to save all three tables with correct foreign keys" is to collect the InsertedId - ParamId pairs from the first table and to translate those values for the second. Then do the same for the second and third tables.

• If a record exists in a table the MERGE does UPDATE, inserted.Id and source.Id are equal to the existing Id.
• If a record does not exist in a table the MERGE does INSERT, inserted.Id is equal to the new Id and source.Id is equal to the negative identity.
• If a record does not exist in source (parameter) table the MERGE does DELETE, inserted.Id and source.Id are equal to NULL, but deleted.Id has Id of deleted record.

Here is the stored procedure which saves our object graphs:

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;

Important Notice

The join columns are in as source on source.Id = target.Id line of MERGE statement.

That is why the user-defined table types above have primary key clustered in their definitions.

That is why the negative identities are ever increasing and start with MinValue.

Note also the definition of foreign keys in the permanent tables. They contain on delete cascade clause, which helps to delete child records when deleting parent records in MERGE statement.

About Source Code

The attached archive contains a solution, created in Visual Studio 2015, which consists of three projects:

• Database - SSDT project to create database for SQL Server 2016
• ObjectGraphs - DLL project which contains a repository class
• Tests - Test project to call the repository methods and see the result

The solution contains the examples of:

• How to fetch one up-level object by Id with all its descendants
• How to fetch a list of up-level objects with all their descendants
• How to save a list of up-level objects with all their descendants

In order to install the database and run the tests, change the connection string in file ObjectGraphs.publish.xml and in Repository.cs to yours.

What's Next?

The consequence and development of the above approach are the articles:

• Artisan.Orm or How To Reinvent the Wheel
• Artisan Way of Data Reply