Exposing your Azure API App to PowerApps and Flow

Since some time Azure allows you to expose your API apps to PowerApps and Flow in an easy way. Just go to your API definition (make sure CORS is enabled) and click on the export-button.


This gives you a dialog where you can download or get a link to your Swagger metadata-file. Download it, and click on Go To PowerApps or Flow.


Importing into PowerApps is the simplest to do.In the PowerApps page, select Connectors, custom and then New Custom API. Just link to your swagger file and click next.


There you go. Flow needs a little bit more work.Go to the Custom APIs for Flow and add a custom API. Again, link to your swagger file.


The extra work is that you can (and probably should for improving usability) provide more information for each of your API functions.


After you've done, start using it in flow and powerapps.

Creating connectors for your Logic Apps/Flow (Part 3 – More triggers)

So, let's recap: we had regular connectors, we had poll triggers. So, we're still missing the push triggers. A poll trigger needs to be polled regularly by the logic app to see if it can continue. A push trigger does not have to be polled, it will tell the logic app when it needs to continue, passing the necessary information. At this moment a push trigger needs to be called by a webhook-action. Your trigger needs two function : Subscribe and Unsubscribe. In the Webhook-action these functions will automatically be called when the Logic App starts or stops. The WebHook action needs to pass the callback-url to your Subscribe/Unsubscribe-action. This callback-url is the url your API app will call for starting the Logic App. Your functions should look like this :

public static List subscriptions = new List();
[HttpPost, Route("api/webhooktrigger/subscribe")]
public HttpResponseMessage Subscribe([FromBody] string callbackUrl)
    return Request.CreateResponse();
[HttpPost, Route("api/webhooktrigger/unsubscribe")]
public HttpResponseMessage Unsubscribe([FromBody] string callbackUrl)
    return Request.CreateResponse();
The idea is that you call back the logic apps using the list of subscriptions as this:
using (HttpClient client = new HttpClient())
    foreach (string callbackUrl in subscriptions)
        await client.PostAsync(callbackUrl, 
            new JsonMediaTypeFormatter(), "application/json");

After this your Logic app continues.

For using the pushtrigger you need a webhook-connector in your logic app. This needs to call the subscribe and unsubscribe-methods of your API App. For the body you need to pass the callback url.



But still it won't work. When going to the Trigger History you can find that you get a failure because of an error with the content-Type. It is simple to solve: Show the advanced options of your webhook-connector and add the content-type to your subscribe- and unsubscribe-headers as this:


Problem solved !

Creating connectors for your Logic Apps/Flow (Part 2 – Triggers)


In part 1, we saw how to add regular connectors for using in Azure Logic Apps. Remember: the purpose of these blog posts is just for pointing out some gaps in the documentation you'll find on the internet.

Next to regular connectors, we can also create connectors to be used as connectors, i.e. connectors that can initiate the logic apps. There are two types of connectors : poll and push connectors. The difference in short is :

  • Poll connectors will be regularly called to see if the logic app should start, using a polling mechanism.
  • Push connectors will call the Logic app to start, using a webhook mechanism.


Poll triggers

As mentioned before: poll triggers will be polled on a regular interval to see if something is changed, starting the logic app when this is the case. So how is this polling working ? If you add your API app as first connector in your logic app, he'll show you your triggerfunctions. Just select your trigger, and specify a polling-interval.The triggerstate can be left blank, but will be used for keeping track of the last poll-time. What will happen now:

  1. The Logic App will poll your API app, by calling the Get-method. The triggerstate will initially be empty. The Trigger should return HTTP 202 Accepted. The triggerstate should also be returned, containing the time of calling.
  2. next polls will contain a value for the triggerstate. Here we can have two options: if we do not need to trigger (i.e. no need to start the Logic App), the App just needs to return HTTP 202 again, with no changes. If the Logic app now has to start, the app needs to return HTTP 200 OK, with a modified triggerstate, and with, of course, the information you would like to pass to the Logic App

In my case, I want to trigger the logic app, when the weather changed. The information to pass looks like this :

