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.

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.
2015-08-12_09-47-21
 
 
 
 
Not happy with it because it doen’t have a cursor. Let’s fix that:

Step 3. Add your Azure credentials

Type

and enter your credentials

add-azure

Next, get the publishsettings.

add-azure4.

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

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.

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:

2015-08-13_06-49-41

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

cert-blur-bla

Step 6. Install the Vagrant Plugin for Azure

https://github.com/MSOpenTech/vagrant-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.

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:

The Vagrantfile is of based on the Vagrantfile supplied by https://github.com/MSOpenTech/vagrant-azure.

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

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).

azure-2015-fout

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:

vagrant-rdp-werkt

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