How to query a SQLite database with PowerShell

Relational databases are really useful to store data and can achieve far more than flat files, so you might be interested in accessing databases with your PowerShell scripts.
However, most of the relational databases applications require proper installation and quite a lot of resource.
For SQLite, this is not the case; this is a "self-contained, serverless, zero-configuration, transactional SQL database engine"; it combines the best of both flat files and relational databases. For more information, please visit the website http://www.sqlite.org/

In order for PowerShell to be able to communicate with SQLite, you need to download and install the ADO.NET adapter for SQLite: http://system.data.sqlite.org, which includes the engine.

Once installed, the following script will show you how to query a SQLite database:


[string]$sqlite_library_path = "C:\Program Files\System.Data.SQLite\2010\bin\System.Data.SQLite.dll"
[string]$db_data_source = "C:\Temp\SQLite\MyDatabase.db"
[string]$db_query = "SELECT * FROM MyTable"

[void][System.Reflection.Assembly]::LoadFrom($sqlite_library_path)

$db_dataset = New-Object System.Data.DataSet

$db_data_adapter = New-Object System.Data.SQLite.SQLiteDataAdapter($db_query,"Data Source=$db_data_source")
[void]$db_data_adapter.Fill($db_dataset)
$db_dataset.Tables[0]




Let's go into details...

Let's talk about the variables:
First, you have to provide the location of the binary to use:
[string]$sqlite_library_path = "C:\Program Files\System.Data.SQLite\2010\bin\System.Data.SQLite.dll"

Then, you need to provide the location of your database:
[string]$db_data_source = "C:\Temp\SQLite\MyDatabase.db"

And finally, provide the SQL query you want to execute:
[string]$db_query = "SELECT * FROM MyTable"

Now, we load the SQLite library:
[void][System.Reflection.Assembly]::LoadFrom($sqlite_library_path)

You need to use a System.Data.DataSet object to store the data retrieved from the database:
$db_dataset = New-Object System.Data.DataSet

The  SQLiteDataAdapter will run the query in the $db_query variable against the database described in the "Data Source=$db_data_source" connection string.
$db_data_adapter = New-Object System.Data.SQLite.SQLiteDataAdapter($db_query,"Data Source=$db_data_source")

Once done, we pass the result to the DataSet using the Fill() method:
[void]$db_data_adapter.Fill($db_dataset)
([void] here is used to ignore the value the Fill() method will return)

Finally, the result is displayed when reading the table from the DataSet:
$db_dataset.Tables[0]

Hope this helped...