ian.blair@softstuff

My technical musings

Getting Audit data out of CRM

When retrieving Audit Log information from Dynamics 365 using 

RetrieveAttributeChangeHistoryRequest attributeChangeHistoryRequest =
                            new RetrieveAttributeChangeHistoryRequest {
                                Target = new EntityReference(Contact.EntityLogicalName, contact.Id),
                                AttributeLogicalName = "new_myfield",
                            };

RetrieveAttributeChangeHistoryResponse attributeChangeHistoryResponse =
                            (RetrieveAttributeChangeHistoryResponse) context.Execute(attributeChangeHistoryRequest);

 

The returned data in the AuditDetails collection will contain an action attribute, this will determine the type of operation that took place.

       foreach (var EachEditRecord in attributeChangeHistoryResponse.AuditDetailCollection.AuditDetails)
       {
             AuditDetail attributeDetail = (AuditDetail) EachEditRecord;
             OptionSetValue action = (OptionSetValue) attributeDetail.AuditRecord["action"];
        }

 

A list of actions are below

The date and userid who made the change can be found with

EntityReference userid = (EntityReference) attributeDetail.AuditRecord["userid"];
                            
DateTime? createdon = (DateTime?) attributeDetail.AuditRecord["createdon"];

 

Some of these will contain more information than others and the resulting AuditDetail result will need to be cast as an AtrributeAuditDetail record to find out the old value of the field, and the new value of the field.

var recType = EachEditRecord.GetType();
if (recType == typeof(AttributeAuditDetail)) {
       if (action.Value==2) { //update

       AttributeAuditDetail ar = (AttributeAuditDetail) attributeDetail;
       bool newval = ar.NewValue.Attributes.Contains("new_myfield")
                              ? (bool) ar.NewValue["new_myfield"]
                             : false;


       bool oldval = ar.OldValue.Contains("new_myfield")
                              ? (bool) ar.OldValue["new_myfield"]
                              : false;
     }
}

 The OldValue and NewValue are both entities that will contain the before and after and can be read in the normal way.

 

Updating your CRM V9 connection code

I had a client ring me in a bit of a panic as their CRM system had upgraded to V9 over the weekend and the forms integration I had built for them ages ago had stopped working. The kept getting a 'Connection forcibly closed' error every time they tried to submit a form.
Fortunately I had recoded the connection part when they went to Dynamics365 from CRM2016 so it looked like this.
 
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Tooling.Connector;
using System.ServiceModel;
var connectionString = "url=https://mycrmsystem; Username=myusername; Password=mypassword; authtype=Office365";
CrmServiceClient conn = new CrmServiceClient(connectionString);
using (OrganizationServiceProxy orgService = conn.OrganizationServiceProxy) {
     if (conn.IsReady) {
          // code to do stuff goes here
      }else{
         throw new invalidoperationexception(conn.LastCRMError);
      }
}

To make it work with CRM V9 turned out to be quite simple.
Firstly I had to install .NET v 4.6 on the server that was running the code, previously it was on 4.5.2. Next I had to install the new versions of the Microsoft.Xrm.Sdk.dll, and the Microsoft.Xrm.Tooling.Connector from NuGet as there doesn't seem to be the a huge SDK download anymore.
 
NuGet packages to install
Microsoft.CrmSdk.CoreAssemblies     v9.x
Microsoft.CrmSdk.XrmTooling.CoreAssembly   v9.x
 
Then the code needs 2 new lines for it to work.
Add a using System.Net; line, then as the new connections need TLS1.2 force this in the line
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
anywhere before you actually make the connection. So the code should now look like
 
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Tooling.Connector;
using System.ServiceModel;
using System.Net;

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
var connectionString = "url=https://mycrmsystem; Username=myusername; Password=mypassword; authtype=Office365";
CrmServiceClient conn = new CrmServiceClient(connectionString);
using (OrganizationServiceProxy orgService = conn.OrganizationServiceProxy) {
     if (conn.IsReady) {
          // code to do stuff goes here
      }else{
         throw new invalidoperationexception(conn.LastCRMError);
      }
}
 
