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...

How to query a SQL database with Powershell

You can use PowerShell to read or execute query to various database like SQL, MySQL, or SQLite.

The following example shows you one of the possibilities of retrieving data from a SQL database:


[string]$db_server = "My_SQL_Server"
[string]$db_name = "My_Database_Name"
[string]$db_query = "My SQL Query"
[string]$db_connectionstring = "Data Source=$db_server;Database=$db_name;Integrated Security=true;"
$db_data_adapter = New-Object System.Data.SqlClient.SqlDataAdapter($db_query,$ db_connectionstring )
$db_dataset = New-Object System.Data.DataSet
[void]$db_data_adapter.Fill($db_dataset)
$db_dataset.Tables[0]

Let's have a look at the different steps involved in this script.

First of all, you need to define your 3 parameters: location of the database, database name and your SQL query.

[string]$db_server = "My_SQL_Server"
[string]$db_name = "My_Database_Name"
[string]$db_query = "My SQL Query"

Using the first two parameters, you'll be able to create your connection string:
[string]$db_connectionstring = "Data Source=$db_server;Database=$db_name;Integrated Security=true;"


Integrated Security = true means you'll use the Windows account running the script to authenticate on the database; if another type of authentication is required, look at System.Data.SqlClient.SqlCredential.

You create a Data Adapter object to retrieve the data from the database mentioned in the connection string using the query (3rd parameter):
$db_data_adapter = New-Object System.Data.SqlClient.SqlDataAdapter($db_query,$ db_connectionstring )

Then, you initiate a DataSet object in order to receive the data from the Data Adapter
$db_dataset = New-Object System.Data.DataSet

You store the data from the Data Adapter to the DataSet object using the Fill method:
[void]$db_data_adapter.Fill($db_dataset)

And finally, you display the result:
$db_dataset.Tables[0]

Another option should be to use the SqlDataReader but it seems to be less efficient and also slower; it keeps the database connection opened longer than the SqlDataAdapter.

Once your script gets more complicated, you might need to use the SqlConnection and SqlCommand objects to gain more control about within your script and connection, but for a simple query, this solution will do it.

Hope this was helpful.

How to create a HTA like in Powershell

HTML Applications (HTA) are very useful to provide a Graphical User Interface (GUI) to your VBscript, to ease its use, avoid typing mistakes and provide better output.

Would it be possible to do similar thing in PowerShell?

Unfortunately, Powershell does not allow you to use HTA as you can do to provide a GUI for your VBscripts.

However, Powershell gives you access to .Net libraries which means that you can create a GUI using Windows Forms. Here is a example of what it looks like:

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

$objForm = New-Object System.Windows.Forms.Form
$objForm.Text = "My PowerShell Form"

$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(20,20)
$objLabel.Text = "Hello World!"
$objForm.Controls.Add($objLabel)

$ExitButton = New-Object System.Windows.Forms.Button
$ExitButton.Location = New-Object System.Drawing.Size(80,80)
$ExitButton.Text = "Exit"
$ExitButton.Add_Click({$objForm.Close()})
$objForm.Controls.Add($ExitButton)

$objForm.ShowDialog()


You can copy the code in file, save it as test.ps1 and execute the script with PowerShell; you'll see a GUI like this:

This is not an easy task as Windows Forms will require a lot more coding than HTA, but you can design really nice interfaces for your scripts.

In order to ease the delivery of forms, without having to type hundreds of lines of code, you can use a free tool from Sapien to create the code behind the GUI called PrimalForms. You'll be able to design the GUI and it will generate the code for you. Then, all you have to do is to merge the "GUI code" into your script and link the events.

To find PrimalForms Community Edition, you'll have to access the download section on Sapien website and create an account. I'm telling you: it's worth doing it, and it's free.

References:
System.Windows.Forms Namespace
Sapien - PrimalForms Community Edition