Dispose of Unmanaged Resources
The .NET Framework takes care of most of the garbage collection on objects that are out of scope and unreferenced. However, certain unmanaged resources (such as database connections, files, windows, and graphics) can use large quantities of memory if not disposed of correctly in the routines.
Figure 5 shows a message I received when running a test database program (listed below) after only 100 connections were created when the code in the Finally block was commented out. Interestingly, 100 connections is the default pool size; by failing to close and dispose of them, I exhausted my whole connection pool.
The snippet in Figure 6 shows what it looked like in the Immediate Window, using Debug.Writeline.
The code without the Finally block commented out runs quickly, creating and destroying 10,000 connections to the database, as shown in Figure 7.
One quick note: cnSqlDb.Dispose(); clears the connection string, which is of no consequence in this loop, where the connection string is rebuilt on every iteration. However, if you move the line that creates the new SQL connection to the button1_Click routine before the loop that calls connect_disconnect, you’ll notice an error after the first connection is created, and no connection information is available for the second connection.
For those interested in trying this, my connection string was to a Northwind database on my local server:
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"