Getting Started with Power Apps and Azure SQL

In this video, the presenter provides a great introduction to SQL for PowerApps and also takes us step by step through the process of developing PowerApps with Azure SQL database repositories.

At 1:02, the presenter points to an organization’s Azure portal, where you will find a lot of services that can be spinned up and run. He adds that today’ presentation will cover the idea of creating and spinning up SQL server very quickly in Microsoft Azure.

Here, choose the ‘SQL database’ option from the list of services, add the basics and networking information related to the database. At 3:02, he briefs that we can choose the ‘All resources’ option to view the available database resources.

He adds that we should enable the option to allow the Azure services and resources to access the server. In the Azure portal, under the ‘Home’ tab, we can find details like resource group, subscription details, server admin, server name and also the different settings that have been conferred for the database and the resources.

Setting up the SQL tables

At 14:04, the presenter shows how we could create different tables in the SQL database. For this example, two tables are created, one is to maintain the stock information while the other is used to maintain the orders information. To begin with, we need to define the data type of the different columns that are going to be part of the tables and whether the columns can allow null values or not.

Once the table structures are defined, you can create some dummy records for both the tables.

At 15:38, he adds that we just need to provide the app name, format to initiate the creation process of a Power App. Now, within the power app, choose the ‘Connectors’ options. Now you will see the list of connectors like Office 365 outlook, onedrive, sharepoint, sql server and twitter.

Here, we will choose ‘SQL server’ and then choose the ‘Add a connection option’. Then, we need to choose the authentication type.

The different options for authentication type includes Azure AD integrated, SQL Server Authentication and Windows Authentication. Then choose the ‘Connect directly’ option and enter the details like SQL server name, SQL database name, username and password and finally hit the ‘Connect’ button.

Establishing SQL connection from the PowerApp

At 20:02, the presenter briefs that the SQL connections should be available on the left pane within the PowerApps. One of the SQL connections is to ‘order’ while the other is to ‘stock’.

You can choose to insert a blank gallery and select any of the SQL connections, say ‘stock’ connection and probably include some UI labels. The main issue here is within the library you will notice the label and only the ‘stock id’ will be present. Information about the stock ID will not be displayed here as we are connecting to the SQL database directly.

In order to avoid this, the best practice here would be to create a stored procedure on the database server that will join the ‘Stock’ and ‘Order’ table together, thereby producing an output which will eventually be read into the Power App.

At 25:00, the presenter adds that in the power app, we cannot directly connect to the stored procedures. Connecting to the store’s procedure is really the key to using SQL with PowerApps and to get useful information out of SQL. This is where ‘Power Automate’ helps us. Power Automate flow is triggered through the Power App application. You can select the ‘add step and action’ option to choose the stored procedure that you would like to connect to and add details like a sample JSON payload.

At 28:37, the presenter adds that within the Power App, galleries can be inserted by choosing the insert option which is present at the top pane. By default, a blank gallery that is not connected to any data will be inserted. Now we need to select the ‘Actions’ menu and choose the ‘Power automation’ icon.

Now you should be able to view the different flows that have been created related to this application. Once the flow is selected, you can insert labels inside the gallery. Once all your desired customization of the gallery is done, you can hit the ‘Run’ button. Then, you should be able to see the data from the Azure SQL within the application that you had created with the help of PowerApps.

Thus in this video, the presenter clearly walk through the different steps that needs to be followed by beginners while they work on developing applications with the help of PowerApps and Azure SQL.