In this article, you will learn to save object graph in master-detail relationship with one stored procedure.
This is the first article in the series. The next articles:
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
Id | Name |
1 | (A) |
-2147483648 | (B) |
@Records
Id | GrandRecordId | Name |
2 | 1 | (A)A |
-2147483647 | 1 | (A)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 |
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 (
InsertedId int primary key,
ParamId int unique
);
declare @RecordIds table (
InsertedId int primary key,
ParamId int unique,
[Action] nvarchar(10)
);
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
inserted.Id,
source.Id
into @GrandRecordIds (
InsertedId ,
ParamId );
merge into dbo.Records as target
using
(
select
Id ,
GrandRecordId = ids.InsertedId,
Name
from
@Records r
inner join @GrandRecordIds ids
on ids.ParamId = r.GrandRecordId
)
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
isnull(inserted.Id, deleted.Id),
isnull(source.Id, deleted.Id),
$action
into @RecordIds (
InsertedId ,
ParamId ,
[Action] );
delete from @RecordIds where [Action] = 'DELETE';
merge into dbo.ChildRecords as target
using
(
select
Id ,
RecordId = ids.InsertedId,
Name
from
@ChildRecords cr
inner join @RecordIds ids
on ids.ParamId = cr.RecordId
)
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
In the MERGE
statement, the source and the target tables MUST have clustered indexes on their join
columns! This prevents deadlocks and guarantees insertion order.
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: