ADO.NET Provider - MarkMpn/Sql4Cds GitHub Wiki

The MarkMpn.Sql4Cds.Engine NuGet package allows you to include SQL 4 CDS functionality in your own .NET applications. This is exposed using an ADO.NET provider which you can access using the Sql4CdsConnection class.

Connection String

You can create a connection using a standard XRM connection string:

using (var con = new Sql4CdsConnection("AuthType=ClientSecret;url=https://contosotest.crm.dynamics.com;ClientId={AppId};ClientSecret={ClientSecret}"))
using (var cmd = con.CreateCommand())
{
	cmd.CommandText = "SELECT name FROM account";

	using (var reader = cmd.ExecuteReader())
	{
		// ...
	}
}

IOrganizationService

If you already have an IOrganizationService instance you can use that instead:

using (var con = new Sql4CdsConnection(svc))
// ...

When you restore the package using .NET Framework, it will expect an IOrganizationService from the Microsoft.CrmSdk.XrmTooling.CoreAssembly package. If you use .NET Core it will use the Microsoft.PowerPlatform.Dataverse.Client version instead.

Multiple Connections

To execute a query that spans multiple instances, pass each one to the connection constructor:

using (var con = new Sql4CdsConnection(svc1, svc2))
// ...

Queries will use the first instance (svc1 in this example) by default. Other instances can be referenced by the unique name of the organization.

Parameters

Parameters can be added to the query as:

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();
}

EntityReference Values

Values in primary key and lookup attributes are returned as SqlEntityReference values. This encapsulates the guid and entity logical name in a similar way to the SDK EntityReference type, but also the name of the data source the value came from in cross instance queries and provides SQL-compatible NULL and ordering semantics.

This type can be cast to both the Guid and EntityReference types, so you any of these options work for extracting data from a data reader:

// Get a guid value directly from the data reader
var guid = reader.GetGuid(0);

// Get the underlying SqlEntityReference value from the data reader and cast it
var raw = (SqlEntityReference)reader.GetValue(0);
var guid2 = (Guid)raw;
var entityReference = (EntityReference)raw;

If you prefer to only ever receive the underlying guid value, e.g. for compatibility with systems that cannot handle custom types being returned from the IDataReader.GetValue method, you can set the ReturnEntityReferenceAsGuid property on the connection to true:

using (var con = new Sql4CdsConnection(org))
using (var cmd = con.CreateCommand())
{
	con.ReturnEntityReferenceAsGuid = true;

	cmd.CommandText = "SELECT accountid FROM account";

	using (var reader = cmd.ExecuteReader())
	{
		while (reader.Read())
		{
			var guid = (Guid)reader.GetValue(0);
			// ...
		}
	}
}

Dapper

The SQL 4 CDS ADO.NET provider is compatible with the popular Dapper micro-ORM, so you can retrieve data using code like:

class Account
{
	public string Name { get; set; }
	public int? Employees { get; set; }
}

var accounts = con.Query<Account>("SELECT name, employees FROM account WHERE createdon >= @yesterday", new { Yesterday = DateTime.Today.AddDays(-1) });

If you retrieve primary key or lookup fields you can choose to map these to Guid, SqlEntityReference or EntityReference properties.

SqlEntityReference

This is the default option - define your properties using the SqlEntityReference type.

class Account
{
	public SqlEntityReference AccountId { get;set; }
	public string Name { get; set; }
	public int? Employees { get; set; }
}

var accounts = con.Query<Account>("SELECT accountid, name, employees FROM account WHERE createdon >= @yesterday", new { Yesterday = DateTime.Today.AddDays(-1) });

If you want to pass a SqlEntityReference value as a parameter you will need to register a custom type handler with Dapper:

class SqlEntityReferenceTypeHandler : SqlMapper.TypeHandler<SqlEntityReference>
{
	public override SqlEntityReference Parse(object value)
	{
		if (value is SqlEntityReference ser)
			return ser;

		throw new NotSupportedException();
	}

	public override void SetValue(IDbDataParameter parameter, SqlEntityReference value)
	{
		parameter.Value = value;
	}
}

SqlMapper.AddTypeHandler(new SqlEntityReferenceTypeHandler());

con.Execute("INSERT INTO account (name) VALUES (@name)", new { name = "Dapper" });
var id = con.ExecuteScalar<SqlEntityReference>("SELECT @@IDENTITY");

var name = con.ExecuteScalar<string>("SELECT name FROM account WHERE accountid = @id", new { id });

Guid

If you only require the guid part of identifiers, set the ReturnEntityReferenceAsGuid property on the connectionas described above.

class Account
{
	public Guid AccountId { get;set; }
	public string Name { get; set; }
	public int? Employees { get; set; }
}

con.ReturnEntityReferenceAsGuid = true;
var accounts = con.Query<Account>("SELECT accountid, name, employees FROM account WHERE createdon >= @yesterday", new { Yesterday = DateTime.Today.AddDays(-1) });

EntityReference

If you want to use the standard SDK EntityReference type you will need to register a custom type handler with Dapper:

class EntityReferenceTypeHandler : SqlMapper.TypeHandler<EntityReference>
{
    public override EntityReference Parse(object value)
    {
        if (value is SqlEntityReference ser)
            return ser;

        throw new NotSupportedException();
    }

    public override void SetValue(IDbDataParameter parameter, EntityReference value)
    {
        parameter.Value = (SqlEntityReference)value;
    }
}

SqlMapper.AddTypeHandler(new EntityReferenceTypeHandler());

class Account
{
	public EntityReference AccountId { get;set; }
	public string Name { get; set; }
	public int? Employees { get; set; }
}

var accounts = con.Query<Account>("SELECT accountid, name, employees FROM account WHERE createdon >= @yesterday", new { Yesterday = DateTime.Today.AddDays(-1) });