Creating password protected database fails

Question

When I create a password protected SQLite database like here ,EnsureCreated(); throws an object disposed exception:

I have no idea why. Added NuGet packages :

Microsoft.EntityFrameworkCore.Sqlite v1.1.0
System.Data.SQLite.Core v1.0.104

DbContext class:

using Microsoft.EntityFrameworkCore;
using System.Data.SQLite;

class TestMenuDataContext : DbContext
{
    public SQLiteConnection Connection { get; set; } 
    public SQLiteCommand Command { get; set; }

    public TestMenuDataContext()
    {
        //Database.Migrate();
        //Database.EnsureCreated();
    }

    public DbSet<Test> TestMenu { get; set; }
    ... and some more other tables ...

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        Connection = new SQLiteConnection(@"Data Source=TestMenu.db;");
        Connection.Open();

        Command = Connection.CreateCommand();
        Command.CommandText = "SELECT quote($password);";
        Command.Parameters.AddWithValue("$password", "TheUltimatePasswordPhraseWillBeAddedLaterHere");
        var escapedPassword = (string)Command.ExecuteScalar();

        Command.Parameters.Clear();
        Command.CommandText = "PRAGMA key = " + escapedPassword + ";";
        Command.ExecuteNonQuery();

        optionsBuilder.UseSqlite(Connection);
        
        // instead of password configuration above with this single line (no pw) below everything is fine
        //optionsBuilder.UseSqlite("Filename=./TestMenu.db");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        ... some key constraints
    }
}

The calling part in the WPF Gui (no MVVM):

private TestMenuDataContext _Db;

private void Window_Loaded(object sender, RoutedEventArgs e)
{
    //Do not load your data at design time.
    if (!System.ComponentModel.DesignerProperties.GetIsInDesignMode(this))
    {
        _Db = new TestMenuDataContext();
        _Db.Database.EnsureCreated();

Before that I had local context objects, local connection and command objects:

using (var db = new TestMenuDataContext())
{
    db.Database.EnsureCreated();

And :

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var conn = new SQLiteConnection(@"Data Source=yourSQLite.db;");
    conn.Open();

    var command = conn.CreateCommand();
    command.CommandText = "PRAGMA key = password;";
    command.ExecuteNonQuery();

    optionsBuilder.UseSqlite(conn);

Additionally tested as proposed :

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=TestMenu.db;"))
    using (SQLiteCommand command = connection.CreateCommand())
    {
        connection.Open();

        command.CommandText = "SELECT quote($password);";
        command.Parameters.AddWithValue("$password", "TheUltimatePasswordPhraseWillBeAddedHere");
        var escapedPassword = (string)command.ExecuteScalar();

        command.Parameters.Clear();
        command.CommandText = "PRAGMA key = " + escapedPassword + ";";
        command.ExecuteNonQuery();

        optionsBuilder.UseSqlite(connection);
    }

You are mixing the SQLite providers, EF Core uses Microsoft.Data.Sqlite, not the .NET desktop based system.data.sqlite, so start by removing that — ErikEJ, Feb 28 '17 at 16:46
In my understanding http://stackoverflow.com/a/39918364/3090544 there was told to add Microsoft.Data.Sqlite to the existing project, that means to mix it... Use EF Core "and" add System.Data.SQLite.Core — MarkusEgle, Feb 28 '17 at 17:56
**Microsoft**.Data.Sqlite and **System**.Data.SQLite are separate products. EFCore uses **Microsoft**.Data.Sqlite. — bricelam, Feb 28 '17 at 22:44
[Citation](http://stackoverflow.com/a/39918364/3090544) "...here is what I've done to get it working with **EF Core**: add the **System.Data.SQLite.Core** package to the project...It is very important to use the `SQLiteConnection` ... from the `System.Data.SQLite.Core` assembly and not the `SqliteConnection` from `Microsoft.Data.Sqlite`. " There is nothing mentioned to skip and remove EF Core. It was mentioned to be able to use a password for the Sqlite database with EF Core. This is exactly what I want. So reading fharreau post is very easy to misunderstand? — MarkusEgle, Mar 01 '17 at 07:51
Now I removed all the nuget packages installed by Microsoft.EntityFrameworkCore.Sqlite and got stopped: ["Entity Framework 6 with SQLite 3 Code First - Won't create tables"](http://stackoverflow.com/a/23128288/3090544) Now I suspect that it may work for fharreau using `EFCore` **and** `System.Data.SQLite` when not creating the database and just use the existing database... — MarkusEgle, Mar 01 '17 at 08:21
Please don't attempt to implement your own authentication scheme. There are many good, off-the-shelf authentication libraries. — Daniel Mann, Nov 20 '22 at 16:06

Creating password protected database fails

0 Answers0