public class WeatherInfo
    public int Temperature { get; set; } = 10;
    public string WeatherCondition { get; set; } = "Stormy";
    public int ChanceOfPrecipitation { get; set; } = 80;

Again : on the get-function, make sure to specify what will be the output. Otherwise your output will not be usable/visible in the logic app.

[SwaggerResponse(HttpStatusCode.OK, "triggered", typeof(WeatherInfo))]

For being able to send back the information, ánd the triggerstate, I created a simple helperfunction.

private HttpResponseMessage GenerateResponse(
    HttpStatusCode code, string triggerState,
    string retryAfter, WeatherInfo data = null)
    HttpResponseMessage responseMessage = Request.CreateResponse(code, data);
        $"{Request.RequestUri.Scheme}://{Request.RequestUri.Host}/api/weather?" +
    responseMessage.Headers.Add("retry-after", retryAfter);
    return responseMessage;


After deploying the API app to AZure (don't forget the metadata and the CORS-settings!), I add my API app to my logic app by first selecting "App Services", and then your API App.
After that, start using your output, e.g. in a mail.


For testing it out I created some view, that allows me to "change" the weather. If that happens, the logic apps starts working (and sends me a mail).

For completeness I'll give the full code for my API polltrigger underneath. Next time : Push triggers !!

public class WeatherController : ApiController
    [SwaggerResponse(HttpStatusCode.OK, "triggered", typeof(WeatherInfo))]
    public HttpResponseMessage Get(string triggerState = "")
        if (string.IsNullOrEmpty(triggerState))
            triggerState = DateTime.UtcNow.ToString();
            return GenerateResponse(HttpStatusCode.Accepted,
                triggerState, "15");
            if (DateTime.Parse(triggerState) < DateTime.UtcNow && WeatherReport.Changed)
                WeatherReport.Changed = false;
                triggerState = DateTime.UtcNow.AddMinutes(2).ToString();
                WeatherInfo info = new WeatherInfo()
                    ChanceOfPrecipitation = 80,
                    Temperature = 8,
                    WeatherCondition = "Storm"
                return GenerateResponse(HttpStatusCode.OK,
                    triggerState, "15", info);
                return GenerateResponse(HttpStatusCode.Accepted,
                    triggerState, "15");
    private HttpResponseMessage GenerateResponse(
        HttpStatusCode code, string triggerState,
        string retryAfter, WeatherInfo data = null)
        HttpResponseMessage responseMessage = Request.CreateResponse(code, data);
            $"{Request.RequestUri.Scheme}://{Request.RequestUri.Host}/api/weather?" +
        responseMessage.Headers.Add("retry-after", retryAfter);
        return responseMessage;

Creating connectors for your Logic Apps/Flow (Part 1–regular Connectors)


Azure logic apps have been around for a while now, most of the time as a preview. These apps allow you to create workflow-like apps, by connecting so called Connectors together. These connectors are actually just REST-services, with some extra's, like the exposure of metadata by using Swagger. These Logic apps have also been made available through Office 365, where it is known as "Flow". Office 365 Flow, or Azure Logic apps have access to a library of Connectors, like connectors for Twitter, Facebook, Outlook Mail and Calendar, SharePoint and many more. At the moment I write this there are 84 connectors available. But of course we can add our own connectors. You can find information on how to do this on the internet, however I found myself struggling with Azure because of incomplete information, even in the official Azure documentation. So, I decided to complete the information.

From a developer perspective, there are two types of connectors we can create: "regular" connectors and triggers. A regular connector can just be used within your flow, but not at the start. It can take input from connectors preceding it in the flow, and it can return output to the flow.  A trigger can be used in the beginning of a flow and is responsible for starting the flow.

Creating a regular Connector

Connectors are REST services. In my example I will do this with Web API, but it can be done in any other web-technology. While creating a web application just choose Azure API App. CaptureI will create a simple example in which I just reverse an incoming string. The code is pretty simple :

[SwaggerResponse(HttpStatusCode.OK,Type =typeof(StringManipulator))]
public IHttpActionResult GetReversedString(string id)
    var temp = "";
    foreach (var item in id)
        temp = item + temp;
    return Ok(new StringManipulator
        Original = id,
        Reversed = temp

However, the second argument in your SwaggerResponse-attribute, the Type, is super important. Without this argument Logic apps, using your API, will just assume your function returns nothing ! It took me some time to find this out, since it used to work fine without Type in an earlier version of Logic apps, and it's nor really clear from the documentation. So, beware.  After deploying your API app to Azure, there are two more things to do. Locate your API app in the Azure portal, and go to the API settings. Capture4The API definition should point to your swagger metadata. Make sure you refer to it using https !!!.Also make sure that CORS is enabled for your logic app, or just use * here. After that you need to restart your app. Otherwise your logic app will not pick it up. There you go. If you want to use it in a Logic app just add the Action App Services, select you API app, and select the action you want to use. Capture2And there you go, the following connectors have access to the output of your API app.
Pretty straightforward, if you don't forget the Type in your SwaggerResponse-attribute. Using API apps as triggers is somewhat more work. We'll discuss that in part 2.

Azure API and Logic Apps

Last monday we did a little event where we talked about some new exciting stuff that was announced on Build San Fransisco. I had a talk about API and Logic apps in Azure, which can be used for creating "microservices" apps.

As Martin Fowler defines it :the microservice architectural style is an approach to developing a single application as a suite of small services, each running in its own process and communicating with lightweight mechanisms, often an HTTP resource API (see more here)

My slides can be found here :

API Apps for web, mobile and logic apps.pdf (764.1KB)

New version of U2U CAML Query Builder

The original U2U CAML Query Builder has been around for many years, and is being used by many SharePoint professionals. So we thought it was time for giving it some small changes, and bringing it to version The query builder now uses CSOM for connecting to SharePoint, and it also allows connecting to Office365 now.
Next to these changes we also have two smaller changes:
  1. The different types of queries that can be created has been reduced to two for simplification.
  2. We added some tabs for directly copying Server- and Client-side code:

There you go, I hope you’ll enjoy it as much as previous versions.We’ll try to get you some more CAML query goodies later ! For now: you can find the Query Builder at http://u2u.be/software.

Creating updatable Live Tile from A to Z (part 2)

At last, part 2 of this blogpost. In part 1, we created an updatable Live Tile. We still need to update it with a Background task. We start by adding a Windows Phone Scheduled Task Agent, which is a projecttype added with the devtools for the Mango-version. This type allows you to ask tasks to be performed in the background. By default the task will run every 30 minutes (It can take longer depending on the memorypressure of the device). In here you can have an override of OnInvoke, that should perform the actual backgroundtask of your app.

The control (Indicator) whom I am using for creating a WriteableBitmap with, needs to be accessible from within the app ánd from within the backgroundtask. Since the app already needs a reference to the BackgroundTask, we cannot create a reference in the opposite direction. So we need to move the Indicator into a separate class-library.

You’ll probably see that this works quiet nicely, until all of a sudden your solution no longer builds. In your code all of the Indicator-properties seems to be absent. I presume it’s a bug in Visual Studio, but from time to time the BuildAction-property for Indicator.xaml changes, which prevents your solution from building. If it happens to you: change it back to “Page” :


Let’s do a little update of the tile we created :

   1:  Deployment.Current.Dispatcher.BeginInvoke(() =>
   2:              {
   3:                  Indicator indicator = new Indicator();
   5:                  indicator.KPI1Brush = new SolidColorBrush(Colors.Magenta);
   6:                  indicator.KPI2Brush = new SolidColorBrush(Colors.Orange);
   7:                  indicator.KPI3Brush = new SolidColorBrush(Colors.Red);
   8:                  indicator.KPI4Brush = new SolidColorBrush(Colors.Yellow);
  10:                  indicator.Measure(new Size(173, 173));
  11:                  indicator.Arrange(new Rect(0, 0, 173, 173));
  14:                  string indic = "/Shared/ShellContent/indicator.jpg";
  15:                  WriteableBitmap bm = new WriteableBitmap(indicator, null);
  16:                  IsolatedStorageFile isolated = IsolatedStorageFile.GetUserStoreForApplication();
  17:                  if (isolated.FileExists(indic))
  18:                  {
  19:                      isolated.DeleteFile(indic);
  20:                  }
  21:                  IsolatedStorageFileStream stream = isolated.CreateFile(indic);
  22:                  bm.SaveJpeg(stream, 50, 50, 0, 100);
  23:                  stream.Flush();
  24:                  stream.Close();
  26:                  ShellTile tile = ShellTile.ActiveTiles.FirstOrDefault();
  27:                  ShellTileData data = new StandardTileData()
  28:                  {
  29:                      BackContent = "Sober at 18:23",
  30:                      BackBackgroundImage = new Uri("isostore:/Shared/ShellContent/indicator.jpg", UriKind.Absolute),
  31:                      BackTitle = "0.123"
  32:                  };
  34:                  tile.Update(data);
  35:              });


This is of course very similar to the code in the first part of this blogpost. You can see that in here we also store the bitmap in isolated storage. The nice thing here is that the isolated storage is being shared between the backgroundtask, and the application for which you create the task. So it could also be used for transferring data from the app to the task.

Actually we’re changing stuff on the GUI Thread from a background thread, that’s why we need the dispatcher-object here.

One little problem remains : I would like to set one of the KPI-colors of my indicator to the PhoneForeGroundColor. In the app I do as such :

new SolidColorBrush((Color)App.Current.Resources["PhoneForegroundColor"]);


(Or use “PhoneForegroundBrush” for getting a Brush-object) But what to do in the BackgroundTask, since I do not have access to the App-class in there ? No problem: you can also access the system-resources through objects inheriting from FrameworkElement :

new SolidColorBrush((Color) indicator.Resources["PhoneForegroundColor"]);


Cool: ready to deploy !

Creating updatable Live Tiles from A to Z (part 1)

Something I haven’t found on the webs, is a nice overview of how to create updatable live tiles. Sure, you can gather all the data, but all the do’s and gotcha’s in one article would be nice. So I decided to get everything together myself.

OK: here’s the scenario. We need an app that visually shows information. Think about e.g. the mail-app that shows how many unread mails you have. Or a very nice one: Amazing Weather, which shows current weather conditions, and predictions. In our app we’ll show four arbitrary KPI’s on the tile, which will be updated in the background.

Step 1 : simple Tile

Your application can have one or several tiles on the start page( several if you implemented Deep link in your app), which can be reached by using the ShellTile.ActiveTiles collection. In my example there’s no deep link, so I should always get one tile, or none when not pinned to start. The Tile can be modified by passing it a ShellTileData-object. This way you can change front (Title, Count, BackgroundImage) and back (BackTitle, BackContent, BackBackgroundImage) of the tile. I will change the time whenever my app closes or deactivates :

   1:  ShellTile tile = ShellTile.ActiveTiles.FirstOrDefault();
   2:  if (tile!=null)
   3:  {
   4:      ShellTileData data = new StandardTileData()
   5:      {
   6:          Title = "12345",
   7:          Count = 5,
   8:          BackContent = "Some message...",
   9:          BackBackgroundImage = new Uri("http://mw2.google.com/mw-panoramio/photos/medium/33672781.jpg"),
  10:          BackTitle = "ABCDEF"
  11:      };
  13:      tile.Update(data);
  14:  }


This gives me following result :

Front : Back :
1 2

Although this can already be handy for some scenario’s, it’s not enough. The count does not appear like in the mail-hub. Unfortunately. And I could show my KPI’s with text, but my Tile will be full of text, and maybe I’ll have more text than can fit on the Tile.

Step 2 : not so simple Tile

Remember my blogpost about saving pictures to the PicturesHub (over here) ? In there I showed you can create bitmapimages from any control. This is also what we’ll be doing when creating our tile-images. I start by creating a usercontrol. Best is to give it height and width equal to 173. This is the height/width for the tile. This will allow you to easily create your tile in a WYSIWYG-way. The usercontrol simply shows my four KPI’s by using “Trafficlights”, showing red, orange or green. I will also add white (or black if the light theme is being used) for “disaster”. Let’s start creating our usercontrol :

   1:  <UserControl x:Class="PhoneApp1.Indicator"
   2:      xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
   3:      xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
   4:      xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
   5:      xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
   6:      mc:Ignorable="d"
   7:      FontFamily="{StaticResource PhoneFontFamilyNormal}"
   8:      FontSize="{StaticResource PhoneFontSizeNormal}"
   9:      Foreground="{StaticResource PhoneForegroundBrush}"
  10:      d:DesignHeight="480" d:DesignWidth="480" Height="173" Width="173">
  12:      <Grid x:Name="LayoutRoot" Background="{StaticResource PhoneAccentBrush}">
  14:          <Grid.RowDefinitions>
  15:              <RowDefinition Height="2*" />
  16:              <RowDefinition Height="*" />
  17:          </Grid.RowDefinitions>
  18:          <Grid.ColumnDefinitions>
  19:              <ColumnDefinition Width="2*" />
  20:              <ColumnDefinition Width="*" />
  21:          </Grid.ColumnDefinitions>
  22:          <Rectangle Name="KPI1Indicator" Stroke="{StaticResource PhoneBorderBrush}" StrokeThickness="2" Fill="Red" Margin="80,80,10,10" RadiusX="20" RadiusY="20" />
  23:          <Rectangle Name="KPI2Indicator" Grid.Column="1" Stroke="{StaticResource PhoneBorderBrush}" StrokeThickness="2" Fill="Green" Margin="3,80,30,10" RadiusX="20" RadiusY="20" />
  24:          <Rectangle Name="KPI3Indicator" Grid.Row="1" Stroke="{StaticResource PhoneBorderBrush}" StrokeThickness="2" Fill="Orange" Margin="80,3,10,30" RadiusX="20" RadiusY="20" />
  25:          <Rectangle Grid.Column="1" Grid.Row="1" Name="KPI4Indicator" Stroke="{StaticResource PhoneBorderBrush}" StrokeThickness="2" Fill="Yellow" Margin="3,3,30,30" RadiusX="20" RadiusY="20" />
  26:      </Grid>
  27:  </UserControl>


The four KPI-indicators need to be exposed to the outside word, so let’s add some properties for that :

   1:  public Brush KPI1Brush
   2:  {
   3:      get { return KPI1Indicator.Fill; }
   4:      set { KPI1Indicator.Fill = value; }
   5:  }
   7:  public Brush KPI2Brush
   8:  {
   9:      get { return KPI2Indicator.Fill; }
  10:      set { KPI2Indicator.Fill = value; }
  11:  }
  13:  public Brush KPI3Brush
  14:  {
  15:      get { return KPI3Indicator.Fill; }
  16:      set { KPI3Indicator.Fill = value; }
  17:  }
  19:  public Brush KPI4Brush
  20:  {
  21:      get { return KPI4Indicator.Fill; }
  22:      set { KPI4Indicator.Fill = value; }
  23:  }


When closing/deactivating the app, I will set the KPI-colors, and use this controls as image for the back of my tile. First, setting the colors:

   1:  Indicator indicator = new Indicator();
   3:  indicator.KPI1Brush = new SolidColorBrush(Colors.Black);
   4:  indicator.KPI2Brush = new SolidColorBrush(Colors.Blue);
   5:  indicator.KPI3Brush = new SolidColorBrush(Colors.Cyan);
   6:  indicator.KPI4Brush = new SolidColorBrush(Colors.Green);

And then making a WriteableBitmap with it :

indicator.Measure(new Size(173, 173));
indicator.Arrange(new Rect(0, 0, 173, 173));

WriteableBitmap bm = new WriteableBitmap(indicator, null);

The Measure and Arrange-stuff is for making the control fit into a 173 by 173 square. Finally I need to save my image to Isolated Storage. Inside isolated storage I can store things in the Shared/Shellcontent-folder. This will also make it accessible by my tiles (info here).

   1:  string indic = "/Shared/ShellContent/indicator.jpg";
   3:  IsolatedStorageFile isolated = IsolatedStorageFile.GetUserStoreForApplication();
   4:  if (isolated.FileExists(indic))
   5:  {
   6:      isolated.DeleteFile(indic);
   7:  }
   8:  IsolatedStorageFileStream stream = isolated.CreateFile(indic);
   9:  bm.SaveJpeg(stream, 173, 173, 0, 100);
  10:  stream.Flush();
  11:  stream.Close();


And finally, I update my Tile to show the image from isolated storage :

   1:  ShellTile tile = ShellTile.ActiveTiles.FirstOrDefault();
   2:  if (tile!=null)
   3:  {
   4:      ShellTileData data = new StandardTileData()
   5:      {
   6:          Title = "12345",
   7:          Count = 5,
   8:          BackContent = "Some message...",
   9:          BackBackgroundImage = 
  10:              new Uri("isostore:/Shared/ShellContent/indicator.jpg", UriKind.Absolute),
  11:          BackTitle = "ABCDEF"
  12:      };
  14:      tile.Update(data);
  15:  }

and there you go :


In next blogpost I’ll tell you about updating the tile with a backgroundtask, which also has some gotcha’s you need to know.


Using a Sterling-database in Windows Phone as alternative to SQL CE

I you remember my blogpost from a very long time ago, I wrote about using SQL CE with Windows Phone (you can find it here). SQL CE can be queried with LINQ to SQL, making it interesting for storing relational data. Recently I came in contact with the Sterling-database (check it here), an Object-database which can be used in .NET, Silverlight and Windows Phone ! You can add “SterlingPhone” to your project by using NuGet. Just like SQL CE I can use Sterling for storing data in Isolated Storage.

I’ll show you how to use it with a “fake” Northwind-database, but it can also be used for persisting full Viewmodels when using MVVM. In my fake Northwind-database I will use Products and Categories, so I simply start by creating classes for these two entities :

   1:  public class Product
   2:  {
   3:      public int ProductId { get; set; }
   5:      public string ProductName { get; set; }
   7:      public double? UnitPrice { get; set; }
   9:      public Category Category { get; set; }
  10:  }
  12:  public class Category 
  13:  {
  14:      public int CategoryId { get; set; }
  16:      public string CategoryName { get; set; }
  18:      public List<Product> Products { get; set; }
  19:  }


These classes are pure and simple POCO-classes, no specific classes to inherit from or a bunch of attributes to use.

Second step is to create our “database-class”, which inherits from BaseDatabaseInstance :

public class FakeNorthWindDatabase : BaseDatabaseInstance

In here I will register my tables by overriding RegisterTables :

protected override System.Collections.Generic.List<ITableDefinition> RegisterTables()
    return new List<ITableDefinition>() 

In here I create the table Category and Products, which are respectively using the CategoryId and ProductId-properties as identifiers. The WithIndex-option creates a relationship between these two entities.

This database also needs to be registered. I do that in the Application_Launching:

   1:  private SterlingEngine engine;
   2:  private SterlingDefaultLogger logger;
   4:  public static ISterlingDatabaseInstance DB { get; set; }
   6:  private void Application_Launching(object sender, LaunchingEventArgs e)
   7:  {
   8:      InitDB();
   9:  }
  11:  private void InitDB()
  12:  {
  13:      engine = new SterlingEngine();
  14:      engine.SterlingDatabase.RegisterSerializer<DefaultSerializer>();
  15:      engine.Activate();
  17:      DB = engine.SterlingDatabase.RegisterDatabase<FakeNorthWindDatabase>();
  18:      FakeNorthWindDatabase.Init(DB);
  19:  }


One of the things we need to do is to specify which serializer will be used for writing and reading data. At this moment we only have the DefaultSerializer and AggregateSerializer, but you could create your own classes here, as long as they inherit from BaseSerializer.

At the end I call the init-method of the FakeNorthWindDatabase, which I implemented as follows :

   1:  internal static void Init(Wintellect.Sterling.ISterlingDatabaseInstance DB)
   2:  {
   3:      if (!DB.Query<Category,int>().Any())
   4:      {
   5:          Category c1 = new Category() { CategoryId=1, CategoryName = "Beverages"};
   6:          Category c2 = new Category() { CategoryId = 2, CategoryName = "Cheese" };
   7:          Category c3 = new Category() { CategoryId = 3, CategoryName = "Seafood" };
   9:          Product p11 = new Product() { ProductId = 1, ProductName = "WestVleteren 12",
  10:              UnitPrice = 3, Category = c1 };
  11:          Product p12 = new Product() { ProductId = 2, ProductName = "Rochefort 10", 
  12:              UnitPrice = 3, Category = c1 };
  13:          Product p13 = new Product() { ProductId = 3, ProductName = "Maredsous 8", 
  14:              UnitPrice = 2.5, Category = c1 };
  16:          Product p21 = new Product() { ProductId = 4, ProductName = "Maredsous", 
  17:              UnitPrice = 3, Category = c2 };
  18:          Product p22 = new Product() { ProductId = 5, ProductName = "Gouda", 
  19:              UnitPrice = 3, Category = c2 };
  20:          Product p23 = new Product() { ProductId = 6, ProductName = "Cambozola", 
  21:              UnitPrice = 2.5, Category = c2 };
  23:          Product p31 = new Product() { ProductId = 7, ProductName = "Shrimps", 
  24:              UnitPrice = 3, Category = c3 };
  25:          Product p32 = new Product() { ProductId = 8, ProductName = "Tuna Fish", 
  26:              UnitPrice = 3, Category = c3 };
  27:          Product p33 = new Product() { ProductId = 9, ProductName = "Calamares", 
  28:              UnitPrice = 2.5, Category = c3 };
  30:          DB.Save(c1);
  31:          DB.Save(c2);
  32:          DB.Save(c3);
  34:          DB.Save(p11);
  35:          DB.Save(p12);
  36:          DB.Save(p13);
  37:          DB.Save(p21);
  38:          DB.Save(p22);
  39:          DB.Save(p23);
  40:          DB.Save(p31);
  41:          DB.Save(p32);
  42:          DB.Save(p33);
  43:      }
  44:  }

This code first checks if there is already categories present, and if not starts filling the database with some sample-data. We can now use our data: let’s simply show the name of a Category. The Sterling-database has a load-function that can be passed the key-value of the entity to find :

   1:  private void button1_Click(object sender, RoutedEventArgs e)
   2:  {
   3:      Category c= App.DB.Load<Category>(1);
   4:      MessageBox.Show(c.CategoryName);
   5:  }

Simple. Retrieving a list is also simple :

listBox1.ItemsSource = App.DB.Query<Category, int>().Select(c => c.LazyValue.Value);


The select-method uses a delegate that takes a TableKey<Category,int> and returns an object, in this case the Category-entity. By using LINQ I can get the products for a specific category :

int catId = Convert.ToInt32(NavigationContext.QueryString["cat"]);
listBox1.ItemsSource = (from p in App.DB.Query<Product, int>()
                        where p.LazyValue.Value.Category.CategoryId == catId
                       select p.LazyValue.Value).ToList();


Let’s add a new Category and product :

   1:  Category c = new Category()
   2:  {
   3:      CategoryId = 4,
   4:      CategoryName = "Condiments"
   5:  };
   7:  Product p = new Product()
   8:  {
   9:      ProductId = 10,
  10:      ProductName = "Aniseed Syrup",
  11:      UnitPrice = 12.2,
  12:      Category = c
  13:  };
  15:  App.DB.Save<Product>(p);

Saving the product also saves the category. Save checks the Id-field to know if it should do an update or an insert. Unfortunately there’s no autonumber-functionality, but Sterling also allows me to create triggers, with which I could create autonumber-functionality myself.

This blogpost just scratches the surface of Sterling, there’s lots more fun stuff you can do with it, so check it out if you need to store persistant data.