Is it necessary to deconstruct singleton sql conne

问题:

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?

回答1:

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.

回答2:

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.