473,228 Members | 1,664 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,228 software developers and data experts.

DbConnection and Finalizer

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?

~CSQL
{
if (this.m_conn.State != System.Data.ConnectionState.Closed)
{
this.m_conn.Close();
}
}

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
valid?

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
consideration.
Trecius
Aug 18 '08 #1
3 2739
Trecius wrote:
I've created a class that wraps DbConnection.

public class CSQL
{
protected System.Data.Common.DbConnection m_conn;
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.
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.
This is correct. Finalizers should only release unmanaged resources. If you
don't own any, don't implement a finalizer.
However, is the following acceptable?

~CSQL
{
if (this.m_conn.State != System.Data.ConnectionState.Closed)
{
this.m_conn.Close();
}
}
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() {
dispose(true);
GC.SuppressFinalize(this);
}

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

See http://msdn.microsoft.com/library/fs2xkftw for the full explanation of
this boilerplate. You will almost never need to implement a finalizer.
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.
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()) {
csql.PerformQuery();
}

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.

--
J.
Aug 18 '08 #2
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.
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.
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"
approach...

Marc
Aug 18 '08 #3
Trecius wrote:
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?

~CSQL
{
if (this.m_conn.State != System.Data.ConnectionState.Closed)
{
this.m_conn.Close();
}
}
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
connection.

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.
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.
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.
Therefore, as long as I have pooling enabled, is this code
valid?
No.
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
consideration.

Trecius
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
_____
http://www.guffa.com
Aug 18 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: jim | last post by:
Why was a destructor (finalizer) included in the syntax of C# if implementing IDisposable is the sure way to release resources held by a class? If you were doing a language from scratch would you...
8
by: Richard Arthur | last post by:
This is a weird problem. 1) I use MediaDet to save a bitmap in a temporary file. 2) I create a bitmap using that temporary file's name. 3) I use the bitmap. 4) I want to destroy the file when...
3
by: esafran | last post by:
I've defined a class, but now I want to Define a Finalizer (destructor)... How do I call the Base Finalizer???, void Finalize() is a protected override method and Type.GetType Does not work. ...
21
by: ajbecker | last post by:
I'm trying to debug a problem I'm having where my finalizer queue is getting filled up faster than the thread can execute them, (or at least I think that's what's going on.) Is there a way that I...
4
by: Zen | last post by:
Hi, Is my finalizer safe? Thanks!! public class MyClass { private string m_str; public MyClass( string s ) {
5
by: tstephan | last post by:
I remember reading a few years back about destructors not being guarenteed to be run. Doing a search all I can find is a reference to some bugs with finalizers and appdomains in 1.0 and 1.1. Are...
6
by: Sharon | last post by:
When an object is register to event (delegate marked as event) with one of its methods, what happen when the event is raised if the object is dead (finalized)? In this case the delegate invocation...
12
by: =?Utf-8?B?VHJlY2l1cw==?= | last post by:
Hello, Newsgroupians: I am creating a wrapper for (I)DbConnection. I can connect to a database and place queries; however, I'm having some problems with my wrapped class. In short, I have the...
2
by: RSH | last post by:
I have a data access layer that needs to handle multiple Database types. I have seen genecric references being made to a DBConnection, DBCommand etc. What namespace are they in? Ron
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.