Tag: Powershell

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.

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.


How to use Powershell without Google

Here are a few tips to use Powershell without using Google.

I know my way around Powershell quite OK. But I Googled a lot and when in a hurry I copied and pasted a lot. So I never took the time to be really in depth. Now is the time! And there is a way to write Powershell without Google! You need to know some very basics and how to study cmndlets, methods and properties.

Let’s go!

Suppose you want to check if there are any PST’s on a harddrive and let’s pretend you know nothing, just like John Snow.

Find out what cmdlets are available with Get-Command

Obviously we need to recurse directories to see if there are any files with a .pst extension. So let’s see if there’s a cmdlet (a function) with ‘dir’ in it.

PS C:\Users\Jacqueline> get-command '*dir*'

CommandType     Name                         
-----------     ----
Alias           chdir -> Set-Location
Alias           dir -> Get-ChildItem
Alias           rmdir -> Remove-Item

Get-ChildItem looks like the cmdlet we need. Let’s see how it works with the help files.

Get the help files

The problem with command line interfaces: you can’t ‘guess’ which command to use and what the parameters are. So you will need to read the help files. And you will want to update them. Unfortunately, the help files are in c:\windows\system32, so you need to run the command as an Administrator. You can only update-help once a day unless you use the -Force parameter. So open a console as an Admin and run:

PS C:\> update-help -UICulture en-US -force

Needless to say an Internet connection is required. What if you don’t have one?


Saving help to an alternate location

In that case you can save the help files on an alternate location or on a netwerk share and then update-help.

PS C:\> save-help -DestinationPath C:\powershell\help2 -force -UICulture en-US

and then (as an Administrator):

PS C:\> Update-Help -SourcePath C:\powershell\help2\ -force -UICulture en-US

Now you can use the help files.

Using the help

PS C:\> Help Get-ChildItem

Will display all there is to know about Get-Childitem. Like parameters and what kind of parameters it accepts (string, arrays and so on). If you scroll down the help you get to see the remarks:

    To see the examples, type: "get-help Get-ChildItem -examples".
    For more information, type: "get-help Get-ChildItem -detailed".
    For technical information, type: "get-help Get-ChildItem -full".
    For online help, type: "get-help Get-ChildItem -online"

The -examples are very convenient if you want to have a quick solution.

So now we can play a bit with Get-ChildItem. Let’s discover its syntax:

    Get-ChildItem [[-Path] ] [[-Filter] ] [-Exclude ] [-Force] [-Include ] [-Name] [-Recurse] [-UseTransaction
    []] []

    Get-ChildItem [[-Filter] ] [-Exclude ] [-Force] [-Include ] [-Name] [-Recurse] -LiteralPath  [-UseTransacti
    on []] []

    Get-ChildItem [-Attributes ] [-Directory] [-File] [-Force] [-Hidden] [-ReadOnly] [-System] [-UseTransaction] [

Here we see it accepts a -Path parameter which is an array because there are brackets: String[]. So we can input multiple search locations by creating an array of locations. Let’s see how we can define an array in Powershell.

get-help array

And you will see you get very valuable information about how to create an array. I could create an array like this:

$search = @($env:HOMEPATH,"c:\temp")

Notice the quotes around c:\temp because we’re dealing with strings.
The $env:HOMEPATH is already a variable which returns a string.

We can test the array like follows:

PS C:\Users\Jacqueline> $search = @($env:HOMEPATH\Dropbox,"c:\temp")
PS C:\Users\Jacqueline> $search

Now we can do a search ilke this:

get-childitem -path $search -Recurse -Include "*.pst" 

I don’t want to look at all those red error messages, so let’s suppress them:

get-childitem -path $search -Recurse -Include "*.pst" -ErrorAction silentlycontinue

And now for real:

PS C:\Temp> get-childitem -path $search -Recurse -Include "*.pst" -ErrorAction SilentlyContinue

    Directory: C:\Users\Jacqueline\Dropbox\work

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----         5-4-2013     18:33      211305472 jacqueline.pst

    Directory: C:\temp

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----        26-7-2015     13:12        5242880 archive.pst
-a----        26-7-2015     13:12        5242880 old.pst

Let’s put the result in a variable, like so:

$pst = Get-ChildItem -Path "c:\" -Include "*.pst" -Recurse -ErrorAction SilentlyContinue

Investigating $pst with Get-Member

Like Get-Command and Get-Help, Get-Member is a really import cmdlet you should know about. With Get-Member we can investigate which properties and methods are available. How can I actually write a script or type a command-line command without having to memorize every object model found on MSDN?

Once you connect to an object you can pipe that object to Get-Member; in turn, Get-Member will enumerate the properties and methods of that object.

PS C:\Temp> $pst | Get-Member

   TypeName: System.IO.FileInfo

Name                      MemberType     Definition
----                      ----------     ----------
Mode                      CodeProperty   System.String Mode{get=Mode;}
AppendText                Method         System.IO.StreamWriter AppendText()
CopyTo                    Method         System.IO.FileInfo CopyTo(string destFileName), System.IO.FileInfo... Create                    Method         System.IO.FileStream Create()
CreateObjRef              Method         System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)
CreateText                Method         System.IO.StreamWriter CreateText()
Decrypt                   Method         void Decrypt()
Delete                    Method         void Delete()
Encrypt                   Method         void Encrypt()
Equals                    Method         bool Equals(System.Object obj)


