I'm a bit sceptical about calling Close() explicitly is different to the "using" statement.
When using "using", it's not the Garbage Collector that closes the connection. The closing brace becomes a call to Dispose. This *explicitly* closes the connection.
The GC would call Finalize, which itself would call Dispose. This *implicitly* closes it, at some unknown time.
In the examples below, I've replaced the using by explicit calls to Close() in a try/finally block, and it made no difference whatsoever.
Anyway, here's a bit of c#4 code that would run plenty of threads, each opening a DB connection and doing some work. I've artificially simulated long running statement (20 seconds) by using the WAIT FOR statement. This results in the same Exception as the
initial poster System.Data.SqlClient.SqlException “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”.
I believe this would be a genuine case of "you're doing a lot of hard work in your application, 100 parallel connections is not enough, increase the connection pool for this application." (or if you don't anything fancy or complicated, write better
sql, add indexes, etc.. so that the queries return faster, which means the connections would be closed faster, therefore being available again in the pool). In Activity Monitor, there are 100 sessions shown, and none are blocked, they are all busy "doing things".
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace ConsoleApplication3
{
class Program
{
static void Main(string[] args)
{
using (IDbConnection conn = new SqlConnection(@"Data Source=localhost\SQLEXPRESS; Initial Catalog=MyDB; UID=myUser; Password=myPwd; Persist security info=false;"))
{
//breakpoint here to let you add a perfmon counter on ".Net Data Provider for Sql Server", "Number of Pooled Connections"
//on this process instance which is only listed once the program has opened 1 connection.
}
Task[] tasks = new Task[500];
for (int i = 0; i < tasks.Length; i++)
{
tasks[i] = Task.Factory.StartNew((Action)delegate { DoFullDBStatement(); });
}
Task.WaitAll(tasks);
}
private static void DoFullDBStatement()
{
using (IDbConnection conn = new SqlConnection(@"Data Source=localhost\SQLEXPRESS; Initial Catalog=MyDB; UID=myUser; Password=myPwd; Persist security info=false;"))
{
using (IDbCommand command = conn.CreateCommand())
{
conn.Open();
command.CommandText = "select * from MyTable WAITFOR DELAY '00:00:20'";
command.CommandType = CommandType.Text;
IDataReader reader = command.ExecuteReader();
}
}
}
}
}
Now, another way to cause the same Exception, run this in Query Analyzer, and after a second, press the stop button. This results in a lock being held on this table, and it's not released because you pressed STOP.
BEGIN TRANSACTION
SELECT * FROM MyTable WITH (TABLOCKX, HOLDLOCK) WHERE 0 = 1
WAITFOR DELAY '00:05:00'
ROLLBACK TRANSACTION
Then run the same Console App as above. It will crash with the same TimeOut exception, but this time, it's caused by the Command not returning in time, because there is a lock held on MyTable, each connection is waiting for that lock to be released and it
doesn't get released, so it timesout. The callstack is different. In SQL Activity Monitor, you will see the column "Locked By" has an ID in it and the wait type is LCK_M_IS.
To resolve this one, you have to find in your application who is keeping a lock and blocking everyone else.
Note: you can make things happen quicker in testing by setting the pool size to a smaller number than the default of 100 (for example just add: Max Pool Size=10).
Also check this recent post on msdn:
http://blogs.msdn.com/b/sqlblog/archive/2011/05/25/ado-net-application-connecting-to-a-mirrored-sql-server-database-may-timeout-long-before-the-actual-connection-timeout-elapses-sometimes-within-milliseconds.aspx 
