connections from a data access class never get closed

[Gianluca Ippolito]

Hi All

I ‘m working on a small web with two pages (main.aspx and cardetails.aspx) that build urls dynamically and display images reading data from a mysql server (4.0 on the test machine and 4.1 on the deployment server - I’m using Connector/Net 1.0.3 gamma on both ) . The application is built on top of a class that implements a data access layer with various methods returning mainly strings or readers. Everything works fine except for anytime a connection is opened it never closes. I’ve checked to catch missing. conn.Colose() but everything seems ok. Furthermore in the main.aspx page and in the cardetails.aspx page as well, when the On_Load event is fired, I have 2 DALobjects returning a datareader declared in this way :



MySqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

As far as I know a reader instantiated with the above syntax should be closed automatically after binding to a server control but, in my case, it never happens.

I’ve also checked threads with mysqladmin processlist to see how many connection would stay opened going back and forth from main aspx to cardetails.aspx: I get one connection When Main.aspx is loaded and two connection when cardetails.aspx is loaded. the bad news is that they never get closed but the number of opened connections increase as soon as one of the two pages is loaded.
I’ve tried to switch pooling off but I get always the same result.

So I’ve decided to move al the data access code to the aspx.cs pages to see if there is something wrong with my code but this time everything has worked smoothly. when pooling is on there is only one connection active on the other and when it’s off all connections get closed.

I can’t explain why when a connection is created within the DAL it never get closed also when I use (at least I hope so!) the right syntax.

Any Idea about this issue?

Here is the code for:

1) main.aspx.cs

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using MySql.Data;


namespace MyJuKey.Projects.CTCarDeploy
{

public class main : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label lblMess;
protected System.Web.UI.WebControls.DataList carList;

private void Page_Load(object sender, System.EventArgs e)
{
MyJuKey.Projects.CTCarDeploy.Components.carsDB catalogue = new MyJuKey.Projects.CTCarDeploy.Components.carsDB();
carList.DataSource = catalogue.selectOnMain();
carList.DataBind();
catalogue.selectOnMain().Close();

}



}
}


2) cardetails.aspx.cs;

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;


namespace MyJuKey.Projects.CTCarDeploy
{
/// <summary>
/// Summary description for CarDetails.
/// </summary>
///



public class CarDetails : System.Web.UI.Page



{
protected System.Web.UI.WebControls.DataList carList;
protected System.Web.UI.WebControls.Label carDesc;
protected System.Web.UI.HtmlControls.HtmlGenericControl CName;

private void Page_Load(object sender, System.EventArgs e)
{
int CarID = Int32.Parse(Request.Params["CarID"]);

MyJuKey.Projects.CTCarDeploy.Components.carsDB carcat = new MyJuKey.Projects.CTCarDeploy.Components.carsDB();

carList.DataSource = carcat.selectOnDetails(CarID);
carList.DataBind();
carcat.selectOnDetails(CarID).Close();

MyJuKey.Projects.CTCarDeploy.Components.carsDB cartext = new MyJuKey.Projects.CTCarDeploy.Components.carsDB();

CName.InnerHtml = Convert.ToString(cartext.selectText(CarID).makemodel);
carDesc.Text = cartext.selectText(CarID).description;


}

#region Web Form Designer generated code

}
}


3) the relevant part of the DAL class:

sing System;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using System.Collections;


namespace MyJuKey.Projects.CTCarDeploy.Components
{
/// <summary>
/// Summary description for carsDB.
/// </summary>
public class cartxt
{
public int ID;
public string makemodel;
public string description;
public string firstimage;


}
public class carsDB
{
//................................................................................

public MySqlDataReader selectOnMain()
{


MySqlConnection myConnection = new MySqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
MySqlCommand myCommand = new MySqlCommand(ConfigurationSettings.AppSettings["SelFirstImg"],myConnection);

myCommand.CommandType = CommandType.Text;


myConnection.Open();
MySqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

return result;

}

public MySqlDataReader selectOnDetails(int CarID)
{


MySqlConnection myConnection = new MySqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
MySqlCommand myCommand = new MySqlCommand(ConfigurationSettings.AppSettings["SelAllImgs"],myConnection);

myCommand.CommandType = CommandType.Text;


MySqlParameter paramCarID = new MySqlParameter("@CarID",MySqlDbType.Int32,11);
paramCarID.Value = CarID;
myCommand.Parameters.Add(paramCarID);


myConnection.Open();
MySqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

return result;

}

public cartxt selectText(int CarID)
{


MySqlConnection myConnection = new MySqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
MySqlCommand myCommand = new MySqlCommand(ConfigurationSettings.AppSettings["SelText"],myConnection);

myCommand.CommandType = CommandType.Text;


MySqlParameter paramCarID = new MySqlParameter("@CarID",MySqlDbType.Int32,11);
paramCarID.Value = CarID;
myCommand.Parameters.Add(paramCarID);


myConnection.Open();
cartxt myCartxt = new cartxt();
MySqlDataReader result = myCommand.ExecuteReader();
if (result.Read())
{ myCartxt.ID = result.GetInt32(0);
myCartxt.makemodel = result.GetString(1)+ " " + result.GetString(2);
myCartxt.description = result.GetString(3);
myCartxt.firstimage = result.GetString(4);
}
result.Close();
myConnection.Close();
return myCartxt;
}

//…………………………………………………………………………………

Ciao