2

Aware of System.Data.SQLite Close() not releasing database file and SQLite keeps the database locked even after the connection is closed

I originally tried Close, but added Dispose and the GC functions. No change. File indefinitely held open by PowerShell. Using http://system.data.sqlite.org/downloads/1.0.115.5/sqlite-netFx40-binary-x64-2010-1.0.115.5.zip.

function Connect-SQLiteDatabase([ref][Object]$conn){
    try{
        $conn.Value.Open()
    }catch{
        Write-Warning -Message ("Failed to connect: " + $_.Exception.Message)
    }
}

function Disconnect-SQLiteDatabase([ref][Object]$conn, [Switch]$Dispose){
    try{
        $conn.Value.Close()
        if($Dispose){
            $conn.Value.Dispose()
        }

        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
    }catch{
        Write-Warning -Message ("Failed to disconnect: " + $_.Exception.Message)
    }
}

function Initialize-SQLite(){
    try{
        Add-Type -Path "$PSScriptRoot\lib\SQLite\System.Data.SQLite.dll" -ErrorAction Stop
        [void][System.Data.SQLite.SQLiteConnection]
    }catch{
        Write-Error -Message "Could not import SQLite. Ensure the dll is present in .\lib\SQLite\"
    }
}
Tyler
  • 31
  • 2
  • 1
    Hi, calling close / dispose is what you are supposed to do... While you show your 3 function, you do not give us a reproducible example to see the problem. The issue probably lies in the way you call these 3 functions. See [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) – Sage Pourpre Dec 08 '21 at 23:37

1 Answers1

1

It seems SQLiteCommand was the problem, ExecuteReader and ExecuteNonQuery. Disposing the object solved it.

function Invoke-SQLiteNonQuery([System.Data.SQLite.SQLiteCommand]$sqlCommand, [Switch]$NoDispose){
    $data = $sqlCommand.ExecuteNonQuery()
    if(-not $NoDispose){
        $sqlCommand.Dispose()
    }

    return $data
}
Tyler
  • 31
  • 2