MarkMpn.Sql4Cds.Engine
9.2.0
See the version list below for details.
dotnet add package MarkMpn.Sql4Cds.Engine --version 9.2.0
NuGet\Install-Package MarkMpn.Sql4Cds.Engine -Version 9.2.0
<PackageReference Include="MarkMpn.Sql4Cds.Engine" Version="9.2.0" />
paket add MarkMpn.Sql4Cds.Engine --version 9.2.0
#r "nuget: MarkMpn.Sql4Cds.Engine, 9.2.0"
// Install MarkMpn.Sql4Cds.Engine as a Cake Addin #addin nuget:?package=MarkMpn.Sql4Cds.Engine&version=9.2.0 // Install MarkMpn.Sql4Cds.Engine as a Cake Tool #tool nuget:?package=MarkMpn.Sql4Cds.Engine&version=9.2.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:
Property | Description |
---|---|
BatchSize |
When executing DML operations, how many requests should be sent to the server at once? |
BypassCustomPlugins |
When executing DML operations, should custom plugins be bypassed? |
UseLocalTimeZone |
When working with date values, this property indicates whether the local or UTC time zone should be used. |
MaxDegreeOfParallelism |
How many requests can be made in parallel? Currently used for DML and partitioned aggregate queries. |
UseTDSEndpoint |
Indicates if the preview TDS Endpoint should be used where possible to execute SELECT queries. |
BlockDeleteWithoutWhere |
Indicates if an error should be produced if running a DELETE query without a corresponding WHERE clause. |
BlockUpdateWithoutWhere |
Indicates if an error should be produced if running a UPDATE query without a corresponding WHERE clause. |
UseBulkDelete |
Set to true to use a bulk delete job instead of deleting individual records for a DELETE query. |
ReturnEntityReferenceAsGuid |
Indicates if lookup values should be returned as simple Guid values rather than the default SqlEntityReference type. |
UseRetrieveTotalRecordCount |
Indicates if a RetrieveTotalRecordCountRequest request should be used for simple COUNT(*) queries. This lets the query run faster but may produce out-of-date results. |
QuotedIdentifiers |
Indicates if " can be used to quote identifiers such as column and table names. Equivalent to SET QUOTED_IDENTIFIERS ON . |
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.
Event | Description |
---|---|
PreDelete <br />PreInsert <br />PreUpdate |
These events on the connection are raised just before an INSERT/DELETE/UPDATE command is about to be executed. The event argument includes the metadata of the entity type that will be affected along with the number of rows. The event handler can prevent the operation by setting the Cancel property of the event argument to true . Cancelling the operation will also cancel the entire batch. |
PreRetrieve |
This event on the connection is raised just before more data is about to be retrieved from the server. The event argument contains the number of rows already retrieved so far. The event handler can prevent the retrieval from continuing by setting the Cancel property of the event argument to true . Cancelling a data retrieval will not cancel the entire batch, but will cause it to operate only on partial results. |
Progress |
This event on the connection is raised when there is some update to the internal progress of executing a query, and can be used to provide feedback to the user that their query is progressing. |
InfoMessage |
This event on the connection is raised when there is some textual output from the query available. |
StatementCompleted |
This event on the command is raised when a statement within the current query has completed successfully. The event arguments show the number of records that were affected by the query as well as the details of the internal query plan that was executed for the statement. |
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net6.0 is compatible. 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. |
.NET Framework | net462 is compatible. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
-
.NETFramework 4.6.2
- Microsoft.ApplicationInsights (>= 2.21.0)
- Microsoft.CrmSdk.CoreAssemblies (>= 9.0.2.49)
- Microsoft.CrmSdk.XrmTooling.CoreAssembly (>= 9.1.1.32)
- Microsoft.SqlServer.TransactSql.ScriptDom (>= 161.8834.0)
- System.Data.SqlClient (>= 4.8.6)
- XPath2.Extensions (>= 1.1.3)
-
net6.0
- Microsoft.ApplicationInsights (>= 2.21.0)
- Microsoft.PowerPlatform.Dataverse.Client (>= 1.1.9)
- Microsoft.SqlServer.TransactSql.ScriptDom (>= 161.8834.0)
- System.Data.SqlClient (>= 4.8.6)
- XPath2.Extensions (>= 1.1.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 |
---|---|---|
9.3.0 | 96 | 8/27/2024 |
9.2.0 | 464 | 7/12/2024 |
9.1.0 | 521 | 6/10/2024 |
9.0.1 | 489 | 5/8/2024 |
9.0.0 | 118 | 5/2/2024 |
8.0.0 | 1,883 | 11/25/2023 |
7.6.1 | 1,474 | 10/16/2023 |
7.6.0 | 1,263 | 9/30/2023 |
7.5.2 | 171 | 9/17/2023 |
7.5.1 | 155 | 9/11/2023 |
7.5.0 | 177 | 9/4/2023 |
7.4.0 | 573 | 8/7/2023 |
7.3.0 | 479 | 6/12/2023 |
7.2.2 | 804 | 5/2/2023 |
7.2.1 | 166 | 4/30/2023 |
7.1.0 | 762 | 1/31/2023 |
7.0.3 | 648 | 10/11/2022 |
7.0.2 | 435 | 10/2/2022 |
7.0.1 | 433 | 9/28/2022 |
7.0.0 | 443 | 9/21/2022 |
6.4.0 | 542 | 7/28/2022 |
6.3.0 | 444 | 6/28/2022 |
6.2.1 | 447 | 5/26/2022 |
6.2.0 | 442 | 5/18/2022 |
6.1.0 | 451 | 5/11/2022 |
6.0.1 | 516 | 4/13/2022 |
6.0.0 | 488 | 4/10/2022 |
5.4.1 | 549 | 1/18/2022 |
5.4.0 | 323 | 1/4/2022 |
5.3.1 | 316 | 12/2/2021 |
5.3.0 | 316 | 11/27/2021 |
5.2.5 | 373 | 9/29/2021 |
5.2.4 | 340 | 9/16/2021 |
5.2.3 | 329 | 9/1/2021 |
5.2.2 | 334 | 8/31/2021 |
5.2.1 | 406 | 6/29/2021 |
5.1.1 | 381 | 5/15/2021 |
5.1.0 | 370 | 5/6/2021 |
5.0.0 | 381 | 4/29/2021 |
4.1.0 | 370 | 3/18/2021 |
4.0.3 | 354 | 2/20/2021 |
4.0.2 | 362 | 2/10/2021 |
4.0.1 | 368 | 1/28/2021 |
4.0.0 | 386 | 1/13/2021 |
3.1.0 | 511 | 12/13/2020 |
3.0.0 | 452 | 11/2/2020 |
2.3.0 | 459 | 8/28/2020 |
2.2.0 | 469 | 7/15/2020 |
2.1.0 | 565 | 5/24/2020 |
2.0.1 | 471 | 4/23/2020 |
2.0.0 | 513 | 4/17/2020 |
1.0.7 | 633 | 1/31/2020 |
Simplify filters that can easily be identified as tautologies or contradictions
Fixed incorrectly matching `null` values in hash joins
Fixed identification of nullable columns in outer joins
Handle `createdon` column being null in some system tables
Fixed use of metadata queries within loops
Fixed filtering and aggregation special cases with `audit` entity
Fixed incorrect type conversion errors when using specialized FetchXML condition operators
Fixed use of `CAST` and `CONVERT` with string types
Fixed paging when using semi-joins
Various fixes when querying virtual entities with unreliable providers:
* values returned as different types
* attributes using names with different case
* not honouring `top`, `offset`, `count`, `order`, `filter`
Improved error reporting:
* when using `*` instead of column name
* when passing incorrect number of parameters to aggregate functions
* when comparing lookup/optionset columns to invalid string values