Is it necessary to deconstruct singleton sql connections?

Question

Normally I'm using the statements:

using (SqlConnection connection = new SqlConnection("Data ...")) 
{
  ....
}

to define areas where I use sql commands. Now for a specific application I'm considering putting the sql connection into a singleton instead of the above construct:

public class SimpleClass
{
    // Static variable that must be initialized at run time. 
    public static SqlConnection singletonConnection;

    // Static constructor is called at most one time, before any 
    // instance constructor is invoked or member is accessed. 
    static SimpleClass()
    {
        singletonConnection = new SqlConnection("Data Source.....");
    }
}

Now my question with this is....normally when I use "using" the connection gets closed,.... . But when I use a singleton it exists for the whole runtime of the program itself. Is it though necessary to make a "deconstructor" there in order to close the connection at the end of the program?

Don't do that...putting a sql connection in a singleton is an absolutely terrible idea, especially when you get to transaction management. Connections are cheap. Create them when you need them and close them as soon as you are done (with the appropriate using statement). — David L, Sep 18 '15 at 06:28
Really, really bad idea - stick to the `using` constructs - much better design and performance! — marc_s, Sep 18 '15 at 06:32
@Thomas How is exposing your application to a memory leak better in terms of performance than not doing so? — kamil-mrzyglod, Sep 18 '15 at 06:38
@Thomas if you tell us what you want to achieve, we may could help you... — BendEg, Sep 18 '15 at 06:40
tnx. its mostly an old construct I used once where I know that its bad design although in a few cases it could be that it is useful (emphasis on could not that it should or really is). When I temporarily considered the construct for a current problem I thought "ok and how should it be closed?" and that is how I came up with the question. That its not a good design in itself is clear there though. Although the comments brought up an interesting point that I will ask in a follow up question (the performance part). — Thomas, Sep 18 '15 at 06:43
And believe it or not.....the singleton design for sql connections above is even teached at some universities around here. Followup question there: http://stackoverflow.com/questions/32645554/is-the-performance-of-non-singleton-sql-connections-better — Thomas, Sep 18 '15 at 06:44
Related: http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060 — Sriram Sakthivel, Sep 18 '15 at 06:56

Chris · Answer 1 · 2015-09-18T07:21:05.740

When we want to have a factory function for something, that implements IDisposable we are using an Action like this:

public class SimpleClass
{
    public static void InvokeSql(Action<SqlConnection> func)
    {
        if (func == null)
        {
            throw new NullReferenceException("func");
        }

        using (SqlConnection connection = new SqlConnection("Data ..."))
        {
            connection.Open();
            func(connection);
        }
    }
}

You can use the SqlConnection now like this

public class Foo
    {
        public void Bar()
        {
            SimpleClass.InvokeSql((connection) =>
                {
                    string sql = "DELETE [User]";
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                });
        }
    }

So the connection is created and Disposed for ervery use.

score -1 · Answer 2 · answered Sep 18 '15 at 06:56

-1

Just consider this: If you use a singleton for the connection and you get a connection error (for example: connection lost...), who handles (=close) the brocken connection? So if you want to stick to your singleton mechanic i would recomend to use a "try-catch" block with a close; statement in the finaly block:

  try
  {
    singletonConnection = new SqlConnection("Data Source.....");
  }
  catch (SqlException)
  {
    throw;
  }
  finally 
  {
    singletonConnection.Close();
  }

But like macr_S said: using a using construct would be the easiest way.

answered Sep 18 '15 at 06:56

Thomas Meyer

384
1
11

Good catch there! In terms of the original question though where should the deinitialization happen and how? OR is it necessary to do it manually at all (the non error happened deinitialization for when the program is to be closed)? – Thomas Sep 18 '15 at 07:06
In gereal you shouln't keep a connection for the whole runtime open. So, yes you always have to make shure that the connection is manualy closed after each use. How you do this depends on you. On the question where: - your code and: - singletonConection.open(); should be also in the try block – Thomas Meyer Sep 18 '15 at 07:23
I did a bit of research: close(); dont trigger IDisposable.Dispose method. So you should always use the using-construct... – Thomas Meyer Sep 18 '15 at 12:27

Is it necessary to deconstruct singleton sql connections?

2 Answers2

Linked