Dataverse.Sql 5.3.1.510

.NET 5.0
dotnet add package Dataverse.Sql --version 5.3.1.510
NuGet\Install-Package Dataverse.Sql -Version 5.3.1.510
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Dataverse.Sql" Version="5.3.1.510" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Dataverse.Sql --version 5.3.1.510
#r "nuget: Dataverse.Sql, 5.3.1.510"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// 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
.NET net5.0 net5.0-windows net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows net7.0 net7.0-android net7.0-ios net7.0-maccatalyst net7.0-macos net7.0-tvos net7.0-windows
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

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 482 12/3/2021