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.
