DbConnection and Finalizer



  • 1. Custom Collection Types
    Okay something has been driving me crazy. I see in the MSDN documentation that ToolBar.ToolBarButtonCollection implements the IList interface. The I look at members in the ToolBars.Buttons collection like: public void Add(ToolBarButton button); public ToolBarButton this[int index]; which don't implement the interface at all. The interface forces Add to take an object and the indexer to return an object. I guess what I am getting at is can I make my own custom collection class that doesn't strictly implement the IList interface but just has methods with the names of the IList interface, and then will it still work in the designer. And Why does the documentation say that collections like ToolBarButtonCollection implement IList when they really don't? Or am I missing something else? thanks, Adam dR.
  • 2. Cannot generate dataset
    When I select 'Generate Dataset' on the oleDbDataAdapter I get the message: 'Visual Studio could not create an instance of xxx.DataSet1. Confirm the class name is a valid DataSet class.' I haven't been able to find an answer to this exact problem in any discussion groups, but I read somewhere that it could be due to the database pathname having spaces in it. This didn't fix the problem. After creating the oleDbDataAdapter and oleDbConnection I can 'Preview Data' once, but after that I get the above error message. After I delete DataSet1.xsd from the Solution Explorer I can preview the data again (but only once). Any thoughts?
  • 3. LOGO in a WMV file
    Hi all, I want to add a logo in the top left corner of a wmv file, I am confused which SDK can I use. If there is any link regarding this then please send me. Amrro

DbConnection and Finalizer

Postby VHJlY2l1cw » Wed, 20 Aug 2008 05:18:00 GMT

Hello, Newsgroupians:

I've created a class that wraps DbConnection.

public class CSQL
  protected System.Data.Common.DbConnection m_conn;

  public CSQL
    // Initialize the m_conn in here

In my constructor, I create the DbConnection.  Now I've read a thousand 
times in MSDN and online that I'm not to call DbConnection.Close() in my 
finalizer.  However, is the following acceptable?

  if (this.m_conn.State != System.Data.ConnectionState.Closed)

Most times -- when I'm debugging my code -- I've noticed that when my 
finalizer is called, the state of m_conn is already closed, for I have 
pooling enabled.  Therefore, as long as I have pooling enabled, is this code 

If it is not valid, how might I solve the problem of creating a connection 
everytime I'd like to perform a query?  For instance, suppose I have the 
possibility of performing 1 to 1000 queries.  With one query, I can see that 
a quick connect and disconnect is valid.  However, if I have 1000 queries in 
a short period of time, obtaining a connection and maintaining that 
connection might be advantageous.  Thank you all for your time and 


Re: DbConnection and Finalizer

Postby Jeroen Mostert » Wed, 20 Aug 2008 05:50:01 GMT

Protected fields considered harmful. Make the base class responsible for 
managing the field, and have derived classes only use it:

   private readonly DbConnection m_conn;
   protected DbConnection Conn { get { return m_conn; } }

There might be a legitimate scenario where derived classes need to change 
the connection instance after construction, but this makes things harder to 
reason about.

This is correct. Finalizers should only release unmanaged resources. If you 
don't own any, don't implement a finalizer.

That still calls .Close() in a finalizer, it just sometimes doesn't. You 
want to avoid finalizers altogether here.

