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:
Categories: Scripting
PowerShell, SQLite
View Koen Vermoesen's profile
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)
}
Thanks for the feedback.
Using The SQLite.NET Library From Powershell
in http://goo.gl/a3Qx
I think you can see that.
How to ref sqlite3.dll