Hooking up SQLite and PowerShell

I’ve been playing around with SQLite on the command line (cmd that is) a bit. I immediately started wondering if it would be possible to combine this portable SQL engine with PowerShell, but I couldn’t find any working samples on the internet.

After some more research I discovered the SQLite ADO.net 2.0 Provider but I couldn’t get it to work immediately.

It took me a couple of days to find the blog post Database Queries with Windows Powershell. (This blog seems to have moved recently as most of the hyperlinks/search results are still referring to Typepad). The post is nicely commented, so I could rather easily translate the MySQL to SQLite statements with some assistance from the included help file.

[void][System.Reflection.Assembly]::LoadFrom("D:\DATA\Tools\System.Data.SQLite.dll")

$cn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$cn.ConnectionString = "Data Source=D:\DATA\Tools\koen.sqlite"
$cn.Open()

$cm = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$sql = "SELECT FirstName, LastName FROM tblKoen"
$cm.Connection = $cn
$cm.CommandText = $sql
$dr = $cm.ExecuteReader()

while ($dr.Read())
{
    write-host $dr.GetString(0) " " $dr.GetString(1)
}

$cn.Close()

Some more interesting links I came across:

http://blog.koenvermoesen.be/about-koen/

Tagged with: ,
Posted in Scripting
3 comments on “Hooking up SQLite and PowerShell
  1. This script doesn’t work, probably because of some upgrades. Do get it working you have to replace the folowing 2 lines:
    $cm.Connection = $cn
    $cm.CommandText = $sql
    with:
    $cm = New-Object -TypeName System.Data.SQLite.SQLiteCommand($sql,$cn)

    If you want the structure of your tables, use the folwing code after the line
    $dr = $cm.ExecuteReader()

    # Write out the result set structure
    for ($i=0;$i -lt $dr.FieldCount;$i++) {
    Write-Host $dr.GetName($i) $dr.GetDataTypeName($i)
    }

  2. image72 says:

    Using The SQLite.NET Library From Powershell
    in http://goo.gl/a3Qx
    I think you can see that.
    How to ref sqlite3.dll

Comments are closed.

IT Pro Summer GP
IT Pro Summer Grand Prix
MVA
Microsoft Virtual Academy
Pro-Exchange
Pro-Exchange / Pro-Lync / Pro-Office365
MEET

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 158 other followers

%d bloggers like this: