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 https://msftdbprodsamples.codeplex.com/downloads/get/880661# -OutFile adventureworksdb.zip
7z e .\adventureworksdb.zip

Import-Module SQLPS

$RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, 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=12.0.0.0, 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.

20160515linq

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

20160515serverexpl

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:

ice_screenshot_20160515-134347

Consume the api with Powershell and test with Pester

A tip: read http://mikefrobbins.com/2014/10/09/using-pester-for-test-driven-development-in-powershell/ 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:

ice_screenshot_20160515-135708

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

So let's invoke a test:

20160515pester-fail

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

function Get-AWPeople 
    [CmdletBinding()]
    param (
        [Parameter(Mandatory, 
                   ValueFromPipeline)]
        [string]$Uri
    )
 
    PROCESS {
       $data = ((Invoke-WebRequest $Uri).content) | ` 
                  ConvertFrom-Json
       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:

20160515pester2-fail

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

20160515pester2-OK

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

agile

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.