Introducing Brian T Jackett: PowerApps and Microsoft Flow Social Media Sample

We are happy to introduce Mr. Brian T Jackett from https://briantjackett.com. Brian is a Senior Premier Field Engineer at Microsoft specializing in SharePoint development / administration and PowerShell for over 9 years. Brian enjoys giving back to the community through giving presentations, planning and volunteering at conferences, maintaining this SharePoint/.Net centric blog, and contributing to the SharePoint Twitterverse. He also holds several Microsoft Certified Technology Specialist (MCTS) for SharePoint related technologies. He authored this blog to walk us through a scenario for gathering social media input and working with that data in various components.  This includes Azure SQL, Azure web apps, Azure API apps, and Custom APIs.  Content below provided by Brian Jackett for the PowerApps and Microsoft Flow Community:

Here is a visual diagram of the high level process we will follow.

• Flow – Capture tweets on Twitter with a specific hashtag and insert into Azure SQL DB
• Azure Web App – Entity Framework model used to display Azure SQL data
• Azure API App – Same Entity Frame model used to scaffold API Controller, add “Swashbuckle” NuGet package to add Swagger support
• Custom API – Generate PowerApps Custom API from Azure API App
• PowerApps – App displays data from Custom API

All source code for this project is hosted on GitHub at for your reference at https://github.com/BrianTJackett/BTJ.PowerApps.AzureDBSample.  Please note this is only for demonstration purposes and is not intended for production use.  Code and related content is provided as-is with no warranty of any kind.  For additional information and a more guided walkthrough on the steps of creating a Custom API and the supporting Azure API app please see this blog post from Carlos Aguilar and a supporting video that he created.

Step 0 - Create Azure SQL Database (pre-requisite)

The first step in our process will be capturing input from social media data connectors and inserting records into an Azure SQL database.  Since we will need to have the database in order to insert records we will create the database first.  We will be using Visual Studio (2015 in this example) with the latest Azure SDK (2.9.5 at the time of writing) but this Azure SQL Database and table schema could be deployed in multiple other ways (see here for more information on creating Azure SQL databases).  Our database is going to capture simple information such as the social media data source, text from the tweet, and user who posted the tweet.  Here is a view of the database schema.

ID	int
Filename	nvarchar(150)
Created	nvarchar(100)
CreatedBy	nvarchar(100)

 

We will not go through the process of  deploying the database but an Azure Resource Group project that deploys a SQL Server instance is included in the reference source code.

Step 1 - Flow

Now that we have a database to store data we can create a flow to populate the database based on social media triggers.  In our example we’ll use Twitter but similar data connectors such as Instagram, Facebook, etc. could be used.  This flow will consist of a trigger for Twitter to fire on any tweets with a specific hashtag.  In this example the hashtag is #BTJPADemo.  After the Twitter trigger add an action for Insert Row to Azure SQL.  Specify the database table previously created and fill in columns with metadata from Twitter such as “tweet text”, “created at” (date timestamp as a string), and “tweeted by” for the Twitter username.  See following screenshot for information.

 

Step 2 - Azure Web App

In order to better work with the database we will create a domain model.  In the sample code an object-relational mapper (ORM) called Entity Framework (EF) was used to add an ADO.Net Entity Data Model using the “EF Designer from Database” option.  Entity Framework can be added to a .Net project via NuGet (more info on how to get Entity Framework).

 

Now that we have an entity data model we can create a scaffolded item for MVC 5 Controller using Entity Framework.

In order to view the new scaffolded controller you can include a link on the home view.  Inside of the Views –> Home folder you can modify the Index.cshtml file.  Insert the following after the ‘<div class="”Jumbotron”">…</div>’ section.

<div>     @Html.ActionLink("Link to new controller", "Index", "InstagramFiles") </div>

After building out the web app you can publish it to Azure.  The documentation on this article walks through the process (same will apply for the Azure API App in Step 3): https://docs.microsoft.com/en-us/azure/app-service-web/web-sites-dotnet-get-started.

Step 3 - Azure API App

The Azure API App will use the same entity data model as the Azure Web App.  This time though instead of creating an MVC controller we will create a Web API 2 Controller using Entity Framework.

 

The last step for the Azure API App will be to enable SwaggerUI so that we can test the API in a browser.  Open the SwaggerConfig.cs file (usually under App_Start folder) and uncomment the line containing “.EnableSwaggerUi(c =>” along with the line preceding and following it.  See the following screenshot for more details.

Step 4 – Custom API

Creating a custom API is covered in good detail on the PowerApps blog on the Register Custom APIs in PowerApps tutorial.  For the purposes of generating the Swagger JSON document from our Azure API App we will need to navigate to the location that the API is hosted and append “/swagger”.  This will take us to the SwaggerUI page that we enabled in the SwaggerConfig.cs file.  At the top of the page will be a box with the location of our hosted API appended with “/swagger/docs/v1”.  Copy this entire URI and paste it into a browser tab.  Depending on the browser and your settings you will either be prompted to download the file or presented with the contents of the file in the browser window.  Save off this JSON document to then use it for registration in the PowerApps Custom API.

Step 5 – PowerApps

After the Custom API is created it is now possible to consume that API within a PowerApps app.  Start by adding a data source that is the Custom API we just created.

Then insert a vertical text gallery and bind the Items property to the “get all items” method from our Azure API App.  In our case   We can modify the field bindings to line up with the columns from database table that are most interesting.

 

See the video below for walkthrough of Step 5.

Video demonstration

See the above components in action in the following video:

Conclusion

This post showed a quick walk through of connecting together multiple services and components between Azure SQL, Azure Web Apps, PowerApps, Flow, Twitter, and more for a social media demo.  Hopefully you’ll take this as an opportunity to explore new areas of development and integration.  Please share back any feedback you have.