title: SQL Server Stored Procedure to Insert 3-Level Hierarchy
body: <p>Given business objects similar to:</p>
  
  <pre><code>public class MyBase
  {
      public int Id { get; set; }
  }
  
  public class Parent : MyBase
  {
      public List&lt;Child&gt; Children { get; set; }
      // Additional objects with 1:1 relationship
  }
  
  public class Child : MyBase
  {
      public Parent Parent { get; set; }
      public List&lt;Grandchild&gt; Grandchildren { get; set; }
      // Some other properties
  }   
  
  public class GrandChild : MyBase
  {
      public Child { get; set; }
      // Some other properties including a timestamp
  }
  </code></pre>
  
  <p>I'm trying to replace bulk insert functionality originally written with Entity Framework with ADO.Net and stored procedures due to the <a href="https://stackoverflow.com/q/13182603/141172">poor performance of EF in this scenario</a>.</p>
  
  <p>Not being a DB expert, I first oriented myself with</p>
  
  <p><a href="http://www.sommarskog.se/arrays-in-sql-2008.html" rel="nofollow noreferrer">http://www.sommarskog.se/arrays-in-sql-2008.html</a></p>
  
  <p>Using that guidance I was able to create a well-performing (relative to EF) stored procedure that inserts a <code>Child</code> along with its <code>List&lt;GrandChild&gt;</code>.</p>
  
  <pre><code>CREATE TYPE tvpInt32List AS TABLE (n int NOT NULL)
  
  CREATE PROCEDURE uspInsertChild @parentId INT, @fk1Id INT, @fk2Id INT, 
  @listValues tvpInt32List READONLY 
  AS
  DECLARE @id INT
  
  BEGIN TRANSACTION   
  
  BEGIN TRY   
      INSERT INTO dbo.Children VALUES(@fk2Id, @fk1Id, NULL, 0, 1, NULL, NULL, NULL, NULL, NULL, @fk1Id, @parentId)
  
      SET @Id = SCOPE_IDENTITY() 
  
      INSERT INTO dbo.Grandchildren SELECT n, '', GETDATE(), @id FROM @listValues
  
      COMMIT TRANSACTION
  END TRY
  
  BEGIN CATCH
      ROLLBACK TRANSACTION
  END CATCH 
  </code></pre>
  
  <p>However, I would like to take this a step further and create a stored procedure that accepts data for a <code>Parent</code> object and then creates the associated <code>List&lt;Child&gt;</code>, and the <code>List&lt;GrandChild&gt;</code> for each <code>Child</code>.</p>
  
  <p>I'm conceptually at a loss as to how to approach that in a stored procedure.</p>
  
  <p>What strategy can I use to pass in that type of data structure and process it in the stored procedure?</p>