ian.blair@softstuff

My technical musings

A small CRM2015 plugin to create a relationship

Recently needing to provide the ability to create a N:N relationship in a workflow process provided an excuse to create a quick workflow plugin as unfortunately it isn't currently possible to do it out-of-the-box if you have defined a relationship rather than using an intersect table.

Fortunately a plugin to do this is a very simple affair:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Workflow;
using System;
using System.Activities;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
  
namespace Softstuff.Workflow.Relationships
{
    public class CreateManyToManyLink : CodeActivity
    {
        protected override void Execute(CodeActivityContext executionContext)
        {
            IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();
            IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.InitiatingUserId);
 
            EntityReference account = Record1.Get<EntityReference>(executionContext);
            EntityReference new_testentity = Record2.Get<EntityReference>(executionContext);
            EntityReferenceCollection relatedEntities = new EntityReferenceCollection();
 
            // Add the related entity
            relatedEntities.Add(account);
 
            // Add the relationship schema name
            Relationship relationship = new Relationship("new_account_new_testentity");
 
            // Associate the account record to new_testentity
            service.Associate(new_testentity.LogicalName, new_testentity.Id, relationship, relatedEntities);
 
        }
 
        [Input("Account")]
        [ReferenceTarget("account")]
        public InArgument<EntityReference> Record1 { get; set; }
 
        [Input("New_TestEntity")]
        [ReferenceTarget("new_testentity")]
        public InArgument<EntityReference> Record2 { get; set; }
    } 
}

 

The plugin takes two parameters both EntityReferencetypes, and although I had hoped to be able to create a universal plugin that would work for all entities, but unfortunately you have to include the [ReferenceTarget(<entity>)] clause that limits theEntityReference to a single entity type.

For this example I have created a N:N relationship between account and new_testentity and the relationship is called new_account_new_testentity.

The lines in the plugin that actually do the work are:

relatedEntities.Add(account);   // the account entityreference is added to the EntityReferenceCollection.
Relationship relationship = new Relationship(<name of the relationship>);  // define the relationship
service.Associate(EntityReference,LogicalName,EntityReference.Id, relationship, relatedEntities);  // actually create the link

The only other thing to remember before you compile and deploy the plugin is to sign the assembly.

Deploy the assembly using the plugin registration tool and it should appear as a custom workflow step.

Building a plugin for CRM 2015 Won or Lost Opportunities

Recently I had to develop a plugin for a CRM 2015 system that triggered each time the opportunity was modified. This worked fine for the normal day to day edits of the opportunity but it failed to catch when the opportunity was closed as Won or Lost.

Normally I would hook the plugin to the Update message on opportunity with the Plugin Registration Tool but when I tested it I wasn't getting the results I wanted. After some digging around I discovered 2 more messages for opportunities, and these are Win and Lose.

On the face of it this seemed quite simple, just hook the plugin to these messages and its job done. Unfortunately its not quite as simple as that as the messages themselves don't actually pass the opportunity entity when they fire, they actually pass the opportunityclose entity which I have to be honest I am not incredibly familiar with. The good news is I didn't really have to worry about it as one of the attributes of the entity is the opportunityid, so all I had to do was get the passed opportunityclose entity and read the opportunityid attribute then search for the opportunity record with a matching id and then use those values. In fact it sounds more difficult than it actually is. 

Here is some sample code to catch the Win message

public void Execute(IServiceProviderserviceProvider)
{
    Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext)serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));
    IOrganizationServiceFactoryserviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
    IOrganizationService _service = serviceFactory.CreateOrganizationService(context.UserId);
    if (context.InputParameters.Contains("OpportunityClose"))
    {
        Entity oppCloseEnt = (Entity)context.InputParameters["OpportunityClose"];
        if (oppCloseEnt.Contains("opportunityid"))
        {
            EntityReference er = (EntityReference)oppCloseEnt["opportunityid"];
            // do stuff here
        }
    }
}

 

The good news is that the code is the same for the Lose message.

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.

 

A SQL Server random number generator

The first function that springs to mind is the RAND() function, but this suffers from a few drawbacks. Firstly it is only a pseudo random number and it generates values between 0 and 1. This is easily rectified by multiplying the result by the range you want, so for values between 0 and 255 the expression would be

 

select cast(rand() * 255 as tinyint)

 

A second drawback is if you try to use it in a query to assign every record a unique number it doesn't, it simply generates a single number for all rows.

 

select top 20 *,cast(rand() * 250 as tinyint) from mytable

 

This generates the same number for every row.

 

 

A more flexible function in SQL server is the CRYPT_GEN_RANDOM() and this will generate a variety of random values and is designed to be cryptographically secure which means it will potentially be more random than the existing RAND() function.

 

This can easily replace the RAND() function and is called like this depending on the size of the value you wish to return.

 

Select CAST(CRYPT_GEN_RANDOM(1) AS TINYINT)

Select CAST(CRYPT_GEN_RANDOM(2) AS SMALLINT)

