Leveraging PowerShell's Overlooked -PassThru Paramter

One of my favorite PowerShell CommandLets is Out-GridView.  It provides a nice graphical grid display with filtering and sorting.  But an often overlooked feature of Out-GridView is the ability to pass selected rows through to the pipeline.  This can be put to good use on a number of database and ETL applications.

Let's consider a hypothetical requirement.  Human Resources wants to give some employees an additional 40 hours of vacation time.  Ok, not the most likely requirement but substitute salary adjustment, title changes, etc, and these types of mass changes are needed all the time.  So how our process will work is:

- Display a list of potential employees to get the increase in a grid view.
- The user will select the employees they want the increase to go to and click Ok.
- Another grid view pops up with just the selected rows so the user can confirm the data.
- The selected employees have their vacation hours increased by 40 hours.
- A grid view pops up showing the employees including the updated ones.

To make this easy for you to run this code, I used good old Adventure Works.  Here's the whole script but I'll go over it in detail.

if(-not(Get-Module -name "sqlps")) {
          Import-Module "sqlps"
          }

$qry = "
SELECT e1.[BusinessEntityID] as EmpID
      ,e1.[FirstName] + ' ' + e1.[MiddleName] + ' ' + e1.[LastName] as Name
      ,e1.[JobTitle]
      ,VacationHours
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
  FROM [AdventureWorks].[HumanResources].[vEmployee]            e1
  join [AdventureWorks].[HumanResources].[vEmployeeDepartment]  e2
    on e1.BusinessEntityID = e2.BusinessEntityID
  join [AdventureWorks].[HumanResources].[Employee]  e3
    on e1.BusinessEntityID = e3.BusinessEntityID"

set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\Databases\Adventureworks\Tables
 
$list =  Invoke-Sqlcmd -Query $qry -QueryTimeout 25 | out-gridview -Title "Select records to reset"  -PassThru | Out-GridView -Title "Confirm Selection..." -PassThru

foreach ($item in $list)
{
      $sql = "update [HumanResources].[Employee] set [VacationHours] = [VacationHours] + 40 where BusinessEntityID = " + $item.EmpID + ";"
     
      Invoke-Sqlcmd -Query $sql -QueryTimeout 25
}

Invoke-Sqlcmd -Query $qry -QueryTimeout 25 | out-gridview -Title "Vacation Hours Updated..."


Let's Review What's Happening in the Script...

The first few lines are just checking to see if the SQL Server module "sqlps" is loaded and if not, it will load it.  If you have SQL Server Management Studio, you should have the "sqlps" module.

if(-not(Get-Module -name "sqlps")) {
          Import-Module "sqlps"
          }

Then I just declare a variable $qry to hold the SQL code I want to run.

$qry = "
SELECT e1.[BusinessEntityID] as EmpID
      ,e1.[FirstName] + ' ' + e1.[MiddleName] + ' ' + e1.[LastName] as Name
      ,e1.[JobTitle]
      ,VacationHours
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
  FROM [AdventureWorks].[HumanResources].[vEmployee]            e1
  join [AdventureWorks].[HumanResources].[vEmployeeDepartment]  e2
    on e1.BusinessEntityID = e2.BusinessEntityID
  join [AdventureWorks].[HumanResources].[Employee]  e3
    on e1.BusinessEntityID = e3.BusinessEntityID"

To connect to the database and set the right location, I use this line...

set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\Databases\Adventureworks\Tables



This abstraction of the SQLPS module lets us treat SQL Server like a file system which makes it easy to connect to the server and database I need.

The next line packs a lot of punch.  It is piping the results of one command to the next.
 
$list =  Invoke-Sqlcmd -Query $qry -QueryTimeout 25 | out-gridview -Title "Select records to reset"  -PassThru | Out-GridView -Title "Confirm Selection..." -PassThru

The cool part is how it does all the work on the right side of the equal sign and then assigns the final result to out variable $list.  So first the select query is invoked and passes the result to Out-GridView so we see something like the screen below.

The user can then select the employees they want updated by clicking on them.  In my testing, I selected the first four so I could verify the update occurred. After selecting the rows desired, the user clicks Ok to continue.  The user will then see another grid like the one below.

You should see just the rows you selected.  The -PassThru option on the first Out-GridView caused  ONLY the selected rows to be passed down the pipe.  From here, you need to select the rows again so they will get passed along.  Note: Control + A will select them all.  Click Ok when you are ready to continue.

Now the update code will loop through the selected rows and update the vacation hours.

foreach ($item in $list)
{
      $sql = "update [HumanResources].[Employee] set [VacationHours] = [VacationHours] + 40 where BusinessEntityID = " + $item.EmpID + ";"
     
      Invoke-Sqlcmd -Query $sql -QueryTimeout 25
}

Finally, the original query is executed again and piped to Out-GridView so we can inspect the updates. 

Invoke-Sqlcmd -Query $qry -QueryTimeout 25 | out-gridview -Title "Vacation Hours Updated..."



And that's all there is to it!  I encourage you to use your imagination on how you could do similar things.  For example, you could select files from a folder to be processed.  You might use it to allow users to approve or reject data.  By combining the power of the "sqlps" module with the Out-GridView -PassThru paramter, you can do a lot of work with very little code.

Thanks,

Bryan Cafferky
Business Intelligence Consultant
BPC Global Solutions LLC