What you want to do is implement IDisposable, just like DbConnection does:

   class CSQL : IDisposable {
     public void Dispose() {

     protected virtual void Dispose(bool disposing) {
       if (disposing) {
         if (m_conn != null) m_conn.Dispose();

See  http://www.**--****.com/ 
this boilerplate. You will almost never need to implement a finalizer.

If connection pooling is enabled (it should be and usually is) there is no 
additional overhead for creating physical connections when you create 
logical ones. There is a slight overhead associated with resetting the 
connection for every logical "open" operation, but it's rarely significant 
compared to the workload.

In other words, when you do this:

   using (var csql = new CSQL()) {

A new CSQL object is created (and discarded), but not a new physical 
connection. Of course, if you really want to execute 1000 queries on one 
connection, you can, but if you do you have to consider the possibility that 
your batch fails halfway, as your connection will be unusable after most 
errors. In this case it's usually appropriate to wrap those 1000 queries in 
a transaction, to ensure they either all of them happen or none of them do.

Reusing logical connection objects buys you little if anything, and it makes 
error handling more complicated. You'll usually want to stick to the "one 
batch, one connection" model, where a batch might be as small as a single query.


Re: DbConnection and Finalizer

Postby Marc Gravell » Wed, 20 Aug 2008 05:52:33 GMT

> However, is the following acceptable?

It is both risky and unnecessary. Let the connection worry about
finalizing itself; you can't know (in a finalizer) whether the
reference is still valid. You might, however, choose to implement
IDisposable and close the wrapped connection in the Dispose() for
deterministic cleanup.

What connection are you wrapping? Some (such as SqlConnection) provide
pooling internally, so simply don't worry about it - the time to
aquire a connection from the pool each time is nil compared to the
time to execute a remote query. Just use the "open late, close early"


Re: DbConnection and Finalizer

Postby R8O2cmFuIEFuZGVyc3Nvbg » Wed, 20 Aug 2008 07:08:54 GMT

No, that is not a good solution, and it doesn't really serve any 
purpose, as the finalizer in the DBConnection object will close the 

By the time the finalizer runs, both the CSQL instance and the 
DbConnection instance are unreachable. That means that the DbConnection 
finalizer will be run at about the same time as the CSQL finalizer, and 
there is nothing that says that the CSQL finalizer will run before the 
DbConnection finalizer.

Actually, you don't want to run any finalizers at all. You should call 
the Dispose method of the DbConnection class when you are done with it, 
which will close the connection and suppress the finalizer. If you don't 
call the Dispose method, the finalizer works as a fallback, but this is 
a more expensive way to clean up the resources. If the GC detects that 
the object still has a finalizer, it will placed it in the freachable 
queue instead of being collected, where a background thread will 
eventually run the finalizer. After that the object can be collected.

No, that has nothing to do with pooling. That's because the DbConnection 
finalizer happened to run before the CSQL finalizer. The connection pool 
can't save you from the connections that you leave open.


Usually the connection pool takes care of that for you. When you close 
the DbConnection object, the low level database connection is returned 
to the pool instead of being disconnected. When you create a new 
DbConnection object using the same connection string, the low level 
database connection will just be reset and reused, which is a lot faster 
than establishing a new connection.

If you really have performace problems because you have a lot of 
database connections, you may need to find a way to keep the connection 
open between database operations, but using a finalizer to close the 
connection is not the way to do that.

G枚ran Andersson

Similar Threads:

1.DbConnection Close Throwing Exception

In the code below, I'm setting a watch on the variable dbCon. When I expand 
the watch on dbCon, one of the visible items under dbCon is "ServerVersion." 

When the dbCon.Close() executes, I see the following:

dbCon.ServerVersion threw an expection of type 

...and the details...

Operation is not valid due to the current state of the object.

I'd rather not throw exceptions. Any ideas what I need to do before closing 
to avoid all this?


DbConnection dbCon = null;

dbCon = db.CreateConnection();
dbCon.ConnectionString = DBConstants.ConnectionString(dbCon.ConnectionString);

adpt.SelectCommand = dbCmd;
adpt.SelectCommand.Connection = dbCon;


2.DbConnection, DbDataReader and DbCommand

Hi all,

I have a datasource and I want to connect to it like ADO.Net and build my 
own DbConnection and stuff. But I don't want to build my own SQL-parser. 
Does someone have a good idea to use SQL and implement it with my own 
DataReader? Or are there some good tutorials to build your own ADO.Net 
implementation? I cannot find it with Google.




I created my own DbConnection class 

using System;
using System.Data;
using System.Data.Common;
using System.Xml;

namespace MyNS
    public sealed class MyConnection : DbConnection, ICloneable
       // code implement DbConnection and ICloneable

But Visual Studio 2005 keeps complaining when I try to open this file as 

 One or more errors encountered while loading the designer. The errors are 
listed below. Some errors can be fixed by rebuilding your project, while 
others may require code changes. 

The designer must create an instance of type 
'System.Data.Common.DbConnection' but it cannot because the type is declared 
as abstract. 

System.ComponentModel.Design.Serialization.CodeDomSerializerBase.Error(IDesignerSerializationManager manager, String exceptionText, String helpLink)
System.ComponentModel.Design.Serialization.TypeCodeDomSerializer.Deserialize(IDesignerSerializationManager manager, CodeTypeDeclaration declaration)
System.ComponentModel.Design.Serialization.CodeDomDesignerLoader.PerformLoad(IDesignerSerializationManager manager)
Microsoft.VisualStudio.Design.Serialization.CodeDom.VSCodeDomDesignerLoader.PerformLoad(IDesignerSerializationManager serializationManager)
System.ComponentModel.Design.Serialization.BasicDesignerLoader.BeginLoad(IDesignerLoaderHost host) 

I also see the icon for the file changes and if I remove the DbConnection 
that MyConnection is inherited from. It is working fine.

Any ideas about this.

4.Dispose, Finalizer, = null, Dead references

I have a form derived mdi child with a private field that holds the
instance of a business class. As i noticed, this business object stays
until the program (which in fact is the parent form) exits, no matter
how many times i call GC.Collect(). if i explicitly call
"m_businessObject = null" in the Dispose() override, it seems to work,
but that can't be right to explicitly set references of managed
classes to null in the dispose method? The class itself in that case
was never even references somewhere (besides in a weakreference[] list
to test it). seems to be no difference in debug and release mode. any

Thx in advance, 
Daniel K

5.Having Trouble Calling ! Finalizer because of Operator Overloads

6. Event regsitration and finalizer

7. Finalizer Queue

8. *Dependent* Unmanaged Resources, Dispose and finalizers

Return to C#


Who is online

Users browsing this forum: No registered users and 64 guest