C# database wrapper design

Question

Hi,

I am designing a database wrapper for C#. Below are the two options I have:

Option A:

class DBWrapper:IDisposable
{
     private SqlConnection sqlConn;

     public DBWrapper()
     {
            sqlConn = new SqlConnection("my connection string");
            sqlConn.Open();
     }

     public DataTable RunQuery(string Sql)
     {
              implementation......
     }

     public Dispose()
     {
            if(sqlConn != null)
                   sqlConn.Close();
     }
}

Option B:

class DBWrapper
{
     public DBWrapper()
     {            
     }

     public DataTable RunQuery(string Sql)
     {
             SqlConnection sqlConn = new SqlConnection("my connection string");
             .....implementation......
             sqlConn.Close();               
     }   
}

For option A connection is opened when class is instantiated. So no matter how many times the caller calls RunQuery the connection is always ready. But If the application instantiates DBWrapper early in the application, the connection will be just opened and doing nothing until the application is finished. Also, it could have many DBWrapper instantiated during the execution. So, it's kinda wasting resources.

For option B it doesn't have the problem option A has, but the a new connection has to be opened and closed everytime the caller calls RunQuery. I am not sure how much it will hurt the performance.

Please share your expertise. Thank you for reading.

Answer 1

A few comments worth considering:

In the approach where you manage a (perhaps) long-lived connection, it is important to check whether the connection is open before running a query. I've run into issues before where NETCF closed unused connections after a while.

In the approach where you open a new connection per-query, ensure that your connection, commands, and (if used) data readers are all properly wrapped in using statements or try/finally+dispose() blocks to free up connections and locks.

Happy coding!

Answer 2

For performance reasons, you'll definitely not want to go with Option B (at least in the cases I experienced.) Let me suggest Option C:

class DBWrapper:IDisposable { private SqlConnection sqlConn;

public void EnsureConnectionIsOpen()
{
    if (sqlConn == null)
    {
      sqlConn = new SqlConnection("my connection string");
      sqlConn.Open();
    }

}

public DataTable RunQuery(string Sql)
{
    EnsureConnectionIsOpen();
    implementation......
}

public Dispose()
{
   if(sqlConn != null)
              sqlConn.Close();
}

} 

You might consider using the singleton pattern to make sure there is only one instance of your DBWrapper.

Answer 3

Option B is more transactional, which has its advantages. ADO.NET uses implicit connection pooling, so you do not have to worry about creating new instances of SqlConnection frequently.

You should consider whether you are using a connected or disconnected data model; as the second approach lends itself better to a disconnected model.

But as i've said above, connection pooling means that it makes virtually no difference in practical terms.

Answer 4

You could have an option C where the database is opened on request in RunQuery (if it is not open) and closed on disposed (when it was opened). That way the database is only opened when really needed and will be opened only once.

So in pseudo code:

class DBWrapper
{

     public DBWrapper()
     {            
     }

     SqlConnection sqlConn = null; 

     public DataTable RunQuery(string Sql)
     {
             if(sqlConn == null) sqlConn = new SqlConnection("my connection string");
             .....implementation......               
     }   
     public Dispose()
     {
            if(sqlConn != null)
                   sqlConn.Close();
     }

}

Also mind that the moment Dispose is called is not always directly after the object is not needed anymore (for example a function variable after the function is used). As far as I know it will be executed when the garbage collector collects the object (which is not directly). But I am not totally sure about this. This behaviour might also differ between web and non web application.

Answer 5

Garbage collector is triggered under rather complex conditions but basically it is invoked when memory exceeds some limit, it is invoked periodically as well but the period is not constant. You never can be sure when exactly garbage collector disposes and consequently (in another run) destroys the object. One thing you can be sure is the fact that garbage collector will never dispose and destroy the object that still has references. For example object that is referenced via static variables on the class neither will be disposed nor destroyed.