File import and read

Question

I have an Asp.Net MVC project with Entity Framework 4 and SQL Server, where there is a Action who receives via POST an archive csv.

Taking into account the use of memory, processing time and resources or any question regarding the database, which of these two code options is the most recommended?

An approximate number of 15000 lines can be used in the file.

Option 1:

var postedFile = Request.Files[0];

using (var reader = new StreamReader(postedFile.InputStream))
{
    using (TransactionScope scope = new TransactionScope())
    {
        while (!reader.EndOfStream)
        {
            var line = reader.ReadLine();
            var values = line.Split(';');

            // Validações

            db.Produtos.Add(new Produto { Descricao = values[0], Valor = values[1] });
        }

        db.SaveChanges();
        scope.Complete();
    }
}

Option 2:

var postedFile = Request.Files[0];

using (var reader = new StreamReader(postedFile.InputStream))
{
    var produtos = new List<Produto>();
    while (!reader.EndOfStream)
    {
        var line = reader.ReadLine();
        var values = line.Split(';');

        // Validações

        var produto = new Produto
        {
            Descricao = values[0],
            Valor = values[1]
        };

        produtos.Add(produto);
    }

    using (TransactionScope scope = new TransactionScope())
    {
        produtos.ForEach(p => db.Produto.Add(p));

            db.SaveChanges();
            scope.Complete();
    }
}

There is a Option 3 best?

Answer 1

There is a Better Option 3?

There is a. Start by installing Filehelpers and the Bulkinsert for the Entity Framework.

Use something similar to option 2:

using FileHelpers;
using EntityFramework.BulkInsert.Extensions;

var postedFile = Request.Files[0];

using (var reader = new StreamReader(postedFile.InputStream))
{
    var engine = new FileHelperEngine<Produto>();
    var produtos = engine.ReadStream(reader);

    using (TransactionScope scope = new TransactionScope())
    {
        db.BulkInsert(produtos);
        db.SaveChanges();
        scope.Complete();
    }
}

Don’t forget to decorate Produto with:

[DelimitedRecord(";")]
public class Produto { ... }

---

About Option 1

Adding one product at a time to the context will slow this context down with a few thousand records. Operation may be excessively slow and take much longer than desired.

About Option 2

It’s not much different than option 1, with an aggravating feature: you’re using more memory to maintain the list and then to add it to the context (it’s not much more memory, but it’s).

During the transaction, there is a risk that the table will be blocked for possible queries by other users?

It does, considering that the standard isolation level of the transactional scope is as high as possible (Serializable).

It is possible to change the level of isolation of records (see option TransactionOptions).

Bulkinsert inserts items into blocks?

Yes, as a single block. There is no separation into smaller blocks.

I can handle this?

Yes, by assembling the blocks in memory before.

To Transaction is open during the insertion of the 15000 items?

Stays open until the using ends or scope.Complete() be called.

This must not interfere with performance?

Transactions are slower than normal insertion operations because the database performs a series of operations to validate this scope. The performance gain comes through the Bulk Insert.

Another issue is that my project is old, and is using version 4 of EF, but Bulkinsert has EF 4.1 in its dependencies, and I’m afraid that installing it will update the EF and cause some headache.

I don’t know any problems with updating the EF version, unless you make use of OriginalValues of some entity somewhere. Other than this, I advise yes to update.

Answer 2

Another way would also be to use handmade Bulkinsert, a generic method that reads the properties and mounts a super fast Insert...

It has a github repository that contains two very useful methods for Bulk operations: Bulkinsert and Bulkupdate using Mysql and EF6+.

Basically, both functions found in the link read the generic properties passed in the methods and assemble the relative querys (update or Insert)...

Obs1: The project was developed aiming at the particular needs, if for example the bank is different, it will be necessary adaptations, but it is already a beginning.

Obs²: The project is open to those who wish to improve it in order to generate value for the community.

Please take a look at here