Blog

Dataverse - How to connect to SQL Server in Model Driven/Portal apps through virtual tables

If you want to build model-driven or portal apps and want to connect to data sources other than Dataverse, it's now much easier to do so with the 'Virtual Connector Provider'. This post walks through how to set up a virtual table based on a SQL Server table, and to use it in a model-driven/portal app.

Virtual tables are a fantastic feature. They provide a conduit through which we can access data from other data sources, such as SQL Server, Excel, SharePoint, and many more, through Dataverse.

One of the most significant benefits of virtual tables is that it enables us to access data from other data sources from model-driven or portable apps.

However, up until now, they have been difficult to set up. The great news is that this changes with the 'Virtual Connector Provider', which is now in preview. Compared to how we previously created virtual tables, the benefit of the 'Virtual Connector Provider' is that we can carry out this task in a no-code way.

To demonstrate this new feature, this post walks through how to create a Dataverse virtual table based on an existing SQL Server table. We can then use this to view and edit SQL data through model-driven and portal apps.

Overview of steps

The steps to set up a virtual table are as follows:

  1. Install the 'Virtual Connector Provider'
  2. Locate an existing connection to a SQL Server connection (or create a new connection)
  3. Create a 'Connection Reference' that points to the SQL connection above
  4. Use the 'Connection Reference' to setup a Virtual Connector in Dataverse
  5. Use the Entity Catalog to select the SQL tables/views that we want to expose as virtual tables
  6. Connect to the virtual tables in your apps

Installing the Virtual Connector Provider

The first step is to install the 'Virtual Connector Provider' from AppSource, by clicking the following link:


 

Locating an existing connection to SQL Server, or creating a new connection

App builders who have worked with canvas apps will most likely have existing connections that they've used in their apps. For clarity, a connection is an object that defines the data source and associated authentication credentials.

Connections appear in the Data > Connections section of the Maker Portal. If there's a connection here that we can use, we can carry on to the next step. Otherwise, it's necessary to create a new connection through this section of the portal. We require the SQL Server and authentication credentials to setup the connection.

For the purpose of this example, we'll use the SQL Server connection that's shown below.


Creating a Connection Reference

A connection reference enables Dataverse to identify a specific connection. We create connection references through a solution. We can use either an existing solution or a new solution.

From the Solution editor, we add a connection reference by clicking New > More > Connection Reference.

We specify the display name for the connection reference, we select SQL Server in the connector dropdown, and we select our SQL connection through the connection dropdown.



Creating a Virtual Table Connector in Dataverse

The next step is to create a virtual table connector in Dataverse. Unfortunately, we need to carry out this task in the old Dynamics portal, which we access by clicking the cog icon from Solution explorer, and clicking the 'advanced settings' link. From here, we navigate to the System > Administration section.


From here, we click the 'Virtual Entity Data sources' link. In the Data sources page that opens, we click New and select the Virtual Connector Data Provider.


In the new Virtual Connector page, we specify a name to identify the virtual connector (TimsSQLConnection in this example) and we select our Connection Reference from the dropdown.


Selecting the SQL Tables/Views to expose as Virtual Tables

At this stage, we can select the tables or views that we want to expose as virtual tables. We do this through an 'Entity Catalog', which we open through the Data > Tables section of the Maker portal. For this example, notice how the Virtual Connector creates an entry called 'Entity Catalog for TimsSQLConnection'.



When we open the Entity Catalog, the Data tab shows all the available tables and views from the underlying SQL Server database.



Let's say we want to expose the SQL Server 'Property' table as a virtual table. To do this, we select the 'Property' record from the list, and click 'edit record'. This opens the page below.


From this page, we select 'Yes' to configure the SQL Property table as a virtual table. There are two other important values to set - the primary key column of the SQL table, and the column that we want to define as the 'primary field'. The 'primary field' defines the default field that Power Apps displays on grids and lists in a model-driven app.

Configuring the SQL Server Virtual Table

At this point, the table will appear in the Data > Tables section of the Maker Portal. In this example, a table called 'Custom Entity [dbo].[Property]' appears in the list.


We can open this virtual table, just like any other Dataverse table. From the Data tab, we can preview the data from the SQL Server table.


Under the Forms tab, we can customise the Main form (or create new forms), which is a pre-requisite for adding record editing functionality to a model-driven app. At this stage, we can use the virtual table in all our apps.

Connecting to the SQL Server Virtual Table from a Model-Driven App

Here's a brief summary of how we can use the virtual table from a model driven app. From an existing or new model-driven app, we can add a new page and select the virtual table from a list (Custom Entity [dbo].[Property]' in this example).


At runtime, we can then view, edit, and create new records in the source SQL Server table.

Connecting to the SQL Server Virtual Table from a Portal App

As expected, we can easily connect to the virtual SQL Server table from a Portal app. The screenshot beneath shows how it's possible to add a list control to a portal page, and to use the properties pane to select the virtual table (eg, Custom Entity [dbo].[Property]')


As with any other Dataverse table, once we configure the permissions and role assignments, the data from the SQL Server table will appear at runtime, as shown below.


Conclusion

The 'Virtual Connector Provider' makes it much easier to set up virtual tables in Dataverse. One of the most compelling benefits of virtual tables is that they enable us to build model-driven and portal apps that can access data from sources other than native Dataverse tables. This post walked through the steps to set up a virtual table based on a SQL Server, and demonstrated how it's possible to access the virtual table from model-driven and portal apps.
  •   Categories: 
  • sql
Previous
Next
Related posts