Garden management with Azure, Part 1

By on 02/03/2017

Microsoft offers with Azure and its SDKs a comfortable and fast way to create pretty nice applications

  • Cloud with SQL database, storage, authentification and notifications
  • Clients with offline data synchronisation
  • Life diagnostics and flexible scalability

When you follow the example implementations, it’s very easy to create an application that connects to the cloud and synchronizes its data (database and files) automatically with your device.

To me it happened that I was tempted to take the examples and throw them together in an complex app. But it didn’t take long, and things started to go crazy…

  • dependencies between nuget packages, and relics of old nuget versions led to unexplainable errors (especially in cross-platform implementations)
  • Data loading and sync worked very slowly, and sometimes buggy
  • The whole solution became chaotic, and the principles I tried to show in my Developing in small circles were soon not applicable anymore

So I decided to start another solution from scratch, to try to really understand what happens inside the SDK’s and nuget packages, and to test every piece nicely. Because this is one of the problems in modern development – you can put together lots of libraries and packages and create complex functionalities, without really knowing what happens inside. It makes you completely dependend on the work of others, and you can’t guarantee the quality of your solutions.

Recently, in the slack channel of a well known UI framework (that I also use), someone had a serious issue one week before his deadline. That’s a worst-case example for this kind of dependencies. I would never say not to use such components (especially this one I like very much), but it’s an example for my point: Never just be happy when something works in the moment. Always create a circle to test the part of the system continuously. And consistently throughout the stack!

So right now, I’ll start developing this app, and document the process step by step. I call it ‘CicloGardens’ and it will be available on GitHub:

As a start, I won’t focus on lots of functionalities and platforms to support, but I want to show and compare different features.

  • Backend with Azure – WebApp, SQL database and storage
  • As a client, I’ll create a UWP application with Xamarin. That’s mobile friendly, and still a fast way to implement and debug under windows
  • In further steps, Android, iOS, offline sync, authentication and notifications will follow. But first things first…

The first circle: simple Azure service with SQL database

I won’t start from ‘create an account’ or ‘first steps’. For this there are lots of docs and blogposts already. So the first things we need are

  • Azure SQL serve
  • Azure SQL database
  • Azure Storage ciclogardensstorage
  • Azure Mobile App (could also be a WebApp)
    • Setup sql dataconnection
    • Set storage dataconnection

Create server project in VisualStudio (todo github link)

  • Download the service quickstart from Azure
  • Add connectionstrings for sql and azurestorage
  • I changed ‘TodoItem’ to ‘Garden’ (DataObject, Controller, Context, Startup.MobileApp)
  • In Startup.MobileApp, I remove the CicloGardensInitializer, because I don’t want the service to setup and fill the database automatically

Now it’s time for a first manual deploy of the service…

  • Make sure you’re logged in to MSDN/Azure in VisualStudio
  • in VisualStudio’s Server Explorer,under Azure – App Service, I find my App Service. In the contextmenu, you can download the publish profile
  • In the solution explorer, right click the service project, select Publish – Import and open the downloaded profile
  • After publishing, you should see a default azure website with a smiley

Now we should setup the migrations for EntityFramework. That’s important for making sure that the service models and the database tables are equivalent.

  • Allow connections to the sql database from your working machine (azure portal – sql server – firewall – Add client IP)
  • Package Manager Console
    • enable-migrations
    • add-migration (I used the name ‘First Setup’)

I prefer to run the migrations manually, because I also change the model implementations manually. But there are many possible approaches for this. It can be run automatically on startup of the service. Or through a deploy script for a productive environment. I do it manually with the command

  • update-database

You will probably run into an error that cost me a loooot of time and nerves, until I found out what’s happening. It’s something about ‘clustered indices’. The problem is that when running migrations from the commandline against an Azure SQL database, the migration behaves different than when running from the service or when running against SQL Server. So you have to add this magic line of code in the constructor of the Configuration.cs class of the service:

SetSqlGenerator(“System.Data.SqlClient”, new EntityTableSqlGenerator());

After migrating, it’s time for closing the first circle, using a test project ‘CicloGardensService.IntegrationTest’. We want to make sure that all the migrations are really in the database, and that all CRUD operations for Garden objects are working through the service. So the first test class is called Database.cs. The only test is called MigrationsAreUpToDate, and this is what it’s doing:

  • Instantiate an instance of Configuration.cs (the connection string must be in app.config of the unit test)
  • get all migrations from the database, the local migrations and the pending migrations
  • assert that local and remote migrations are the same
  • assert that there are no pending migrations

So far, so good. But just checking the migrations doesn’t make sure that the actual POCOS and the database scheme are the same. If you made code changes, but forgot to create a migration for it, the last test won’t fail. For this, we really are going to establish a connection to the service, and perform the CRUD operations for the Garden class. The test class is called GardenControllerTest.cs, and I’ll be using a simple HttpClient, to not depend on the Azure SDKs and to really understand how the requests and Uri must look for the Azure service endpoints. This is what the tests are doing:

  • Setup a HttpClient with necessary config in the constructor
  • Method ‘Initialize’ that runs before each tests. It’s connecting directly to the database with EntityFramework, cleans up the ‘dbo.Gardens’ table and checks that it’s really empty. Like this, every test runs independently from the others, and can have precisely defined test data
  • ‘PostGarden’ posts default garden objects to the service. Checks that the POST responses contain the objects including ‘Id’ and ‘CreatedAt’ timestamp
  • ‘GetAllGardens’ posts the same default gardens. Then reads all gardens from the service and checks they’re complete
  • ‘GetGarden’ gets one garden from the service and checks that its content is correct
  • ‘PatchGarden’ posts the default gardens. Then creates a Delta<Garden> object containing overrides for the properties ‘Name’, ‘Longitude’ and ‘Latitude’ and sends a patch to the service. Then reads the same garden from the service and tests the overriden properties and that the ‘UpdatedAt’ field was changed
  • ‘DeleteGarden’ posts all default gardens, deletes one of them. Then reads all gardens and checks that the deleted one is gone

Now the first circle is complete. We have a SQL database, an Azure service and Integration Tests that make sure that the database scheme is updated (entityframework migrations) and that the actual POCO operations work against the running service. These tests use direct database connections and a simple HttpClient.

I am aware that this circle is not what I defined in my previous post Developing in small circles. There, I proposed the automatic setup of a small local database and unit testing the controller directly (without deploying it into a server) against it. In this case, I decided to use the dev database directly, and also the deployed azure service. This made it easier to write the test (no sql setup needed), and directly solved the circle for the real http call. It just seemed more convenient for me at the point. But how and which circles you define, is always up to you and your team.

Just don’t make the big mistake that happened far too often: don’t let the circles become too big! Developing tests early ALWAYS saves you more time and trouble in the future than what you invest in the moment!

Part 2 of this series will be creating a real client implementation using the Microsoft SDK for Azure. And implement the supported offline synchronization with a client-side SQL database.

So long…