SQL 4 CDS ADO.NET Provider

As well as lots of new SQL language support in SQL 4 CDS v6, I also tried to make it much easier to reuse the SQL 4 CDS engine in your own applications. The latest version of the NuGet package now includes an ADO.NET provider you can use in exactly the same way as a standard SqlClient!

Getting Started

Add the MarkMpn.Sql4Cds.Engine NuGet package to your application. Then you can connect to your Dataverse/D365 instance using the Sql4CdsConnection class:

var connectionString = $"AuthType=ClientSecret;url=https://contosotest.crm.dynamics.com;ClientId={AppId};ClientSecret={ClientSecret}";

using (var con = new Sql4CdsConnection(connectionString))
using (var cmd = con.CreateCommand())
{
  cmd.CommandText = "SELECT name FROM account";

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

If you’re connecting to only a single instance you can just pass a standard XRM connection string to the Sql4CdsConnection constructor in the same way you would use any other ADO.NET provider.

If you already have an IOrganizationService instance, or you want to do cross-instance queries, you can pass all your connections into the constructor instead:

var uatConnectionString = $"AuthType=ClientSecret;url=https://contosouat.crm.dynamics.com;ClientId={AppId};ClientSecret={ClientSecret}";
var prodConnectionString = $"AuthType=ClientSecret;url=https://contoso.crm.dynamics.com;ClientId={AppId};ClientSecret={ClientSecret}";

var uatSvc = new CrmServiceClient(uatConnectionString);
var prodSvc = new CrmServiceClient(prodConnectionString);

using (var con = new Sql4CdsConnection(uatSvc, prodSvc))
using (var cmd = con.CreateCommand())
{
  // Find accounts that exist in both UAT and PROD instances but with different names
  cmd.CommandText = @"
    SELECT uat.name, prod.name
    FROM   contosouat.dbo.account AS uat
           INNER JOIN
           contoso.dbo.account AS prod
             ON uat.accountid = prod.accountid
    WHERE  uat.name <> prod.name";

  using (var reader = cmd.ExecuteReader())
  {
    while (reader.Read())
    {
      Console.WriteLine(reader.GetString(0) + " -> " + reader.GetString(1));
    }
  }
}

Parameters

As for any database code, you should always use parameters to incorporate user-supplied data rather than generating dynamic SQL code, in order to prevent SQL injection attacks.

public void AddAccount(string connectionString, string accountName)
{
  using (var con = new Sql4CdsConnection(connectionString))
  using (var cmd = con.CreateCommand())
  {
    cmd.CommandText = "INSERT INTO account (name) VALUES (@accountName)";

    var param = cmd.CreateParameter();
    param.Name = "@accountName";
    param.Value = accountName;
    cmd.Parameters.Add(param);

    cmd.ExecuteNonQuery();
  }
}

Configuration

The Sql4CdsConnection class has several properties to control how commands get executed in more detail – please see the readme file on GitHub for more information.

Feedback & Support

I hope you find this useful in your projects! If you do hit any problems with it, please add an issue in GitHub with the details!