Use Task with database connection in C #

Question

Use Task with database connection in C #

Navigation

#1 by (3 votes)

4

I am developing a task that basically executes a series of stored procedures for each branch that exists in our company, for this I use the task that comes in the .net framework 4.0 but when executed 50 times the procedure throws an exception in the catch and it's referring to the connection:

System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The current state of the connection is open.

I always keep the connection open, the idea is that when I finish running all the Store procedures the connection is closed but I can not find the way to do it, since it only executes an approximate of 50 SP and the error occurs

Main method:

public static void Main(string[] args)
{

    Console.WriteLine("--INICIO DEL PROCESO. {0:dd-MM-yyyy HH:mm}", HORA_INICIAL);

    Console.WriteLine("---------------------------------------");
    Console.WriteLine();
    DateTime inicio = DateTime.Now;

    DataSet dsTiendas = getTiendas();
    DataSet dsProcedures = getProcedimientos();
    int sizeT = dsTiendas.Tables["Tienda"].Rows.Count;
    int sizeP = dsProcedures.Tables["Procedure"].Rows.Count;
    int i;
    string serverDb, procedure, tienda = "";
    if (sizeT > 0 && sizeP > 0)
    {
        OpenConnection(true);
        for (i = 0; i < sizeT; i++)
        {
            serverDb = dsTiendas.Tables["Tienda"].Rows[i]["SERVERDB"].ToString();
            tienda = dsTiendas.Tables["Tienda"].Rows[i]["U_TIENDA"].ToString() + ": " + dsTiendas.Tables["Tienda"].Rows[i]["NOMBRE"].ToString();
            Console.WriteLine("Incio de Ejecucion de procesos Tienda: " + tienda);
            Console.WriteLine("IP: " + dsTiendas.Tables["Tienda"].Rows[i]["DIRECCIONIP"].ToString());                    
            for (int j = 0; j < sizeP; j++)
            {
                procedure = dsProcedures.Tables["Procedure"].Rows[j]["NOMBRE"].ToString();
                Console.WriteLine("Procedimiento: " + procedure);
                Console.WriteLine("--Inicio del proceso. {0:dd-MM-yyyy HH:mm}", DateTime.Now);

                Task t = Task.Factory.StartNew(() => ejecutarNonQuery("EXEC " + procedure + " '" + serverDb + "'"));

                Console.WriteLine("--Fin del proceso. {0:dd-MM-yyyy HH:mm}", DateTime.Now);                    
            }  
        }
        Console.WriteLine("Fin de la tarea " + tienda);
        Console.WriteLine("--Hora finalizada. {0:dd-MM-yyyy HH:mm}", HORA_INICIAL);

    }

    CloseConnection(true);
    System.Threading.Thread.Sleep(30);
}

Methods to open / close connection:

private static void OpenConnection(bool pMantenerConexionAbierta = false)
{
    InitDB();

    if (!(con.State == ConnectionState.Open))
    {
        con.Open();
        MantenerConexionAbierta = pMantenerConexionAbierta;
    }
}

public static void CloseConnection(bool FinalizarConexionAbierta = false)
{
    if (FinalizarConexionAbierta)
    {
        MantenerConexionAbierta = false;
    }

    if (con.State == ConnectionState.Open & !MantenerConexionAbierta)
    {
        con.Close();
    }
}

Method to execute queries

static public void ejecutarNonQuery(object dbQuerys)
{
    string dbQuery = dbQuerys.ToString();
    //bool respuesta = false;

    if (dbQuery.Trim().Length == 0)
    {
        return;
    }

    using (SqlCommand cmd = getCommand(dbQuery, false))
    {
        try
        {

            cmd.ExecuteNonQuery();
            //respuesta = true;
        }
        catch (Exception ex)
        {
            LogError(dbQuery, ex.ToString(), ex.StackTrace);
            //respuesta = false;
        }
        finally
        {

        }        
    }

    //return respuesta;
}

c# .net asincronismo

asked by Jose Felix 07.05.2016 в 01:39

source

1 answer

Format percentage in C # Error opening the Mac Terminal

score 3 · Answer 1

The error occurs because the connection is closing before the asynchronous tasks are finished running.

This can be easily avoided by saving asynchronous tasks in a list and then using Task.WaitAll to wait for all of them to finish before closing the connection

The code would be like this:

// Lista para guardar las tareas de ejecución de consultas
var taskList = new List<Task>(); 

OpenConnection(true);
for (i = 0; i < sizeT; i++)
{
    ...

    for (int j = 0; j < sizeP; j++)
    {
        procedure = dsProcedures.Tables["Procedure"].Rows[j]["NOMBRE"].ToString();
        Console.WriteLine("Procedimiento: " + procedure);
        Console.WriteLine("--Inicio del proceso. {0:dd-MM-yyyy HH:mm}", DateTime.Now);

        Task t = Task.Factory.StartNew(() => ejecutarNonQuery("EXEC " + procedure + " '" + serverDb + "'"));

        // Añadir tarea a la lista
        taskList.Add(t);

        Console.WriteLine("--Fin del proceso. {0:dd-MM-yyyy HH:mm}", DateTime.Now);                    
    }  
}
Console.WriteLine("Fin de la tarea " + tienda);
Console.WriteLine("--Hora finalizada. {0:dd-MM-yyyy HH:mm}", HORA_INICIAL);

// Esperar a que se ejecuten todas las consultas antes de cerrar la conexión
Task.WaitAll(taskList.ToArray());

// Cerrar conexión
CloseConnection(true);
System.Threading.Thread.Sleep(30);

Update: Error handling

If an error occurs within one of the asynchronous tasks, the rest of the asynchronous tasks are not affected. Then, when calling Task.WaitAll , an exception of type AggregateException that will contain the list of exceptions that have occurred

Using this code as an example:

var taskList = new List<Task>();

for (int i = 1; i<=10; i++)
{
    var t = Task.Factory.StartNew((number) => {

        if ((int)number == 4 || (int)number == 6) 
        {
            throw new InvalidOperationException("Error en la tarea: " + number);
        }

        Console.WriteLine("Ejecutada tarea nº {0}", number);
    }, i);
    taskList.Add(t);
}

try 
{
    Task.WaitAll(taskList.ToArray());
}
catch(AggregateException ex)
{
    Console.WriteLine("**Se han producido errores**");
    ex.InnerExceptions.ToList().ForEach(e => {

        Console.WriteLine(e.Message);
    });
}

The result will be:

Ejecutada tarea nº 1
Ejecutada tarea nº 3
Ejecutada tarea nº 5
Ejecutada tarea nº 7
Ejecutada tarea nº 8
Ejecutada tarea nº 9
Ejecutada tarea nº 10
Ejecutada tarea nº 2
**Se han producido errores**
Error en la tarea: 4
Error en la tarea: 6