Integrating a Blazor Grid with Dynamics 365 Using Data Connectors
Blazor WebAssembly is a new technology for creating browser applications using .NET. Dynamics 365 is a CRM for managing different business systems. In many cases, users need reports based on Dynamics 365 data in an external application. Getting these reports requires fetching data from Dynamics 365 and displaying it in an efficient way inside a grid or report viewer.
ComponentOne provides controls that make both processes very easy to work with; the Dynamics 365 data connector is a .NET standard library that could be used to fetch data with either ADO. NET or Entity Framework Core from the Dynamics 365 server.
Blazor FlexGrid is the most versatile data grid in the market; this control can display data in an Excel-like grid with all the features users are familiar with from the VB6 days of FlexGrid.
Let's walk through the steps for creating an invoice report using data from the Dynamics 365 Sales Invoice entity that displays a country's yearly sales inside a grid.
Authentication Prerequisites for Dynamics 365
Dynamics 365 supports OAuth authentication to access the universal data service through which the CRM data is exposed. You will need to register your app with the Azure active directory to allow access to the data. Please refer to this tutorial to register your app and get OAuth credentials.
The ComponentOne Dynamics 365 data connector supports password credentials and client credentials grant type. You can refer to the details about using both of these grant types to authenticate in the documentation.
In this article, we will use the client credentials grant type to authenticate. Therefore, we need to get the following details beforehand:
- OAuth Token Endpoint: Use OAuth 2.0 token endpoint for authentication.
- OAuth Client Secret: Use the OAuth 2.0 Client Secret for authentication.
- OAuth Client ID: Use the OAuth 2.0 Client ID for authentication. The client identifier issued to the client during the registration process.
- OAuth Extend Properties: Designed to support any custom information users want to add to the connection string so that our library can obtain the access token properly.
- We also need the Dynamics 365 instance URL
Setup the Dynamics 365 Sales Invoice Project
Create an ASP. NET Core hosted Blazor WASM application using Visual Studio 2019 and name it InvoiceReport. This application will create three projects: InvoiceReport.Client, which is the Blazor WASM application and InvoiceReport.Server, which is the ASP. NET Core server app. The third is InvoiceReport.Shared app that we will use to share data contracts between the server and client.
Next, add the following NuGet packages from nuget.org to the server app:
- C1.AdoNet.D365S
Add the following NuGet packages from nuget.org to the client app:
- C1.Blazor.Grid
- C1.Blazor.Core
- C1.Blazor.Input
- C1.Blazor.List
In the client app, open index.html from wwwroot folder, and add the following resources:
Inside head tags:
<link rel="stylesheet" href="_content/C1.Blazor.Core/styles.css" />
<link rel="stylesheet" href="_content/C1.Blazor.Grid/styles.css" />
<link rel="stylesheet" href="_content/C1.Blazor.ListView/styles.css" />
<link rel="stylesheet" href="_content/C1.Blazor.Input/styles.css" />
<link rel="stylesheet" href="_content/C1.Blazor.DataPager/styles.css" />
Inside body tags:
<script src="_content/C1.Blazor.Core/scripts.js"></script>
<script src="_content/C1.Blazor.Input/scripts.js"></script>
<script src="_content/C1.Blazor.Grid/scripts.js"></script>
Fetching Data from Dynamics 365 Sales Server
Next, we will configure the server project to fetch data from the Dynamics 365 Sales server.
Create the Connection String to Microsoft Dynamics
In the server project, open the appsettings.json and add connection attributes. The information below is for a hypothetical Dynamics 365 server.
"DynamicsConnection": {
"UrlDynamics": "https://myCRM.api.crm8.dynamics.com/api/data/v9.1/",
"ClientID": "386e8e6f-7d54-4558-93a3-8757640737fa",
"CllentSecret": "de?@RbRZgsTHMpVvK3R2k80Jg:Jtj:Mh",
"TokenEnpoint": "https://login.microsoftonline.com/eaff65e3-6625-4ge4-a534-5ca7b11098b6/oauth2/token",
"Resource": "https://myCRM.crm8.dynamics.com/"
}
Add DynamicsConnection class in the server project:
public class DynamicsConnection
{
public string UrlDynamics { get; set; }
public string ClientID { get; set; }
public string CllentSecret { get; set; }
public string TokenEnpoint { get; set; }
public string Resource { get; set; }
}
Open Startup.cs class and configure DynamicsConnection as a service in the ConfigureServices method by adding the below line of code:
services.Configure<DynamicConnection>(Configuration.GetSection("DynamicsConnection"));
Add a Controller to the Server Application
Next, we need a controller in the server app. Under the Controller folder, add an empty MVC or API controller class using the Add->Controller context menu from the Controller folder. In this example, we have added an empty MVC controller class. Name it Index.
In the Index controller class, place the following attributes just above the IndexController class:
[ApiController]
[Route("[controller]")]
public partial class IndexController : Controller
{
}
Next add a constructor with IOptions DynamicsConnection parameter:
string connectionString ;
public IndexController(IOptions<DynamicConnection> config)
{
this.config = config;
var settings = this.config.Value;
string extendProperties = "{\"resource\":\"" + settings.Resource + "\"}";
string connectionString= $@"Url={settings.UrlDynamics};Use Etag=true;OAuth Client Id={settings.ClientID};OAuth Client Secret={settings.CllentSecret};OAuth Token Endpoint={settings.TokenEnpoint};OAuth Extend Properties={extendProperties};Use Cache=true;Cache Tolerance=60;Cache Location='C:\temp\c1cache3.db';Max Page Size = 100";
}
Note that we have set "Use Cache" to true and provided "Cache Tolerance" and "Cache Location" settings. Cache Tolerance is the number of seconds before the cache is refreshed; this enhances performance and reduces multiple trips to the Dynamics server. The Cache Location is a cache database file. In this example, we have used a local folder, but in a production app, you will place it in the App_Data folder.
Invoice Contract
Add an InvoiceData class to the InvoiceReport.Shared project. We will use this class to map data returned from Dynamics 365:
public class InvoiceData
{
/// <summary>
/// Total amount of invoice
/// </summary>
public decimal Totalamount { get; set; }
/// <summary>
/// Bill to postal code of invoice
/// </summary>
public string Billtopostalcode { get; set; }
/// <summary>
/// Billtocountry of invoice
/// </summary>
public string Billtocountry { get; set; }
/// <summary>
/// Bill to country of invoice
/// </summary>
public decimal Discountpercentage { get; set; }
/// <summary>
/// Delivered date of invoice
/// </summary>
public string Datedelivered { get; set; }
/// <summary>
/// Total tax of invoice
/// </summary>
public decimal Totaltax { get; set; }
/// <summary>
/// Total discount amount tax of invoice
/// </summary>
public decimal Totaldiscountamount { get; set; }
/// <summary>
/// Name of invoice
/// </summary>
public string Name { get; set; }
/// <summary>
/// Year delivered date of invoice
/// </summary>
public string Year { get; set; }
/// <summary>
/// Month delivered date of invoice
/// </summary>
public string Month { get; set; }
}
Back in the Server project's Controller class, we will fetch the data and create a list of InvoiceData to be sent to the client.
Update the IndexController constructor as follows:
public List<InvoiceData> data;
string connectionString;
public IndexController(IOptions<DynamicConnection> config)
{
this.config = config;
var settings = this.config.Value;
string extendProperties = "{\"resource\":\"" + settings.Resource + "\"}";
connectionString= $@"Url={settings.UrlDynamics};Use Etag=true;OAuth Client Id={settings.ClientID};OAuth Client Secret={settings.CllentSecret};OAuth Token Endpoint={settings.TokenEnpoint};OAuth Extend Properties={extendProperties};Use Cache=true;Cache Tolerance=60;Cache Location='C:\temp\c1cache3.db';Max Page Size = 100";
data = GetData(connectionString).ToList();
}
Here the List InvoiceData is populated by the GetData() method. This method uses the C1D365SConnection class to create a connection to Dynamics 365. Then, using an ADO. NET command object, executes a SQL query and returns a data reader object. The rest of the code massages the data and maps to the InvoiceData class.
private IEnumerable<InvoiceData> GetData(string connectionString)
{
using (C1D365SConnection con = new C1D365SConnection(connectionString))
{
//Open the connection to dynamic 365 server
con.Open();
var cmd = con.CreateCommand();
//query to select from dynamic 365 server
cmd.CommandText = "Select totalamount, billto_postalcode, billto_country, datedelivered, discountpercentage, totaltax, totaldiscountamount, name from invoices where (billto_country = 'India' or billto_country='United states' or billto_country='Japan') limit 100";
var reader = cmd.ExecuteReader();
List<InvoiceData> result = new List<InvoiceData>();
CultureInfo provider = CultureInfo.InvariantCulture;
//Generate List<InvoiceData> from reader
while (reader.Read())
{
//Put the default value if totalamount is null. Just use for this example
var totalamount = String.IsNullOrEmpty(reader[0].ToString()) ? 0 : Convert.ToDecimal(reader[0].ToString());
var billto_postalcode = reader[1].ToString();
var billto_country = reader[2].ToString();
var datedelivered = reader[3].ToString();
DateTime? deliveredDate = null;
if (!String.IsNullOrEmpty(datedelivered) && DateTime.TryParse(datedelivered, out DateTime convertedDate))
{
deliveredDate = convertedDate;
}
else
{
//Put the default value if datedelivered is null. Just use for this example
deliveredDate = new DateTime(2019, 12, 1);
}
//Put the default value if discountpercentage is null. Just use for demo
var discountpercentage = String.IsNullOrEmpty(reader[4].ToString()) ? 0 : Convert.ToDecimal(reader[4].ToString());
//Put the default value if totaltax is null. Just use for demo
var totaltax = String.IsNullOrEmpty(reader[5].ToString()) ? 0 : Convert.ToDecimal(reader[5].ToString());
//Put the default value if totaldiscountamount is null. Just use for demo
var totaldiscountamount = String.IsNullOrEmpty(reader[6].ToString()) ? 0 : Convert.ToDecimal(reader[6].ToString());
var name = reader[7].ToString();
//Add InvoiceData model from the reader
result.Add(new InvoiceData
{
Totalamount = totalamount,
Billtopostalcode = billto_postalcode,
//Put the default value if billto_country is null. And Standardize the billto_country. Just use for demo
Billtocountry = String.IsNullOrEmpty(billto_country) ? StandardizeName("United States") : StandardizeName(billto_country),
Discountpercentage = discountpercentage,
Totaltax = totaltax,
Totaldiscountamount = totaldiscountamount,
//Standardize the Name. Just use for demo
Name = StandardizeName(name),
Datedelivered = deliveredDate.Value.ToString(CultureInfo.InvariantCulture),
Year = deliveredDate.Value.Year.ToString(),
Month = deliveredDate.Value.ToString("MMMM"),
}); ;
}
return result;
}
}
/// <summary>
/// Function to Standardize string. Upper first letter of string
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
private static string StandardizeName(string input)
{
if (!String.IsNullOrEmpty(input))
{
return input.First().ToString().ToUpper() + input.Substring(1).ToLower();
}
else
{
return "";
}
}
Next, add an Index function for ActionResult that returns InvoiceData as JSON.
public ActionResult Index()
{
return Json(data);
}
This action completes the data fetching work in the server app.
Create a Report Using FlexGrid in Blazor Client
In the InvoiceReport.Client project, open the Index.razor file. Clear the contents except for @page directive. Then add the following @using statements and inject HttpClient object.
@page "/"
@inject HttpClient http
@using InvoiceReport.Shared
@using C1.Blazor.Grid
@using C1.Blazor.Input
@using C1.DataCollection
We will use the HttpClient object to get data from the server IndexController.
Declare FlexGrid with column auto generations set to false and column declarations for required columns:
<FlexGrid ItemsSource="invoices" Style="@("max-height:70vh")" AutoGenerateColumns="false">
<FlexGridColumns>
<GridColumn Header="Country" Binding="Billtocountry" />
<GridColumn Header="Year" Binding="Year" />
<GridColumn Header="Month" Binding="Month" />
<GridColumn Header="Amount" Binding="Totalamount" Format="c" Aggregate="C1.Blazor.Grid.GridAggregate.Sum" />
</FlexGridColumns>
</FlexGrid>
Now fetch the data from the server and populate a C1DataCollection inside @code:
@code {
C1.DataCollection.C1DataCollection<InvoiceData> invoices;
protected override async Task OnInitializedAsync()
{
var datacollection = await (http.GetFromJsonAsync<List<InvoiceData>>("Index"));
invoices = new C1DataCollection<InvoiceData>(datacollection);
await invoices.GroupAsync(m => m.Billtocountry);
}
}
Note that we added grouping to the C1DataCollection object on the Billtocountry property. This action helps demonstrate a country-wide yearly invoice with the sum of the amount.
Additional Considerations for Integrating Blazor Grids with Dynamics 365
This application is a simple example of getting the required data and displaying it in FlexGrid. Business applications have an ample amount of data, and we should give proper considerations to caching when refreshed. It's critical to update cache on an hourly or daily basis.
Incremental Cache is another feature that helps in this case. ComponentOne Data Connectors will support incremental cache starting 2020 v2 if the source data has fields which record the last modified timestamp.
Another aspect to consider is how much data is pushed to the browser and how data is provided to the client application; massive data can slow down the browser. ComponentOne DataCollection library has a data virtualization feature that can help in this case. The virtualization feature downloads data in chunks and keeps the application responsive; it supports server-side sorting and filtering. Here is a sample showing this feature.