Scrolling down the list you will notice a Method GetType. Let’s run that:

PS C:\Temp> $pst.GetType()

IsPublic IsSerial Name          BaseType
-------- -------- ----          --------
True     True     Object[]      System.Array

So $pst is an Array (we already knew that..) but what is in the array?

PS C:\Temp> $pst | ForEach-Object { write-host $_.GetType()}

So, we’ve got an array full of FileInfo objects. Each objects has a set of methods and properties, which we can query by using Get-Member.

Copying and renaming the PST’s to another location

Let’s copy the PST’s to another location and rename then so some admin can import the PST into a mailbox.

Just copying is not that hard:

$pst | Copy-Item -Destination C:\Temp\pst-share

But if I want to rename the file as well I have to be a bit more ‘developerish’:

foreach ($f in $pst) {
    $name = $env:USERNAME + '-' + $f.Name
    Copy-Item $f.FullName -Destination "C:\temp\pst-share\$name"

Let’s debate on this script tomorrow.

Powershell Desired State Configuration simple example

While still in ‘Devop Mood’, let’s quickly figure out Desired State Configuration.

This image gives a nice overview of the DSC architecture:


If you have a Windows 2012 R2 Server with the latest updates and KB2883200 installed, you’re good to go. Check it like so:
wmic qfe | find "KB2883200"

What Powershell Modules are there installed anyway? Go ahead, open a Powershell console and type $env:PSModulePath -split ";"
This displays the locations on your PC where there are Powershell modules installed.

PS C:\Users\vagrant> $env:PSModulePath -split ";"
C:\Program Files\WindowsPowerShell\Modules

Now if you cd into C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ you get to see all the modules. If all is well, one of them is PSDesiredStateConfiguration. This is where the DSC commandlets are hidden.


A resource is an ‘object’ which you can configure with DSC. There are 3 sources for resources.

Out of the box resources
Out of the box with Powershell v4, there are modules for File, Registry, Services etcetera. To see the complete list, do:

PS C:\> Get-DscResource | select name

This outputs:


Community resources
The Powershell community has also written some modules for DSC resources, like DNS, Active Directory and Hyper-V. You can find them here and they are prefixed with a c.

Experimental resources
The Powershell team itself also provides some experimental resources which you can find here. These resources are prefixed with an x.

You can download these resources and add them to the $env.PSModulePath folder. Which is in my case: C:\Windows\system32\WindowsPowerShell\v1.0\Modules\.

Create your first configuration

These steps describe the DSC process:

  • Add the required DSC resources
  • Create a configuration script
  • Execute the script to generate a MOF file
  • Apply the MOF to the target nodes

Now let’s start with adding a folder named “c:\Replica” on every node. That means we could use the File resource, which comes out of the box. Now, how to use this file resource? Luckily, the Powershell folks have made things very easy for us. We can just type get-dscresource File -syntax and lo and behold:

PS C:\> Get-DscResource File -Syntax
File [string] #ResourceName
    DestinationPath = [string]
    [ Attributes = [string[]] { Archive | Hidden | ReadOnly | System }  ]
    [ Checksum = [string] { CreatedDate | ModifiedDate | SHA-1 | SHA-256 | SHA-512 }  ]
    [ Contents = [string] ]
    [ Credential = [PSCredential] ]
    [ DependsOn = [string[]] ]
    [ Ensure = [string] { Absent | Present }  ]
    [ Force = [bool] ]
    [ MatchSource = [bool] ]
    [ Recurse = [bool] ]
    [ SourcePath = [string] ]
    [ Type = [string] { Directory | File }  ]

So this basically explains how to use the File resource.

Fire up Powershell ISE and start type:

Configuration MyFirstConfig {

Node DSC1 {
    File SyncDir {
        DestinationPath = "c:\replica"
        Type = "Directory" 
        Ensure = "Present"   

MyFirstConfig -OutputPath c:\DSC

If you execute this script a MOF file will be created in c:\DSC.

Apply the MOF

Now go ahead and type: Start-DscConfiguration -Path c:\DSC

This will output:

PS C:\Windows\System32\WindowsPowerShell\v1.0\Modules> Start-DscConfiguration -Path c:\DSC

Id     Name            PSJobTypeName   State         HasMoreData     Location             Command
--     ----            -------------   -----         -----------     --------             -------
8      Job8            Configuratio... Running       True            DSC1                 Start-DscConfiguration...

And if all is well there is indeed a folder named ‘replica’ on your C:\ drive.


This was a very simple example of Powershell DSC. I think it is quite nice and I am planning to explore its possibilities in the near future (like tomorrow or so).