And you are done. 
Easy.

Changing the colour of a windows title bar in a universal application

By default when creating a blank Universal Windows application the basic window style is a white title bar on a white window.

By adding a few lines in the OnLaunched function the title bar can be coloured easily.

protected override void OnLaunched(LaunchActivatedEventArgs e)
{
            var appView = Windows.UI.ViewManagement.ApplicationView.GetForCurrentView();
            appView.TitleBar.BackgroundColor = Colors.LightBlue;
            appView.TitleBar.ButtonBackgroundColor = Colors.LightBlue;
            appView.TitleBar.ForegroundColor = Colors.White;
            appView.TitleBar.ButtonForegroundColor = Colors.White;
            appView.Title = "Title Text";

 

The example above will show a Light blue title bar with White text.

 

Using the CLR to create a Random Number generator

Using the CLR to create a Random Number generator

 

Following on from my last post about a random number generator in SQL, another method of providing a random number generator would be to add the function using C# and the Common Language Runtime, and fortunately the tools to do this are now installed with SQL Server.

We could do this using Visual Studio but for a small function like this we can do it all with notepad and the command line compiler installed with .net.

The first thing to do is check that the compiler is accessible and that the path to the csc.exe file is included in your path statement. Open a CMD window and at the prompt type CSC. If you see a message like Microsoft visual C# compiler version x.x.xxxxx.xxxxx then all is well. If you get an error message then you need to check you path statement.

The compiler will be installed in the c:\windows\microsoft.net\framework\(version) folder where version is the version of dot net that you have installed. If you check in the framework folder you may have multiple versions, and if this is the case go for the latest version on the machine.

Next add the path to the folder into the PATH variable. This is usually located in the Environment Variables option under system properties and the exact method of accessing it will vary depending on the version of the operating system you are running.

Once you have updated your path statement go back to your CMD window and try entering the CSC command again, if all is well you will see the Microsoft visual C# compiler version x.x.xxxxx.xxxxx message, if not check the previous step and retry.

Now that the compiler is working, we can now write the code for the function we wish to create. Open a blank file in Notepad.exe and enter the following:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Security.Cryptography;

public class RandomNumberFn
{
	[SqlFunction(Name="CryptoRnd")]
	public static SqlByte CryptoRnd()
	{
		SqlByte value= new SqlByte(255);
		RNGCryptoServiceProvider rngCsp = new RNGCryptoServiceProvider();
		byte[] randomNumber = new byte[1] ;	
		while(value>250)
		{
			rngCsp.GetBytes(randomNumber);
			value = randomNumber[0];
		}
		return value;
	}
}

 

If you are familiar with C# this code should be self-explanatory, and uses the RNGCryptoServiceProvider() which is the C# implementation of the CRYPT_GEN_RANDOM() number generator we looked at in TSQL in the last entry.

Save the file somewhere simple as rnd.cs, in my case K:\ as because I was using the command line version of the compiler I wanted something easy to type in.

In the CMD window enter the command csc /target:library K:\rnd.cs

After a second or two it should return you to the command prompt with no errors.

The /target:library switch on the compiler generates a .dll file rather than a .exe. The .dll file will be created in the same directory as the source file and appropriate permissions will be required to ensure the file can be created.

Next we need to add the function into SQL server, and we can do this by opening up the SQL Management Studio and creating a new query window.

In the query window enter and execute

CREATE ASSEMBLY CryptoRnd from 'k:\rnd.dll' with PERMISSION_SET=SAFE

 

If you have an error about the CLR Integration not being enabled, run the following script:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

 

This script will only need to be done once, and you will need the ALTER SETTINGS permission for your SQL Server user.

 

Next enter this query into the query manager and execute it

Create function CLR_RandomNumber() returns tinyint

as external name CryptoRnd.RandomNumberFn.CryptoRnd

 

This will actually create the function from the CLR code. The External name is in the form <assembly>.<class>.<functionname>

 

You can now test your new function by executing this query

 

Select dbo.CLR_RandomNumber()

 

Or it can be used in place of the random functions previously as

 

Select *,dbo.CLR_RandomNumber() from mytable.