By Mark Carrington, supported by Data8
SQL 4 CDS provides an engine and XrmToolBox tool 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
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 entity
INNER JOIN attribute
ON entity.logicalname = attribute.entitylogicalname
WHERE attribute.description IS NULL
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.