How to insert nested object using stored procedure in sql

Question

I am having the nested object model as follows:

      public class Product
        {
         public List<ProductOffering> ProductOfferings { get; set; }
        }
      public class ProductOffering
        {
            public int OfferingId { get; set; }
            public string OfferingDescription { get; set; }
            public string OfferingType { get; set; }
            public List<OfferingPriceRegion> PriceRegions { get; set; }
        }

I want to insert Product along with list of ProductOffering which having again list of OfferingPriceRegion in single stored procedure (SPInsertProduct)using C#. what is the best approach except entity framework. because ProductOfferings in Product may be in large number in count say 400. where entity framework may take more time in looping save functionality. Please suggest.

Answer 1

Dapper being an ADO.Net based object mapper, best option would be using TableValuedParameters, where complete required data can send to the database in a single call.

Following are the important points:

• Dapper takes the TVP as a DataTable
• For IEnumerable<T> to DataTable, you can use the System.Data.DatasetExtensions method CopyToDataTable or there's an Nuget API FastMember to achieve the same

Few Caveats:

• Number of Columns, columns names and their order shall be exactly same for TVP and the DataTable, else it will not work and error will not suggest the issue, this mapping is not same as Json mapping where schema mismatch isn't an issue

If the number of records are very high, you may want to divide into multiple DataTables and use Async-Await to do the same operation concurrently.