If you’ve been facing the pain of using CRM SDK or its Web API to fetch data, this post is for you. Normally, each time I want to fetch some data out of CRM quickly, I could just use FetchXML Builder within XRMToolBox. With this post, I’ll tell you why having the ability to fetch data using raw SQL queries against Dataverse endpoint is so important and how it helps make our life a lot easier.
If you’re reading through Microsoft documentation, you can see that the doc only tells how to query using SQL Server Management Studio (SSMs) and this feature is till in preview. I’ve tested it and it worked perfectly fine. However, we won’t be using SSMS to execute our queries, we’ll do this from code. This is super important to separate our commands and queries against our CRM instance and allows us to achieve CQRS much more easier.
Let’s us first walk through the beauty of fetching data via SSMS.
Querying Dataverse using SSMS
You maybe aware that Dataverse is backed by Azure SQL data store and in order to authenticate against it, we need to use our Azure AD account to sign in. When you install SSMS, it doesn’t come with an ADAL.dll that allows Azure AD authentication so you need to download and and install it your own. Navigate to this link and download the Microsoft Active Directory Authentication Library for Microsoft SQL Server library.
Once it’s installed, fire up SSMS and follow this screen (notice you will only see Azure Active Directory – Password authentication when you have installed the library above):
If you can’t log in due to port 5558 not being opened, just append the port to to server name: your_crm_instance.crm6.dynamics.com;5558
Once successfully signed in, you will then query data just like you normally do with any SQL Server databases. Note the database name is your CRM data store name. Byte byte Fetch Xml!
Query Dataverse from C#
Now the fun part, I’ve been searching for ways to separate my applications’ commands and queries against CRM as neat as possible. With this ability, we can write code that can leverage CQRS for better maintainability. Typically a CQRS based structure would be as follow:
D:.
├───DeviceReconciliation.API
│ │ appsettings.Development.json
│ │ appsettings.json
│ │ appsettings.Staging.json
│ │ DeviceReconciliation.API.csproj
│ │ DeviceReconciliation.API.csproj.user
│ │ Dockerfile
│ │ Program.cs
│ │ Startup.cs
│ │
│ ├───Controllers
│ │ ApiControllerBase.cs
│ │ ContractsController.cs
│ │ DevicesController.cs
│ │
│ └───Properties
│ launchSettings.json
│
├───DeviceReconciliation.Application
│ │ DependencyInjection.cs
│ │ DeviceReconciliation.Application.csproj
│ │ SqlConnectionFactory.cs
│ │
│ ├───Commands
│ │ MarkCustomerChangesOnHoldCommand.cs
│ │ UpdateDeviceAssetTagCommand.cs
│ │
│ ├───Common
│ │ ├───Constants
│ │ │ DbConnectionConstants.cs
│ │ │
│ │ └───Interfaces
│ │ INCentralRepository.cs
│ │
│ └───Queries
│ DeviceHistoryVm.cs
│ DeviceReconcileQueries.cs
│ IDeviceReconcileQueries.cs
│ NCentralDeviceVm.cs
│ ServiceContractVm.cs
│ ServiceItemVm.cs
│
├───DeviceReconciliation.Domain
├───DeviceReconciliation.Infrastructure
│ │ DependencyInjection.cs
│ │ DeviceReconciliation.Infrastructure.csproj
│ │
│ ├───Connected Services
│ │ └───NCentralService
│ │ ConnectedService.json
│ │ Reference.cs
│ │
│ └───Repositories
│ NCentralRepository.cs
│
└───DeviceReconciliation.UnitTests
│ appsettings.Development.json
│ DeviceReconcileQueriesTests.cs
│ DeviceReconciliation.UnitTests.csproj
│ NCentralRepositoryTests.cs
│
└───Setup
TestFixture.cs
TestHelper.cs
TestHostingEnvironment.csIf you pay attention to the the DeviceReconciliation.Application layer, you would see that we have separated our commands and queries into their own folders. We use CRM SDK or its Web API to perform commands while fetching CRM data using raw SQL queries.
How can this work in code?
Back in the old days we use System.Data.SqlClient to execute our SQL queries but this doesn’t work with Dataverse as it requires Azure AD authentication and an extra library I mentioned previously (although you can if execute your queries from your local machine where the new ADAL.ll is installed). If you are curious to realize that some applications now need to run in Docker containers, we can’t just install ADAL.dll like we did with SSMS for the Linux based. For this reason, we need to switch to a different C# namespace – Microsoft.Data.SqlClient. It’s been out there for quite a while now, I use it because it allows me to embed a bearer token for Azure AD authentication as it comes with OAuth 2.0 grant flows support. If you want to find out how to obtain a token for your CRM instance, please refer to my blog on how to do this.
For different types of authentication support, you can find more here.
Here is how it can be done from code:
using Microsoft.Data.SqlClient;
public class SqlConnectFactory
{
private readonly IConfiguration _configuration;
public SqlConnectFactory(IConfiguration configuration)
{
_configuration = configuration;
}
public SqlConnection GetOpenConnection(string system, string connectionString)
{
var _retryPolicy = Policy
.Handle<Exception>()
.WaitAndRetry(
10,
retryAttempt => TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)),
(exception, timeSpan) =>
{
Log.Error("Could not establish SQL Server connection. Retrying now. {@Error}", exception);
Log.Debug($"Debug: Trying to establish SQL Server connection in {timeSpan.TotalSeconds} seconds.");
}
);
return _retryPolicy.Execute(() =>
{
var connection = new SqlConnection(connectionString);
if (system == DatabaseSystem.CRM)
connection.AccessToken = AcquireTokenAsync();
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
return connection;
}
return connection;
});
}
private string AcquireTokenAsync()
{
var _context = new AuthenticationContext(_configuration["AdTenant:Instance"] + "/" + _configuration["AdTenant:TenantId"]);
var token = _context.AcquireTokenAsync(_configuration["CrmInstance:InstanceUrl"],
new ClientCredential(_configuration["CrmServiceAccount:ClientId"], _configuration["CrmServiceAccount:ClientSecret"])).Result.AccessToken;
return token;
}
}Once you get back a SqlConnection instance, you’ll then execute your SQL queries in the same way you did with any other SQL databases.
public Result<Guid> GetContactId(string navContactNo)
{
string query = $@"SELECT [contactid]
FROM [dbo].[contact]
WHERE [dict_foreignid] = @No";
using (var con = _sqlConnectFactory.GetOpenConnection(DatabaseSystem.CRM, _dataverseConnection))
{
try
{
var result = con.Query<Guid>(query, new { No = navContactNo }).FirstOrDefault();
if (result != Guid.Empty)
{
return Result.Success(result);
}
return Result.Failure<Guid>($"CRM Contact Id Not Found: {navContactNo}.");
}
catch (Exception ex)
{
Log.Error("Failed to get Contact Id by BC Contact No {NavContactNo}. {@Error}", navContactNo, ex);
return Result.Failure<Guid>($"Failed to get Contact Id by BC Contact No {navContactNo}. Error: {ex.Message}");
}
}
}