Categorie: Algemeen

My Productivity System or How I Get Things Done




Why a productivity system?



My personal system


This is how it works:

2. Finish every task that will take only 2 minutes.
5. Focus on your tasks for today and nothing else.
6. Track Time for these tasks





Mac only and Expensive?


Take Aways


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",
                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)
            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 and Export a bacpac file to Azure Storage

Next in my Azure database series is the bacpac file, Azure works with bacpac files, we can up- and download to azure. We need a storagespace in order to up and download files.

We start creating storage space. This may come in handy when we need file tranfer, and again you can access the files from your local device.

To acces your storage account, you need the access keys, which are generated when creating the storage account. On the footer of the page, where the red arrow is visible in the screenshot below, you can open up and acces the keys.

This key you need for the net step, we will download a windows Azure storage explorer to be able to acces the files. Azure has a broad choise in downloads for this option. I downloaded the Azure storage explorer from codeplex storage explorer.

Now I will walk you through on how to create a bacpac file on your local database. Choose the Export Data-tier Applicationbacpac1

Now we have to options, you can create the bacpac file locally and upload it with the storage explorer or link it to azure directly, we choose tthe latter. Fill in the storage credentials we just created by connecting the storage account with the access keys. Name a container and filename and thunderbirds are GO!




Connect your Azure database from your local SSMS #Error 40615

Ofcourse we wanna access our fresh imported database on Azure locally!

And this is so easy if you just follow these simple steps.

In order to be able to access the database located on your Azure cloud, you have to know the Azure address, which you can find on the Windows Azure Management Portal. Click the database icon and open up the database. Here you will find the connectionstring.


Next step is to make sure Azure knows your device by adding it to the trusted devices in Azure, if your device is unknow to Azure it will bring you the following message:1error-whenlocal_connection

you can add the device by going to the database icon in Azure and click the CONFIGURE tab. Here you add and save your device IP.2local_adTosavelist

Now you can login to your Azure cloud with the credentials you made while creating the database on Azure.
3Local_db connect

SQL database migration to Microsoft Azure with codeplex

sql-database-windows-azureThere are multiple ways to migrate your database to the Azure cloud. Today, I am testing this sql azure plugin by codeplex. I unzipped it to my local folder and run it as admin.


Now fire up the SQLAzureMW.exe and the script wizard starts up, which is very intuitive, choose the available options, In my case, I will migrate from SQL database to Azure SQL Database. Fill in your database details and run the export. This might take a while depending on how big your SQL Database is. You can also make a selection of tables/views you want to export, or just export the whole database.


Azure2 Azure3   Azure4 Azure5 Azure6   Azure7

Make sure you fill in the correct details for your target server (you can find your sql connection data on your azure Database configure page.) Azure10  Azure11

Now the database will be brought to the Azure cloud, again this might need some time, but you can see the progress on screen.

Azure11 Azure12


I’m a big fan of this sql azure plugin by codeplex , Like i said it’s very intuitive and gives you several options to work with your local and azure data.