There is a method in my code, which initializes sqlite3 db to a specified directory as you can see in the following code snippet:

using System;
using System.IO;
using Microsoft.Data.Sqlite;

namespace test
{
    public static class DatabaseUtils
    {
        public static void InitializeDatabase(string directoryPath)
        {
            if (!Directory.Exists(directoryPath))
            {
                throw new Exception();
            }

            string sqliteFile = Path.Combine(Path.GetFullPath(directoryPath), "test.sqlite3");

            if (File.Exists(sqliteFile))
            {
                throw new Exception();
            }
            else
            {
                try
                {
                    File.Create(sqliteFile).Dispose();

                    using (SqliteConnection connection = new SqliteConnection($"Data Source={sqliteFile};Mode=ReadWrite"))
                    {
                        connection.Open();
                        
                        using (SqliteCommand command = new SqliteCommand())
                        {
                            command.Connection = connection;
                            command.CommandText = "DROP TABLE IF EXISTS MyTable; CREATE TABLE MyTable (Primary_Key INTEGER PRIMARY KEY, Text_Entry NVARCHAR(2048) NULL)";
                            command.ExecuteNonQuery();
                        }

                        connection.Close();
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }
    }
}

Case 1: When calling this this method for the first time, everything works as expected. Which means => Database file is created with table(MyTable) that is specified in the code.

Case 2: When calling this this method for the 2nd time, after manual database file deletion, database file is created WITHOUT table(MyTable) that is specified in the code and the following error occurs => SQLite Error 8: 'attempt to write a readonly database'

It seems to me, as if connection to the database was not properly disposed after first run or database that was created during first run, stays cached in memory somehow.

Expected behaviour: Method should create sqlite database file with specified tables, every time it's called, when the conditions above are met.

Used packages: "Microsoft.Data.Sqlite, Version 6.0.2", "PowerShellStandard.Library, Version 5.1.0"

Target framework: .NET 6

Worth to mention: When replacing 'File.Create()' method with 'Mode=ReadWriteCreate' ConnectionString, the file doesn't get created for the 2nd time, after manual deletion. Only during the first method run.

EDIT: Swapped 'Microsoft.Data.Sqlite' for 'System.Data.SQLite.Core' and now the method works as expected every time.

I guess It's either a BUG in 'Microsoft.Data.Sqlite', or I am just missing an important difference between those 2 libraries.

Would be great to have an official answer from MSFT devs as to what I was doing wrong.

CodePudding user response：

there might be a problem on opening and closing the connection 2 times: Could you try it like this:

    using (SQLiteConnection connection = new SQLiteConnection($"Data Source={SqliteFile};Mode=ReadWrite"))
    {
        using (SQLiteCommand command = new SQLiteCommand())
        {
            command.Connection = connection;
            command.CommandText = "DROP TABLE IF EXISTS MyTable; CREATE TABLE MyTable (Primary_Key INTEGER PRIMARY KEY, Text_Entry NVARCHAR(2048) NULL)";
            command.ExecuteNonQuery();
        }
    }

CodePudding user response：

Ah ok, I missed one part. For me it always works like this (in short):

private SQLiteConnection OpenDataBase(string dataBasePath)
{
    SQLiteConnection connection = new SQLiteConnection($@"Data Source = {dataBasePath}; Version = 3; New = True; Compress = True; ");
    connection.Open();
    return connection;
}

public void CreateTable(string dataBasePath)
{
    using (SQLiteConnection? connection = OpenDataBase(dataBasePath))
    {
        string create = "DROP TABLE IF EXISTS MyTable; CREATE TABLE MyTable (Primary_Key INTEGER PRIMARY KEY, Text_Entry NVARCHAR(2048) NULL)"; ;
        using (SQLiteCommand command = new SQLiteCommand(create, connection))
        {
            command.ExecuteNonQuery();
        }
    }
}