How to pass .NET Collection of objects (Parent-Child) hierarchy to a SQL Server stored procedure

Question

I need to pass a .NET Collection to a stored procedure. My collection contains 1000 records. Each record contains 2-3 child objects of other types.

All I need to insert them at once by calling stored procedure from .NET.

I already have tried a TVP (table-valued parameter), but that simply doesn't fulfills my needs.

For each and every root level record, I need to call stored procedure by passing root record and it's corresponding child records. This is making the whole process very slow.

That's why I am looking a way, so that I can pass whole hierarchical list at once, and inside stored procedure, by looping and iterating child records I can insert parent child records.

How can I achieve this?

Answer 1

I actually just did this 3 weeks ago.

1. create a "temp" association key (I used a GUID) to link the parent and children together because you don't have database ids (the temp key is never saved in the database)
2. Call a TVP stored procedure. Pass the all of the parents and all of the childred in two separate table vars
3. create a temp table or table var to store the tempid/database id relationship
4. Use the merge statement with OUTPUT to get the Inserted.Id value and temp id into the temp table created in step 3
5. use a CTE and merge statement to insert the child records using the actual DB id's from the parent record

Here are a couple of links about the process with "real" examples:

merge parent and child SQL Server tables

T-SQL - Insert Data into Parent and Child Tables

Answer 2

You can use XML to pass records as well as their children to stored procedure. For example

DECLARE @xml XML = '<root>
<parent value="213">
    <child childValue="1111"></child>
    <child childValue="1112"></child>
</parent>
<parent value="2313">
    <child childValue="3333"></child>
    <child childValue="3332"></child>
</parent>
</root>'

Then you can do many ways, one of them is to denormalize records and save to temp table for further processing

    SELECT
    T.c.value('parent::*/@value', 'INT') AS ParentValue,
    T.c.value('@childValue', 'INT') AS ChildValue
    FROM @xml.nodes('/root[1]/parent/child') T(c)

Result will look like

    ParentValue  ChildValue
    213          1111
    213          1112
    2313         3332
    2313         3333