News combit Dev Blog

Accessing Microsoft’s Cosmos DB

Posted: Friday, June 1, 2018 | 0 comment(s)
 
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!




Author: Jochen Bartlau
Head of Development List & Label

Jochen Bartlau leads the development at combit as Managing Director. He's a Microsoft .NET enthusiast driving innovation & agile project management. The mobile devices geek who used to be a physicist in his first life loves to spend his spare time with his family.


  

+1 800 256 3608 (toll-free North America only)