CODEWITHSUNDEEP
c#asp.netazuresqlite
Mahmudul Hasan
Mahmudul Hasan

Reputation: 828

ASP.NET CORE Web App throwing SQLiteException: unable to open database file

I have an SQLite DB and I uploaded it to my server [hosted in azure]. But when I try to access it, It throws SQLiteException: unable to open database file.

My connection string is something like this -

string connectionString = "Data Source=https:\\mywebsite.azurewebsites.net\\DB\\persondb.db; Version=3"

I also tried this too -

string connectionString = "Data Source=~/DB/persondb.db; Version=3";

Here is my full method's code -

        public static List<PersonDetails> GetPersons()
        {
            // string connectionString = "Data Source=https:\\mywebsite.com\\DB\\persondb.db; Version=3";
            string connectionString = "Data Source=~/DB/persondb.db; Version=3";

            using (IDbConnection cnn = new SQLiteConnection(connectionString))
            {
                var output = cnn.Query<PersonDetails>
                    ("SELECT * FROM persons",
                    new DynamicParameters());
                return output.ToList();
            }
        }

How can I resolve this issue?

Upvotes: 1

Views: 750

Answers (3)

dynamiclynk
dynamiclynk

Reputation: 2331

This worked for me .Net 6

Program.cs

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
var services = builder.Services;
GlobalContext.ApplicationRootPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)??"";

appSettings.json

"DefaultConnection": "DataSource={dbPath}\\SqlLite\\app.db;Cache=Shared",

Usage:

var connectionString = connectionString.Replace("{dbPath}", GlobalContext.ApplicationRootPath);
                    options.UseSqlite(connectionString, sqlLiteOptions =>
                    {
                        sqlLiteOptions.MigrationsAssembly("MyAssembly");
                    });

Upvotes: 0

Mahmudul Hasan
Mahmudul Hasan

Reputation: 828

Jack Jia's answer clears the scenario. But since Server.MapPath doesn't work in ASP.NET Core, we can get the server root path via IWebHostEnvironmentinterface.

This problem can be resolved like this -

  1. Create a string that can be accessed globally [e.g. internal static string rootpath;]
  2. In startup.cs file, assign rootpath in the method public void Configure(IApplicationBuilder app, IWebHostEnvironment env) [e.g. Program.rootpath = env.WebRootPath;]
  3. Now a valid connection string should look like this - string connectionString = $"Data Source={Program.rootpath}/DB/demo.db; Version=3";

Upvotes: 1

Jack Jia
Jack Jia

Reputation: 5549

Refer to About SQLite

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files.

You may use the following code in your ASP.NET web project:

    string path = Server.MapPath("~/DB");
    string connectionString = $"Data Source={path}/demo.db; Version=3";

    using (var con = new SQLiteConnection(connectionString))
    {
        con.Open();
        var cmd = new SQLiteCommand("SELECT SQLITE_VERSION()", con);
        return cmd.ExecuteScalar().ToString();
    }

The code Server.MapPath("~/DB") will find the physical path of the DB folder in your project, and then the connection string should be work.

Upvotes: 1

Related Questions