Where to put a common database connection for my classes

Question

I have several classes (Repositories) which do the task of saving/retrieving some objects in/from database; all of them need to establish a connection to one database.

I thought in order to avoid redefining the ConnectionString and the SqlConnection in each class, passing an open connection to them. Then where/when is the best place/time to define/open that connection and pass it to the classes?

Are there better approaches/patterns to have access to this common resource?

score 10 · Accepted Answer · edited Apr 12 '17 at 07:31

Passing an open connection to each class is probably not the best idea. Take a read of Creating database connections - Do it once or for each query?

It recommends opening and closing the connection per each query you wish to run, rather than passing the open connection to your repository.

You could try something like this to help you with managing your connections.

public class Repository
{
    private readonly string _connectionString;

    public Repository()
    {
        _connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString
    }

    protected SqlConnection GetConnection()
    {
         return new SqlConnection(_connectionString);
    }


}

public sealed class UserRespository : Repository
{

    public User GetUsers()
    {
        using (var connection = GetConnection())
        {
            using (var commnad = new SqlCommand("SqlQuery", connection))
            {
                //Execute Query
                //Return results
            }
        }
    }
}

score 5 · Answer 2 · answered Apr 28 '15 at 08:50

Having an open connection and passing it between classes are generally a bad idea. Sure, opening a connection is quite a performance hit, but that's already taken care of by the connection pool by reusing already opened connections. A remark though: always wait as long as possible to call connection.Open(), especially in multithreaded code since this will assign the connection to your method (wich will potentionally increase the needed amount of open connections to the database).

To make your classes as generic as possible, I would recommend having a base class exposing a method with IDbConnection, and your repositories will have a more generic implementation.

internal abstract class Repository
{
    private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString;

    protected IDbConnection GetConnection()
    {
        return new SqlConnection(ConnectionString);
    }
}

public class MyRepository : Repository
{
    public IEnumerable<object> Get()
    {
        using (var connection = GetConnection())
        {
            connection.Open();

            ...
        }
    } 
}

Where to put a common database connection for my classes

2 Answers2