Event-Horizon

11/05/2011

SQLite gebruiken in Powershell

Filed under: Powershell — Michel @ 18:30
Tags: ,

SQLite is a lightweight database, that saves all data in one file. The ADO.Net version is easy to use from Powershell.

Here is a small script that create a new database:

Add-Type -Path “C:\Program Files\System.Data.SQLite\bin\System.Data.SQLite.dll”

$cn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$cn.ConnectionString = “Data Source=C:\Event-horizon\Temp\test.db;New=True;”
$cn.Open()

$cm = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$sql = “create table ConnectNet(idKey INTEGER PRIMARY KEY, Sender varchar(50), Recipient varchar(50));”
$cm.Connection = $cn
$cm.CommandText = $sql
$cm.ExecuteNonQuery()
$cn.Close()

Insert some information in the database:

Add-Type -Path “C:\Program Files\System.Data.SQLite\bin\System.Data.SQLite.dll”

$cn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$cn.ConnectionString = “Data Source=C:\Event-horizon\Temp\test.db”
$cn.Open()

$cm = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$sql = “insert into ConnectNet values(1,’aa1@aaa.net’,’b1bbb@bbb.nl’);”

$cm.Connection = $cn
$cm.CommandText = $sql
$cm.ExecuteNonQuery()
$cn.Close()

And a select operation on the database:

Add-Type -Path “C:\Program Files\System.Data.SQLite\bin\System.Data.SQLite.dll”

$cn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$cn.ConnectionString = “Data Source=C:\Event-horizon\Temp\test.db”
$cn.Open()

$cm = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$dt = new-object “System.Data.DataTable”
$sql = “select * from ConnectNet;”
$cm.Connection = $cn
$cm.CommandText = $sql
$dr = $cm.ExecuteReader()
$dt.Load($dr)

$cn.Close()
$dt | Format-Table

Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.