TheGeekery

The Usual Tech Ramblings

PowerShell and SQLite

A while back I mentioned I was using SQLite with PowerShell. I was doing this because I had to access the database for gPodder to tweak some of the subscriptions. A need came up again today after upgrading gPodder to the latest release, and having issues with it.

The first task is to get the ADO.Net provider, which I found over here. The provider hasn’t been updated in a while, but still seems to work just fine. Next is loading the libraries, this is a case of calling the Assembly loader, so the code looks like this:

1
[void][System.Reflection.Assembly]::LoadFrom("C:\Temp\System.Data.SQLite.DLL")

Remember to update the path to reflect the actual location. I have [void] at the beginning to hide the output of the library being loaded. If you are having issues, try removing it to see any errors.

Next is just like using any data object.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Add-Type @'
public class Subscription
{
 public string url;
 public string title;
}
'@

[Void][System.Reflection.Assembly]::LoadFrom("C:\temp\System.Data.SQLite.DLL")
$cn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$cn.ConnectionString = "Data Source=C:\temp\config\database.sqlite"
$cn.Open()

$cm = New-Object -TypeName System.Data.Sqlite.SQLiteCommand
$sql = "select title,url from channels"
$cm.Connection = $cn
$cm.CommandText = $sql

$dr = $cm.ExecuteReader()

$subs = @()

while ($dr.Read()) {
    $sub = New-Object Subscription
  $sub.url = $dr.GetString(1)
  $sub.title = $dr.GetString(0)
  
  $subs + $sub
}

$subs

In this code, I’m creating a custom object to hold the subscription data in it, loading the libraries, and querying the “channels” table from the file c:\temp\config\database.sqlite, and then outputting the Title, and URL for the feed.

The great thing about PowerShell is it’s ability to pull in, and use any of the .NET libraries, making it incredibly flexible for quick scripts, as well as more in-depth work. The above script took all of about 5 minutes, and got my podcast list back.

Comments