Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Channels ▼


Windows PowerShell

Directories in PowerShell

To discuss a little "housekeeping" for PowerShell, it is a good practice is to have two subdirectories: src and lib. The src directory contains the projects created for the managed code; these will be accessed from PowerShell. The lib directory contains the DLLs; pointing the build area to this directory allows access to PowerShell. Also, add the "root" directory-where the scripts are kept-to your path. That way, when you launch PowerShell, you can type in the name of your command without the file extension .ps1 to execute the script.

Diving Deeper in PowerShell

Having reviewed some of the fundamentals of PowerShell, it's time to "dive deeper" into some specific examples. These will cover how PowerShell works seamlessly with .Net components, scripts, files, and other applications.

Example 1: Developing a Simple .Net Class

In this example, we'll create a simple .Net class library called Calculator. It contains a single class (Calc) and only one method (DoAddition). This method returns the sum of two arguments.

namespace Calculator
   public class Calc
      public int DoAddtion(int var1, int var2)
         return var1 + var2;

Once we've created the class, we must compile the code, start PowerShell and change directory to where the DLL is. The DLL needs to be loaded into the current domain using the static method LoadFrom located in the Reflection.Assembly namespace. PowerShell has special syntax for loading the DLL-it requires the namespace and class containing the static method to be in braces, two colons, and the name of the static method. The LoadFrom method also needs the path and name of the DLL. For the path, the $pwd automatic variable is used. The complete syntax is as follows:


If the DLL is found and loads successfully you should see the following:

[Click image to view at full size]

Now an instance of the class needs to be created using the New-Object cmdlet. It must be fully qualified with the namespace. Once successfully created, you can call the DoAddition method.

[Click image to view at full size]

PowerShell's scripting ability is expanded with .Net. Using the range syntax and the ForEach cmdlet, we can use DoAddition to count by twos.

[Click image to view at full size]

Note, as mentioned earlier, the $_ PowerShell variable refers to the current object in the pipeline. However, alternate syntax is available. The following lines of code accomplish the same

Option A:

1..5 | ForEach { $calc.DoAddition($_, $_) }

Option B:

1..5 | ForEach { $calc.DoAddition($i, $i) }

Example 2: Developing Advanced .Net Classes

In this example, we'll create another class library and return a custom type. The class library will be called People and will contain two classes, PeopleHelper and our custom type Person. Person is a simple class; it has three properties: FirstName, LastName and a Guid. All three properties get set in the constructor. The PeopleHelper class has one method (NewPerson) and takes two strings (FirstName and LastName).

using System;
namespace People
   public class PeopleHelper
      public Person NewPerson(string firstName, string lastName)
         return new Person(firstName, lastName);
   public class Person
       private string _firstName;
       private string _lastName;

       private Guid _guid;
       public Guid Guid
       { get { return _guid; } set { _guid = value; } }
       public string LastName
       { get { return _lastName; } set { _lastName = value; } }
       public string FirstName
       { get { return _firstName; } set { _firstName = value; } }
       public Person(string firstName, string lastName)
       _firstName = firstName;
       _lastName = lastName;
       _guid = Guid.NewGuid();

We create this DLL as we would any other .Net component. In PowerShell we load it into the current application domain, instantiate the class, and call the NewPerson method with parameters.

[Click image to view at full size]

To illustrate the functionality, we create an array of first names at the command line. This array is piped in order to create a Person object for each one.

[Click image to view at full size]

If first names are listed in a file (for example, names.txt), we can retrieve the data using the Get-Content cmdlet. This cmdlet will read the file, pipe it, and create a Person object for each name.

[Click image to view at full size]

In both cases -- whether the source data is file-based or an array at the command line -- the results are the same. Our .Net components are used in conjunction with PowerShell in order to create unique identifiers and first-last name combinations.

Example 3: Creating Scripts

The previous example illustrated the seamless interaction between PowerShell and .Net DLL. Now we'll build these up into constructs that are reusable. Both a legacy .Net component and the People.DLL will be wrapped in PowerShell constructs so they can operate seamlessly in the pipeline. This will demonstrate PowerShell's ability to script existing .Net DLLs.

In effect, this example will enable a transformation of data from one form to another. Starting with a simple array of names, we'll transform the array to a "table" of objects. Then, the input will come from an alternative source-a file. Moving along-while using the People.DLL -we'll filter the list of names (similar to a WHERE clause in SQL). The data-transformation will be will be complete as the data is exported to a CSV file.

First, we'll build a script file with the PowerShell file extension: People-Setup.ps1. The contents of this script file will be the individual PowerShell commands. In the script we'll setup two functions, a filter and code to call one of them. The contents of the file are as follows:

