Pipeworks | Simplified SQL

Simplified SQL

1 of 9

PowerShell Pipeworks contains several functions to help you simplify interaction with SQL.

The core functions are:

Add-SQLTable
Get-SQLTable
New-SQLDatabase
Remove-SQL
Select-SQL
Update-SQL

The two important ones for you to remember are:

Select-SQL, which runs SQL and unrolls each result
Update-SQL, which takes a pipeline of objects and updates a SQL database

To show you how these work, let's create and update a simple table.

First, we need to create a new database. This example requires that you run it on a computer with SQL server

# Create a random DB name            
$dbName = "Db" + (Get-Random)            
 # Create the database on the local computer            
New-SQLDatabase -DatabaseName $dbName            
 # Craft a connection string to the database            
$connectionString = "Data Source=$env:ComputerName;Initial Catalog=$dbName;Integrated Security=SSPI;"

2 of 9

Now we need to create some input for our table. We'll do this by creating a pair of random input objects, and then piping them into Update-SQL. If you provide -Force to Update-SQL, it will create the table and modify it to fit your input object.

# Create the list of input objects            
$inputObjs = @()            
 # Add a couple of items to the list            
$inputObjs += New-Object PSObject -Property @{            
    "a" = Get-Random            
    "B" = Get-Random            
}             
$inputObjs += New-Object PSObject -Property @{            
    "a" = Get-Random            
    "B" = Get-Random            
}            
            
$inputObjs |            
    Update-Sql -TableName "TestTable" -Force -connectionStringOrSetting $connectionString

3 of 9

Now let's query the table to check that the items were inserted. Since we've already supplied a connection string, we don't need to do it again

Select-SQL -FromTable TestTable

4 of 9

Great! Now let's query again, and this time let's modify the values for B

Select-SQL -FromTable TestTable |            
    Add-Member NoteProperty B (Get-Random) -Force -PassThru |            
    Update-Sql -TableName "TestTable"

5 of 9

And let's check our table:

Select-SQL -FromTable TestTable

6 of 9

Now this time, let's modify only the first item's value for B

Select-SQL -FromTable TestTable -Top 1  |            
    Add-Member NoteProperty B (Get-Random) -Force -PassThru |            
    Update-Sql -TableName "TestTable"

7 of 9

Let's take one last look:

Select-SQL -FromTable TestTable

8 of 9

It's great that Pipeworks simplifies SQL, but it also gives you easy access to full SQL. Select-SQL has an alias, "sql", and it's first positional parameter is a raw SQL statement. This means you can write as complicated SQL as you'd like in PowerShell with very simple syntax like this:

SQL "SELECT COUNT(*) as Count FROM TestTable"

9 of 9

Let's remove our table to clean up:

Remove-SQL -TableName TestTable -ConnectionStringOrSetting $connectionString -Confirm:$false