When it comes to interfacing with another system, the possibilities are numerous and depend on many characteristics of the system, but also on performance, integration model, data, security, functional objectives, etc.
We won’t go into detail about all this, but we will focus on a brand-new feature, in fact we prefer to talk about an improvement of a feature that has existed for a while: “Custom” Virtual Entities Tables.
Introduction
I have to admit that I have very rarely used Virtual Tables in real life for the simple reason that they never really interested me but also because on paper they seemed relatively limited.
You probably haven’t heard of these Virtual Tables or very little about them because this feature hasn’t really been improved in a while!
But recently we have seen an update that allows us to extend these Virtual Tables which leads me to talk about it today.
To quickly define what a Virtual Table is, it is a Custom Table configured in the same way as we normally do, but it will consume an external data source (Data Source Provider) and not the Dataverse database. Therefore, this table does not have the fields that were created after its creation such as CreatedOn, ModifiedBy etc.
The aim is to be able to read data from third party systems directly into the Dataverse database without having to replicate the data with scenarios and integration components to implement. On the user side, the experience remains the same and is completely transparent.
Note that there are still some limitations to their use: https://docs.microsoft.com/en-us/powerapps/developer/data-platform/virtual-entities/get-started-ve#limitations-of-virtual-entities
I won’t explain in detail what this feature is for, as others have already done so well. If you would like more information on this topic, please see the following blog posts:
- https://carinaclaesson.com/2020/01/19/virtual-entities-a-forgotten-feature-which-is-still-alive/
- https://www.itaintboring.com/dynamics-crm/virtual-entities-revisited-setting-up-a-virtual-entity/
As I said, an update has recently been released that allows you to go much further and not only read the information but also create, update and delete!
A documentation has appeared explaining this new feature (Sample: Custom virtual table provider with CRUD operations) with a rather simple example, and we could also notice that it is part of the latest release of the Plugin Registration Tool !
Use case scenario
Let’s say you work for a real estate company that has a historical third-party tool based on a SQL server hosted on Azure that allows it to reference all available real estate listings.
You are implementing a new system that will not replace it, but you still need to access that data and allow users to control the lifecycle of that data, i.e. create new entries, modify them, etc.
You can indeed imagine doing integration and synchronising records, but you will understand that it is about setting up integration components and managing monitoring, error, reprocessing but also conflict scenarios as you are in a scenario where two systems can modify the same data!
So we’re going to implement this case using this new functionality that will allow us to enter directly into this SQL database from our Dataverse!
Azure SQL Server
First, we will initialize an Azure SQL Server with a database that we will call: “realestate” and then we will create a table “Accomodation” to store the different offers available.
Here is the script to create this table:
CREATE TABLE [dbo].[accommodation] (
[AccommodationId] UNIQUEIDENTIFIER NOT NULL,
[AccommodationType] INT NOT NULL,
[City] VARCHAR (100) NOT NULL,
[Price] DECIMAL (10, 2) NOT NULL,
[Surface] DECIMAL (5, 2) NOT NULL,
[Category] INT NOT NULL,
[Name] NVARCHAR (50) NULL
);
We’re adding a few things to make sure our scenario is more real 😉
INSERT INTO [dbo].[accommodation] ([AccommodationId], [AccommodationType], [City], [Price], [Surface], [Category], [Name]) VALUES (default, 1, 'London',1565000.00,150.90, 2, 'London Loft Update CRM')
INSERT INTO [dbo].[accommodation] ([AccommodationId], [AccommodationType], [City], [Price], [Surface], [Category], [Name]) VALUES (default, 1, 'Paris', 99999.00 ,99.00, 1, 'Update From CRM')
INSERT INTO [dbo].[accommodation] ([AccommodationId], [AccommodationType], [City], [Price], [Surface], [Category], [Name]) VALUES (default, 1, 'Paris', 165000.00,19.50, 2, 'Paris Appartment')
In our case, columns of type INT will be used to populate the OptionSet Choice in Dataverse and the decimals will be interpreted as decimals and not as money columns (for simplicity, but we could have handled this case very well). Note that the data consistency between this definition and the one we will create in Dataverse will have to match each other. For example, for all Choices, we must have these values available but also respect the precision of the decimal fields!
Dateverse Plug-ins Developments
All we have to do is create the different plug-ins that will run for the different events of our new data source! We’re going to simplify as much as possible and not use a plug-in base without even adding constraints with regard to the execution context, we’re really setting up a PoC and we’re allowing ourselves to abstract from certain practices ;).
We will first create our method allowing us to connect to the SQL database :
public static class Connection
{
public static SqlConnection GetConnection()
{
try
{
//sample database to connect to
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "url.database.windows.net";
builder.UserID = "login";
builder.Password = "password";
builder.InitialCatalog = "catalogname";
SqlConnection connection = new SqlConnection(builder.ConnectionString);
return connection;
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
throw;
}
}
}
We now create the different plug-ins, taking care to respect the mapping of the fields we will create on the Dataverse side. You will notice here that we will create a Virtual Table called “Accommodation“.
public class RetrieveMultiplePlugin : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
IPluginExecutionContext context = (IPluginExecutionContext)
serviceProvider.GetService(typeof(IPluginExecutionContext));
EntityCollection collection = new EntityCollection();
string cmdString = "SELECT AccommodationId,AccommodationType,City,Price,Surface,Category,Name FROM [dbo].[accommodation]";
SqlConnection connection = Connection.GetConnection();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = cmdString;
connection.Open();
try
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Entity e = new Entity("adc_accommodation");
e.Attributes.Add("adc_accommodationid", reader.GetGuid(0));
e.Attributes.Add("adc_accommodationtypecode", new OptionSetValue(reader.GetInt32(1)));
e.Attributes.Add("adc_city", reader.GetString(2));
e.Attributes.Add("adc_price", reader.GetDecimal(3));
e.Attributes.Add("adc_surface", reader.GetDecimal(4));
e.Attributes.Add("adc_categorycode", new OptionSetValue(reader.GetInt32(5)));
e.Attributes.Add("adc_name", reader.GetString(6));
collection.Entities.Add(e);
}
}
}
finally
{
connection.Close();
}
context.OutputParameters["BusinessEntityCollection"] = collection;
}
}
}
public class RetrievePlugin : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
var context = serviceProvider.Get<IPluginExecutionContext>();
if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is EntityReference entityRef)
{
Entity e = new Entity("adc_accommodation");
string cmdString = "SELECT AccommodationId,AccommodationType,City,Price,Surface,Category,Name FROM [dbo].[accommodation] WHERE AccommodationId=@AccommodationId";
SqlConnection connection = Connection.GetConnection();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = cmdString;
command.Parameters.AddWithValue("@AccommodationId", entityRef.Id);
connection.Open();
try
{
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
e.Attributes.Add("adc_accommodationid", reader.GetGuid(0));
e.Attributes.Add("adc_accommodationtypecode", new OptionSetValue(reader.GetInt32(1)));
e.Attributes.Add("adc_city", reader.GetString(2));
e.Attributes.Add("adc_price", reader.GetDecimal(3));
e.Attributes.Add("adc_surface", reader.GetDecimal(4));
e.Attributes.Add("adc_categorycode", new OptionSetValue(reader.GetInt32(5)));
e.Attributes.Add("adc_name", reader.GetString(6));
}
}
}
finally
{
connection.Close();
}
// other codes.
}
context.OutputParameters["BusinessEntity"] = e;
}
}
}
public class DeletePlugin : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
var context = serviceProvider.Get<IPluginExecutionContext>();
//comment
if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is EntityReference entityRef)
{
Guid id = entityRef.Id;
//change the table name below to the source table name you have created
string cmdString = "DELETE [dbo].[accommodation] WHERE AccommodationId=@AccommodationId";
SqlConnection connection = Connection.GetConnection();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = cmdString; command.Parameters.AddWithValue("@AccommodationId", id);
connection.Open();
try
{
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
// other codes.
}
}
}
}
public class UpdatePlugin : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
var context = serviceProvider.Get<IPluginExecutionContext>();
if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity entity)
{
string cmdString = "UPDATE accommodation SET {0} WHERE AccommodationId=@AccommodationId";
SqlConnection connection = Connection.GetConnection();
using (SqlCommand command = connection.CreateCommand())
{
command.Parameters.AddWithValue("@AccommodationId", entity["adc_accommodationid"]);
List<string> setList = new List<string>();
if (entity.Attributes.Contains("adc_name"))
{
command.Parameters.AddWithValue("@Name", entity["adc_name"]);
setList.Add("Name=@Name");
}
if (entity.Attributes.Contains("adc_accommodationtypecode"))
{
command.Parameters.AddWithValue("@AccommodationType", entity.GetAttributeValue<OptionSetValue>("adc_accommodationtypecode")?.Value);
setList.Add("AccommodationType=@AccommodationType");
}
if (entity.Attributes.Contains("adc_city"))
{
command.Parameters.AddWithValue("@City", entity["adc_city"]);
setList.Add("City=@City");
}
if (entity.Attributes.Contains("adc_price"))
{
command.Parameters.AddWithValue("@Price", entity["adc_price"]);
setList.Add("Price=@Price");
}
if (entity.Attributes.Contains("adc_surface"))
{
command.Parameters.AddWithValue("@Surface", entity["adc_surface"]);
setList.Add("Surface=@Surface");
}
if (entity.Attributes.Contains("adc_categorycode"))
{
command.Parameters.AddWithValue("@Category", entity.GetAttributeValue<OptionSetValue>("adc_categorycode")?.Value);
setList.Add("Category=@Category");
}
command.CommandText = string.Format(cmdString, string.Join(",", setList)); connection.Open();
try
{
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
// other codes.
}
}
}
}
public class CreatePlugin : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
var context = serviceProvider.Get<IPluginExecutionContext>();
if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity entity)
{
Guid id = Guid.NewGuid();
//change the table name below to the source table name you have created
string cmdString = "INSERT INTO [dbo].[accommodation] (AccommodationId,AccommodationType,City,Price,Surface,Category,Name)" +
" VALUES (@AccommodationId,@AccommodationType,@City,@Price,@Surface,@Category,@Name)";
SqlConnection connection = Connection.GetConnection();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = cmdString;
command.Parameters.AddWithValue("@AccommodationId", id);
command.Parameters.AddWithValue("@AccommodationType", entity.GetAttributeValue<OptionSetValue>("adc_accommodationtypecode")?.Value);
command.Parameters.AddWithValue("@City", entity["adc_city"]);
command.Parameters.AddWithValue("@Price", (decimal)entity["adc_price"]);
command.Parameters.AddWithValue("@Surface", (decimal)entity["adc_surface"]);
command.Parameters.AddWithValue("@Category", entity.GetAttributeValue<OptionSetValue>("adc_categorycode")?.Value);
command.Parameters.AddWithValue("@Name", entity["adc_name"]);
connection.Open();
try
{
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
// other codes.
}
context.OutputParameters["id"] = id;
}
}
}
Creating Data Provider
When everything is finished, we can then register our assembly using the tooling and then start registering our new data provider.
To do this we need to choose a solution (I recommend you create it first) then enter the name of the entity you are going to use (in this case “adc_Accommodation”) and finally define the assembly used and the different plug-ins that will be used for the different events.
Note that on the next screen it says “Update” because I already created this Data Provider and forgot to take a screen, but the steps are the same :).
Once this is done, we can start creating our Virtual Table and to do this we must first create a new Virtual Entity Data Source in the Administration section (Settings > Administration > Virtual Entity Data Source in the classic explorer) by selecting the Data Provider we just created:
For the name you can simply write “Accommodation Source” 🙂 .
Create Virtual Table Definition
All we have to do is create our table that uses this data source and create all the fields we are interested in!
As you can see, we make sure to check the “Virtual Entity” box and choose the data source we just created “Accommodation Source”, we can also enter the name of the external table and its plural name in case it is not identical to what we implement in Dataverse! Be sure to respect the name of the entity you use in your plug-in 🙂
You will notice that the property cannot be only of type organisation for obvious reasons 🙂
All that remains is to create the different columns on this entity to reflect the data and make them available on the form!
Testing Part
Once the Virtual Table has been added to the sitemap, we can then access the SQL server information and perform the different operations required 🙂
I hope you have enjoyed this article and this PoC and that you have understood the usefulness of these Custom Virtual Tables!
Note that there are many limitations to consider and that this scenario is not necessarily to be used when we encounter an integration problem. Some shortcuts were taken in this demo such as authenticating to SQL Server with hard-coded credentials (you can use Azure Key Vault for this 🙂 ) or on how to code plug-ins and should not necessarily be implemented this way in production!
Leave a Reply