mysql - DataAdapter 不会自行关闭连接

Question

众所周知，DataAdapter 会打开和关闭尚未打开的连接。但是用我的代码它打开但没有关闭..我正在使用 MySql.Data.MySqlClient.MySqlDataAdapter，不确定我做错了什么。下面是我的代码

Public Function GetDT(ByVal SqlQuery As String, ByVal ConString As String) As DataTable
    Dim da As New MySql.Data.MySqlClient.MySqlDataAdapter(SqlQuery, ConString)
    Dim ds As New DataSet
    da.Fill(ds)
    GetDT = ds.Tables(0)
    da.Dispose()
    ds.Dispose()
    da = Nothing
    ds = Nothing
End Function

我正在使用此连接字符串:"server=localhost;port=3306;user=someuser;pwd=somepassword;database=mydatabasename;Allow Zero Datetime=True;"

在调试代码时，我发现一旦 DataAdapter.fill 执行其启动连接线程，但由于线程仍处于 sleep 状态，因此连接没有关闭。请检查下面的图片。

有人可以帮我解决这个问题吗？

谢谢

Answer 1

这是正常行为，因为 ADO.NET 默认使用 SQL Server 的连接池。来自 MSDN:

http://msdn.microsoft.com/en-US/library/8xx3tyca(v=vs.110).aspx

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

....

The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes,...

DataAdapter中“using”或“dispose”的效果是一样的。 “using”确保即使发生异常，也会执行 dispose。与使用 try/finally 语句并将“dispose”放在finally部分相同。