MarkMpn.Sql4Cds.Engine 9.4.0

SQL 4 CDS

By Mark Carrington, supported by Data8

SQL 4 CDS provides an engine, XrmToolBox tool, Azure Data Studio plugin and SSMS plugin for using standard SQL syntax to query data stored in Microsoft Dataverse / Dynamics 365.

It converts the provided SQL query into the corresponding FetchXML syntax and allows the associated query to be executed, including the following types of query:

• SELECT
• INSERT
• UPDATE
• DELETE

For example:

-- Get contact details
SELECT   c.firstname,
         c.lastname,
         a.telephone1
FROM     contact AS c
         INNER JOIN account AS a
         ON c.parentcustomerid = a.accountid
WHERE    c.firstname = 'Mark' AND
         a.statecode = 0
ORDER BY c.createdon DESC

-- Deactivate contacts without an email address
UPDATE contact
SET    statecode = 1, statuscode = 2
WHERE  emailaddress1 IS NULL

✅ Although you are writing SQL, you are not directly running the queries against the back-end database. All data retrieval and modification is done through the supported Dataverse API. Running an UPDATE/INSERT/DELETE command against the underlying SQL database is unsafe, but the same query in SQL 4 CDS is translated to safe & supported API requests.

The engine converts all the SQL syntax that has a direct equivalent in FetchXML. It also attempts to support some more SQL features that do not have an equivalent in FetchXML, such as calculated fields, HAVING clauses and more.

When executing a query it will take into account specific Dataverse features to improve the performance or results compared to simply executing the FetchXML directly, e.g.:

• Faster SELECT count(*) FROM entity query execution using RetrieveTotalRecordCountRequest
• Automatically retrieving multiple pages of large result sets
• Work around AggregateQueryRecordLimit errors by retrieving all the individual records and applying the aggregation in-memory.

As well as querying data with FetchXML, SQL 4 CDS can also query metadata by translating the SQL query into a RetrieveMetadataChangesRequest or RetrieveAllOptionSetsRequest:

-- Find attributes without a description
SELECT entity.logicalname,
       attribute.logicalname
FROM   metadata.entity
       INNER JOIN metadata.attribute
       ON entity.logicalname = attribute.entitylogicalname
WHERE  attribute.description IS NULL

You can also execute messages using stored procedure or table valued function syntax:

-- Execute the WhoAmI message as a stored procedure. Capture the output parameters in variables
declare @orgid uniqueidentifier, @buid uniqueidentifier, @userid uniqueidentifier
exec WhoAmI @UserId = @userid output, @BusinessUnitId = @buid output, @OrganizationId = @orgid output

-- Show the values of the variables
select @userid, @orgid, @buid

-- Execute the same message using table valued function syntax
select * from WhoAmI()

FetchXML Builder Integration

As well as writing and executing queries as SQL, the generated FetchXML can be sent to FetchXML Builder for further editing or converting to another syntax such as OData. You can also start building a query in FetchXML Builder and then edit it in SQL 4 CDS.

Library Usage

The NuGet package includes assemblies for .NET Framework 4.6.2 and later, and .NET Core 3.1 and later.

You can use it in the same way as a SQL Server connection, using Sql4CdsConnection instead of SqlConnection:

using (var con = new Sql4CdsConnection(connectionString))
using (var cmd = con.CreateCommand())
{
	cmd.CommandText = "INSERT INTO account (name) VALUES (@name)";

	var nameParam = cmd.CreateParameter();
	nameParam.Name = "@name";
	nameParam.Value =  "My New Account";
	cmd.Parameters.Add(nameParam);

	// Add the new account
	cmd.ExecuteNonQuery();

	// Get the IDs of all accounts with the same name
	cmd.CommandText = "SELECT accountid FROM account WHERE name = @name";

	using (var reader = cmd.ExecuteReader())
	{
		while (reader.Read())
		{
			var accountId = reader.GetGuid(0);
			Console.WriteLine(accountId.ToString());
		}
	}
}

The connection string should be any standard XRM connection string.

If you already have an IOrganizationService connection to the instance you want to use, you can pass that to the constructor instead of the connection string.

You can also connect to multiple instances at once and execute queries to combine or copy data between them. To use this, pass all the IOrganizationService instances to the Sql4CdsConnection constructor. You can then reference data from a specific instance using instancename.dbo.tablename.

Advanced Options

There are various properties available on the Sql4CdsConnection class that you can use to control exactly how your queries are executed:

There are also events that you can attach to to receive notifications while a query is executing. The InfoMessage and StatementCompleted events follow the pattern provided by the SqlClient classes for SQL Server, but add extra data specific to SQL 4 CDS.