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.
    }
}

Answer 1

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()){

}