This is another post in a series of posts on creating performant and scalable web APIs using ASP.NET core 2.0. In this post we’ll focus on database connections, making sure the connection pool is used efficiently …

We’ve chosen Dapper as our ORM in a previous post. So, we are responsible for creating SQL connections for our queries.

Some problematic code

We have a very dirty controller action method below for GET /api/contacts/{contactId}. This code has a serious problem with it - we catch and return the exception with a “200” status code so that we can easily see the problem. Obviously this isn’t production code!

[HttpGet("{contactId}")]
public IActionResult Get(Guid contactId)
{
    try
    {
        SqlConnection connection = new SqlConnection(_connectionString);

        connection.Open();
        string sql = @"SELECT ContactId, Title, FirstName, Surname
                        FROM Contact
                        WHERE Contact.ContactId = @ContactId";

        Contact contact = connection.QueryFirstOrDefault<Contact>(sql, new { ContactId = contactId });
        if (contact == null)
        {
            return NotFound();
        }

        return Ok(contact);
    }
    catch(Exception ex)
    {
        return Ok(ex);
    }
}

If we hit the endpoint in Postman, it responds fine. It we hit it again, it’s still fine … and again … still fine. It’s pretty quick as well.

So, what’s the problem? Let’s load test the endpoint …

We’re going to use Web Surge for the load test and hit the endpoint for 30 seconds with 8 threads. If we watch the Output window, - the test starts fine and then grinds to a halt. In fact we start to get timeouts.

If we try to hit the same endpoint again in Postman, we get the following, which gives us more information:

So, there is a problem getting a connection from the connection pool because all the connections are in use. If we look at our code again, we aren’t closing our connection. So, the connection stays open until the garbage collector closes it. This means that the next request that comes in won’t be able to use that connection from the connection pool - it will have to take a different connection. This process continues until there are no connections left in the pool … which is where we get the timeout error.

The fix

So, we can see that connections are limited resources and should only be open for when it is in use - i.e. we should close the connection straight after the query has finished.

A quick fix attempt is:

[HttpGet("{contactId}")]
public IActionResult Get(Guid contactId)
{
    try
    {
        SqlConnection connection = new SqlConnection(_connectionString);

        connection.Open();
        string sql = @"SELECT ContactId, Title, FirstName, Surname
                        FROM Contact
                        WHERE Contact.ContactId = @ContactId";

        Contact contact = connection.QueryFirstOrDefault<Contact>(sql, new { ContactId = contactId });

        connection.Close();
        if (contact == null)
        {
            return NotFound();
        }

        return Ok(contact);
    }
    catch(Exception ex)
    {
        return Ok(ex);
    }
}

… but what if the query errors? We’ll be left with an open connection again …

A better implementation is to wrap the query in a Using block. This results in our connection automatically being closed and disposed for us - even if an exception occurs.

[HttpGet("{contactId}")]
public IActionResult Getgood(Guid contactId)
{
    Contact contact = null;

    using (SqlConnection connection = new SqlConnection(_connectionString))    {        connection.Open();

        string sql = @"SELECT ContactId, Title, FirstName, Surname
                        FROM Contact
                        WHERE Contact.ContactId = @ContactId";

        contact = connection.QueryFirstOrDefault<Contact>(sql, new { ContactId = contactId });
    }
    if (contact == null)
    {
        return NotFound();
    }

    return Ok(contact);
}

If we run the same load test again, we find that our endpoint continues to operate fine for the full 30 seconds:

Conclusion

So, the key point to remember is that connections are precious resources and should only be open when we need them. As a rule of thumb we should create and open the connection immediately before executing the required database query and close the connection immediately after the query has finished and we have captured the results. The Using keyword is a robust way of ensuring our connection is always closed and disposed and returned to the connection pool.

We’ll remain on the topic of data access for our next post - this time looking at database round trips.