Month: January 2017

Below are some code samples for reading and writing data in Channel database. This is just for Channel database. This does not necessary mean that the data needs to go to D365 HQ. It may, either via CDX pull job or via Real-time service call. This information is only for simple reads and writes in Channel db.

Prerequisites:
Dynamics 365 for Operations (1611)
KB3214687
KB3214679

First, create the SQL table in ax schema and a view in crt schema:

    CREATE TABLE [ax].[MY_EXTENSION](
		[DATAAREAID] [nvarchar](4) NOT NULL,
		[RECID] [bigint] NOT NULL,
		[COL1] [int] NOT NULL,
		[COL2] [nvarchar](100) NOT NULL,
		[COL3] [bit] NOT NULL,
		[ACCOUNTNUM] [nvarchar](20) NOT NULL)
    . . .
    END
    GO

    CREATE VIEW [crt].[MY_EXTENSIONVIEW] AS
    (
        SELECT ACCOUNTNUM, DATAAREAID, COL1, COL2, COL3 FROM [ax].[MY_EXTENSION]
    )
    GO

    GRANT SELECT ON [crt].[MY_EXTENSIONVIEW] TO [UsersRole];
    GO

The shown sample adds 3 columns to be stored from 3 extension properties.

Grant the right permissions to table and view. This is the list of supported SQL roles:

DataSyncUsersRole	Used by CDX process account
PublishersRole	Used by publishing process account (eCommerce)
ReportUsersRole	Used by reporting user account
UsersRole	Used by runtime user (RetailServer)

Next, create a sproc for updating:

CREATE PROCEDURE [crt].[MY_UPDATEEXTENSIONPROPERTIES]
    @TVP_EXTENSIONPROPERTIESTABLETYPE         [crt].EXTENSIONPROPERTIESTABLETYPE READONLY
AS
BEGIN
	DECLARE @nvc_DataAreaId NVARCHAR(4);
	DECLARE @recId bigint;
	DECLARE @accountNum [nvarchar](20);

	DECLARE @Col1Value int = 0;
	DECLARE @Col2Value nvarchar(100) = '';
	DECLARE @Col3Value bit = 0;
	
       SELECT DISTINCT TOP 1 @recId = tp.PARENTRECID, @nvc_DataAreaId = ct.DATAAREAID, @accountNum = ct.ACCOUNTNUM
       FROM @TVP_EXTENSIONPROPERTIESTABLETYPE tp
       JOIN [ax].CUSTTABLE ct on ct.RECID = tp.PARENTRECID
       WHERE tp.PARENTRECID <> 0
			
	SELECT @Col1Value = COALESCE(tp.PROPERTYVALUE, 0)
	FROM @TVP_EXTENSIONPROPERTIESTABLETYPE tp 
	where tp.PARENTRECID <> 0 and tp.PROPERTYNAME = 'COL1'
	
	SELECT @Col2Value = COALESCE(tp.PROPERTYVALUE, '')
	FROM @TVP_EXTENSIONPROPERTIESTABLETYPE tp 
	where tp.PARENTRECID <> 0 and tp.PROPERTYNAME = 'COL2'
	
	SELECT @Col3Value = CAST(CASE WHEN tp.PROPERTYVALUE = 'True' THEN 1 ELSE 0 END AS BIT)
	FROM @TVP_EXTENSIONPROPERTIESTABLETYPE tp 
	where tp.PARENTRECID <> 0 and tp.PROPERTYNAME = 'COL3'

	MERGE INTO [ax].[MY_CUSTOMEREXTENSION] dest
	USING (SELECT @accountNum as ACCOUNTNUM) as source on  dest.ACCOUNTNUM = source.ACCOUNTNUM
	WHEN matched then
		update set dest.COL1 = @Col1Value, dest.COL2 = @Col2Value, dest.COL3 = @Col3Value
	when not matched then
		INSERT (RECID, ACCOUNTNUM, DATAAREAID, COL1, COL2, COL3)
		VALUES (@recId, @accountNum, @nvc_DataAreaId, @Col1Value, @Col2Value, @Col3Value);
END
GO

GRANT EXECUTE ON [crt].[MY_UPDATECUSTOMEREXTENSIONPROPERTIES] TO [UsersRole];
GO

Note the MERGE INTO statement. This allows to either create or update a record.

For reading in the CRT, here especially for extension properties, use SQLPagedQuery, SQLServerDatabaseContext and ExtensionsEntity:

var query = new SqlPagedQuery(QueryResultSettings.SingleRecord)
{
    Select = new ColumnSet(new string[] { "COL1", "COL2", "COL3" }),
    From = "MY_EXTENSIONVIEW",
    Where = "ACCOUNTNUM = @accountNum AND DATAAREAID = @dataAreaId",
};

query.Parameters["@accountNum"] = customer.AccountNumber;
query.Parameters["@dataAreaId"] = request.RequestContext.GetChannelConfiguration().InventLocationDataAreaId;
using (var databaseContext = new SqlServerDatabaseContext(request))
{
    ExtensionsEntity extensions = databaseContext.ReadEntity(query).FirstOrDefault();
    if (extensions != null)
    {
        var col1 = extensions.GetProperty("COL1");
        if (col1 != null)
        {
            customer.SetProperty("COL1", col1);
        }
        else
        {
            customer.SetProperty("COL1", 0);
        }
 	 
        . . .
    }
    else
    {
        customer.SetProperty("COL1", 0);
    }
}

In order to write the data to the db, use this code to call the sproc shown above:

using (var databaseContext = new SqlServerDatabaseContext(r))
using (var transactionScope = new TransactionScope())
{
    if (!r.Customer.ExtensionProperties.IsNullOrEmpty())
    {
        ParameterSet parameters = new ParameterSet();
        parameters["@TVP_EXTENSIONPROPERTIESTABLETYPE"] = new ExtensionPropertiesTableType(r.Customer.RecordId, r.Customer.ExtensionProperties).DataTable;
        databaseContext.ExecuteStoredProcedureNonQuery("MY_UPDATEEXTENSIONPROPERTIES", parameters);
    }

    transactionScope.Complete();
}

In the case of you want to write other data to the database (no extension properties to entities), build the SqlParameters one by one and match in your sproc. For reading, it may be easier to just treat it as extension properties, then convert to whatever you want. Or you could create your own entity and query for that.