Recently while working on one of my web projects, I’ve started wondering if it’s possible to minimize the number of database connections to 1 per http request in such a way that would
- keep the code simple
- keep connection time to the minimum (open connection when it is actually needed and close it as soon as the operation is done)
These constraints directly dictate that all database operations must be performed together within a single open connection and that connection must be closed as soon as the operations have finished. The best (and probably the only) place that the database operations can be grouped together is inside the presentation layer or in MVC terminology, the controller. It would look something like this:
public class PostsController : Controller
{
public ActionResult Get(int id)
{
using (var context = new MyDbContext())
{
ViewBag.Post = context.Posts.FirstOrDefault(p => p.Id == id);
ViewBag.Tags = context.Tags.ToList();
}
return View("Post");
}
}
There is nothing new here, and actually the code above demonstrates a bad practice of putting data access logic into the presentation layer (controller). To alleviate the issue, we will encapsulate our data access logic in repository classes, and make all database calls through these repositories.
public class PostsController : Controller
{
public ActionResult Get(int id)
{
ViewBag.Post = new PostRepository().GetPostById(id);
ViewBag.Tags = new TagRepository().GetAllTags();
return View("Post");
}
}
Now, however we are opening the connection twice and that’s not what we want. To tackle the problem we need a way to share a connection between repositories. We want something like this:
public class PostsController : Controller
{
public ActionResult Get(int id)
{
using (var context = new MyDbContext())
{
ViewBag.Post = context.PostRepository.GetPostById(id);
ViewBag.Tags = context.TagRepository.GetAllTags();
}
return View("Post");
}
}
Enter, RepositoryManager class:
public class RepositoryManager : IDisposable
{
private readonly SqlConnection Connection;
public PageRepository PageRepository { get; private set; }
public TagRepository TagRepository { get; private set; }
public RepositoryManager()
{
this.Connection = new SqlConnection("myconnectionstring");
this.PageRepository = new PageRepository(this.Connection);
this.TagRepository = new TagRepository(this.Connection);
this.Connection.Open();
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing,
/// or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
if (this.Connection != null)
{
this.Connection.Dispose();
}
}
}
The idea is that the RepositoryManager will have a connection which will be shared among all repositories. When RepositoryManager is no longer needed, the connection will close.
Notice however, how RepositoryManager never actually opens the connection, and that is because we only want to open the connection when the first database call is being made and not any earlier. This means that we can instantiate a RepositoryManager, but a connection won’t be opened until one of its repositories is being used. To achieve this, inside the repositories, before we open a connection we we need to check if the connection has already been opened, in which case we simply use this existing connection. If the connection is not then we open one. Now what do we do when the operation has finished? Do we close the connection or keep it open? The question can be translated into another form:
Is the connection shared or not?
“Sharing” however can happen in several different ways:
- No sharing - connection is opened and closed for each method of a repository
- Sharing within same repository
- Sharing among different repositories
This leads us to ConnectionType enum:
public enum ConnectionType
{
/// <summary>
/// Instructs repository to share connection with other repositories.
/// Repository will not be responsible for opening/closing the connection.
/// </summary>
SharedConnection,
/// <summary>
/// New connection will be opened for each separate operation of the
/// repository.
/// </summary>
ConnectionPerMethod,
/// <summary>
/// Connection per repository.
/// </summary>
ConnectionPerRepository
}
From this definition of connection types we create a base class for repositories.
public class BaseRepository : IDisposable
{
private readonly ConnectionType ConnectionType;
public BaseRepository()
{
this.ConnectionType = ConnectionType.ConnectionPerMethod;
}
public BaseRepository(ConnectionType connectionType)
{
this.ConnectionType = connectionType;
}
public BaseRepository(SqlConnection connection)
{
this.Connection = connection;
this.ConnectionType = ConnectionType.SharedConnection;
}
protected SqlConnection Connection { get; private set; }
public void Dispose()
{
if (this.Connection != null)
{
this.Connection.Dispose();
}
}
protected T Execute(Func operation)
{
bool isNewConnection = this.OpenConnection();
try
{
using (var context = new MyDbContext(this.Connection))
{
return operation(context);
}
}
finally
{
this.CloseConnection(isNewConnection);
}
}
protected void Execute(Action operation)
{
bool isNewConnection = this.OpenConnection();
try
{
using (var context = new MyDbContext(this.Connection))
{
operation(context);
}
}
finally
{
this.CloseConnection(isNewConnection);
}
}
private bool OpenConnection()
{
if (this.Connection == null)
{
this.Connection = new SqlConnection("myconnectionstring");
this.Connection.Open();
return true;
}
else if (this.Connection.State != System.Data.ConnectionState.Open)
{
throw new Exception("Connection is not open. Current state is: " +
this.Connection.State);
}
return false;
}
private void CloseConnection(bool isOwner)
{
// Connection can be closed only if the caller owns it.
if (isOwner)
{
if (this.ConnectionType == ConnectionType.ConnectionPerMethod)
{
this.Connection.Dispose();
// We also need to set the connection to null, because otherwise
// it will be assumed as an open connection.
this.Connection = null;
}
}
}
}
Our repository will then look something like this:
public class PostsRepository : BaseRepository
{
public PostRepository()
{
}
public PostRepository(SqlConnection connection)
: base(connection)
{
}
public Post GetPostById(int id)
{
return this.Execute(context =>
{
return context.Posts.FirstOrDefault(p => p.Id == id);
});
}
}
There you go, a repository that can be used on its own, together with other repositories and always making sure that the number of connections we open is no more than 1.
Feedback?