Some of you have asked about other familiar file/storage technologies for storing and accessing data collected by HomeOS, such as database systems and tools that you've been working with. So, on this page we will look at how to use Azure Mobile Services in a sample HomeOS app to store sensor data in an Azure SQL table. It's a quick and simple example of using a lightweight API to store data in a cloud database.

Mobile Services

Windows Azure Mobile Services makes it incredibly easy to connect a scalable cloud backend to your client and mobile applications. It offers many useful features for common mobile and web scenarios; however for the purpose of data access/storage, we will focus on how to connect and upload sensor data to a Azure SQL database. , again, we picked this feature in Mobile Services just to demonstrate how easy it is to integrate different cloud services in a HomeOS application.

Additional resources

AzureMobileServicesSample App

The structure of this sample app is based on the original 'Sensor' sample, which logs data collected by the z-wave multi-sensor. AzureMobileServicesSample will use the Mobile Services .Net API to store data in Azure SQL. The sample app is now in the repository of this this codeplex project, in Hub/Apps/AzureMobileServicesSample 

Here are the high level steps to get AzureMobileServicesSample working:

(Instructions with screenshots in this doc

  1. Create a new mobile service (and an Azure SQL database if one hasn't been created already). See the Create a new mobile service section in the doc. 
  2. Create a table and data columns you will be storing data in. You do this in the Azure Management Portal. See the Create Azure DB table and columns section in the doc.
  3. Create a HomeOS app with references to the Mobile Services packages to connect to your mobile service. In Azure Management Portal, go to the quick start page for the mobile service you've created, and, then follow the instructions on that page. See the Connect to a Mobile Service section in the doc.
  4. Add the mobile service calls in the app to store data to the database. See the source code in SensorDB.cs.

 

Querying and Viewing Data

Once your device is installed and the app is running, sensor data will be logged to the SQL table you set up in the cloud. There are a number of ways you can access and see the data; all of the familiar tools you've been using as well as new cloud based ones that deal with databases are at your disposal.

  • You can use the SQL Server Object Explorer tool in Visual Studio. In this tool you can use t-sql to query your data.

  • You can also run queries on the Azure Management Portal:
    • Click on the SQL DATABASES button on the right nav
    • Select your database
    • Click on the "Run Transact-SQL queries against your SQL database" link (you'll be prompted to log into the database)
    • Once logged in, you can click on New Query and start running query (be sure to select your table containing the sensor data)

  • Another great way to access and visualize data is by using some of Microsoft Excel's rich BI features which integrate with a variety of data sources including Azure SQL tables. Below screenshots show data tables and graphs created from querying an Azure SQL table by using the Power Query add-on for Excel.

For more information on how to connect to a cloud database using Power Query please see this page: https://support.office.com/en-IN/Article/Connect-to-a-Microsoft-Azure-SQL-Database-9f621fd9-5f22-4c57-b247-eb0be8b7bac4

temperature data pulled from azure sql db shown in Excel

 

luminosity data pulled from sql db displayed in Excel

 

Issues:

You may come across error messages about not having the right NuGet Packager version when you try to build the sample app:
The 'Microsoft.Bcl 1.1.9' package requires NuGet client version '2.8.1' or above, but the current NuGet version is '2.7.41115.310' The 'Microsoft.Bcl.Build 1.0.14' package requires NuGet client version '2.8.1' or above, but the current NuGet version is '2.7.41115.310'. The 'Microsoft.Net.Http 2.2.28' package requires NuGet client version '2.8.1' or above, but the current NuGet version is '2.7.41115.310'. 

To fix this problem:
• Right click on the sample app project , and select Manage NuGet packages    

• In the NuGet Packager dialog You should see a little warning thing to ask you to restore your package sources. Press Restore button there. 

 

Last edited May 7, 2015 at 7:41 PM by dannyh206, version 24

Comments

No comments yet.