The purpose of this post is to demonstrate how you can use a Power Apps Canvas app to perform CRUD operations on an Azure SQL database.

To demonstrate the CRUD operation I used the Wide World Importers sample database, and specifically the Application. People table.

Create New Record

In this PowerFX code, a new record is created in the ‘Application. People’ data source using the Patch function. It takes three arguments: the name of the data source, the default values to apply to the newly created record, and the field values.
Field values for the newly created record are specified using a record literal (i.e., curly braces {}), where each key-value pair corresponds to a field in the data source.
Finally, the code sets various field values for the new record, such as IsEmployee, IsSalesperson, IsSystemUser, LastEditedBy, IsExternalLogonProvider, and IsPermittedToLogon.

Patch(
'Application.People', 
Defaults('Application.People'), // Define default values to apply if the record is new
{
FullName: TextInputeFullNameValue_NR.Text,
PreferredName: TextInputeFullNameValue_NR.Text,
PersonID: Value(Label1.Text),
LogonName: TextInputLogonNameValue_NR.Text,
PhoneNumber: TextInputPhoneNumberValue_NR.Text,
IsEmployee: true,
IsSalesperson: false,
IsSystemUser: false,
LastEditedBy: 1,
IsExternalLogonProvider: false,
IsPermittedToLogon: true
}
);
//Clean up fields
Reset(TextInputeFullNameValue_NR);
Reset(TextInputLogonNameValue_NR);
Reset(TextInputPhoneNumberValue_NR);

How it works

The video below demonstrates how the above code works.

 

Update a Record

This code uses the Patch function in Power Apps to update a record in a data source.
A record in the ‘People’ entity of the ‘Application’ data source is updated with new values for the ‘FullName’, ‘LogonName’, and ‘PhoneNumber’ columns.
The Patch function updates an existing record or creates a new record if one doesn’t already exist. In this case, it updates a record in the ‘People’ entity of the ‘Application’ data source with new values for the ‘FullName’, ‘LogonName’, and ‘PhoneNumber’ columns.
The First function chooses the first (and only) matching record from the Filter function. The Filter function finds a record in the ‘People’ entity that matches the PersonID of the currently selected item in Gallery1.

Patch(
    'Application.People', // Patch the 'People' entity in the 'Application' data source
    First( // Select the first (and only) record that matches the following condition
        Filter(
            'Application.People', // Filter the 'People' entity in the 'Application' data source
            PersonID = Gallery1.Selected.PersonID // Select the record that matches the PersonID of the currently selected item in Gallery1
        )
    ),
    {
        FullName: TextInputeFullNameValue.Text, 
        LogonName: TextInputLogonNameValue.Text, 
        PhoneNumber: TextInputPhoneNumberValue.Text 
    }
);

 

How it works

The video below demonstrates how the above code works.

 

Delete a Record

This code is a Power Apps formula that removes a record from a data source.
The UpdateContext function is used to set the variable ‘VarDialogBox’ to true. This variable is most likely controlling the visibility of a dialogue box or pop-up window on the app screen.
The RemoveIf function deletes a record from the ‘Application’ data source’s ‘People’ entity where the PersonID matches the PersonID of the currently selected item in Gallery1. The selected record is effectively deleted.
Finally, the UpdateContext function is used once more to reset the value of the ‘VarDialogBox’ variable to ‘false’. This most likely closes the dialogue box or pop-up window that was previously opened.

 // Set the value of the 'VarDialogBox' variable to 'true'
UpdateContext({VarDialogBox: true})

// Remove the record from the 'People' entity in the 'Application' data source where the PersonID matches the PersonID of the currently selected item in Gallery1
RemoveIf('Application.People', PersonID = Gallery1.Selected.PersonID); 
UpdateContext({VarDialogBox:false}); // Set the value of the 'VarDialogBox' variable to 'false' - (This setting will close the dialog box)

How it works

The video below demonstrates how the above code works.