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.

Without an ORM probably your best bet are Table Value Parameters — Steve
– Steve, Commented Oct 1, 2016 at 10:56
@red-swan If you're using SQL Server 2016, JSON might be an option, although currently TVPs or even XML are your best options. — Matt McCabe
– Matt McCabe, Commented Oct 3, 2016 at 13:58

Mrinal Kamboj · Accepted Answer · 2016-10-01 11:30:25Z

4

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.

answered Oct 1, 2016 at 11:30

Mrinal Kamboj

11.6k5 gold badges45 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jorge Yanes Diez Over a year ago

I'm running .NET Core which does not include DataSet/DataTable yet (and I hope it never does because it's horrible). It'd be great if there was IEnumerable<T> support directly, but I don't think there is :( Currently I'm stuck.

Collectives™ on Stack Overflow

How to insert nested object using stored procedure in sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related