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, it executed SaveChanges()
of the bank context with each line of the file. But this was taking too long and after 20 thousand lines gave timeout error.
After some research I decided to fill the entity and add it to the context and after the entire file is read, save the entire context at once. but now I'm getting the error "The transaction associated with the current connection has been completed, but it has not been discarded. The transaction must be discarded before using the connection to execute the SQL statements."
I tried to use the BulkInsert package, but I 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;
}