Close it or break it

After writing my blog, I put the URL on several websites and my CV to use as an example of the work I had done. A week later, I went on to add a blog only to find there was a database error and the website was not working.

The error suggested that the database could not be created but I knew the database existed and, when I went to my host account, I could access the database using the tools provided. Something was clearly preventing access to it resulting in the code assuming it did not exist and attempting to create it.

I had previously received another error in the code to do with a naming confusion between a namespace and a class. I first thought this was the reason for the error.

I decided to rebuild the code in Visual Studio. I created a new project, imported the code into it and this cleared the namespace / class naming error. However, it did not solve the issue with the database.

I asked on StackOverFlow and contacted the website host but neither gave me an answer which solved the problem.

So, I:

  • Created a new database,
  • Exported all the data and table structures from the old database
  • Imported them into the new one
  • Updated the connection string

It worked! The website connected to the database and all was well. Or so I thought...

A few days later, I added two new categories to the database. When I returned to the website later on, it gave the same error. Clearly, I was doing something to damage the database, but what?

I had read that SQL databases only had a limited number of possible connections and I wondered if I was failing to close the database properly. When I checked the controllers, I noticed I was creating a connection to the database but was not doing so inside a using statement. I rewrote all the controllers adding the following:

using (BlogAppContext db = new BlogAppContext())
{
    //do stuff here
}

However, as Entity Framework uses lazy loading (waiting until the data is needed before fetching it) this created its own error - the connection had been closed when the data was needed.

As I had noticed the error when I created a Category, I wondered if there was a difference between the Category controller and the Blog controller. There was. The Blog controller had the following and the Category controller did not:

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }

The lack of this routine meant I was not closing the database connection properly when working with Categories. This was leaving it open and blocking further connections.

I have added this to the category Controller and wait to see if this solves the problem.