Select CAST(CRYPT_GEN_RANDOM(4) AS INT)

Select CAST(CRYPT_GEN_RANDOM(8) AS BIGINT)

 

Numbers returned by this function will be between the MIN and MAX values for the datatype you are returning and as such may be both positive and negative.

 

This time if you run the query

 

select top 20 *,cast(CRYPT_GEN_RANDOM(1) as TINYINT) from mytable

 

You will notice a different random number returned against each row.

 

If you only wish to have a value between 0 and 250 it would initially be quite easy to simply use the modulus function to return it, so a query like

 

select top 20 *,cast(CRYPT_GEN_RANDOM(1) as TINYINT) % 250 from mytable

 

will provide results in the 0-250 range. A potential issue here is that the values may not be completely random as some of the values between 1 and 5 will have a higher probability of being returned as for these numbers as there are two possible output values that are in the modulus operator that will give the same final result. To output the number 1 the outputs from the random number could be 1 or 251, and for 2 it could be 2 or 252 and so on.

 

To prevent this being an issue you could simply throw away all the numbers higher than 250 so each number only has a 1 chance of being included in the output.

 

There are two possible solutions here, firstly we could make the range of the CRPT_GEN_RANDOM() function so large that most of the statistical deviations could be removed and the simplest solution could be

 

Select CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) % 250

 

As the output could swing from -9223372036854775808 through 9223372036854775807 the possible values for each of the desired 250 output values are many so that it should produce a nice smooth spread of random numbers. It wont be perfect but for most applications it will be fine.

 

The second and slightly more convoluted way to remove this deviation would simply be to throw away any numbers higher than 250 and rerun the CRYPT_GEN_RANDOM() function again.

 

One issue that will be encountered is that the CRYPT_GEN_RANDOM() function cannot be run from within a UDF which would be the simplest way to handle this.

 

First we have to embed the function in a view as unfortunately embedding it in a stored procedure will not work from within the function.

 

CREATE VIEW vWGenerateRandomNumber

AS

SELECT CAST(CRYPT_GEN_RANDOM(1) AS TINYINT) AS RandomNumber

 

This view is really more of a workaround as we aren't actually returning any actual database rows just the value from the CRYPT_GEN_RANDOM() function.

 

Next we can create the function that calls the view

CREATE FUNCTION Get_RandomNumber()
RETURNS TINYINT
AS
BEGIN
	DECLARE @Value TINYINT
	 /* rerun the loop while the value is out of range */
	WHILE (@Value IS NULL OR @Value >= 250) 
	select @Value= RandomNumber from dbo.vWGenerateRandomNumber
RETURN @Value
END

 

In the function we have added the login in the WHILE loop so that it calls the CRYPT_GEN_RANDOM() function again if the value is > =250 or NULL.

 

Now whenever we need a random number we can simply call the function.

 

select top 20 *,dbo.Get_RandomNumber() from mytable

Getting random data out of SQL Server

SQL Server is excellent for storing data and passing it back in nice ordered blocks, but what happens if you just want to take a random selection of data for example a random list of customers to test a new marketing campaign on.

Since SQL2005 there has been a clause called TABLESAMPLE that will allow you to get some random data from a table either as a percentage of the table or a set number of rows.

Select * from mytableTABLESAMPLE(10 PERCENT)

Or

Select * from mytableTABLESAMPLE(100 ROWS)

You can also specify a REPEATABLE option so that if no updates, inserts or deletes have been made to the database inbetween queries the same results will be returned.

Select * from mytableTABLESAMPLE(10 PERCENT) REPEATABLE(1)

The (1) is an arbitrary value, as long as this stays the same between queries and no other changes have been made you will see the same results.

Unfortunately due to the way this clause works under the hood the results may not be truly random as it uses the way the data is stored internally in pages to sample information rather than taking the database rows as a whole and returning the sample from there.

This also has some limitations in that it cannot be used in Views, or Rowsets.

 

Another more flexible method to get random data out from a sql table is to simply sort by NEWID(), this works by temporarily creating a Guid for each row and sorting by it, and is a very effective way especially when combined with a TOP command to extract a random set of rows. A Guid or Globally Unique Identifier is a computer generated unique reference number that is 128bits long and should be unique as it can be generated with 2^122 unique values. A Guid usually takes the form {21EC2020-3AEA-4069-A2DD-08002B30309D}.

Select TOP 50 * from mytable order by NEWID()

This will return a random 50 rows from the table, but if the query is run a second time it will return a different 50 rows, although it is possible that some of the rows from the first query may appear in the second.

This method has the advantage that the order by clause can be included in almost existing query or view but care has to be taken about the order it appears in if there is already an existing ORDER BY clause.

Select top 50 * from mytable order by name,NEWID()

This will probably not return the results you are looking for as the query will perform a fixed sort before the random part you are looking for.

The query should be rewritten as

Select top 50 * from mytable order by NEWID(),name

This will return a random sample sorted in the order defined after the NEWID().