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.

#asp.net #.net core #api