1

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.

  • don't you think "object pool" pattern would satisfy your need? I don't have a good .net background but I was looking for same designing solution for my java project. I found this implementation to be a valid approach: javaworld.com/article/2076690/java-concurrency/… .Let me know what you think? – Sas Aug 14 '14 at 2:48
  • @Sas Interesting. So the objects request the connection from the pool manager, use it, then release it through a custom mechanism without closing it. The pool manager would keep manual reference counts and if no one has any connection checked out for a given length of time, close the connection. If someone wants it after that, reopen it. – Mike Supports Monica Aug 14 '14 at 2:57
  • exactly, except user doesn't need to close the connection coz the manager will take care of it. Even if user happened to close the connection, we still have a validation check to make sure we don't keep any closed connection in the pool. – Sas Aug 14 '14 at 3:15
  • In this way we have limited number of connections object in the pool ready to be used. Which is much faster than creating it on every client request and closing it again and again. Also when there is no more connection available, rather than holding the client for connection object to avilable, we are creating a new connection. At the same time, when the connection object not being used for certain amount of time, we just simply destroy the connection objects from the pool to minimize the resource usage. – Sas Aug 14 '14 at 3:26
1

Don't share a connection, unless you have multiple commands in a single transaction (which requires you to keep the connection open). Open / Close after each action which you state as 'obviously bad' is actually the way to go, as connection pooling makes sure you don't lose much (if at all) time.

It also looks like you're re-inventing some data-access system, and you're on track to do it poorly (as you don't know how to solve this simple solution). Pick one of the shelve (there are many free / OSS ones if you don't want a commercial solution) and spend your time on what you should do: write business logic instead of plumbing code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.