Accessing Microsoft’s Cosmos DB

Microsoft’s Cosmos DB is a cloud based, scalable database service that has become quite popular since its initial launch in May 2017. I thought it might be a nice idea to check it out and – while I’m at it – see how I can connect my favorite reporting tool to it.
 

Start from the Azure portal at portal.azure.com and first of all, create a new Cosmos DB account. The following screenshot is German, however you’ll likely find your way:

Note that there are several APIs you can use for Cosmos DB. Let’s choose “SQL” as I fully trust we’d be able to connect to MongoDB or Cassandra using the corresponding data providers.
This takes a while and leaves me with enough time to change the language to English <g>:

Once the process is finished it’s time to add a collection:

Handily, Microsoft offers the download of an automatically generated .NET sample application once the collection has been created. The solution is called “To-Do” and – at the heart of it – has a model for the items contained in the collection:

public class Item
{
[JsonProperty(PropertyName = "id")]
public string Id { get; set; }

[JsonProperty(PropertyName = "name")]
public string Name { get; set; }

[JsonProperty(PropertyName = "description")]
public string Description { get; set; }

[JsonProperty(PropertyName = "isComplete")]
public bool Completed { get; set; }
}

Once the application is running, create a few new To-Dos from the UI:

The main view of the provided sample app then shows the To-Dos:

On the server side, I can then view these documents in Azure’s Data Explorer:

In order to connect to my database using “normal” SQL, the endpoint URI and keys are available from the “Keys” section in the Azure Portal – either for read-only which is sufficient in my use case or for R/W access. We’ll need the two in a minute.

Back on the client side, I create a WPF app as re-using the existing To-Do app seemed illegally simple to me – it has all the hard data access stuff wired up readily.
Luckily, Microsoft ships a NuGet package that can be referenced in order to access Cosmos DB SQL data:

Add this package to the project. Once everything is set up nicely, just add some namespaces to the “uses” clause and set up a new DocumentClient to access your data like this:

private const string endpointUrl = "…";
private const string primaryKey = “…”;
private DocumentClient client;

private void button_Click(object sender, RoutedEventArgs e)
{
client = new DocumentClient(new Uri(endpointUrl), primaryKey);
IQueryable<Item> itemQuery = this.client.CreateDocumentQuery<Item>(
UriFactory.CreateDocumentCollectionUri("ToDoList", "Items"),
"SELECT * FROM Items");
}

The rest is a piece of cake – connect List & Label to the item query and call up the Designer:

using (ListLabel LL = new ListLabel())
{
LL.DataSource = itemQuery;
LL.Design();
}

Then find the Item fields and quickly create a report using Drag & Drop:

All of this is done in less than 30 minutes, including me fiddling to set up everything – great job ;). We call stuff like this “Friday afternoon projects” internally. TGIF

Related Posts

Leave a Comment