Browsing:

Auteur: Jacqueline

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:

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)
        {
            config.MapHttpAttributeRoutes();
            GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
            GlobalConfiguration.Configuration.Formatters.Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);

            config.Routes.MapHttpRoute(
                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 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:

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("*", "*", "*");
            config.EnableCors(cors);
            config.MapHttpAttributeRoutes();
            GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
            GlobalConfiguration.Configuration.Formatters.Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);

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

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)
        {
            GlobalConfiguration.Configure(WebApiConfig.Register);
        }

 

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:

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

packer

You can checkout the Packer templates on my Github page here: https://github.com/jacqinthebox/packer-templates.

TL;DR

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
SYSPREP

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

end

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 https://github.com/jacqinthebox/packer-templates.git

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 windows_2016_virtualbox.box

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`}}",
        "./answer_files/10/unattend.xml",
        "./scripts/bootstrap.ps1",
        "./scripts/sdelete.exe",
        "./scripts/oracle-cert.cer"
      ],
      "vboxmanage": [
        [
          "modifyvm",
          "{{.Name}}",
          "--memory",
          "2048"
        ],
        [
          "modifyvm",
          "{{.Name}}",
          "--cpus",
          "2"
        ]
      ]
    }
  ],
   "provisioners": [
    {
      "type": "powershell",
      "scripts": [
        "./scripts/provision.ps1"
      ]
    }
  ],
  "post-processors": [
    {
      "type": "vagrant",
      "keep_input_artifact": false,
      "output": "windows_10_{{.Provider}}.box",
      "vagrantfile_template": "vagrantfile-windows_10.template"
    }
  ],
  "variables": {
  
    "iso_url": "http://care.dlservice.microsoft.com/dl/download/6/2/4/624ECF83-38A6-4D64-8758-FABC099503DC/10240.16384.150709-1700.TH1_CLIENTENTERPRISE_S_EVAL_X64FRE_EN-US.ISO",
    "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`}}",
     "./answer_files/10/unattend.xml",
     "./scripts/bootstrap.ps1",
     "./scripts/sdelete.exe",
     "./scripts/oracle-cert.cer"
      ],

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": [
        "./scripts/provision.ps1"
      ]
    }
  ],

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: http://www.hurryupandwait.io/blog/creating-a-hyper-v-vagrant-box-from-a-virtualbox-vmdk-or-vdi-image and this Github repo: https://github.com/joefitzgerald/packer-windows.


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 https://gdgnoco-fortune.appspot.com/api/fortune).content) | 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
Get-MOTD
write-host ""
write-host ""
$fc = ((Invoke-WebRequest https://gdgnoco-fortune.appspot.com/api/fortune).content) | 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.


My predictions for 2016 and beyond

For the first time a post not about coding or information technology, but about my predictions for 2016 and beyond. Here goes. These are going to get huge in 2016:

  • Blockchain is going to get momentum. Blockchain helps us solve the problem of the trusted third parties (like banks), which have created an inefficient society with has led to inequality. Blockchain is a technology which allows us to keep a ledger together. The authority to control, to divide and to conquer belongs to us!

 

  • Soylent, the food replacement. When I first read about Soylent it was still a crowdfunding initiative from Rob Rhinehart at Kickstarter in 2013. Soylent is open source, so you can create your own. But you can also buy it from several suppliers (mostly in the Netherlands funny enough). I have tried Joylent myself and I must admit it is rather tasty. Now I’m eagerly awaiting my Jakefood Light shipment. What is so disruptive about Soylent? Well, it is a food drink that contains only what the body needs. Better than pizzas and hamburgers for sure. For just 9 euros per day.
    Imagine, this can decrease our ecological footprint. It might be a solution for world hunger as well.

 

  • Blue Origin and Spacex. Well, they are inventing rockets that can be re-used. Space travel will be cheaper. It allows us to dream about a future where we can colonize Mars. And this dream can become reality.

  • Ok, I have to admit. Microsofts Continuum is pretty cool. Imagine the situation where you only need to own a smartphone. It will become a desktop computer if you attach it to a dock.  Now we only have to wait until applications become Continuum aware. (But I’m not sure if I would ditch my Macbook).

 

So, there you have it. These are my predictions for the future. They will be big. Maybe not so in 2016, but we are well on our way.

 


ASP.NET 5 getting started from scratch

Let’s see how ASP.NET 5 works and discover how we can build a basic website with an API and an Angularjs frontend. Just like we’re used to doing with Node.js. Let’s see how the Microsoft way compares. Maybe I’m a little late because the current release seems to be 1.0.0.-rc2 on Github. So there’s tons of info on the Internet already. And here is my bit as well. 🙂

I will use the Visual Studio Community Edition and I’ve got a VirtualBox VM running. And let’s also host the app in Azure and see how we can collaborate using the Visual Studio Online tools.

First, select File, New, Project and select the Empty ASP.NET 5 template:

aspnet5_1

 

 

 

 

 

 

 

Let’s debug and run it immediately!

aspnet5_2

 

 

 

 

Cool! Now where did that came from?

Startup.cs

Startup.cs is the entrypoint for an ASP.NET 5 application. I feel it compares to the app.js or index.js in an Express application which requires all the dependencies needed for the application. It is what the Global.asax was before. There are 2 sections: ConfigureServices and Configure. I think the comments describes their purposes really well:

public class Startup
    {
        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app)
        {

           
            app.UseIISPlatformHandler();
              
            app.Run(async (context) =>
            {
                await context.Response.WriteAsync("Hello World!");
            });             
        }

        // Entry point for the application.
        public static void Main(string[] args) => WebApplication.Run(args);
    }

Adding a static page

If we follow along the Express.js workflow, I would now need to add the possibility to add a static file (index.html). And I would need to plug that in to the Startup.cs. And I would need to install the dependencies in a package.json sort of file:

And that is correct. There is a project.json file in the solution and I need to add a dependency to Microsoft.AspNet.StaticFiles. Add it like I did at line 10. (It has intellisense, cool!)

{
  "version": "1.0.0-*",
  "compilationOptions": {
    "emitEntryPoint": true
  },

  "dependencies": {
    "Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
    "Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
    "Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final"
  },

  "commands": {
    "web": "Microsoft.AspNet.Server.Kestrel"
  },

  "frameworks": {
    "dnx451": { },
    "dnxcore50": { }
  },

  "exclude": [
    "wwwroot",
    "node_modules"
  ],
  "publishExclude": [
    "**.user",
    "**.vspscc"
  ]
}

Now I can add an index.html file in wwwroot and edit Startup.cs. Delete everything in the Configure method and add ‘app.UseStaticFiles();’.

 public void Configure(IApplicationBuilder app)
        {
            app.UseStaticFiles();
        }

Run the app and now this page is served:
aspnet5_3

 

 

 

 

But of course I don’t want to type in the URI. I want the web app to server default files.
Then it seems I still need to add middleware to serve the index.html. Add ‘app.UseDefaultFiles(); to the Configure method in Startup.cs:

public void Configure(IApplicationBuilder app)
        {
            app.UseDefaultFiles();
            app.UseStaticFiles();
        }

You can find a great explanation here.

So great! That means I can go ahead and add Bootstrap and Angular and that I can write some serious api in C#.

BTW, if you want to know how Microsoft (and the community I must add) has envisioned an ASP.NET 5 web project, please try the MVC web app template.