NOTE: This entry assumes the reader has experience implementing and deploying MS CRM 4.0 plug-ins. More detailed information on how to develop and deploy plug-ins is available here: Altriva Blog - Execution Pipeline Overview and here: MSDN-Registering Plug-ins.
Using SQL Server to generate auto-incrementing numbers is a simple way to avoid number generation risks like number collision, where two entities could be assigned the same number. The procedure flow for generating auto-incrementing entity numbers is as follows:
- When a user creates a new entity, a plug-in registered on pre-create or post-create of that entity fires.
- The plug-in confirms that the correct entity fired the plug-in.
- The plug-in then calls a stored procedure to insert a row into a table with an auto-incrementing identity column. If the plug-in was registered on post-create of the entity, the GUID of the entity can be inserted into the table for future reference.
- The stored procedure then selects the generated identity for that row and returns it to the plug-in.
- The plug-in then sets an attribute on the entity to the identity and returns.
A separate plug-in must be created and registered for each entity that uses auto-numbering. However, it is up to the implementer whether to use one table to generate all auto-numbering or to create separate tables and stored procedures for each entity. Using one table would mean all entities would share the same sequence of numbers, while using multiple tables would allow each entity to start at “1” and have its own sequence.
I suggest creating the table and stored procedure in the MSCRM database with unique names. While creating database objects in the MSCRM database is unsupported, it allows for much simpler system backups and deployment maintenance when working with multiple environments. Care should be taken in naming tables and stored procedures to avoid naming the objects something that Microsoft might use in future versions of CRM.
Below is the plug-in and stored procedure code for an implementation of auto-numbering for the opportunity entity. The plug-in is registered on pre-create due to a client’s requirement that the displayed opportunity name “new_name” be updated and displayed on save with the auto-number appended. The plug-in stores the auto-number in the “name” attribute. Note that the connection string is hardcoded in this sample code, so changing database servers would require a recompilation of the plug-in. Be sure to modify the connection string as appropriate.
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Web.Services;
namespace Altriva.CRM.Plugins
{
public class OpportunityCreateHandler: IPlugin
{ public void Execute(IPluginExecutionContext context)
{
DynamicEntity entity = null;
// Check whether the input parameters property bag contains a target
// of the create operation and that target is of type DynamicEntity.
if (context.InputParameters.Properties.Contains("Target") &&
context.InputParameters.Properties["Target"] is DynamicEntity)
{
// Obtain the target business entity from the input parmameters.
entity = (DynamicEntity)context.InputParameters.Properties["Target"];
// Verify that the entity represents an opportunity.
if (entity.Name != EntityName.opportunity.ToString()) { return; }
}
else
{
return;
}
try
{
int sequentialId = 0;
string connectionString = "Data Source=<Data source>;Initial Catalog=<Organization Name>_MSCRM;Integrated Security=sspi";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Altrivacustp_GenerateUniqueId", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@objectID", null);
conn.Open();
sequentialId = (int)cmd.ExecuteScalar();
conn.Close();
if (entity.Properties.Contains("name") == false)
{
StringProperty opportunityID = new StringProperty("name", sequentialId.ToString());
entity.Properties.Add(opportunityID);
if (entity.Properties.Contains("new_name") == true)
{
StringProperty opportunityName = new StringProperty("new_name", sequentialId.ToString() + " - " + entity.Properties["new_name"]);
entity.Properties.Remove("new_name"); entity.Properties.Add(opportunityName);
}
}
else
{
// Throw an error, because Opportunity ID must be system generated
throw new InvalidPluginExecutionException("The Opportunity ID can only be set by the system");
}
return;
}
catch (System.Web.Services.Protocols.SoapException ex)
{
throw new InvalidPluginExecutionException("An error occurred in the OpportunityCreateHandler plug-in.", ex);
}
}
}
}
Table Creation and Stored Procedure Code:
GO
/****** Object: Table [dbo].[Altrivacustp_OpportunityNumber] Script Date: 05/07/2008 14:28:54 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Altrivacustp_OpportunityNumber]')
AND type in (N'U'))
DROP TABLE [dbo].[Altrivacustp_OpportunityNumber]
GO
CREATE TABLE Altrivacustp_OpportunityNumber
(objectId Uniqueidentifier NOT NULL,
ObjectNumber Int IDENTITY(1,1),
Generated bit)
GO
/****** Object: StoredProcedure [dbo].[Altrivacustp_GenerateUniqueId] Script Date: 05/07/2008 14:27:43 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Altrivacustp_GenerateUniqueId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Altrivacustp_GenerateUniqueId]
/****** Object: StoredProcedure [dbo].[Altrivacustp_GenerateUniqueId] Script Date: 05/07/2008 13:29:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[Altrivacustp_GenerateUniqueId]( @objectID UniqueIdentifier = NULL) as
/************ Test the Stored procedure
Declare @MyTestGuid UniqueIdentifier
SELECT @MyTestGuid = NEWID()
EXECUTE Altrivacustp_GenerateUniqueId @MyTestGuid
SELECT @MyTestGuid = NULL
EXECUTE Altrivacustp_GenerateUniqueId @MyTestGuid
SELECT * FROM Altrivacustp_OpportunityNumber
**********************/
BEGIN
set nocount on
Declare @Generated bit
SELECT @Generated = 0
-- Check if @ObjectID is null
if @objectID IS NULL
begin
select @objectID = NEWID(), @Generated = 1
end
INSERT Altrivacustp_OpportunityNumber
(objectId, Generated)
VALUES (@objectID, @Generated)
SELECT ObjectNumber
FROM Altrivacustp_OpportunityNumber
WHERE objectId = @objectID
GO
Note that users who are offline and using the CRM for Outlook with Offline Access client will not have a number generated until they go back online and the plug-in is fired during the playback of the playback graph.
I hope these code snippets give you a good starting place for integrating with other systems when entity GUID primary keys are not ideal, or when a simple integer numbering scheme helps your business processes.
Phil Edry