I made a classic rookie mistake with ASP.NET (MVC) the other day. In my spare time, I’m working on this small sample application for myself in order to learn more about ASP.NET MVC. After working on this small web application for a while, I came to the point where I needed to set up a database for persisting some data. I just wanted to get this out of the way as quickly as possible, so I used Fluent NHibernate’s schema generation to quickly set up a SQLite database. Everything worked splendid so far.
But when I did some manual tests by using the web application for entering some data and storing it in the database, I started to notice some strange behaviors. Particularly, after the application managed to successfully store a record in the database, the next thing when it tried to read it back out again, the record was gone. After checking the code involved, I tried to debug this a couple of times. Although the transaction completed successfully, and the record became visible in the database (I used SQLite Administrator for that), the next thing I knew, the record disappeared again.
At first I thought it had something to do with the transaction. I’ve had some issues with transactions and SQLite in the past, but I was quickly able to root this out. After doing some more digging and debugging I found the actual reason for this strange behavior.
Whenever I’m working on a small console or Windows application, I tend to save the SQLite database file in the bin folder of the application. However, this isn’t a very good idea for ASP.NET applications. Making modifications to the bin folder of an ASP.NET web application causes the application to restart. The fact that storing a record in the SQLite database modifies the file, the web application got restarted as a result. Because I’m bootstrapping the ASP.NET application (IoC, NHibernate, etc. …) in a bootstrapper class that is initiated by the Application_Start method of the Global.asax file, the database got recreated for every application startup which caused the record to ‘magically disappear’.
I changed the configuration for the SQLite database file so that it got created in the app_data folder instead of the bin folder, and everything worked just fine again.
What can I say? I’m a noob.