使用GC.Collect终止数据库连接 [英] Terminating database connection with GC.Collect

问题描述

我有一个程序，可以通过以下应用程序角色将用户连接到数据库.

I have a program which connects a user to a database via an application role as follows.

ConnectionString = "WW"
con1.ConnectionString = connectionString
con1.Open()
cmd.Connection = con1
Call GetPermissions()
cmd.CommandText = "sp_setapprole"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@rolename", "XX")
cmd.Parameters.AddWithValue("@password", "XX")
cmd.ExecuteNonQuery()

在13:30到14:00之间，我想终止与数据库的连接，然后显示一个消息框，告诉用户GUI不可用.
原因是允许管家作业在数据库上运行.
我是使用此代码来实现的.

Between 13:30 and 14:00 I want to terminate the connection to the database then display a messagebox telling the user the GUI is unavailable.
The reason is to allow housekeeping jobs to run on the database.
I do so using this code.

con1.Close()
GC.Collect()
MessageBox.Show("Database housekeeping job in progress.  GUI unavailable")
End

但是，当查看数据库上的活动监视器时，似乎连接仅在应用程序结束后才终止.另外，GC.collect可能需要花费几分钟的时间.
因此，在出现消息框时，连接仍处于活动状态.
在出现消息框之前，如何确保连接已终止.

[已修改:添加了用于代码格式设置的pre标签... pre标签是您的朋友...]

However when looking at the activity monitor on the database it seems as though the connection is only terminated after the application ends. Alternatively it may take a few minutes for GC.collect to work.
Therefore at the time that the message box appears the connection is still active.
How can I make sure the connection has terminated before the messagebox appears.

[Modified: added the pre tags for code formatting...pre tags are your friend...]

推荐答案

默认情况下，ADO.net Connection对象使用连接池

"ADO.NET 2.0引入了两种清除池的新方法: ClearAllPools 和 ClearPool . ClearAllPools 清除给定提供程序的连接池，并且 ClearPool 清除与特定连接关联的连接池，如果在调用时有正在使用的连接，则会对其进行适当的标记.当关闭时，它们将被丢弃而不是返回到游泳池."

摘自本文 MSDN:SQL Server连接池ADO.NET [ ^ ]

By default ADO.net Connection object uses connection pooling

"ADO.NET 2.0 introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections being used at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool."

taken from this article MSDN: SQL Server Connection Pooling ADO.NET[^]

您根本不需要调用GC.Collect.这样做会干扰GC的内部调整，实际上会降低应用程序的性能.

You do not need to call GC.Collect at all. Doing so screws with the internal tuning of the GC and can actually degrade the performance of your app.

有一个内部连接池，该连接池将打开的数据库连接保留了一段时间.因此，寻找一种方法来重新初始化该池(如果可能).

There is an internal connection pool which holds connections to database opened for some time. So look for a way how to reinitialize this pool (if possible).

这篇关于使用GC.Collect终止数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！