SQLite data source path strange issue?

Question

The most basic form of a SQLiteConnection string is "data source = ..."

I've tried a demo in which I had a SQLite database file called MyData.sdb, this file was placed right in my demo project folder tree (for example: DemoProject\MyData.sdb). And the following SQLiteConnection string worked:

"data source = MyData.sdb"

I could select tables OK. I even tried adding MyData.sdb to another child folder in my demo project folder (for example, DemoProject\Data\MyData.sdb) and the above connection string still worked? Wow, can't believe. But now is the most strange thing (against what I've experienced in the demo project):

When I tried another project and applied the same to it, it threw an exception called "No such table..." when I tried selecting a table which did exist in my SQLite database. It's so strange. I've doubted that it's for the incorrect path to the database file. And I've tried using the absolute path of the database file like this:

"data source = D:\\demo project\\MyData.sdb"

Then it worked, Why the hell did I use a relative path for my SQLite database file in the demo project well but can't apply in my new project?

I don't think it's easy, it needs your experience to explain. The strange thing may be in the demo project or in my new project, there should be one which worked abnormally.

Please help! Thank you!

Answer 1

The relative path to your database files might not work between projects because of what your app's current working directory could be. You can find out what your app's working directory is by getting Environment.CurrentDirectory while the app is running.

For example, in a C# Console app, the working directory could be:

D:\Projects\SQLiteTest\bin\Debug

If your data source happens to be "data source = MyData.sdb", then your Console app will be looking for that database at D:\Projects\SQLiteTest\bin\Debug\MyData.sdb.

If your next app is an asp.net app running in IIS, the working directory could instead be:

C:\windows\system32\inetsrv

Your app won't be able to find your database location because you most likely put the database in your project folder instead of at C:\windows\system32\inetsrv\MyData.sdb.

This SO question contains some solutions to consistently resolving a relative path to your database.