1
I have a project where the user selects a txt or csv file, and the system reads line by line passing the fields to an entity. The first time, he executed SaveChanges()
from the bank context to each line in the file. But this was taking too long and after 20,000 lines gave timeout error.
After some researches I decided to fill the entity and add to the context and after the whole file is read, save the whole context at once. but now I’m getting the error "The transaction associated with the current connection has been completed but has not been dropped. The transaction must be dropped before using the connection to execute the SQL statements."
I tried to use the package Bulkinsert, but also could not.
Does anyone know a solution? Follow my code.
using (TransactionScope scope = new TransactionScope())
{
ContextoBanco context = null;
try
{
using (context = new ContextoBanco())
{
context.Configuration.AutoDetectChangesEnabled = false;
int count = 0;
if (extension.ToLower() == ".csv")
{
//string filename = Path.GetFileName(UploadedFile.PostedFile.InputStream);
StreamReader csvreader = new StreamReader(file.InputStream, Encoding.Default);
var header = csvreader.ReadLine();
string[] cols = header.Split(';');
while (!csvreader.EndOfStream)
{
var line = csvreader.ReadLine();
var values = line.Split(';');
Cadastrar(cols, values, file.FileName, context);
}
}
}
else if (extension.ToLower() == ".txt")
{
//string filename = Path.GetFileName(UploadedFile.PostedFile.InputStream);
StreamReader sr = new StreamReader(file.InputStream);
sr.ReadLine();
while (!sr.EndOfStream)
{
var line = sr.ReadLine();
var values = line.Split(';');
Cadastrar(values, file.FileName, context, count);
}
}
context.SaveChanges();
}
}
catch(Exception ex)
{
ex.InnerException.ToString();
}
scope.Complete();
}
private void Cadastrar(string[] cols, string[] values, string filename, ContextoBanco context, int count)
{
Entidade evento = new Entidade();
evento.nome = values[0];
evento.data = values[1];
evento.tipo = values[2];
evento.valor = values[3];
RepositorioBase<Entidade> rep = new RepositorioBase<Entidade>();
rep.AddToContext(context, evento);
}
public ContextoBanco AddToContext(ContextoBanco context, TEntidade entity)
{
context.Set<TEntidade>().Add(entity);
return context;
}