1. $scriptDefinition = $MyInvocation.MyCommand.Definition
2. $scriptDir = Split-Path $scriptDefinition
3. $libDir = Join-Path $scriptDir "lib"
4. $dllName = "People.dll"
5. $resolvedDLL = Join-Path $libDir $dllName
6. [Reflection.Assembly]::LoadFrom($resolvedDLL)

In line 1, the $MyInvocation automatic variable is used to determine the source of the script and the location of the DLL lib directory. This path is then split using the Split-Path cmdlet. Then, it is built up with the lib directory and DLL that is needed for the LoadFrom cmdlet in Line 6.

The function Add-Assembly takes two arguments-a script definition ($MyInvocation.MyCommand.Definition) and the DLL name (People.DLL). The $MyInvocation.MyCommand.Definition command-when used in the script-returns the full name of the script location. Using this as a starting point along with two cmdlets (Join-Path and Split- Path) we'll resolve our People DLL location and load the DLL.

As the previous example shows, we can create scripts that work with both .Net and PowerShell components. Reaching back to our previous Calculator class, we can see a simple example.

[reflection.assembly]::loadfrom("$pwd\Calculator.dll") | Out-Null
1..10 | % { $calc.DoAddition($_, $_) }
1..10 | % { $calc.DoAddition($_, $_*2) }

1..10 | % { $calc.DoAddition($_, $_*3) }

This example, presents an interesting way of using .Net DLLs. The feedback loop of code, compile, run, and observe is reduced dramatically. More importantly, existing .Net components can be scripted.

Example 4: Working with Microsoft Excel

With PowerShell, you have the ability to uniquely handle data in Microsoft Excel. Not only can you retrieve data from a worksheet within a workbook, but you can create custom properties for the created objects using PowerShell's wrapper object: PSObject. In this example, we'll connect to an Excel workbook, retrieve the data from a specific worksheet, and then use PSObject within nested loops to add custom properties.

In the following code, observe that in the Get-ConnectionString cmdlet, we build a connection string to connect to an Excel workbook. The specific workbook is passed in as a parameter.

param($workbook, $sheetName)
   function Get-ConnectionString
      param ($workbook)
Source=$workbook;Extended Properties=Excel 8.0;"

In the Get-Data function, we retrieve the data from the specified workbook. Moreover, we will be extracting data from a specific worksheet within the workbook (as defined by the parameter). By using ADO.NET, we consider the sheet like a pseudo-database. As such, we can execute a SQL statement against it and store the output. Note that we suppress the output by piping to the Out-Null cmdlet.

function Get-Data
  param($workbook, $sheetName)
  $workbook = Resolve-Path $workbook
  $cnnString = Get-ConnectionString $workbook
  $cnn = new-object System.Data.OleDb.OleDbConnection($cnnString)
  if(!$sheetName.EndsWith("$")) {$sheetName += "$"}
  $sql = "Select * from [$sheetName]"
  $cmd = new-object System.Data.OleDb.OleDbCommand($sql,$cnn)
  $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)

  $ds = new-object System.Data.dataset("ExcelData")
  $da.fill($ds, "Data") | Out-Null
  $ds ,"data"

The following function is the focus of our operations. Here, we will be converting the dataset object into a custom PSObject. Notice the operations occurring here. First, if the two arguments are not passed in, we will exit the function. Second, we define our column names. Third, we pipe the content of our rows into a foreach loop. In the loop, we create a new PowerShell extended object for each row. Fourth, with each column name, we pipe that into an inner loop where we use the Add-Member cmdlet. This cmdlet will add a custom property to our recently-created object as it passes through the pipeline. The name will be that of the column variable we're currently processing; the value will be the current row's value in that same column. Finally, to close out our loop, we will reset our object variable.

function Convert-DSToObject
  param ($dataSet, $tableName)
  if(!$dataSet) { "need a dataset please"; return }
  if(!$tableName) { "need a tablename please"; return}
  $columnNames = $dataSet.Tables[$tableName].Columns
  $dataSet.Tables[$tableName].Rows |foreach {
    $row = $_
    $o = New-Object PSObject
    $columnNames | foreach {
      $columnName = $_
      $o = $o | Add-Member -PassThru NoteProperty
$columnName $row[$columnName]

In this final portion of code, we call our functions, and develop our custom objects. The Get-Data function requires two parameters-the workbook and worksheet name. The output of that function is then passed into our DSToObject function.

  if (!$workbook) {"need a workbook please"; return}
  if (!$sheetName) {"need a sheet name please"; return}
  $ds, $name = Get-Data $workbook $sheetName
  Convert-DSToObject $ds $name

Clearly, PowerShell affords an efficient method of extending an object by creating a custom object. In this example, we've added members to our object iteratively as we looped through the rows of an Excel worksheet.

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.