EFCore SQLite connection string with relative path in asp.net

Question

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

bricelam · Accepted Answer · 2018-03-15 16:28:24Z

12

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 Mar 15, 2018 at 16:28

bricelam

30.7k9 gold badges97 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SimpForJS · Accepted Answer · 2022-02-21 11:38:41Z

Note: This solution was tested for .Net Core 5, and one can presume it will work on 2.x, 3.x, 5

If you want to use a diferent project than the one provided when you started, you have to specify the correct path ("Data Source = ..\\MyApplication.DAL\\sqliteDatabase.db") in the appsettings.json.

In this presented case, you don't even need to write the method OnConfiguring(DbContextOptionsBuilder optionsBuilder) in the ApplicationDbContext.cs.

You have a full setup bellow (Startup & appsettings.json).

My project structure:

 -> MyApplication (solution)
      -> MyApplication.UI (initial project of the solution)
      -> MyApplication.BL (project)
      -> MyApplication.DAL (project)

Inside Startup.cs

 public void ConfigureServices(IServiceCollection services)
        {
           //... other services 
           
           services.AddDbContext<ApplicationDbContext>
                (x => x.UseSqlite(Configuration.GetConnectionString("SqliteConnection"))); 
           
           //.... other services and logic
        }

In appsettings.json :

 "ConnectionStrings": {
    "SqliteConnection": "Data Source = ..\\MyApplication.DAL\\sqliteDatabase.db"
  }

Danil Shaykhutdinov · Accepted Answer · 2020-08-22 10:24:00Z

2

Works perfectly for me.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var dataSource = Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "siteDB.db");
    optionsBuilder
        .UseSqlite($"Data Source={dataSource};");
}

answered Aug 22, 2020 at 10:24

Danil Shaykhutdinov

2,32726 silver badges27 bronze badges

Comments

lonix · Accepted Answer · 2021-11-03 10:39:43Z

2

Works for me on linux, .net core 5.

var builder = new SqliteConnectionStringBuilder("Data Source=MyDatabase.db");
builder.DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, builder.DataSource);

services.AddDbContext<MyContext>(o => o.UseSqlite(builder.ToString());

Assumes database is in the bin directory, e.g. MyProject/bin/Debug/MyDatabase.db or MyProject/bin/Release/MyDatabase.db.

answered Nov 3, 2021 at 10:39

lonix

22.4k32 gold badges149 silver badges301 bronze badges

Comments

Pushpa Kumara · Accepted Answer · 2021-08-03 10:28:18Z

0

If you are a .Net Core backend developer who use sqlite, make sure to use below code example. Otherwise SQLite Error 14: 'unable to open database file' error will come.

Startup.cs

var baseDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData);
string dbPathSystemLog = Path.Combine(baseDirectory, "CAMSCoreSystemLog.db");

SystemLogDBContext.cs

public class SystemLogDBContext : DbContext
{
    public SystemLogDBContext(DbContextOptions<SystemLogDBContext> options) : base(options)
    {
        Database.EnsureCreated();
    }
}

This line will create the Db if not exist

Database.EnsureCreated();

I was struggling two days. This will help someone.

edited Aug 3, 2021 at 10:28

answered Aug 3, 2021 at 8:22

Pushpa Kumara

4151 gold badge4 silver badges7 bronze badges

2 Comments

Gert Arnold Over a year ago

This is certainly not a generic solution. Who says that the database file is always in that location?

Pushpa Kumara Over a year ago

You have to create that there.. Other locations lead to permission issues.. The user accessing the API endpoint is not a user in the server or pc.

Collectives™ on Stack Overflow

EFCore SQLite connection string with relative path in asp.net

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related