CODEWITHSUNDEEP
c#sqlite
Observer
Observer

Reputation: 19

'Microsoft.Data.Sqlite' unexpected behaviour

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.

Upvotes: 0

Views: 1837

Answers (3)

Observer
Observer

Reputation: 19

After some deep digging in github, I have found an actual answer to my original question. There's a feature called 'connection pooling', which caused this 'Unexpected behaviour' that I have described in my question.

To DISABLE this feature, either add 'Pooling=False' to ConnectionString, or call 'SqliteConnection.ClearAllPools()' at the point where you want the connection to be disposed (using statement is not sufficient).

Upvotes: 2

Piepe
Piepe

Reputation: 108

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();
        }
    }
}

Upvotes: 0

Piepe
Piepe

Reputation: 108

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();
        }
    }

Upvotes: 0

Related Questions