Dataverse.Sql
5.3.1.510
dotnet add package Dataverse.Sql --version 5.3.1.510
NuGet\Install-Package Dataverse.Sql -Version 5.3.1.510
<PackageReference Include="Dataverse.Sql" Version="5.3.1.510" />
paket add Dataverse.Sql --version 5.3.1.510
#r "nuget: Dataverse.Sql, 5.3.1.510"
// Install Dataverse.Sql as a Cake Addin #addin nuget:?package=Dataverse.Sql&version=5.3.1.510 // Install Dataverse.Sql as a Cake Tool #tool nuget:?package=Dataverse.Sql&version=5.3.1.510
Dataverse.Sql
This project uses the code of Mark Carrington's sole SQL 4 CDS engine which originally targets only full framework. The original engine and their tests were ported to .NET5 (Core). Some modifications were necassary since Dataverse.Sql uses the Microsoft.PowerPlatform.Dataverse.Client but the original namespace is left as is.
Dataverse.Sql wraps around the SQL 4 CDS engine and exposes the methods to retrieve data from your Dataverse Environment via SQL. It also provides the opportunity to set the engine options in a settings file (dataversesql.json
) which is delivered with the package.
This library will continuously be synchonized with changes in the SQL 4 CDS engine. Version numbers will be accordingly updated. The Revision number of the Dataverse.Sql version will hereby be taken from the Version number of the used Dataverse Client (currently using 0.5.10
which becomes .510
)
The SQL engine is able to convert 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
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, Dataverse.Sql 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
JOIN metadata.attribute ON (entity.logicalname = attribute.entitylogicalname)
WHERE
attribute.description IS NULL
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 is compatible. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
-
net5.0
- Microsoft.Dynamics.Sdk.Messages (>= 0.5.10)
- Microsoft.Extensions.Configuration.Json (>= 5.0.0)
- Microsoft.PowerPlatform.Dataverse.Client (>= 0.5.10)
- Microsoft.PowerPlatform.Dataverse.Client.Dynamics (>= 0.5.10)
- Microsoft.SqlServer.TransactSql.ScriptDom (>= 150.4897.1)
- System.Data.SqlClient (>= 4.8.3)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
5.3.1.510 | 721 | 12/3/2021 |