This project is read-only.

Bulk data insert

Dec 9, 2013 at 4:13 AM
I'm looking for the fastest way of INSERTING bulk data (datatable - C#) to database, The scenario is I am calling a RFC to retrieve data from SAP and I store it in C# datatable now i need to Insert that datatable to database. And amount of data is around 20K. Is their anyway to insert bulk data
Dec 9, 2013 at 4:30 AM
Edited Dec 9, 2013 at 4:31 AM
Dear Dmx9600,

You have two options here,
  1. The latest version of the Nido-Framework has a fairly optimized method with the name "AddBulkGeneric" where it support adding multiple records in bulk form. You can use that and for 20k records I assume it would take some 10-30 seconds depending on the amount of data in the table.
  2. Second is to write it in your own way.. for that you need to use the HandlerClass of the respective entity and write a method in it. Let me show you a sample code here..
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Nido.Common.BackEnd;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel;
using DemoTest.Bll.Models;
using DemoTest.Bll.DB;

namespace DemoTest.Bll.Handlers
{
    public partial class StudentHandler : HandlerBase<Student, SchoolDBContext>
    {
        protected override Type LogPrefix
        {
            get { return this.GetType(); }
        }

        public GenericResponse<List<Student>> StudentBulkInsert(List<Student> studentList)
        {
            try
            {
                _context.Configuration.AutoDetectChangesEnabled = false;
                _context.Configuration.ValidateOnSaveEnabled = false;

                this.AddBulkNew(studentList);
                if (this.SaveChanges() > 0)
                    this.LogInfo("Calling End Successfully for AddGeneric(" + studentList.ToString() + ")");
                else
                    this.LogInfo("Calling failed at save for AddGeneric(" + studentList.ToString() + ")");

                return new GenericResponse<List<Student>>(true, new string[] { "Bulk Insert operation is successfull" });
            }
            catch (Exception e)
            {
                return this.HandleException<List<Student>>(e);
            }
            finally
            {
                _context.Configuration.AutoDetectChangesEnabled = true;
                _context.Configuration.ValidateOnSaveEnabled = true;
            }
        }
    }
}
I mean virtually you have access to the dbContext here (both DbContext, and ObjectContext) so then you can do what ever you want.

Note: It is important to return GenericResponse here as it gives the user a 'reference Id' for error reporting. The development team can directly identify the error in the log using the said reference ID.

Additionally for further reading
http://stackoverflow.com/questions/6107206/improving-bulk-insert-performance-in-entity-framework

http://stackoverflow.com/questions/17274430/efficient-way-to-do-bulk-insert-update-with-entity-framework
Dec 9, 2013 at 7:30 AM
Thank you for quick response, I use 1st option but i am getting a error while inserting
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
List<TempInvoiceDetail> Tempmodel;
Tempmodel = new List<TempInvoiceDetail>();

foreach (var item in model) {

      Tempmodel.Add(new TempInvoiceDetail {
       --
       --
       --
       PostingDate = item2.PostingDate,
       --
       --
       --                   
      });
}

TempInvoiceDetailHandlers.AddBulkGeneric(Tempmodel);
TempInvoiceDetailHandlers.SaveChanges();
Dec 9, 2013 at 8:21 AM
Dear Dmx9600,

This has nothing to do with the method you are using but has something to do with the entity property types of your Business Object. check them and verify, this error should come not only when inserting but retrieving as well. This means that your business object datatypes does not tally with the ones that you have in your database..
Dec 9, 2013 at 11:18 AM
Is their any way to bulk update as well

Now i am using StoredProcedure
UPDATE InvoiceDetail
SET Status=1
WHERE 
CompanyCode= @CompanyCode
and PlantCode= @PlantCode
and PostingDate = @PostingDate
and PurchaseOrderNo = @PurchaseOrderNo
and VendorCode = @VendorCode
and InvoiceNo = @InvoiceNo
Dec 9, 2013 at 12:17 PM
Yes you can ... that is pretty easy.. "UpdateGeneric" method has many different options to do that.. Please check the documentation as well as the sample project. You will find your way out very easily...