[SOLVED] EFCore SQLite connection string with relative path in asp.net

Issue

I have just added SQLite to my asp.net webApi project, and am having trouble working out how get the path to the App_Data folder to pass to DbContextOptionsBuilderUseSqlite

I have the following in the web.config I have a link to an external a config file with the conenction string…

<connectionStrings configSource="config\connectionStrings.config"/>

and in there I have…

    <connectionStrings>
      <add name="MyDatastore"
             connectionString="DataSource=./App_Data/test.sqlite" />
    </connectionStrings>

And in my DbContext.OnConfiguring I Have….

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
          if (!optionsBuilder.IsConfigured)
          {
            string path = WebConfigurationManager.ConnectionStrings["MyDatastore"].ConnectionString;
            optionsBuilder.UseSqlite(path);
          }
    }

The path is correctly retrieved (I can see I get the path as configured on connectionStrings.config

so ./App_Data/test.sqlite is passed to optionsBuilder.UseSqlite(path).

However, I get the following error…

SQLite Error 14: 'unable to open database file'. 

If I use just connectionString="DataSource=test.sqlite" /> then it seems to magically find the file in the App_Data folder, when I ran on my dev machine in debug, but I had problems on another machine (release build). I assume it is the path, though all I get back is ‘unable to open database file’.

I also tried..

 connectionString="DataSource=|DataDirectory|test.sqlite" />

This gives me a Illegal characters in path error.

The following does work (full path)

 connectionString="d:\0\test.sqlite" />

But I want to be able to use relative paths, eg maybe even .\datastore\test.sqlite.

Does any one have any ideas on this?

Thanks in advance

Solution

You’ll have to fix up the relative paths at runtime:

var builder = new SqliteConnectionStringBuilder(connectionString);               
builder.DataSource = Path.GetFullPath(
    Path.Combine(
        AppDomain.CurrentDomain.GetData("DataDirectory") as string
            ?? AppDomain.CurrentDomain.BaseDirectory,
        builder.DataSource);
connectionString = builder.ToString();

Answered By – bricelam

Answer Checked By – Willingham (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *