Table of Contents

1. About the Fusion Scenario
2. Prerequisites
3. How-To
   • Create an Azure SQL database
   • Modify your SQL database
   • Create your minimal web API with ASP.NET Core
   • Add your model and database context classes
   • Add your HTTP request pipeline
   • Publish to Azure API Management
   • API Management and Custom Connector
   • Custom Connectors and Power Apps

Self-Service Facility Ticket System

Power App that allows employees to independently review facility service tickets and create new tickets. The Power App will interface with a Web API running in Azure, which provides controlled access to an Azure SQL database.

Situation:

• This Fusion scenario is inspired by a use case from an insurance company, but can be used in many different industries.
• A simple and scalable solution was developed to make it easier to handle facility management requests from employees.
• Facility Management, as the business stakeholder, approached the IT department for a suitable solution that they could customize and maintain themselves.

Solution:

• This solution has 2 interfaces, one for all employees (non-admin interface) that any employee can create and manage ticket requests through. One admin interface where requests can be managed individually.
• Due to the high number of ticket requests, a SQL database managed by API queries was used.
• API Management as a secure and consistent data strategy was implemented and supported with a simple and easy generation of custom connectors for Power Apps.

Prerequisites

• Power Apps environment - Get started with Power Apps canvas apps
• Azure SQL database - Getting started with single databases in Azure SQL Database
• Visual Studio .NET 6 web API - Create a minimal web API with ASP.NET Core
• API Management - About API Management

Create an Azure SQL database

Please make sure to note down your server admin login data and password

Check out our Quickstart: Create an Azure SQL Database single database for more detailed information.

Modify your SQL database

First you need to create a database table Run the following query for this:

CREATE TABLE dbo.facilityrequests
(
[id] int IDENTITY(1,1) NOT NULL,
[id_status] varchar(30) NULL,
[id_type] varchar(30) NULL,
[id_requestor] varchar(30) NULL,
[id_requestor_email] varchar(100) NULL,
[id_requestor_department] varchar(30) NULL,
[id_requestor_phone] varchar(30) NULL,
[id_assignment] varchar(100) NULL,
)

Next, you will add rows to your database, so you will have some sample content in your database. Run the following query for this:

INSERT INTO [dbo].[facilityrequests]
       ( [id_status]
       , [id_type]
       , [id_requestor]
       , [id_requestor_email]
       , [id_requestor_department]
       , [id_requestor_phone]
       , [id_assignment]
       )
VALUES
       ('OPEN'
       ,'Mail Room'
       ,'Julia Test'
       ,'[email protected]'
       ,'Dev Div'
       ,'+1000000000'
       ,'[email protected]'
       );

Create your minimal web API with ASP.NET Core

For this we are using a Visual Studio web API template. This will automatically create a base structure for our scenario. Based on the template structure, we will then build our API logic.

For the following modifications, make sure you have the following NuGet packages installed:

• Microsoft.EntityFrameworkCore.Design
• Microsoft.EntityFrameworkCore.SqlServer
• Swashbuckle.AspNetCore

Also make sure you have your ConnectionString configured with your credentials from Create an Azure SQL database

Add your model and database context classes

Find more general information about the minimal API context here - Minimal APIs overview. Or check out our Tutorial: Create a minimal web API with ASP.NET Core.

Our fusion scenario contains the following model:

class FacilityRequest
{
    [Column("id")]
    public int Id { get; set; }
    [Column("id_status")]
    public string? IdStatus { get; set; }
    [Column("id_type")]
    public string? IdType { get; set; }
    [Column("id_requestor")]
    public string? IdRequestor { get; set; }
    [Column("id_requestor_email")]
    public string? IdRequestorEmail { get; set; }
    [Column("id_requestor_department")]
    public string? IdRequestorDepartment { get; set; }
    [Column("id_requestor_phone")]
    public string? IdRequestorPhone { get; set; }
    [Column("id_assignment")]
    public string? IdAssignment { get; set; }

}

Our fusion scenario also contains the following database context class:

class FacilityRequetsDb : DbContext
{
    public FacilityRequetsDb(DbContextOptions<FacilityRequetsDb> options)
        : base(options) { }

    public DbSet<FacilityRequest> FacilityRequests => Set<FacilityRequest>();
}

Add your HTTP request pipeline

In the following, we will create our GET, POST, PUT, DELETE methods. HTTP methods allow to make particular type of calls to servers (in our case our Azure SQL database). Web APIs help to support complex operations and accessing data.

Have a look at our Program.cs file for the final implementation.

Publish to Azure API Management

As a next step, we will publish our API to Azure App Services and API Management. Why API Management - it helps meet these common challenges:

• Abstract backend architecture diversity and complexity from API consumers
• Securely expose services hosted on and outside of Azure as APIs
• Protect, accelerate, and observe APIs
• Enable API discovery and consumption by internal and external users

API Management and Custom Connector

Here, we will cover how to easily export your API to your Power Apps environment using Subscription Key. Further documentation can be found here - Export APIs from Azure API Management to the Power Platform.

Custom Connectors and Power Apps

Next, we will use our custom connector in our Power App, we have created for our use case. The custom connector will call our API. More detailed steps can be found [here.](Use a custom connector from a Power Apps app)