Access data in SQL Server

Add a data source

Direct access:

If you select the Start with data option when you create an app, the Items property of your gallery uses a Power Fx formula with a data source name that points directly to your database table.

For example, if you have a BOOKLENDING table, you see this formula:

Search([@'[dbo].[BOOKLENDING]'], SearchInput1.Text, author, author,book_name,category)

Views and stored procedures:

A common professional data access pattern is to use views and then stored procedures for create, update, and delete instead of allowing direct access. If you want to use views or stored procedures, change the example formula. Similarly, the form for the record doesn't use the built-in direct approach of the SubmitForm() formula.

Triggers:

One database pattern is to use triggers on tables. If a table has a trigger, you can't use the direct pattern Submit() for create, update, and delete. Submit() conflicts with SQL trigger handling and the built-in Power Apps behavior, which use the same output parameter.

You can directly access the table for queries, but to handle Create, Update, or Delete, call a stored procedure.

Use a view

A view is a saved query that shows as a single table of data.

Views appear in the list of tables you can select when you add a data source. Views only support queries—not updates. To update data, use a stored procedure.

If you create a table with the Start with data option, you get screens and formulas that show records in a gallery and form. You see formulas and functionality for creating, editing, and deleting records. But if you use a view, you only see a display screen for the gallery and form.

You might want autogenerated screens from Start with data for views.

To use this autogenerated option:

1. Choose Start with data with a basic table.
2. Delete and replace the table data source.

Example:

For example, if you have a BOOKLENDINGVIEW table and add it as a data source for Power Apps, the formula can be as simple as:

BOOKLENDINGVIEW

You can also replace other create, update, and delete formulas with a view data source and calls to stored procedures.

Use stored procedures

When you add a SQL Server connection to your app, you can add stored procedures and call them directly in Power Fx.

After you select a stored procedure, a child node appears, and you can designate the stored procedure as Safe to use for galleries and tables.

A stored procedure is safe if it doesn't perform any action that might be unwanted in certain scenarios. For example, if a stored procedure collects all accounts from a given city and then sends them an email, you might not always want emails sent every time the stored procedure is called. In that case, don't mark the stored procedure as safe.

Check a stored procedure as safe only if:

1. There are no side effects to calling this procedure on demand.

   You can call the procedure multiple times or whenever Power Apps refreshes the control. When you use it with an Items property of a gallery or table, Power Apps calls the stored procedure whenever the system determines a refresh is needed. You can't control when the stored procedure is called.

2. The stored procedure returns a modest amount of data.

   Action calls, like stored procedures, don't have a limit on the number of rows retrieved. They aren't automatically paged in 100-record increments like tabular data sources such as tables or views.

   If the stored procedure returns too much data (many thousands of records), your app might slow down or crash. For performance reasons, bring in fewer than 2,000 records.

If you check a stored procedure as safe, you can assign it as an Items property in galleries or tables in your app.

SQL namespace prepended to stored procedure name

The SQL Server namespace name, where you store the procedure, is added to the start of the stored procedure name. For example, all stored procedures in the 'DBO' SQL Server namespace have 'dbo' at the start of the name.

For example, when you add a stored procedure, you might see more than one data source in your project.

Calling a stored procedure

To use a stored procedure in Power Apps, add the connector name before the stored procedure name, such as Paruntimedb.dbonewlibrarybook.

Arguments are passed as a Power Apps record with named value pairs:

<datasourceName>.<StoredprocedureName>({<paramName1: value, paramName2: value, ... >})

Here's an example of what stored procedures could look like when assigning them to an OnSelect property.

Variables and all stored procedures

Access a stored procedure for the Items property of a gallery after you declare it safe for the UI. Reference the data source name and the name of the stored procedure followed by ResultSets. Access multiple results by referencing the set of tables returned, such as Table 1, Table 2, and so on.

For example, a stored procedure from the table Paruntimedb with the name dbo.spo_show_all_library_books() looks like this:

Paruntimedb.dbospshowalllibrarybooks().ResultSets.Table1

This query populates the gallery with records. However, stored procedures are action behaviors on the tabular model. Refresh() only works with tabular data sources and doesn't work with stored procedures. Refresh the gallery when a record is created, updated, or deleted.

Use a variable to populate and refresh the gallery

Use a variable in the OnVisible property for the screen, and set the stored procedure to the variable.

Set(SP_Books, Paruntimedb.dbospshowalllibrarybooks().ResultSets.Table1);

You can then set the Items property of the gallery to the variable name.

SP_Books

After you create, update, or delete a record with a call to the stored procedure, set the variable again to refresh the gallery.

Paruntimedb.dbonewlibrarybook({   
  book_name: DataCardValue3_2.Text, 
  author: DataCardValue1_2.Text,
    ...
});
Set(SP_Books, Paruntimedb.dbospshowalllibrarybooks().ResultSets.Table1);

Use Power Automate to call stored procedures

Power Automate handles asynchronous actions best. Call stored procedures as part of a series of calls in a business process.

To call Power Automate and then call stored procedures, create input variables as part of your flow.

Then pass your input variables into the call of your stored procedure.

Add this Power Automate flow to your app and call it. Pass optional arguments as a record “{ … }”. The following example includes all optional arguments.