Moving from Command Shell scripting to PowerShell

A recent project that I was working on was originally written using Windows Command Shell scripts. The project essentially revolved around two levels of script files. At the top level was driver script which kicked off a number of second level scripts.  The project worked but occasionally there were problems with the Command Shell scripts not calculating certain variables like time intervals correctly. Mathematic operations were never really a natural part of the Command Shell scripts and the code that performed the calculations was more than a bit esoteric. In addition, I wanted the scripts to be able to use some ADO.NET objects for database access and there was no way to do that directly in Command Shell scripts.
Initially, I must admit that I was a bit worried about the learning curve. I had done a number of smaller projects with PowerShell but I was definitely not as familiar with it I was Command Shell scripting. At first editing and attempting to debug the scripts was a major hurdle. However, Quest’s PowerGUI (www.powergui.org) provided a productive and free development environment that helped get the project off the ground.
The power of PowerShell really began to shine as I moved into the portions of the project where I wanted to use ADO.NET code. PowerShell was easily able to instantiate the ADO.NET SQLClient objects. You can see and example of using PowerShell with the ADO.NET SqlConnection, SqlCommand and SQLDataReader objects in the following code listing:

 $con = New-Object System.Data.SqlClient.SqlConnection
 # Create a SqlCommand object, define command text, and set the 
 #  connection string
 $cmd = New-Object System.Data.SqlClient.SqlCommand
  
$con.ConnectionString = "Server=" + $vm + ";Database=AdventureWorks;integrated security=true"
 $con.Open()
 Write-Host "Query 1 "
 $cmd.CommandText = "SELECT * FROM HumanResources.Department"
 $cmd.Connection = $con
 $rdr = $cmd.ExecuteReader()
 # Write out the results
 $row = ""
 while ($rdr.read())
 {
   for ($i=0; $i -lt $rdr.FieldCount; $i++ )
   {
   $row = $row + $rdr.GetValue($i)
   }
   Write-Host "Query 1 " $row
   $row = ""
 }
 $rdr.close()

If you have experience in Visual Studio this style of coding is very natural and easy – and not too unlike VB or C#. You use the New-Object cmdlet to instantiate objects. Then you use those object’s same methods and properties that you would in VB or C#. This example creates a SqlCommand object and then a SqlCommand object. Next, it sets the SqlConnection object’s ConnectionString property with the required connection string. Then it uses the SqlConnection object’s Open method to open a connection to the target database. Next, the SqlCommand object’s CommandText property is assigned a SQL SELECT statement. Then a while loop is used to read the result set produced by the SQL query.

Some of the best points about PowerShell for this project were:
• Productive development debugging environment
• Support for robust control flow
• Support for a complete set of math operations
• Support for elapsed time
• Support for sleep without external programs
• Support for .NET objects

The PowerShell conversion wasn’t all peaches and cream. There was one sticky issue:
• No equivalent to the Command Shell Start command – In this project the driver script launched a number of other scripts without waiting for the earlier scripts to complete. I saw no way to do that in PowerShell 1.0. I wound up using a Command Shell script as the driver program.

For this project the move to PowerShell wasn’t nearly as tough as I thought it would be. Getting the right tools was half the battle. After that learning which cmdlets to use was a matter of experience combined with a dash of Google.

End of Article