access sqlite via ado.net driver in powershell

read_all_in_once.ps1

# Inspiration: https://www.pipperr.de/dokuwiki/doku.php?id=windows:powershell_oracle_db_abfragen 
# https://docs.microsoft.com/de-de/dotnet/framework/data/adonet/retrieving-and-modifying-data

$assemblyFile = "C:\PathToDLL\System.Data.SQLite.dll" # download precompiled binaries for .net or "System.Data.SQLite"
$connString = 'Data Source="file.sqlite";Version=3;'
$sqlCommand = "Select * from households limit 100"
[Reflection.Assembly]::LoadFile($assemblyFile) 





$conn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$conn.ConnectionString = $connString
$conn.Open()

$cmd = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$cmd.CommandText = $sqlCommand
$cmd.Connection = $conn

$dataAdapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter
$dataAdapter.SelectCommand = $cmd





$data = New-Object -TypeName System.Data.DataSet
$dataAdapter.fill($data)
$data.tables.rows | Out-GridView






$dataAdapter.Dispose()
$cmd.Dispose()
$conn.Dispose()

read_record_by_record.ps1

# Inspiration: https://www.pipperr.de/dokuwiki/doku.php?id=windows:powershell_oracle_db_abfragen 
# https://docs.microsoft.com/de-de/dotnet/framework/data/adonet/retrieving-and-modifying-data

$assemblyFile = "C:\Program Files\Apteco\FastStats Designer\System.Data.SQLite.dll"
$connString = 'Data Source="C:\Users\Florian\Desktop\20180323\201803 NetAachen Map\dslac.db";Version=3;'
$sqlCommand = "Select * from households limit 2"
[Reflection.Assembly]::LoadFile($assemblyFile) 





$conn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$conn.ConnectionString = $connString
$conn.Open()

$cmd = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$cmd.CommandText = $sqlCommand
$cmd.Connection = $conn







$reader=$cmd.ExecuteReader() # or ExecutePageReader() for large datasets https://www.devart.com/dotconnect/mysql/docs/Devart.Data.MySql~Devart.Data.MySql.MySqlCommand~ExecutePageReader.html
 
# Struktur des Ergebniss Records  anzeigen
for ($i=0;$i -lt $reader.FieldCount;$i++) {
    Write-Host  "Position ::" $i "::" $reader.GetName($i)"::" $reader.GetDataTypeName($i)
}
 
# Ausgeben der Ergebnisse
# auf die richtige Nummerierung achten 
$rows = @()
while ($reader.read()) {
    

    $columns = New-Object psobject
    for($i=0;$i -lt $reader.FieldCount;$i++)
    {
       $columns | Add-Member -type NoteProperty -name $reader.GetName($i) -value $reader[$i]
    }

   $rows +=$columns

}


$rows.SyncRoot | Out-GridView


$cmd.Dispose()
$conn.Dispose()