ByteFX Connection Pooling Issue

[Keith Farmer]

I am currently using ByteFx as the connector between a set of web services and a MySql backend. I’ve been noticing that after several hours of use, I will encounter connection pooling issues.

8/20/2004 11:35:17 AM :: End Execute: Error
System.Web.Services

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Exception: Encountered error near conn.Open(). DbConnectionState: Closed ---> ByteFX.Data.MySqlClient.MySqlException: error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at ByteFX.Data.MySqlClient.MySqlConnection.Open() in C:\Visual Studio Projects\###OMIT###\Tools\NET\Libraries.NET\ByteFX.MySqlClient\MySqlClient\Connection.cs:line 314
at ###OMIT###.Data.DataAccessLayer.MySqlDatabaseAccess.DataAdapter(IDbCommand command) in c:\visual studio projects\###OMIT###\tools\net\libraries.net\###OMIT###.dataaccesslayer\mysqldatabaseaccess.cs:line 252

I believe I’ve already reduced the connection lifetime to a small value, but I am new enough to MySql that I can’t guarantee I’ve fiddled with everything I need to.

Following is the contents of MySqlDatabaseAccess.cs (trimmed). Typical calls look like DbDataAdapter dataAdapter = DatabaseAccess.DataAdapter(DatabaseAccess.GetSqlString("foo"));

.. do I need to call dataAdapter.Dispose()?

MySqlDatabaseAccess.cs:

public sealed class MySqlDatabaseAccess: IDatabaseAccess
{
public IDbConnection DbConnection
{
get
{
return new MySqlConnection(BuildMode.MySqlConnectionString);
}
}

public DbDataAdapter DataAdapter(string sql)
{
MySqlConnection conn = (MySqlConnection) DbConnection;

try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
catch (MySqlException e)
{
throw new Exception("blah");
}

try
{
MySqlDataAdapter result = new MySqlDataAdapter(sql, conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(result);

return result;
}
catch (MySqlException e)
{
throw new Exception("blah");
}
}

public IDbCommand DbCommand()
{
return new MySqlCommand();
}

public IDbDataParameter DbDataParameter(string newName, object newValue)
{
return new MySqlParameter(newName, newValue);
}

public IDataReader DataReader(IDbCommand comm)
{
MySqlConnection conn = (MySqlConnection) DbConnection;

try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}

comm.Connection = conn;

return comm.ExecuteReader();
}
catch (MySqlException e)
{
throw new Exception("blah");
}
}

}