SqlVirtualEntityDataProvider

A Virtual Entity Data Provider using a direct SQL Connection to Azure SQL / SQL On-Prem. With contributions from Mark Carrington and Jonas Rapp. Relies on Sql4CDS from Mark Carrington.

The project includes a managed / unmanaged solution that you can import and configure for a quick start to creating SQL based Virtual Entities as well as the source code to customize to your specific requirements.

Features:

Display SQL Tables as Virtual Entities using only a connection string to your SQL instance Automatically convert FetchXml queries to SQL via Sql4Cds. Automatically convert integer key values to guids without the need for a guid column in the Azure SQL table Display Virtual Entities as lookups on OOB or custom entities or other virtual entities.

You can get started using the Sample App using the instructions below. More to follow with respect to configuring the Provider for personal / corporate use outside of the provided sample App.

1. Required: A Dynamics 365 or Power Apps Subscription
2. Required: An Azure Subscription with Rights to Create Resources
3. Optional: SQL Server Management Studio (to test your connection)

Setting Up Your Sample SQL Azure Instance

Configure a new Azure SQL Server and Database

1. Navigate to portal.azure.com and signin
2. Click Create a Resource + Databases
3. Click SQL Database or Managed Instance
4. Select your Subcription and Resource Group
5. Select Create New if you don't already have a SQL Server Instance
   1. Enter Server Name
   2. Enter Server Admin Login
   3. Enter and Confirm the Password for your Admin Account
   4. Select Okay
6. Now Click on Networking
   1. Set Connectivity Method to Public Endpoint
   2. Select Add Current Client IP Address and Allow Azure Services to Access this server
7. Click Additional Settings
   1. Next to Use existing data select Sample
8. Select the remaining defaults to create your Azure SQL Server and Database
9. Once the server and database are provisioned go to the resource and select Connection Strings from Menu
10. Copy the ADO.NET Connection String to use in the next step

Installing and Configuring the Solution

1. Download either the Unmanaged or Managed (recommended) SQL Virtual Entity Sample Solution. (NOTE: I plan to grow this over time and add more features, but it's a good starting point and I welcome feedback and contributions. A big thanks to @rappen for allowing me to use some of his code to translate FetchXML to SQL that he has included as open source in FetchXML Builder with some slight modifications.)
   • NOTE: There are 4 solutions available both the managed / unmanaged solution for the Sample App, which contains the preconfigured entities for the sample SQL database and the Provider only App that contains only the provider so you can roll your own Virtual Entities.
2. Import the solution by going to make.powerapps.com and logging into your tenant.
3. Select the environment you want to import the solution into.
4. Go to Solutions and select Import and select the solution you downloaded.
   1. After the Solution has been imported you should see a new Model Driven App under Apps called "Azure SQL Product Catalog Sample"
   2. NOTE: This app is based on the sample data that is installed with the Azure SQL database when you choose to install sample data. In the next article I'll walk through how I created this app so you can create your own using the same Data Provider.

1. The final step to configuring the app to display Products and Product Categories from Azure SQL is to configure the Provider using your specific SQL Connection String.
   • Open the Azure SQL Product Catalog Sample App (NOTE: You'll see an error when you launch the app because you haven't configured the connection)

• Open Advanced Find
  • Search for 'SQL Providers'
  • Open the record that is returned and paste the SQL Connection String from Azure SQL and Save the Record
    • IMPORTANT: You will need to replace the {your_password} in the connection string with your password.

• Click on Products or Product Categories from the Left Menu to see the Products and Categories stored in Azure SQL.
  • NOTE: If you get an error at this point it could be related to your Azure SQL Firewall and as such you may need to allow specific IPs through.