My current project consists of 3 standard layers: data, business, and presentation. I would like to use data entities for all my data access needs. Part of the functionality of the app will that it will need to copy all data within a flat file into a database. The file is not so big so I can use SqlBulkCopy. I have found several articles regarding the usage of SqlBulkCopy class in .NET. However, all the articles are using DataTables to move data back and forth.
Is there a way to use data entities along with SqlBulkCopy or will I have to use DataTables?
As complement to @DaveHogan answer,
There is now new libraries which allow performing Bulk Insert (using SqlBulkCopy under the hood) for Entity Framework using data entities instead of DataTable.
Disclaimer: I'm the owner of the project Entity Framework Extensions
This library is not free but allows to easily perform:
Under the hood, SqlBulkCopy is used. Using extension method make is way easier/faster than coding a custom solution for every Bulk Insert to use.
// Easy to use context.BulkSaveChanges(); // Easy to customize context.BulkSaveChanges(bulk => bulk.BatchSize = 100);
I'm sorry you are getting poor performance,
Make sure you are not including time from some common mistake like forgetting to JIT Compile, using Add instead of AddRange which impact the test performance but is not related to our library.
Most people report getting between 25-50x performance improvement when excluding all common performance benchmark mistake.
You'll need to convert the Entities to a IDataReader or DataTable.
There is a small helper class designed to assist with with: http://archive.msdn.microsoft.com/LinqEntityDataReader/Release/ProjectReleases.aspx?ReleaseId=389
EDIT: msdn link is broken, alt copy can be found here: https://github.com/matthewschrager/Repository/blob/master/Repository.EntityFramework/EntityDataReader.cs
Then you can use SqlBulkCopy like so:
var sbCopy= new SqlBulkCopy(connectionString); sbCopy.DestinationTableName = "TableName"; sbCopy.WriteToServer(entitiesList.AsDataReader());