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

How to Add an Exit Function to a HTA

Following my previous post about the WScript.sleep function, here is an function to add to an exit function to a HTA.


Again, using HTA is really useful to bring life to a VBScript by adding an interface. So, converting your VBScript files to a HTA is not really complicated as VBScript is native, however some functions like the WScript.Quit are not available.


In a HTA, an easy way of exiting is to call the close method of the current Window object (self). Then, you have just to call the sbExitHTA sub to close the Window.


Also, if you want to get rid of the close button from the Windows title bar, add SysMenu="no" in the HTA:APPLICATION section.


<html>
<head>
<title>Simple HTA</title>
<HTA:APPLICATION
     ID="oSimpleHTA"
     APPLICATIONNAME="SimpleHTA"
>
</head>
<script Language="VBScript">
Sub Window_Onload
      dataarea.innerhtml = "<p>This is a simple HTA</p>" & _
      "<p>Exit button: </p>" & _
      "<input type=""button"" value=""Exit"" onClick=""sbExitHTA"" />"
End Sub
Sub sbExitHTA
      Self.Close()
End Sub
</script>
<body>
    <h1>Simple HTA</h1>
    <span id="dataarea"></span>
</body>
</html>

How to add a sleep function to an HTA

The WScript.Sleep function in VBScript is useful. However, when you decide to use HTA to provide an interface to your script, the sleep function become a problem as you can't use WScript functions in HTA.


For the WScript.Echo, you can use MsgBox, however, there is no equivalent for the WScript.Sleep.


You could do it using a loop, that's working but it's resource intensive. You could also create a VBScript file on the fly where you include the WScript.Sleep command and run it via the HTA but there is an easier, faster and quite efficient solution. 
So, here is an option that I used several times which did the trick for me.

In the following example, the sbWait sub is run a command line to do a ping to the localhost IP address for the number of times of your choice. Because each ping request is done every second, you just have to mention using the iSeconds variable the number of time you want to do it.

Enjoy.

<html>
<head>
<title>Simple HTA</title>
<HTA:APPLICATION
     ID="oSimpleHTA"
     APPLICATIONNAME="SimpleHTA"
>
</head>
<script Language="VBScript">
Sub Window_Onload
    dataarea.innerhtml = "<p>This is a simple HTA</p>"
    dataarea.innerhtml = "<p>Let's wait for 5 seconds</p>"
    sbWait(5)
    dataarea.innerhtml = "<p>Done.</p>"
End Sub
Sub sbWait(iSeconds)
    Dim oShell  : Set oShell = CreateObject("WScript.Shell")
    oShell.run "cmd /c ping localhost -n " & iSeconds,0,True
End Sub
</script>
<body>
    <h1>Simple HTA</h1>
    <span id="dataarea"></span>
</body>
</html>