I created a pretty simple webpart recently that used the BDC to access a back-end database. Getting the data I needed involved a few round-trips from the webpart to the BDC. Early testing indicated that I was leaking connections from my connection pool.
I looked back at my data access code, and realized with a little embarrassment that I wasn’t doing anything to close my IEntityInstanceEnumerators. In my defense, this was sample code that I got from the SDK. But just because you start with sample code doesn’t mean you don’t have to think. So that’s on me…
So — there are really two "patterns" for making sure that you’re closing down your connections to your datasource so that the connection can be returned to the pool. We’re all familiar with this from using objects like the SqlDataReader.
- Pattern 1: try/catch/finally and call the close method in your finally block.
- Pattern 2: use a using block. The dispose method of your object will be called when the using goes out of scope.
I’ve always been under the impression that pattern 2 was preferable. So I went with that approach.
Unfortunatally, that had no impact on my connection leak. This scared me, since my first thought was that maybe there was an issue with the BDC (it is still pretty new) or that maybe I was doing something with the BDC that I shouldn’t be…
I decided to use Lutz Roeder’s Reflector to find out what was going on. It turns out that the DbEntityInstanceEnumerator’s Dispose method DOES NOT call this.Close. So it’s up to you to close your connections yourself. Which means you’re stuck with pattern 1 above — try/catch/finally. I don’t know if this behavior (or lack of behavior rather) is intentional or not. It seems unfortunate to me, but there may be a good reason for it.
- Lesson 1: Take a closer look at that sample code.
- Lesson 2: Don’t assume that the Dispose method of an object that’s accessing a database is going to close the connection.
- Lesson 3: Lutz Roeder’s Reflector is still one of the most useful .net development tools out there.
