Entity framework 6 : When to call Savechanges ?

desanti

Hello ! I'm using vb.net 2017 with EF 6 and Sql Server 2008 R2. I'm writing a code to read data from an excel file and after to save on the database.The excel file will have very large number of records. ( maybe 50.000 or even more ). When is better to call context.savechanges : - After adding each record - After adding a number of n records ( !! If yes , what is the best number ) - After adding all records Thank you !

Lost User

desanti wrote:

If yes , what is the best number

42. Ask yourself what happens if you don't save; if the thing crashes, you have to start anew. Saving means rewriting the Excel-file, since it is not a DB - and that will take some time.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Dave Kreskowiak

As always, it depends on business rules. Should all the records in the Excel sheet be treated individually? Should they be treated as one unit? What happens if you're app crashed or is otherwise interrupted during the write to the database? What do you track, if anything, to recover? Do you start over and rewrite the records you already wrote in the database? Do you try to resume from the last successfully written record? You COULD save after every record. It'll slow things down but you'll get a verified write on each and every one. What's the "best" number of records? It's impossible for anyone to say. Again, it depends on a ton of factors. There is no "one value" to rule them all. You COULD save after the entire set is created. That will result in a long save operation that'll return a single error if there is a problem with any record in the operation. It's impossible for anyone, other than yourself, to answer your question.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

desanti

Quote:

Saving means rewriting the Excel-file, since it is not a DB

Sorry ,i'm reading from excel file not writing.

desanti

The problem is that or i should write all the records from Excel to database , or if a single record has problems no records will be saved. I will make this proces in 2 steps : 1. I read one by one all the records from Excel and test them if they are correct and can be saved to database.If one of the record is ok , I add it on entity. If one record has problems , the context is disposed , and the user has to correct the problems and to start testing again the excel data. 2.If the test procedure goes without error , I call savechanges. So , i call savechanges after all objects are added to entity. But i don't know if this is safe , so i want your opinion and i should changes the code that I've described before.

Lost User

In a single transaction :thumbsup:

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Richard Deeming

I'd be inclined to avoid Entity Framework for this task, and use the SqlBulkCopy class instead. SqlBulkCopy Class (System.Data.SqlClient) | Microsoft Docs[^] The Best Way To Import Data From Excel To SQL Server via ASP.NET[^] (Mike's code is in C#, but it should be fairly simple to convert that to VB.NET.)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer