Category: Powershell

Build a C# REST API and consume it with Powershell (and write Pester tests!) (part 2)

This is the sequel to part 1.
Now we are going to query the AdventureWorks Database.

Install the AdventureWorks database

The script below downloads the AdventureWorks database for SQL Server 2014, extracts it in c:\temp and restores it. As you can see I had to change the data and the log locations because the original AdventureWorksDb is created in another version of SQL Server.

With the SQL Powershell commandlets you have to create 2 'Microsoft.SqlServer.Management.Smo.RelocateFile' objects to do so. Now, if you have both SQL Server 2014 Express and Visual Studio 2015 Community Edition installed, the SQL Server Management dlls get messed up, because both version 12 and 13 are loaded in the app domain. You can check that with

[appdomain]::CurrentDomain.GetAssemblies() | ? { $_.Location -like "*smo*" } 

This knowledge results in this script. See also this question from StackOverflow.

New-Item 'c:\temp' -ItemType Directory -Force 
Set-Location C:\temp
choco install 7zip.commandline --yes --force
wget -OutFile
7z e .\

Import-Module SQLPS

$RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "AdventureWorks2014_Data", "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.mdf"

$RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "AdventureWorks2014_Log", "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log\AdventureWorks2014.ldf"

#Invoke-Sqlcmd -ServerInstance '.\sqlexpress' -Query "DROP DATABASE AdventureWorks2014"
Restore-SqlDatabase -ServerInstance localhost\sqlexpress -BackupFile C:\temp\AdventureWorks2014.bak -Database AdventureWorks2014 -RelocateFile @($RelocateData,$RelocateLog)

Add Linq to SQL classes

Now it's time to add an Object Relation Mapper. If the app is using Microsoft SQL and is relatively easy (only a few tables, not too much relationships) then why not go ahead and use good old Linq2SQL. It's incredibly easy.

So type CTRL+Shift+A
Add Linq to SQL Classes and call it AdventureWorks.dbml.


Next, open Server Explorer and drag the Person table to the canvas like this:


Now build the Project. And we are done. This simple drag and drop action created a Person class and added the ConnectionString to the Web.Config file.

Create a Person Controller

In the Controllers folder, add a new Class named PersonController.

Change the first Get method as follows:

// GET: api/Person
public IQueryable<Person> Get()
       var db = new AdventureWorksDataContext();
       var people = db.Persons.Take(10);
       return people;

So here we changed the return type in 'Queryable', because that is the return type of a AdventureWorksDataContext collection. You see that we only pick 10 records because the Person table has almost 20.000 records and it takes a long time to load them all.

Now, hit build and start debugging. Fire up Powershell and type:

curl http://localhost:49491/api/person

You may need to change the url to match yours. The result should be:


Consume the api with Powershell and test with Pester

A tip: read because it explains in a very concise way how to use Pester. It's great to be able to write unit tests for Powershell. If you are not sure why you should be writing tests, read this.

Shouldn't I test the api as well? Yes, I definitely should and I will, but it's not in scope of this article. So let's get started with Powershell.

First, add a new Test:

New-Fixture -Name Get-AWPeople -Path .\AWcmdlets -Verbose

This creates 2 files in the AWcmdlets folder:


This is the contents of the Get-AWPeople.Tests.ps1 file:

So let's invoke a test:


Of course, it fails. Now, let's write code in the Get-AWPeople.ps1 file:

function Get-AWPeople 
    param (
       $data = ((Invoke-WebRequest $Uri).content) | ` 
       Write-Output $data.count


We would expect the output to be 10 of course, because our api returns only 10 entries.

Next, write the test:

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path).Replace(".Tests.", ".")
. "$here\$sut"

Describe "Get-AWPeople" {
    It "returns 10 entries from the Person table" {
        $result = Get-AWPeople -Uri "http://localhost:49491/api/person"
        $result | Should Be 0

Let's run the test again:


Now change the 0 in the file to 10 and run Invoke-Pester:


And we have a success. Now on to write some more functionality!


Query a database through a C# REST API with Powershell (part 1)

It is probably known that you can query an SQL database in Powershell relatively easy, but wouldn't it be great to quickly write a REST API in front of the database? So that you can add business logic if you wish? And use Powershell as a REST client? And then be able to code a decent frontend for the API for whatever device?

Let's get started!
In this series I will first create a WebApi from scratch. Of course, you can also use the templates in Visual Studio, but I prefer to have a bit of knowledge of the code that's in my project. It's not that hard and you will end up with a clean code base.

Step 1. Get your dev environment ready

You can use a Vagrant box. If you use this Vagrantfile a install.ps1 script will be copied to your desktop. Run it, grab a coffee or go shopping because we are on Windows and Windows apps can be huge.

Step 2. Getting the VS Project in place

Start Visual Studio
Create a new empty solution:


I named the empty solution BusinessApp (I'm lacking inspiration for a better name).

Then right click the newly made solution in the Solution Explorer (the pane on the right) and click Add and the New Project:







I named the new Project BusinessApp.Api. If you set your solution up like this you can add more projects as you continue extending the app, for example for an Angular (or whatever framework) frontend, or if you want to separate your datalayer. You can also put your Powershell client modules in a separate project if you wish.

Then open up the Nuget Package Manager Console and install the WebApi dll's:

Install-Package Microsoft.AspNet.WebApi

Make sure to choose the correct Package source (Microsoft and .NET).

Step 3. Add routing

Add a new folder and name it App_Start.
Create a new class in the folder and name it WebApiConfig.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;

namespace BusinessApp.Api
    public static class WebApiConfig
        public static void Register(HttpConfiguration config)
            GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;

                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }

In this class we configure that we want our api to return and consume json. Also, we configure our routes to match the controller name, followed by id, wich is optional. E.g would match a controllername Employees, and it would return employee with id 1.

Step 4. Enable CORS

We need to enable CORS else we won't be able to consume the api from from another domain outside the domain from which the resource originated. In a production web environment you should configure this very carefully. I will CORS very permissive because I want my code to work.

Install CORS with in Nuget console:

Install-Package Microsoft.AspNet.WebApi.Cors

Then modify the WebApiConfig.cs class as follows:

using System.Web;
using System.Web.Http;
using System.Web.Http.Cors;

namespace BusinessApp.Api
    public static class WebApiConfig
        public static void Register(HttpConfiguration config)
            var cors = new EnableCorsAttribute("*", "*", "*");
            GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;

Step 5. Add a Controller

  • Create a folder named 'Controllers'
  • Right click the Controllers folder and click Add and then Controller
  • Click Web API 2 Controller with read/write actions.


I named the Controller Test Controller.

Step 5. Add a Global.asax file

We need to add a Global.asax file to call the WebApiConfig.cs methods at startup.

Right click the solution, click Add, click New Item and search for Global.asax, then Add it.


Modify Global.asax (see the highlighted lines):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Security;
using System.Web.SessionState;

namespace BusinessApp.Api
    public class Global : System.Web.HttpApplication

        protected void Application_Start(object sender, EventArgs e)


Step 6. Test the API

Hit F5 and browse to http://localhost:/api/test


And it works. You can also consume the API with Powershell at this point:

((Invoke-WebRequest http://localhost:53601/api/test).content) | ConvertFrom-Json

It should return value1 and value2.

Done! Now let's query a database. This will be explained in Part 2.

Building a development environment with Vagrant, Packer, Windows 10 and Server 2016

Here is another post about creating Windows demo environments with Vagrant. This time we create our own Windows 10 LTSB and Windows 2016 CTP 5 core boxes from scratch with Packer.


You can checkout the Packer templates on my Github page here:


For the impatient: I published my boxes at Hashicorp here. You can use them right away with Vagrant and Virtualbox. I'm currently writing Parallels providers as well.

Here is how.
Install Virtualbox and Vagrant for your OS.
Create a folderstructure on your machine for the Vagrant boxes. Then issue a 'vagrant init':

#Create a folder structure to host the Vagrant boxes 
#Can be named anything and placed anywhere you like
New-Item \Boxes\Win10 -Type Directory -Force; cd C:\Boxes\Win10

vagrant init jacqinthebox/windows10LTSB
vagrant up --provider Virtualbox

And now we wait!
When the box has finished downloading you can provision the box to your liking and sysprep it (there is an autounnattend.xml already in c:\logs).
I currently use this Vagrantfile:

# -*- mode: ruby -*-
# vi: set ft=ruby :

$sysprep = <<'SYSPREP'
& $env:windir\system32\sysprep\sysprep.exe /generalize /oobe /unattend:C:\logs\unattend.xml /quiet /shutdown

Vagrant.configure(2) do |config|
  config.vm.define "client01" = "jacqinthebox/windows10LTSB"
  config.vm.hostname = "client01" "private_network", ip: "" :forwarded_port, guest: 3389, host: 33989, id: "rdp", auto_correct: true
config.vm.provision :shell, inline: $sysprep


The box comes with Chocolatey and Package Management preinstalled, so you can install Chrome with a simple:

Install-Package -Providername chocolatey googlechrome -ForceBootstrap -Force

OK, onto how the box was built and why.

Why build a Windows demo environment?

Indeed, why on earth would I want to build a demo environment? Well, I am a Powershell trainer. And I do most of my trainings at the customer premises. So there's no training equipment and I always ask my students to bring their own device. Now how would I make sure we are working on the same machines and the demo's work? Enter Vagrant.

Why not just create sysprepped golden images and clone them all the time?

I love Vagrant. I love automation! Here are some of my other Vagrant posts:

I did learn a lot in the meantime, so I will basically repeat everything in this post. And now Packer supports WinRM, so installing SSH on Windows is no longer necessary.

How to build the box with Packer

Install Vagrant, Virtualbox and Packer. I'm on Windows 10 and using OneGet, indeed, from the commandline! Hurray!

Install-Package -ProviderName Chocolatey - ForceBootstrap -Force vagrant,virtualbox,packer

Then clone the packer-templates Git repo:

git clone

Now build the box like this and add it to Vagrant:

packer build -only virtualbox-iso windows_2016.json
vagrant box add --name windows_2016

How does Packer work?

Basically Packer builds the VM with parameters it reads from a json file.
This is the json file for the Windows 10 LTSB machine:

  "builders": [
      "type": "virtualbox-iso",
      "iso_url": "{{user `iso_url`}}",
      "iso_checksum_type": "{{user `iso_checksum_type`}}",
      "iso_checksum": "{{user `iso_checksum`}}",
      "headless": false,
      "guest_additions_mode": "attach",
      "boot_wait": "2m",
      "communicator": "winrm",
      "winrm_username": "vagrant",
      "winrm_password": "vagrant",
      "winrm_timeout": "5h",
      "shutdown_command": "shutdown /s /t 10 /f /d p:4:1 /c \"Packer Shutdown\"",
      "shutdown_timeout": "15m",
      "guest_os_type": "Windows81_64",
      "disk_size": 61440,
      "floppy_files": [
        "{{user `autounattend`}}",
      "vboxmanage": [
   "provisioners": [
      "type": "powershell",
      "scripts": [
  "post-processors": [
      "type": "vagrant",
      "keep_input_artifact": false,
      "output": "windows_10_{{.Provider}}.box",
      "vagrantfile_template": "vagrantfile-windows_10.template"
  "variables": {
    "iso_url": "",
    "iso_checksum_type": "md5",
    "iso_checksum": "c22bc85b93eb7cc59193f12f30538f78",
     "autounattend": "./answer_files/10/Autounattend.xml"

So, the whole creation process of a Vagrant box goes something like this:

  • Packer creates and configures the VM
  • Packer attaches the floppy files to the VM
  • It starts installing the OS, the Windows Installer grabs the answer file from the floppy
  • In the answer file the Vagrant user is created
  • In the answer file we will call a powershell script (bootstrap.ps1) which enables WinRM
  • As soon as WinRM is available, Packer will start provisioning scripts the box by executing provision.ps1
  • By running provision.ps1 the guest additions are installed and the box is compacted with sdelete.exe

Let's dissect parts of this the json file.

 "type": "virtualbox-iso",
 "iso_url": "{{user `iso_url`}}",
 "iso_checksum_type": "{{user `iso_checksum_type`}}",
 "iso_checksum": "{{user `iso_checksum`}}",

Here we declare that we are creating a Virtualbox vm.
The iso_url , iso_checksum_type and iso_checksum are variables, which are set in the variables block in the bottom of the file.

"headless": false,
"guest_additions_mode": "attach",
"boot_wait": "2m",
"communicator": "winrm",
"winrm_username": "vagrant",
"winrm_password": "vagrant",
"winrm_timeout": "5h"

We set headless to false to see what is going on. The guest_additions_mode is attach. Valid options are "upload", "attach", or "disable", but we choose attach because uploading the tools to the VM is not working via WinRM (it does work with SSH, but we are not using SSH). So we attach the guest additions iso and install the tools from there in the provision stage (more about that later). We set the winrm_timeout to 5h. This is the time Packer waits until WinRM becomes available.

"floppy_files": [
     "{{user `autounattend`}}",

This is the part where we define the scripts that are placed on the floppy disk that gets attached to the box.

  "provisioners": [
      "type": "powershell",
      "scripts": [

This part is executed after WinRM is available. In the provison.ps1 script we enable RDP, install chocolatey, install the guest additions and last but not least the disk gets compacted with sdelete.exe.

So there it is! Clone the repo, check out the answer files and the scripts and build your boxes already!

Special thanks goes to..

This article: and this Github repo:

Powershell Profile

Here is a quick and dirty way to create a Powershell profile.

First, start the Powershell terminal and type:

new-item $profile -Force
notepad $profile

An empty profile file appears, and you can type the commands you want to run at start up:

cd ~/Documents

Fortune cookie

If you want to be greeted with a fortune cookie, add the following line:

$fc = ((Invoke-WebRequest | ConvertFrom-Json
write-host `t `"$($fc.fortune)`"

Thanks to the Google Developers Group Nort Colorado for hosting the api!

Some nice ASCII art

Finally, add a nice ASCII picture to spice up your day. I found one here. Clone the repo and save Get-MOTD.ps1 in your $env:HOMEPATH\Documents\WindowsPowershell\ directory.

Your $profile file will look like this:

. $env:HOMEPATH\Documents\WindowsPowershell\Get-MOTD.ps1
write-host ""
write-host ""
$fc = ((Invoke-WebRequest | ConvertFrom-Json
write-host `t `"$($fc.fortune)`"
write-host ""
cd ~

And it if you start the Powershell terminal you will be greeted like this:

2016-04-25 12_57_37-Windows PowerShell

Tip: don't do this in ISE. Also, install posh-git and follow the profile adjustments described on their github site.

Create a lab on Azure with Vagrant and Powershell

If you want to spin up a lab quickly to test things in a Windows environment, you can use an Azure trial account. It is possible to create trial accounts indefinitely so it will cost you nothing. So, let's go.

For this scenario, I am assuming you are on Windows. By the way, I did the same on a Macbook but instead of Powershell I used the Azure CLI for Mac (runs on Node.js). Check this.

Step 1. Create an Azure trial account

Create a trial account on Azure here.
You will need to supply your credit card info and you should use an mail address that has not been used before for a trial. I am on Google Apps, so I can create mail addresses as much as I like.

Step 2. Install Azure Powershell

You'll need Azure Powershell to query the available images.
Install the Azure Powershell with the msi (or Web Platform Installer).
I've been trying to install the SDK with OneGet, but it seems to be not available.

This gives you a brand new shell.
Not happy with it because it doen't have a cursor. Let's fix that:

[Console]::CursorSize = 25

Step 3. Add your Azure credentials



and enter your credentials


Next, get the publishsettings.



Save your publishsettings (e.g. on c:\temp) and import them:

Import-AzurePublishSettingsFile c:\temp\%your trial account%-credentials.publishsettings

Step 4. Generate certificates

I would advise to use Cmder with msysgit integration, if you don't already. Cmder is my go to terminal emulator. I use it for Powershell, Git Bash and ordinary DOS. So install Cmder with Chocolatey.

  • First create a pem certificate which is conveniently valid for 10 years. This contains a public key and private key.
  • Then create a pfx certicate based on this pem certifcate.
  • From the pfx, generate a cer to upload to Azure.

openssl req -x509 -nodes -days 3650 -newkey rsa:2048 -keyout azurecert.pem -out azurecert.pem

openssl pkcs12 -export -out azurecert.pfx -in azurecert.pem -name "Vagrant Azure Cert"

openssl x509 -inform pem -in azurecert.pem -outform der -out azurecert.cer

Thanks to this article.

Step 5. Upload the cer file to Azure

I can't figure out how this works with Powershell, so log on to your subscription and add the .cer file:


First go to settings, then to Management Certificates and upload your .cer file.


Step 6. Install the Vagrant Plugin for Azure

vagrant plugin install vagrant-azure
vagrant box add azure

Now take a look at the Vagrant file for this box. It is located here: C:\Users\yourname\.vagrant.d\boxes\azure\0\azure\Vagrantfile.
In this file you can define some constants that will be applied to every Azure box you create. I've changed 'azure.vm.size' from 'Small' to 'Medium' and added 'azure.vm.location' = West Europe.

# -*- mode: ruby -*-
# vi: set ft=ruby :

# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  # All Vagrant configuration is done here. The most common configuration
  # options are documented and commented below. For a complete reference,
  # please see the online documentation at

  config.vm.provider :azure do |azure|
    azure.vm_size = 'Medium'
    azure.vm_location = 'West Europe' # e.g., West US

Step 7. Create a new Vagrant file for your Azure box

Now it's time to create the Azure Vagrant box. Without much further ado, this is my Vagrantfile:

# --
Vagrant.configure('2') do |config| = 'azure'

    config.vm.provider :azure do |azure, override|
        azure.mgmt_certificate = 'insert path to you pem certifcate'
        azure.mgmt_endpoint = ''
        azure.subscription_id = 'insert your Azure subscription ID'
        azure.vm_image = ''
        azure.vm_name = 'box01' # max 15 characters. contains letters, number and hyphens. can start with letters and can end with letters and numbers

        azure.vm_password = 'Vagrant!' # min 8 characters. should contain a lower case letter, an uppercase letter, a number and a special character

        azure.storage_acct_name = 'azureboxesstorage2015' # optional. A new one will be generated if not provided.
        azure.cloud_service_name = 'azureboxes' # same as vm_name. leave blank to auto-generate
        azure.vm_location = 'West Europe' # e.g., West US

    azure.tcp_endpoints = '3389:53390' # opens the Remote Desktop internal port that listens on public port 53389. Without this, you cannot RDP to a Windows VM.

The Vagrantfile is of based on the Vagrantfile supplied by

You can get a list of available Azure VM images by logging on to your Azure subscription with Powershell and issue the following command:

Get-AzureVMImage | where-object { $_.Label -like "Windows Server 2012 R2 *" }| select imagename,imagefamily

Step 8. Vagrant up

Now it's time to issue a Vagrant up.

This is will generate some error messages because the vm needs to initialize (I assume).


Just issue an vagrant up again until it says: The machine is already created.

Then you can go ahead and RDP into your new VM:


So there you go, now you are all set to deploy Azure images until the cloud bursts.