Fixing Too Many Connection Problem

My very first .Net project way back in year 2003 using Visual Studio 2003 (later switch to 2005, 2008 and 2010 as time went by), and using MySQL 4.1 (later migrated to MySQL 5.5) as the backend database, was faced with the same database connectivity problem, the application often would encounter this  "Too many connection.." error, when the database driver (MySQL.NET), after some prolong running, could not established database connection anymore.

I've fixed this by inserting the following code under the try/catch database connectivity error handler.

System.Data.SqlClient.SqlConnection.ClearAllPools();

The same line of code works in .NET default data driver ADO.NET hence MySQL.Net driver has adopted ADO.NET namespace for ease of using either SQL Server or MySQL database.

The nature of my application is multithreaded and demands for very fast database connection as each task per user has to spin each own background thread from the threadpool. But if your application is not very particular about database connection speed and would like each instance being destroyed immediately after closing the database connector, you can adjust it on IIS application settings or via the connection string by setting Pooling=false.