Integrating SQLite with a Windows application

Question

So I created a simple WPF application that allows a user to INSERT a string into a SQLite database (a .S3DB file). The user can also SELECT a string from the database and DELETE the string from the database...very simple application.

I want to distribute this simple application, so I've created setup.exe file for the application by creating a new project and using the setup wizard on VS2010. When the user installs the application, it creates a "TestDatabase" folder in the Program Files directory and adds these two files along with the .exe file:

1. System.Data.SQLite.DLL file (since it's a Detected dependency)
2. TestDatabase.s3db file (A test database for the user to use)

When I install it on the computer I'm developing on...everything works. However, when I install it on another computer, it crashes when I try to open the database from the application (which is stored in C:\Program Files (x86)\TestDatabase\TestDatabase.s3db).

I was wondering if someone can help solve this problem? Thanks.

Answer 1

You can't modify files in the Program Files directory unless you're running in Administrator mode. You should deploy the .s3db file to a folder inside %appdata% instead.

Answer 2

You definitely want to resolve the permissions part of the equation, but you may also run into the DbProviderFactories issue I ran into over the weekend while figuring out how to use the latest binaries from sqlite.org. You can read the details on my blog post but the short answer is that you need a config file entry or equivalent code like this:

<system.data>
  <DbProviderFactories>
    <remove invariant="System.Data.SQLite" />
    <add name="SQLite Data Provider" 
      invariant="System.Data.SQLite" 
      description=".Net Framework Data Provider for SQLite" 
      type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
  </DbProviderFactories>
</system.data>

And you need to make sure that you have set "Copy local" equal to true in your assembly reference so that the SQLite assembly will be included in your \bin and setup project.

Answer 3

I realized that the reason why it was crashing when I opened the database is because I didn't install the setup package on the new computer I was testing on. The setup package includes all the runtime components and dependencies. The setup packages can be found here:

http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Also, ravenspoint, RandomEngy, and bryanbcook...you guys were right. Reading/writing a database from the Program Files folder is a bad idea because of the permission problem that can occur when accessing the database from the Program Files folder. So I saved my database in the AppData folder where the database can be read/written without any permission problems.

Answer 4

Could be a permissions problem. You might want to use Isolated Storage for this.

Also could be that your project is using assemblies from the GAC. Make sure your assemblies are local to your project.

Answer 5

The problem is that your user does not have permission to write to the Program Files folder. The quick fix is to install the test db file in

C:/Program Data/<your company name>/<your application name>/

However, it is not a good idea to use the installer to drop database files into particular folders. You will end up with problems when your want to distribute an updated applications to users that have installed a previous version. It is better to write code in your application the looks for the database and creates a new one if it is not found. That way, an updated application can use a database that has been generated by a previous version and may therefore represent a significant amount of user investment.

For more details of how to handle in detail database driven application upgrades, see How to manage desktop file database versions?