Import and read from file

Question

Import and read from file

Navigation

#1 by (3 votes)
#2 by (0 votes)

3

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

Considering memory usage, processing time and resources, or any questions about the database, which of these two code options is the most recommended?

An approximate number of 15000 rows 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();
    }
}

Is there a Option 3 better?

sql-server c# entity-framework asp.net-mvc

asked by anonymous 31.05.2016 / 21:39

2 answers

0

Another way would also be to use BulkInsert handmade, a generic method that reads properties and assembles 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 assembles the queries (update or insert) ...

Obs1: The project was developed to meet the particular needs, if for example the bank is different, adaptations will be necessary, but it is a start.

Obs2: The project is open to those who wish to improve it in order to create value for the community.

Please take a look at here

17.12.2018 / 16:49

JS parameter for PHP file Validate form data with registration in the database

score 3 · Accepted Answer

Is there a better Option 3?

There is. Start by installing FileHelpers and BulkInsert for the Entity Framework .

Use something like 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();
    }
}

Do not forget to decorate Produto with:

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

About Option 1

Adding one product at a time to the context will make this context slow with a few thousand records. The operation can be overly slow and take much longer than desired.

About Option 2

It's not much different from option 1, with an aggravating factor: you're using more memory to keep the list and then adding it to the context (not much more memory, but it is).

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

There is, since the standard isolation level of the transactional scope is as high as possible (% with%).

You can change the isolation level of the records ( see the Serializable option).

Does BulkInsert insert the items into blocks?

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

Can I control this?

You can by mounting the blocks in memory first.

Is open while inserting 15000 items?

It stays open until TransactionOptions is terminated or using is called.

Can not this 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 from 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 when installing it, the EF update is made and causes some pain Of Head.

I'm not aware of issues with updating the EF version, unless you use scope.Complete() of some entity somewhere. Other than that, I do advise you to upgrade.