Import Data from text file into sql DB using CSLA

Question

I am trying to import data from ~ Delimited Text file into SQL Server using CSLA. My text file has 92,000 records in it. Here are the issues i am having with the import

1. When i create a BusinnessListBase .new and add all my records to it, it gives me a "Out of meory exception". So to fix it i create a new bussinessbase object and save it. this works fine and is much faster too. It takes 15 minutes
2. I have to run my program again and check for any changes and hence update them, this is where it takes too much time.
3. Is there any alternative way to speed up my import?

Answer 1

A perfect way to do that is to use either OpenRowSet or LinkedServers in Microsoft SQL Server. OpenRowSet is so handy. For reference please take a look at here: http://msdn.microsoft.com/en-us/library/ms190312.aspx

Also in my opinion 15 minutes is not a good performance! it should be much more faster.

Cheers

Answer 2

I do not know why using both CSLA and Entity Framework was taking so much time. But i changed my program to use SQL Bulk Copy and since i had to also check for existing records, i created another staging table and imported my records into that and them did an Upsert in SQL Stored Procedure. The entire process took less than a minute to process 92,000 records.