locked
Many sessions open and hang when I call the application running with ASP: NET and the Oracle database RRS feed

  • Question

  • User-1660955953 posted

    Hello, I am working with ASP.NET (Visual Studio 2012) and Oracla database 11g. I have the problem that many sessions are created in the database when I use classes, that is, when I have the connection on the same page I only have one but when I call a class many sessions are hung, and they do not close until I finish the application.

    example:

    In my page:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using Oracle.DataAccess.Client;

    public partial class _Default: System.Web.UI.Page
    {
    static string _connstring = "Data Source=BDORACLE;User ID=PRUEBA;Password=1";

    protected void Page_Load(object sender, EventArgs e)
    {
    Mostrar(gvCliente,e);
    TxtMunicipio.Text = ClaseFunciones.Municipio("95");
    }

    private void Mostrar(object sender, EventArgs e)
    {
    DataTable dt_CLIENTE = new DataTable();
    using(OracleConnection con = new OracleConnection(_connstring))
    {
    con.Open();
    using(OracleCommand cmd = new OracleCommand())
    {
    cmd.Connection = con;
    cmd.CommandText = "SELECT * FROM CLIENTE";
    cmd.CommandType = CommandType.Text;
    dt_CLIENTE.Load(cmd.ExecuteReader());
    ViewState["dt_CLIENTE"] = dt_CLIENTE;
    gvCliente.DataSource = ViewState["dt_CLIENTE"] as DataTable;
    gvCliente.DataBind();
    }
    }
    }
    }

    In my class 

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Data;
    using Oracle.DataAccess.Client;
    ///<summary>

    ///Descripción breve de ClaseFunciones

    ///</summary>

    static public class ClaseFunciones
    {
    static public string Municipio(string MUN1)
    {
    string _connstring = "Data Source=BDORACLE;User ID=PRUEBA;Password=1";
    using (OracleConnection con = new OracleConnection(_connstring))
    {
    con.Open();
    using(OracleCommand cmd = new OracleCommand())
    {
    cmd.Connection = con;
    cmd.CommandText = "ADMPL.NOM_MUNICIPIO";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("MUNICIPIO1", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.Add("ID", OracleDbType.Int32, 3).Value = MUN1;
    cmd.ExecuteNonQuery();
    return cmd.Parameters["MUNICIPIO1"].Value.ToString();
    }
    }
    }
    }

    Thank you in advance for your help.

    ManoloT

    Friday, November 9, 2018 5:26 PM

All replies

  • User753101303 posted

    Hi,

    Do the same thing for your SqlDataReader :

    using(var rdr=cmd.ExecuteReader())
    {
          dt_CLIENTE.Load(rdr);
    }

    to make sure it is disposed as well.

    Friday, November 9, 2018 6:43 PM
  • User-893317190 posted

    Hi ManoloT,

    Are you should you are using the same connection string?

    By default, the connection pool is opened, which will not cause ado.net to open and close connection again and again.

    If you are using different connection strings , this will cause the ado.net to create more connection pools, which may cause performance problems.

    If it is not your case , you could try to customize the connection pool configuration . Please refer to oracle reference document to learn how to customize the connection pool in your connection string.

    https://docs.oracle.com/database/121/ODPNT/featConnecting.htm#ODPNT170

    You should also ensure your connection is closed correctly to make your connection back to the connection pool.

    Best regards,

    Ackerly Xu

    Monday, November 12, 2018 3:01 AM