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

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

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:

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:

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:

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:

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

Next, write the test:

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

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:

ice_screenshot_20160508-093224

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:

20150508-context

 

 

 

 

 

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:

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

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 http://example.com/api/employees/1 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:

Then modify the WebApiConfig.cs class as follows:

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.

ice_screenshot_20160508-092302

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.

ice_screenshot_20160508-095951

Modify Global.asax (see the highlighted lines):

 

Step 6. Test the API

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

ice_screenshot_20160508-100831

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

It should return value1 and value2.

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