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:

About these ads

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 157 other followers

Follow

Get every new post delivered to your Inbox.

Join 157 other followers

%d bloggers like this: