19

I need to set in the app.config the sqlite connection string. I want to set the path relative to the debug/release folders the database file will be copied to those folders.

<add name="EmailsSQLite" connectionString="data source=c:\Users\Test\Documents\Visual Studio 2008\Projects\TestConsole\Emails\data\EmailDatabase.sqlite" providerName="System.Data.SQLite"/>

and I want to have something like: 

<add name="EmailsSQLite" connectionString="data source=\data\EmailDatabase.sqlite" providerName="System.Data.SQLite"/>

Is that possible?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Radu D
  • 3,505
  • 9
  • 42
  • 69
  • 3
    Try this one: http://stackoverflow.com/questions/1833640/connection-string-with-relative-path-to-the-database-file#answer-6941582 – Alex M Feb 05 '12 at 20:27
  • @jo_asakura That is not a duplicate as that question is about an SQL Server CE database, not a SQLite database (I'm posting this comment as it just came up in the review queue to close as duplicate). – Mark Rotteveel Oct 01 '16 at 10:26

3 Answers3

24

You can specify a relative path as described in Lefty's answer.

However this will be relative to the current working directory, which will not necessarily be the directory containing your executable.

One way round this is to modify the connection string before using it, e.g.

In app.config:

 connectionString="data source={AppDir}\data\EmailDatabase.sqlite

In your code:

ConnectionStringSettings c = ConfigurationManager.ConnectionStrings[name];    
if (c == null)
{
    ... handle missing connection string ...
}
string fixedConnectionString = c.ConnectionString.Replace("{AppDir}", AppDomain.CurrentDomain.BaseDirectory);
... use fixedConnectionString
Joe
  • 122,218
  • 32
  • 205
  • 338
  • the only constraint is you can't parameter-ize the connection string easily if you use an auto-load framework like SubSonic (afaik - hope I'm wrong!) since you only provide a connection setting name (which is read-only by that point); shame you can't use "~" like in web.config – AJ. Apr 07 '11 at 11:23
  • Does this question really answer the question? I have the exact same problem, but it's appearing when I'm using the EDMX designer. It cant' show my data source (which is a SQLite db), because my coworker set it up, and it's using his path. – Jonathon Reinhart Jun 13 '12 at 18:56
  • In `App.config` i can't use `neither `{AppDir}` nor `%AppDir%`. This just creates folder witn exact same name in `AppData\Local\JetBrains\Installations\ReSharperPlatformVs14` – murzagurskiy Feb 25 '17 at 09:53
7

Yes it is possible. Try using |DataDirectory|.

In App.config

<add name="SqliteConnectionString" connectionString="Data Source=|DataDirectory|\Database.sqlite;" providerName="System.Data.SQLite"/>

More information on this page https://msdn.microsoft.com/en-us/library/cc716756.aspx

bluekushal
  • 499
  • 6
  • 14
  • 4
    This is for a SQLite file inside your project? You probably have to set the Build Action for the file to content so it gets copied to debug/bin or release/bin for this to work – DerpyNerd Nov 28 '17 at 12:24
5

Try using a "." before the first backslash in the data source part of the string.

eg.

data source=.\data\EmailDatabase.sqlite
Lefty
  • 166
  • 1
  • 12
  • This will only work if you use the relative path (which points at current working directory) _before_ you show any file open / save type dialogs which switch the cwd - assuming it was set to the executable path to start with (which may not be the case) – AJ. Apr 07 '11 at 11:25
  • 3
    If db doesn't exist you'll get this error: `A file activation error occurred. The physical file name '.\Database.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors.` but in a full-path method it will create the db for you. – Yar Jan 14 '16 at 22:04