I hope you can help me
I'm writing an ASP.NET application and I have to loop on over more than 70,000 datarows within a datatable in order to update database records (each datatable row has information to update a database record). My idea was to break the table into 10,000 rows tables and create some async methods to update the database (one async method per table). I need to make sure every record on the table is updates, so everything is wrapped into a TransacionScope block.
The problem is, when I run the Page.ExecuteRegisteredAsyncTasks() I get a Timeout exception telling me that the max allowed time to get a connection was finished.
My code is as follows
using (TransactionScope transaccion = new TransactionScope(TransactionScopeOption.RequiresNew, new TimeSpan(0, 120, 0)))
{
//Validando momentos
clConsultoraCompensationPlan.asignaValoresFinales(dtConsultoraCompensationPlan,
compensation_plan, usuario,pagina);
transaccion.Complete();
}
private static void asignaValoresFinales(DataTable dtConsultorasMaster, clCompensationPlan compensation_plan, int usuario,Page pagina)
{
List<DataTable> lista_tablas = new List<DataTable>();
//Separadno la tabla en 30 tablas
foreach(IEnumerable<DataRow> renglones in LinqExtensions.Split(dtConsultorasMaster.AsEnumerable(), 50))
{
lista_tablas.Add(renglones.CopyToDataTable());
}
foreach (DataTable dtConsultoraCompensationPlan in lista_tablas)
{
AsignaValoresFinalesAsincrono tarea = new AsignaValoresFinalesAsincrono();
PageAsyncTask tarea_asincrona = new PageAsyncTask(tarea.OnBegin, tarea.OnEnd, tarea.OnTimeout, new ContenedorAsignaValoresFinalesAsincrono(dtConsultoraCompensationPlan,compensation_plan,usuario), true);
pagina.RegisterAsyncTask(tarea_asincrona);
}
pagina.ExecuteRegisteredAsyncTasks();
}
In order to run the query I'm using a singleton instance, does this affect something? Is there a better way to complete this task rather than split the table and multithread?
Thanks in advance
This is a timeout issue in your updates. If the transaction scope is using one connection then you probably need to update the command timeout in either the command or connection string.