I am using SubSonic for the first time on a project. First let me say that this is a GREAT utility. However, I did run into an issue that I have since resolved.
I am using Subsonic version 2.1.0.0 with MySql.Data 5.1.4.0
I was working on my site when I got a MySql exception of "Too Many Connections". I took at a look at my server monitor and found there were about 150 connections being used. Whoa! So I started doing some troubleshooting. What I found was that everytime I pulled up a new page in my site, at least one new connection would be opened, sometimes two. Since I have pooling on, it stayed open but for some reason was not being reused. So after clicking on 10 or 20 pages, I had something like 30 connections open.
After a lot of testing I found out what was happening. Based on the Subsonic documentation whenever I was populating something like a dropdownlist, I was doing it like so:
ddlUsers.DataSource = Users.FetchAll();
ddlUsers.DataBind();
The problem is that there is no connection release after a call like this. I am not sure if this is by design or a flaw with the MySql management but here is the solution:
IDataReader r = Users.FetchAll();
ddlUsers.DataSource = r;
ddlUsers.DataBind();
r.Dispose();
Calling the subsonic FetchAll() method in this way properly releases the connection and everything works as it should.
I was surprised that I did not find anyone else writing about this problem. I hope this helps someone.