Timeout expired. all pooled connections were in use and max pool size was reached. 自動關閉 Connection ?

前言

我們有一個 ASP.NET Core 的系統，跑一跑就會有 System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. 的錯誤。但是它的 Max Pool Size 已經設定到了 6,000 ，還是會噴錯。
那系統中一定有沒有關掉的 Connection 才會如此 …

問題

針對連線到 DB 存取的 Method 來查看，發現有一個 SqlCommand.ExecuteReaderAsync ，它的寫法大致如下，

static async Task<DataTable> getDT()
{
    DataTable dt = new DataTable();
    var reader = await getReader();
    dt.Load(reader);
    reader.Close();
    return dt;
}

static async Task<IDataReader> getReader()
{
    var connString = @"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=BookStore;Integrated Security=True;Max Pool Size=4;Application Name={0}";
    connString = String.Format(connString, "ap-Reader");
    var cn = new SqlConnection(connString);
    cn.Open();
    var cmd = new SqlCommand(@"select * from [dbo].[AbpRoles]", cn);
    var reader = await cmd.ExecuteReaderAsync();
    return reader;
}

for(var xi = 0; xi < 10; xi++)
{
    var dt1 = await getDT();
    var v = await getScalarValue();
    Console.WriteLine($"{xi}");
}
Console.ReadKey();

主要是透過 IDataReader 來將資料放入 DataTable 之中，但是因為這樣子的做法，
DB 的 Connection 就要等到取得 DataTable 後才可以 Close 。
可是，DB Connection 都離開了 Function 範圍還不會 Close 嗎?
是的，像上面的程式碼，設定 Max Pool Size 為4個，
所以迴圈跑到第5個時，就會發生錯誤了，因為 Pool 中的 Connection 被已被佔滿了。

• 註: 在 Connection String 中加入 **Application Name={0}**，然後再透過 connString = String.Format(connString, "ap-Reader") 來置換 Application Name ，是為了在 DB 中更容易查詢到，那裡的程式有造成 Connection 沒有 Close 。
• 註: 不同的 Connection String 會有個別的 Connection Pool ，所以當 Application Name 不同時，就會有不同的 Pool 哦~

可以透過以下的 SQL Script 來查看連線的狀況，

SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END
    , CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , ConnectionCount = COUNT(1)
	, des.program_name
    , des.login_name
FROM sys.dm_exec_connections dec
    INNER JOIN sys.dm_exec_sessions des
        ON dec.session_id = des.session_id
GROUP BY CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END
    , des.login_name
	,des.program_name

知道是因為 ExecuteReader /ExecuteReaderAsync 造成 Connection 無法被回收到 Pool 中，
就要來想看看如何將 Connection 關掉。
有想到在傳回 DataReader 時，一併將 Connection 也傳回去，或是改用 DataAdapter ，
但是因為該 Method 已被一堆程式碼參考使用，如果動程式，相對要調整的地方也很多 …QQ
後來發現 ExecuteReader /ExecuteReaderAsync 可接受一個 CommandBehavior 的參數，其中有一個 CloseConnection 的選項可以使用。

CloseConnection : When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

當相關的 DataReader 關閉時，Connection 也會關閉。

所以原本的 cmd.ExecuteReaderAsync() 就多加入 CommandBehavior.CloseConnection 參數，當用完 DataReader 後，Connection 也會 Close 了。

static async Task<IDataReader> getReader()
{
    var connString = @"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=BookStore;Integrated Security=True;Max Pool Size=4;Application Name={0}";
    connString = String.Format(connString, "ap-Reader");
    var cn = new SqlConnection(connString);
    cn.Open();
    var cmd = new SqlCommand(@"select * from [dbo].[AbpRoles]", cn);
    //多加入 CommandBehavior.CloseConnection 參數，當 reader close 後，Connection 也會跟著被關閉
    var reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
    return reader;
}

參考資源

SqlCommand.ExecuteReaderAsync
SqlCommand.ExecuteReader
已超過連接逾時的設定。在取得集區連接之前超過逾時等待的時間，可能的原因為所有的共用連接已在使用中，並已達共用集區大小的最大值。
Will ExecuteReader(CommandBehavior.CloseConnection) always close connection?
CommandBehavior