Custom Database Connection Object: Should i use a general one (singleton) or one per object instance?

Question

What is the best practice: To create just one single static class (Singleton) that provide all needed connection to the database or create one object per DAO instance? Note that my project access more than one database simultaneously, so i created a class AcessoBanco that receives a .INI configuration file e returns me all the connections i need. I was using a single static class approach but i was receiving sporadic exceptions about concurrency because the system do some multi-threaded tasks. I solved it by creating locks in the AcessoBanco class, but, it is really a good idea? Maybe, if i put one instance of AcessoBanco per dao object the concurrency problem can be solved more elegantly, am i right? Some examples:

Using the Singleton Approach

public class Repository1
{
    public Repository1(string iniFilePath)
    {
        AcessoBanco.Configure(iniFilePath); // Singleton that creates all the connections (concurrency excepction solved using locks)

        // After configured, just call AcessoBanco.GetConnections() in any point of the code to get the connections 
    }
}

Using one instance per object

public class Repository2
{
    public AcessoBanco Conexoes { get; set; }
    public Repository2(string iniFilePath)
    {
        Conexoes = new AcessoBanco(iniFilePath); // Using one instance of AcessoBanco in each DAO. I will need to do it in every DAO.
    }
}

score 1 · Accepted Answer · answered Nov 23 '18 at 20:09

The details you provided do not indicate that using a Singleton pattern is a good idea; on the contrary, as you already discovered, it will likely cause issues with multi-threaded apps. Most database engines support connection pools and the overhead of opening/closing connections should be minimal. Do not open the connection in advance either. Just open / close the connection for the time you strictly need it. You are creating more headaches than you need to.

There's nothing wrong with a pattern like this or something similar:

using (var connection = new Connection()){

}

Yes, i agree, but my application user a variable number of database and i need to know in advance what is the list of available databases. The `AcessoBanco` class do it for me evaluating a .ini file. My question is: I need to create the list of available databases just one time (eg.: in the application start) or every time i need a connection. — Ewerton, Nov 23 '18 at 20:44

score 0 · Answer 2 · answered Nov 23 '18 at 20:00

0

It highly depends on what kind of a db you use.

Just 2 examples:

CosmosDB - its recommended to use singleton instance of the client since it uses (most often) http calls which likes HttpClient singleton instance
ADO.NET (for sql server) - its extremely bad idea to have singleton instances since your app might want to use connection from different threads and you'll run into a whole bunch of different issues. That's exactly why it uses connection pool under the hood.

answered Nov 23 '18 at 20:00

dee zg

13,793
10
42
82

I use SQL Server. so, one connection per instance, right? – Ewerton Nov 23 '18 at 20:45
per instance of what? – dee zg Nov 23 '18 at 20:56
per instance of the object that needs to access de database (my dao's), each object that needs do access the database will have its own instance of `AcessoBanco`. We are talking about the same thing? – Ewerton Nov 23 '18 at 21:43
no. you want to open connection as late as possible and close it as early as possible. which means either using `open()` and `close()` or as @Icarus mentioned, with `using` block. – dee zg Nov 23 '18 at 23:04

Custom Database Connection Object: Should i use a general one (singleton) or one per object instance?

2 Answers2