I am implementing what I thought was a simple object wrapper around a database where one object “PartRecord” represents one row of a database table.

I want a client to be able to request a given record and to receive an object that contains the data. The data read and exposed properties are all a piece of cake, but I am trying to share the MySqlConnection object in a coherent way.

My initial naive implementation was to keep a private MySqlConnection object in a VB module and to have a functions that my classes can call. This would effectively be a singleton that any of my classes could share.

Private MySqlCn as MySqlConnection

Friend Function GetMySqlConnection() as MySqlConnection
   If IsNothing(MySqlCn) then  MySqlCn = New MySqlConnection(cnstring)
   if MySqlCn.State = ConnectionState.Closed then MySqlConnection.Open(cnstring)
   Return MySqlCn
End Function

Friend Sub CloseMySqlConnection()
   If Not IsNothing(MySqlCn) AndAlso (Not MySqlCn.State = ConnectionState.Closed) Then  MySqlCn.Close
End Function

With this method you can generally ensure that the objects will share a connection and that it will be open whenever the database is available, but you cannot guarantee that the connection gets closed until the program ends.

Some alternatives that I have tried:

1. close connection after every read

   'in PartRecord constructor:
   Using MySqlCn as MySqlConnection = GetMySqlConnection
     'read data
   End Using
   'this causes an open and close for every object you instantiate
   'obviously bad
   

2. just leave it open

   'in constructor:
   Dim MySqlCn as MySqlConnection = GetMySqlConnection
   'read data
   'no close or dispose, open for the next guy
   'we are sharing an open connection, but
   'the connection won't really close until the program ends.
   'I think it will close then, but I was a lot more certain of it
   'in the COM days when I could wrap it in a class and
   'close it in the Terminate event when the reference count hit zero
   

3. quit having public constructors and have a object factory that implements IDisposable to manage the connection

   'maybe not as nice for the client code
   'but does force a sharing of connections
   'and the client can call PartFactory.Close or PartFactory.Dispose when they are done
   'the PartFactory replaces the singleton
   'even though you could have more than one if you wanted
   

4. your better idea . . .

I would like to know if I can keep my preferred style of public object constructors for a given record, but still manage the shared connection in a responsible way. I think this might be impossible since the standalone publicly creatable objects do not know when the client is ever done. I think I must leak the “we are connecting to a database y’know” abstraction to the client or else cause open connections or unnecessary network traffic.

Also, the documentation tells you that IDisposable is usually used for unmanaged things (files, handles, etc). Is is appropriate/necessary for holding a managed object like a .NET DBConnection object? I like that the client would have the Using syntax and that I am signalling that it needs closed in a standard way, but is the deterministic close that I am trying to get necessary and worth my effort in a garbage collected world? I am not comfortable saying “well the connection will close eventually when no one wants it” since this component will be eventually be used by many different clients in the organization. What if they crash, or we use it in a long-running server process?

If it matters, this is the first of several DB wrapper classes. Whatever scheme I use here, I want to maintain across the entire component.