How, when and whether it is necessary to close the connection to the database in the singleton class at all?

Question

SQL

There must be a single connection to the database for the entire application.
Implemented using the singletion pattern.

public sealed class Connection
{
      private static Connection _instance = null;
      private Connection() { }

      public static Connection Instance
      {
          get
          {
              if (_instance == null) _instance = new Connection();
              return _instance;
          }
      }

      public SqlConnection GetSqlConnection()
      {
          return new SqlConnection(ConfigurationManager.ConnectionStrings["CancellationCS"].ToString());
      }
}

What is the best way to do it: -
open the connection once and do not close it until the application ends? (what is the best way to close the connection when the application ends)
-every time (i.e. for any query to the database) open and close the connection?

Answer 1

In the general case 1. Opening a connection is a rather expensive procedure to do it for every sneeze. You can measure the speed / load yourself in the test with the opening of one connection or many connections.

Answer 2

Depends on how often the application accesses the database.
If often, it is better to use one common connection. If rare, then it is better to close to free up resources.
For example, in web applications, as a rule, a separate connection is opened for each HTTP request and several SQL queries are executed in it.
Also read about connection pool .
Roughly speaking, this is a way to optimize the application, in which there is a certain pool of connections. When a connection is opened, if there is already one in the pool, it is taken from there (respectively, no resources are spent on its initialization). When a connection is closed, it is returned to the pool and can be reused. The .NET Framework manages the connection pool according to the settings you specify.

Answer 3

It is worth starting with the question: is this a desktop application or a web one?
If the former, then something like:

class StaticClass 
{
   static StaticClass() {
       AppDomain.CurrentDomain.ProcessExit +=
           StaticClass_Dtor;
   }

   static void StaticClass_Dtor(object sender, EventArgs e) {
        // clean it up
   }
}

If the web, then cut your class to hell. :)
PS: in general, the use of singletons to work with the database is evil. It is better to use some kind of ORM, the same Entity Framework or NHibernate.

Answer 4

The base connection class cannot be a singleton, otherwise you cannot create multiple connections.