Access data in SQL Server

Add a data source

Direct access:

If you choose the Start with data option when creating an app, the Items property of your gallery has 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 the following 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 rather than allow direct access. If you want to use views or stored procedures, you must change the example formula. Similarly, the form for the record doesn't use the built-in direct approach of the SubmitForm() formula either.

Triggers:

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

You can, however, directly access the table for query purposes, but to handle Create, Update, or Delete you must call a stored procedure.

[!NOTE]

The SQL Server connector, like all of the connectors that work with relational data, assumes that tables have a primary key. A primary key is critical for finding specific records to update. If a SQL Server table doesn't have a primary key then the data will be treated as read-only. If you have access and edit rights to the SQL Server table, consider adding an auto-generated key.

Use a view

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

Views show up in the list of tables you can select when you add a data source. Views only support queries—not updates. You must use a stored procedure for updates.

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

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

For 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 had a BOOKLENDINGVIEW table and added it as a data source for Power Apps, the formula could be as simple as:

BOOKLENDINGVIEW

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

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.

Note

This feature also works with secure, implicit connections.

Once 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 has no action it performs that might be unwanted in certain scenarios. For example, if a stored procedure collected all accounts from a given city, then sent them an email. You might not always want emails to be sent every time the stored procedure is called. Therefore, the stored procedure shouldn't be marked as safe.

Check a stored procedure as safe only if:

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

   You should be able to call the procedure multiple times or whenever Power Apps refreshes the control. When used 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. You return a modest amount of data in the stored procedure.

   Action calls, such as 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), then your app might slow down or crash. For performance reasons, bring in less than 2,000 records.

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

Important

The schema of the return values of the stored procedure should be static, so the values don't change from call to call. For example, if a stored procedure returns two tables, then it always returns two tables. You can work with either typed or untyped results.

The structure of the results also need to be static. For example, if the schema of the results are dynamic, then results are untyped and you must provide a type in order to use them in Power Apps. For more information, see Untyped results.

SQL namespace prepended to stored procedure name

The SQL Server namespace name, where you store the procedure, is prepended to 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, prefix the stored procedure name with the name of connector associated with it followed by the stored procedure name, such as Paruntimedb.dbonewlibrarybook.

Note

When Power Apps brings in the stored procedure, it concatenates the namespace and procedure name so that dbo.newlibrarybook becomes dbonewlibrarybook.

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

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

Tip

Remember to convert values if needed as you pass them into your stored procedure, since you're reading from a text value in Power Apps. For example, if you're updating an integer in SQL you must convert the text in the field using Value().

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

Variables and all stored procedures

You can 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. You can access multiple results by referencing the set of tables returned such as Table 1, Table 2, etc.

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

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 can't be used with stored procedures. You must refresh the gallery when a record is created, updated, or deleted.

Note

When you use a Submit() on a form for a tabular data source, it effectively calls Refresh() under the hood and updates the gallery.

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 update 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. You can call stored procedures as part of a series of calls in a business process.

To call Power Automate followed by a call to 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. The optional arguments are passed as a record “{ … }”. The following example has all optional arguments.