PowerShell Basics 103 – SharePoint – SQL – Nintex Workflow

Start of the Story

Querying a database is something I do quite regularly.  Whether it’s for work at Nintex or other projects. Sometimes this is just from using SQL Management Studio, othertimes, it is for something more complex and requires some code to be written that makes SQL queries and does something on the data.

So today, I decided to link my interest in learning PowerShell, my experience with SQL and my work at Nintex.  I decided to write some PowerShell that will query the Nintex Workflow database.

Specifically, I am interesting in seeing all the Nintex Workflow actions I have available in my environments, excluding “internal” actions.

The PowerShell Script

$ServerInstance = “localhost”
$Database = “NW2010DB”
$ConnectionTimeout = 30
$Query = “SELECT ActivityName FROM [Activities] WHERE [Category] <> ‘Internal'”
$QueryTimeout = 120

$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = “Server={0};Database={1};Integrated Security=True;Connect Timeout={2}” -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
$da.fill($ds)
$conn.Close()
foreach($items in $ds.Tables[0].Rows)
{
  $items[0]

}

What the Script Does

This script is fairly straight forward.  It initializes some variables for which server the database is on, the name of the database, timeouts etc.

It then builds a connection string, runs the query and uses a ForEach to iterate through each result and display it.

I don’t think each line require explanation, as it’s fairly straightforward.

The good thing is, we are using the information we learnt in the previous 2 posts, and starting to utilize them to build solutions for real world scenarios.  Sure, you may not want to know what actions you currently have, but this atleast gives you the idea of how to query a DB from PowerShell.