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 useSqlite.
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.
So, to summarise, you need to have 3 programs to use SQLite. SQLite,System.Data.SQLite, and SQLite Manager.
Just now started my first sample will update you on that in the posts to come in future...