Power Apps for reading and writing data to a SQL Server database in Azure

By:   |   Updated: 2021-11-09   |   Comments (3)   |   Related: > Power Apps


Free SQL Server Performance and Monitoring Report


Dear Database Professional,

Download your free copy of the MSSQLTips.com SQL Server Performance and Monitoring Report. This survey was conducted in 2022 and polled 588 database professionals about various aspects of tuning and optimizing SQL Server.

Click here to download the free report

Problem

Power Apps is a powerful citizen developer tool. It offers its own formula language with formulas allowing to read and write to data sources. In a previous tip we showed how to use the Patch function to persist data in SQL Server. But what if you wanted to take all complexity away from your app and put it on the shoulders of SQL Server?

Solution

We will combine Power Apps and Power Automate to enable writing to a SQL database without using the Patch formula.

Environment setup

There are three things you need in case you want to follow along with this tip in your environment. A database instance in Azure, and a suitable premium Power Apps and Power Automate license. Unfortunately, this setup does not work with SQL on-prem as connections over a gateway are not supported by the Power Automate action I use. However, there has been a request raised for that and I encourage you to vote on it so it gets in Microsoft’s list of things to add: click here to vote.

Database

I am using a database instance in Azure. I will create a new database called Inventory. In it, I will put a table with the following schema:

CREATE TABLE Products (
ProductName VARCHAR(255),
ProductWeight FLOAT,
Category VARCHAR(255),
LaunchDate DATE
);

With this setup I will be able to illustrate how to create a new record in the table by running a query instead of using the Patch function.

Canvas app

I will use the canvas app from the previous tip of this mini-series. I will just create a new screen and add the relevant input fields, such as Name, Weight, a Category dropdown, and a Launch Date date picker. Of course, two buttons for Save and Cancel:

app layout

Power automate flow

The final part of the setup is Power Automate flow. Begin by going to flow.microsoft.com. From My Flow, click New Flow and choose Instant cloud flow:

create new flow

Then select Power Apps as your trigger. This will take to the flow authoring canvas. Click on the plus icon and find the action "Execute an SQL query":

power apps execute sql query

Also please keep in mind this is a premium connector. In a production scenario all app users must have a per-app or per-user premium license to be able to use this connector. When you add this action click on the three dots and configure a connection to your Azure SQL instance depending on your authentication type and connection parameters:

sql connection config

For example, I use SQL Server Authentication, so I must know my SQL server name, database name, username and password. Caution: this setup is not supported via an on-prem data gateway, as mentioned. This means that if you want to execute a SQL query on a SQL Server on-prem or on Azure SQL that is behind a private endpoint, you will not be able to until Microsoft updates the connector.

Next, in the query field press Ask in PowerApps. We do this four times as we have four incoming variables:

query config

The result should look like this. Make sure to enclose the string values in single quotes and separate all parameters by a comma. The numerical value and data/time value will be parsed automatically by SQL Server:

final query config

Once this is done you can save the flow and go to the power apps studio.

Put it all together

Next, we must add the flow to the canvas app. Go to the Action tab and select Power Automate. In the pane to the right, you will see a list of the available flows with the Power Apps trigger. Note, for a flow to appear in this list it must be in the same environment as the app and must be shared with the user editing the canvas app.

It is handy to select the Save button and pick the OnSelect property prior to clicking the flow you need. In this way it will directly populate the property value.

add flow to canvas app

The flow requires four arguments: name, weight, category and date. We supply those by calling the relevant values of the inputs we have:

passing arguments to flow in power apps

Additionally, or depending on the table schema, you may wish to ensure all fields are populated. Here is an example formula how to do this. If only some fields are mandatory, then just include those. If only one field is mandatory, wrap the "not is blank" checks in an Or instead of the And.

If(
    And(
        !IsBlank(ProductName.Text),
        !IsBlank(ProductWeight.Text),
        !IsBlank(CategoriesMenu.Selected.Value),
        !IsBlank(LaunchDate_1.SelectedDate)
    ),
    Edit,
    Disabled
)

Here is how this looks:

implement check for required data

Running this with some test values will write to SQL successfully:

successful result

Enhancements

It is a good practice to provide as much useful feedback to the user as possible. In this case, they would be interested if their record made it to the database. To configure this, go back to your flow. After the execute SQL query action add the respond to power apps action. Add a single text output called statusCode. As its value pass the status generated from the body of the request of the previous action. This value though we need to provide manually as an expression. It is not available directly:

configure response output in flow

Finally click on the three dots of the Respond action and configure it to run only if the previous action has failed:

respond action configure run after

This is a prerequisite for the whole configuration; the execute query action has a different output if it succeeds.

Finally, go back to your app. As you have added an output to the flow, you must remove it and add it again to the OnSelect of the Save button so it refreshes. Wrap the call to the flow in a variable assignment with Set or UpdateContext. Then add a suitable check:

check the response status code in the app

As you can see the feedback variable is a representation of the response action. As a property we have the statuscode output we created inside the action. With this configuration users would get a success message if everything went fine or an error message if nothing was recorded.

That’s it, you can rely on SQL server from this point on!

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Hristo Hristov Hristo Hristov is a Microsoft certified data professional, specializing in Power Apps, Power BI and Python.

View all my tips


Article Last Updated: 2021-11-09

Comments For This Article




Tuesday, December 21, 2021 - 4:34:18 AM - Hristo Hristov Back To Top (89607)
To everyone who read this article: as I said in my previous comment, I was investigating how to get the execute query action potentially work with an Azure on-prem data gateway and SQL connection in logic apps, instead of using MS Power Automate directly with the gateway. Unfortunately, as of December 2021, it appears this is completely unsupported. This is the error I got when making the SQL connection in Logic Apps:

"Gateway ExecutePassThroughNativeQueryAsync - this operation (Execute Native Sql) is currently not supported using an on-prem gateway connection\r\nclientRequestId: cc0e262d-b1d0-458b-b120-2e77effa73a2".

I would encourage anyone interested in the "Execute SQL query" action either via Power Automate or Logic Apps to vote for the idea: https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Support-quot-Execute-a-SQL-query-quot-action-through-an-on/idi-p/69514#M4819

Monday, December 6, 2021 - 10:28:21 AM - Hristo Hristov Back To Top (89534)
Hi JB, thanks for your comment and great addition. I am researching how to make "execute query" work with an on-prem database. I hope to publish another tip on that soon.

Monday, December 6, 2021 - 4:53:02 AM - JB Back To Top (89533)
Thank you for this great article!

Good news for on-prem databases... it's only the “Execute a SQL query” action which is not supported for on prem.
All other actions like “Insert row”, “Delete row” etc. are working. Just make sure your table has a primary key as the “Delete row” action needs it.

Maybe you can add this info to the intro section…














get free sql tips
agree to terms