Database connection object: Should I only have one for the whole system or one instance per class?

Question

What is the best practice: to have only one point (Singleton) that returns the connections to the database or to create an instance of the connection for each object?

My system accesses several banks and so I created a class AcessoBanco which, from a file .INI creates the necessary connections.

My project was using a static class (Singleton) that returned the bank connections I need. I started to have sporadic exceptions because my system is multi-threaded. I decided to create Locks for exclusive access to the properties that expose the connections, however, I was in doubt, this is a good practice.

I imagine that if each DAO created its own instance of the connections, this competition problem would be eliminated. I’ll give you some examples

Using a Singleton

public class Repositorio1
{
    public Repositorio1(string caminhoArquivoINI)
    {
        AcessoBanco.Configurar(caminhoArquivoINI); // Singleton único que cria as conexões (gerava problemas de concorrencia resolvido com locks)

        // Depois de configurado basta usar AcessoBanco.ObterConexao() em qualquer ponto do código para obter uma conexão 
    }
}

Using one instance per object

public class Repositorio2
{
    public AcessoBanco Conexoes { get; set; }

    public Repositorio2(string caminhoArquivoINI)
    {
        Conexoes = new AcessoBanco(caminhoArquivoINI); // Utilizando uma instancia por objeto. Isso tem que ser feito em toda classe que deseja acessar o banco.
    }
}

Answer 1

What’s best is always hard to say without a specific context.

People think that creating connections costs money and that there will be a complicated process. At least that’s what I imagine, I’m not in their head. I know a lot of people use that premise, but it could be they’re just copying what they’ve seen wrong around.

The fact is that the connecting object is a pool, It gives you a connection as needed and knows how to manage connections, and is a well thought out and developed object by people who understand how to do it properly. Everything works perfectly, with simplified performance.

It’s even possible to create a simple abstraction to simplify something specific, but to create a Singleton, or a substitute for the connection, as I’ve been seeing in almost every code that people put here, it’s absurd, it’s a antipattern. It is a person who understands little or nothing of the subject trying to create something certainly worse for little or no gain.

So the two cases presented seem to be wrong. Of course, I didn’t see this class AcessoBanco, but almost everyone does wrong.

These reported problems probably occur because it is implementing wrong. And the correct thing is not to implement any of this. There it begins to create new problems to solve the problems that should not even exist. If such an abstraction were required for any . NET application it would provide. Never implement anything you can’t properly justify.

I believe that the problem is because people try to create project patterns without even understanding what problem they’re solving, it includes DAO or repository, in general it’s quite complicated to do right, and it almost always doesn’t give the benefit that one thinks it will, she’s just doing what "everyone" is doing, without understanding how to do.

From the comment it seems to make no sense to have a Singleton, after all it has different connections, it is plural and not singular.