Re: connections from a data access class never get closed

[David]

I thought I'd readdress this post after doing extensive testing with the DataReader. This from an article on MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag03/html/the_netconnectionpoollifeguard.asp)...

"Some developers insist that if you set the CommandBehavior.CloseConnection option, the DataReader and its associated connection close automatically when the DataReader finishes reading the data. Those developers are partially right—but the option works this way only when you're using a complex bound control in an ASP.NET Web application. Looping through a DataReader result set to the end of its rowset (that is, when Dr.Read—the DataReader's Read method—returns false) isn't enough to trigger automatic connection closing. However, if you bind to a complex bound control such as the DataGrid, the control closes the DataReader and the connection—but only if you've set the CommandBehavior.CloseConnection option."

So, the MySqlDataReader is behaving as it should. You can't pass the DataReader object between classes without losing the .CloseConnection behavior.

I mentioned earlier that I wasn't seeing any extra connections when I used this type of code, but since I have connection pooling enabled, I wasn't seeing what was actually happening. Only one connection was being created, and it was being reused. Since the connection was never being closed properly, it was causing another problem. During the evening when the connection was recycled (mine is an Intranet app that only gets used diring the day), it would break the application. When I would come into the office in the morning, there would be an error on the server because it couldn't connect to the database. I'd have to copy the MySql.Data.dll over to the server each morning when I'd get in.

I stopped passing the DataReader, and now pass disconnected objects such as DataTables and Command objects. Everything is working great.

David