When using SqlConnection, it's important to always close it when used - either by .Close() or placing the SqlConnection in a "using". Unfurtunately, people, including myself, tend to forgot that and this is where the garbage collectors rescues me for a while until i've forgot to close my connections too many times or the number of people using the application grows.
在使用SqlConnection时，在使用时总是要关闭它——通过. close()或将SqlConnection放在“using”中。显然，人们，包括我自己，倾向于忘记这一点，这就是垃圾收集者拯救我一段时间的原因，直到我忘记多次关闭连接，或者使用该应用程序的人数增加。
I'd like to know, if even possible, how to detect if the garbage collector disposed the SqlConnection because it figured that it wasn't in use anymore or if the SqlConnection was closed the correct way.
我想知道，如果可能的话，如何检测垃圾收集器是否处理了SqlConnection，因为它认为它不再使用，或者SqlConnection是以正确的方式关闭的。
Another way could be inheriting SqlConnection and putting a timer on it's initializer and check how long it took for the connection to be closed when disposing the class. I don't really like timers but the idea just came up while writing this.
另一种方法是继承SqlConnection并在其初始化器上设置一个计时器，并检查在配置类时关闭连接所需的时间。我不太喜欢计时器，但我写这个的时候想到了这个主意。
Maybe there's a third and even smarter way to all this... What would you recommend?
也许有第三种更聪明的方法可以解决这一切……你有什么建议?
6 个解决方案
#1
4
Since SqlConnection is sealed, you won't be able to inherit from it. (And I don't think it is a good idea to do so -- If it was possible, you should probably add your code in Dispose(false), because that is what the finalizer calls).
因为SqlConnection是密封的，所以您无法继承它。(我认为这样做不是个好主意——如果可能的话，您应该在Dispose(false)中添加代码，因为终结器调用的就是这个)。
It is better to detect these kind of problems by using a static code analysis tool, which has the power to detect all places in your code where you forget to dispose a connection. There is one built in in Visual Studio, or you can use FxCop.
最好使用静态代码分析工具来检测这些问题，静态代码分析工具可以检测代码中忘记处理连接的所有位置。在Visual Studio中有一个内置的，或者您可以使用FxCop。
To help you not to forget disposing the connection, it is a good idea to:
为了帮助您不忘记处理连接，最好是:
- Keep all DB connection code in one layer / assembly / module, so it is not scattered through the projects.
- 将所有的DB连接代码保存在一个层/程序集/模块中，这样就不会分散在项目中。
- Have utility methods to perform SQL commands and returning the results; so you don't create SQLConnection more places than you need to.
- 有实用方法来执行SQL命令并返回结果;所以您不会创建SQLConnection所需的位置。
- Remember to utilize the C# using construct.
- 记住使用c# using构造。
#2
2
One rule of thumb says, "If you have to think about the garbage collector, you're probably doing something wrong." (Of course, there are other thumbs...)
一条经验法则是，“如果你不得不考虑垃圾收集器，你可能做错了什么。”(当然，还有其他的拇指…)
Seems to me that ensuring that connections are closed either explicitly or via using and finally blocks is the best route.
在我看来，确保连接被显式关闭或通过使用和最终块关闭是最好的途径。
Obviously, you already understand these techniques... so maybe a once-through-the-code and a possible refactor is all you need.
显然，你已经了解了这些技巧……因此，您可能只需要一次遍历代码和一个可能的重构。
#3
1
If your application is using SQL Connection Pooling (the default) it will not matter since connections are re-used and don't get closed when you call .Close() or leave a using() {} block.
如果您的应用程序正在使用SQL连接池(默认)，这并不重要，因为连接被重用，并且在调用. close()或保留using(){}块时不会被关闭。
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
To answer your question, I dont believe there is a event for GC collection, however it would not matter if there was, because you would never know if the GC chose to recycle your object on that collection pass (not all objects are cleaned because of the generational algorithm).
为了回答您的问题，我不认为存在GC收集的事件，但是如果有的话，也没关系，因为您永远不会知道GC是否选择在该集合传递上回收您的对象(不是所有对象都因为分代算法而被清理)。
I would also avoid attempts to use timers and and "checks" as you will likely hold references to the object and prevent it from ever being disposed.
我还将避免尝试使用计时器和“检查”，因为您可能持有对对象的引用，并防止它被处理。
#4
1
I am not very familiar with the garbage collector and don't know if there is a direct way to get the information but my first idea is the following one.
我对垃圾收集器不是很熟悉，也不知道是否有直接的方法来获取信息，但是我的第一个想法是下面这个。
Just create a weak reference to a dummy object. If you look at the weak reference later and the object is no longer alive you can probably assume the a garbage collection occurred.
只需创建对虚拟对象的弱引用。如果您稍后查看弱引用，并且该对象已不再活动，您可能会假设发生了垃圾收集。
(This answer only applies to detecting garbage collector runs. I completely ignored the reason for doing this - there are much better strategies to deal with resource leaks.)
(此答案仅适用于检测垃圾收集器运行。我完全忽略了这样做的原因——有更好的策略来处理资源泄漏。
#5
0
First, if you're even considering using the GC, you've got serious problems somewhere. I suggest that the best way to ensure your code calls Close is to unit test your code using techniques like mocking. If your unit test indicates that Close was called, your code is correct and you don't have to do anything unnecessarily dangerous like mucking around with the Garbage Collector.
首先，如果您正在考虑使用GC，那么您在某些地方遇到了严重的问题。我建议确保代码调用接近的最好方法是使用mock等技术对代码进行单元测试。如果您的单元测试表明Close被调用，那么您的代码是正确的，并且您不需要做任何不必要的危险工作，比如在垃圾收集器中混日子。
Second, if you still insist on going the runtime checking route, the only thing you should even consider doing is hooking the StateChange event of SqlConnection. That'll fire if the ConnectionState changes from Open to Closed, for example.
其次，如果您仍然坚持执行运行时检查路线，那么您甚至应该考虑的惟一事情就是连接SqlConnection的StateChange事件。例如，如果ConnectionState从Open更改为Closed，则会触发。
#6
0
If you forget to dispose, the object will be finalized. There is no way to control the time at which this happens, and there is also no way for you to know if an object is finalized. A seperate thread has to be created in order to finalized objects, so it slows your application down. That's why you want to dispose. In all classes in the framework that implement IDisposable, a call to GC.SuppressFinalize is made, so the object isn't finalized.
如果忘记处理，对象将被最终确定。没有办法控制发生这种情况的时间，也没有办法让您知道一个对象是否已经完成。为了最终完成对象，必须创建一个独立的线程，因此会减慢应用程序的速度。这就是你要处理的原因。在实现IDisposable框架中的所有类中，对GC的调用。进行了打压，因此对象没有最终完成。
There is no way for you to controll this behaviour. If your object isn't used anymore, it will automatically be collected. All you can do to stop this, is call GC.SuppressFinalize, but I wouldn't recommend that, because if you then forgot, you'd be screwed for life.
你没有办法控制这种行为。如果您的对象不再被使用，它将自动被收集。要阻止这种情况，只需调用GC。抑制，但我不建议这样做，因为如果你忘记了，你将会被终生监禁。
You can create a wrapper class though (not a child class), that you use in your code that provides a few simple methods that always call Dispose. Otherwise, just check really well.
您可以创建一个包装类(而不是一个子类)，在代码中使用一些简单的方法，这些方法总是调用Dispose。否则，检查一下。
#1
4
Since SqlConnection is sealed, you won't be able to inherit from it. (And I don't think it is a good idea to do so -- If it was possible, you should probably add your code in Dispose(false), because that is what the finalizer calls).
因为SqlConnection是密封的，所以您无法继承它。(我认为这样做不是个好主意——如果可能的话，您应该在Dispose(false)中添加代码，因为终结器调用的就是这个)。
It is better to detect these kind of problems by using a static code analysis tool, which has the power to detect all places in your code where you forget to dispose a connection. There is one built in in Visual Studio, or you can use FxCop.
最好使用静态代码分析工具来检测这些问题，静态代码分析工具可以检测代码中忘记处理连接的所有位置。在Visual Studio中有一个内置的，或者您可以使用FxCop。
To help you not to forget disposing the connection, it is a good idea to:
为了帮助您不忘记处理连接，最好是:
- Keep all DB connection code in one layer / assembly / module, so it is not scattered through the projects.
- 将所有的DB连接代码保存在一个层/程序集/模块中，这样就不会分散在项目中。
- Have utility methods to perform SQL commands and returning the results; so you don't create SQLConnection more places than you need to.
- 有实用方法来执行SQL命令并返回结果;所以您不会创建SQLConnection所需的位置。
- Remember to utilize the C# using construct.
- 记住使用c# using构造。
#2
2
One rule of thumb says, "If you have to think about the garbage collector, you're probably doing something wrong." (Of course, there are other thumbs...)
一条经验法则是，“如果你不得不考虑垃圾收集器，你可能做错了什么。”(当然，还有其他的拇指…)
Seems to me that ensuring that connections are closed either explicitly or via using and finally blocks is the best route.
在我看来，确保连接被显式关闭或通过使用和最终块关闭是最好的途径。
Obviously, you already understand these techniques... so maybe a once-through-the-code and a possible refactor is all you need.
显然，你已经了解了这些技巧……因此，您可能只需要一次遍历代码和一个可能的重构。
#3
1
If your application is using SQL Connection Pooling (the default) it will not matter since connections are re-used and don't get closed when you call .Close() or leave a using() {} block.
如果您的应用程序正在使用SQL连接池(默认)，这并不重要，因为连接被重用，并且在调用. close()或保留using(){}块时不会被关闭。
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
To answer your question, I dont believe there is a event for GC collection, however it would not matter if there was, because you would never know if the GC chose to recycle your object on that collection pass (not all objects are cleaned because of the generational algorithm).
为了回答您的问题，我不认为存在GC收集的事件，但是如果有的话，也没关系，因为您永远不会知道GC是否选择在该集合传递上回收您的对象(不是所有对象都因为分代算法而被清理)。
I would also avoid attempts to use timers and and "checks" as you will likely hold references to the object and prevent it from ever being disposed.
我还将避免尝试使用计时器和“检查”，因为您可能持有对对象的引用，并防止它被处理。
#4
1
I am not very familiar with the garbage collector and don't know if there is a direct way to get the information but my first idea is the following one.
我对垃圾收集器不是很熟悉，也不知道是否有直接的方法来获取信息，但是我的第一个想法是下面这个。
Just create a weak reference to a dummy object. If you look at the weak reference later and the object is no longer alive you can probably assume the a garbage collection occurred.
只需创建对虚拟对象的弱引用。如果您稍后查看弱引用，并且该对象已不再活动，您可能会假设发生了垃圾收集。
(This answer only applies to detecting garbage collector runs. I completely ignored the reason for doing this - there are much better strategies to deal with resource leaks.)
(此答案仅适用于检测垃圾收集器运行。我完全忽略了这样做的原因——有更好的策略来处理资源泄漏。
#5
0
First, if you're even considering using the GC, you've got serious problems somewhere. I suggest that the best way to ensure your code calls Close is to unit test your code using techniques like mocking. If your unit test indicates that Close was called, your code is correct and you don't have to do anything unnecessarily dangerous like mucking around with the Garbage Collector.
首先，如果您正在考虑使用GC，那么您在某些地方遇到了严重的问题。我建议确保代码调用接近的最好方法是使用mock等技术对代码进行单元测试。如果您的单元测试表明Close被调用，那么您的代码是正确的，并且您不需要做任何不必要的危险工作，比如在垃圾收集器中混日子。
Second, if you still insist on going the runtime checking route, the only thing you should even consider doing is hooking the StateChange event of SqlConnection. That'll fire if the ConnectionState changes from Open to Closed, for example.
其次，如果您仍然坚持执行运行时检查路线，那么您甚至应该考虑的惟一事情就是连接SqlConnection的StateChange事件。例如，如果ConnectionState从Open更改为Closed，则会触发。
#6
0
If you forget to dispose, the object will be finalized. There is no way to control the time at which this happens, and there is also no way for you to know if an object is finalized. A seperate thread has to be created in order to finalized objects, so it slows your application down. That's why you want to dispose. In all classes in the framework that implement IDisposable, a call to GC.SuppressFinalize is made, so the object isn't finalized.
如果忘记处理，对象将被最终确定。没有办法控制发生这种情况的时间，也没有办法让您知道一个对象是否已经完成。为了最终完成对象，必须创建一个独立的线程，因此会减慢应用程序的速度。这就是你要处理的原因。在实现IDisposable框架中的所有类中，对GC的调用。进行了打压，因此对象没有最终完成。
There is no way for you to controll this behaviour. If your object isn't used anymore, it will automatically be collected. All you can do to stop this, is call GC.SuppressFinalize, but I wouldn't recommend that, because if you then forgot, you'd be screwed for life.
你没有办法控制这种行为。如果您的对象不再被使用，它将自动被收集。要阻止这种情况，只需调用GC。抑制，但我不建议这样做，因为如果你忘记了，你将会被终生监禁。
You can create a wrapper class though (not a child class), that you use in your code that provides a few simple methods that always call Dispose. Otherwise, just check really well.
您可以创建一个包装类(而不是一个子类)，在代码中使用一些简单的方法，这些方法总是调用Dispose。否则，检查一下。