How do I avoid too many open connections?

Question

How do I avoid too many open connections?

Navigation

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

2

My Dilemma is that I have a method of type MySqlDataReader that executes a query that sent it by parameter and returns the data ... To read this data I must create a Variable of the same type from where the Method is called, until then Well, it works but when I progress in the Project, the server blocks me because I exceed the number of open connections, but if I close the connection in the method, I can not read the data when I invoke the method.

Code:

The Varieble Connection is Declared like this in my Class:

    //VARIABLE CONEXION (USADA EN LOS METODOS)
    private MySqlConnection Conexion;

And the Method is this:

    public MySqlDataReader Consultar(String sql)
    {
        try
        {
            //INICIALIZAMOS UNA NUEVA CONEXION CON LOS PARAMETROS DEL CONSTRUCTOR
            Conexion = new MySqlConnection(StringConexion.ToString());

            //ABRIMOS LA CONEXION
            Conexion.Open();

            //SE CREA UN COMANDO CON EL SQL OBTENIDO POR PARAMETRO Y LA VARIABLE CONEXION
            MySqlCommand Comando = new MySqlCommand(sql, Conexion);

            //VARIABLE "Datos" QUE GUARDARA LOS DATOS DE LA BDD AL EJECUTAR EL COMANDO
            MySqlDataReader Datos = Comando.ExecuteReader();

            //RETORNAMOS LOS DATOS OBTENIDOS DE LA BASE DE DATOS
            return Datos;
        }
        catch (Exception evt)
        {
            //EN CASO DE ERROR EN LA EJECUCION SE CAPTURA Y SE IMPRIME EL MENSAJE
            MessageBox.Show("Error Consultando: " + evt.Message);

            //EN CASO DE ERROR EL RETORNO SERA NULO, NO RETORNA DATOS
            return null;
        }
        finally
        {
            //CERRAMOS LA CONEXION EN CASO DE ERROR
            //this.CerrarConex();
        }

    }

and to read the returned data I must do this:

ClaseBDD Obj_Consulta = new ClaseBDD();

And I read the data like this:

MySqlDataReader datos = Obj_Consulta.Consultar("SELECT * FROM USUARIOS LIMIT 1;");
if(datos.Read()){
    //carga de datos ejemplo
    TextBox1.Text = datos.GetString(1);
}

If I close the connection in the method, it gives me an error because it does not return anything but if I keep it open, the data load works but it depends on the number of connections because I have many forms where I use this method Any suggestions how can I optimize the method or how can I solve this?

c# clase metodos

asked by Benjamin Garrido H. 26.05.2017 в 17:55

source

2 answers

1

Change the method type to a DataTable and solve my problem. I hope someone else finds this information useful.

    public DataTable Consultar(String sql)
    {
        try
        {
            using (Conexion = new MySqlConnection(StringConexion.ToString()))
            {
                Conexion.Open();

                MySqlDataAdapter da = new MySqlDataAdapter(sql, Conexion);
                DataTable dt = new DataTable();

                da.Fill(dt);

                return (dt);
            }
        }
        catch (Exception evt)
        {
            MessageBox.Show("Error: "+evt.Message);
        }
        finally
        {
            Conexion.Close();
        }
    }

and it is used like this:

private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            DataTable data = BDD.Consultar("select * from invcom where cod_com = 101");
            textBox1.Text = data.Rows[0].Field<string>("des_com");
            textBox2.Text = data.Rows[0].Field<string>("cod_com");

            //o vincular los datos
            //dataGridView1.DataSource = Consultar("SELECT * FROM Clientes");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

answered by 27.05.2017 в 00:13

Error generating Database in Android Studio [closed] BLOB type images stored in a table

score 0 · Accepted Answer

I added a bit of code to yours:

 public DataTable Consultar(String sql)
    {
        System.Data.DataTable dt;
        try
        {
            Conexion = new MySqlConnection(StringConexion.ToString());
            Conexion.Open();
            MySqlCommand Comando = new MySqlCommand(sql, Conexion);
            MySqlDataReader dr = Comando.ExecuteReader();

            dt = new System.Data.DataTable();
            dt.Load(dr);
            dt.CreateDataReader();
            Conexion.Close();//Todo bien cierra conexion
        }
        catch (Exception evt)
        {
            dt = null;// Null
            MessageBox.Show("Error Consultando: " + evt.Message);
        }
        finally
        {
            if (Conexion.State != System.Data.ConnectionState.Closed)//la conexion aun esta abierto
            {
                Conexion.Close();
            }
        }
        return dt;// retornar
    }