Visual Studio 2008 integrates Sql server 2008 express nicely into App_Data folder. You just create the database using Server Explorer, and it inserts the connection string into web.config for you. I used it when I worked on my toy project – ToastManager. I finished coding and testing on my machine and deployed it to an integration server which belongs to my SCRUM team.

A problem, as usual, arose on the integration box. My windows login had sysadmin access to my local Sql Server express, but the process that runs ToastManager did not on the box.

I blindly thought that Sql Server express is a stand-alone DB and it can be deployed together with the applicaiton. It was misunderstanding. Sql Server Express is still a server application that needs to be installed on a remote machine. I looked for an alternative and soon found that many people use Sqlite.

Sqlite is an open source database and people testify that it is really fast. In order to use with .Net, you need to install System.Data.SQLite, which is another open source ADO.NET provider for SQLite database engine.

Once you download Sqlite, you can use its own command-prompt based tool, but probably, your first reaction is to try to find some GUI management tool. Fortunately, there is one too. It is FireFox Add-on: SQLite Manager.

Make sure you have System.Data.SQLite.dll and System.Data.SQLite.Linq.dll in your application’s bin folder. For some reason, though I referenced those two dlls in the project, they were copied into the folder. If those dlls are not in bin folder, you will have “Failed to find or load the registered .Net Framework Data Provider”. (http://sqlite.phxsoftware.com/forums/t/251.aspx)

When you compare data using LINK, do not use .Date property. For some reason, it works with Sql Express, but not with Sqlite. I use “where o.OrderDate >= DateTime.Today” instead of “where o.OrderDate.Date == DateTime.Today”