Project Description
This is prototype project showing how CRM 2011 transaction can be part of enterprise transaction.
All changes done to CRM will be committed or rolled back with changes done to other parties.

WARNING: This is unsupported scenario so please use it on your own risk.

The goal:

1. Make multiple changes to CRM

2. Make changes to another MS SQL database (let say using Entity Framework)

3. Make changes to Unknown database through COM+ application  (assume it supports transaction)

4. Read all changed/inserted  data 

5. Commit/Rollback all changes together.

As per CRM 2011 design plugin executed in stage 40 (Post Operation) is in transaction and data is saved to database as part of this transaction. Any changes done to CRM by plugin attached to this stage are pat of transaction and will be committed by CRM after plugin execution is completed.

Transaction opened by CRM has type of SqlTransaction (easy to find using Reflector) so it cannot be Enterprise transaction by design. At the same time CRM Connection (which is SqlConnection) cannot be enlisted in enterprise transaction as currently has pending transaction. 

Solution:

1. Introduce custom entity which will play role of transaction trigger. Name it "Business Transaction". 

2. Create plugin and attach it to post event on "Create" message to Business Transaction entity.

3. When plugin receives control, extract reference to SqlTransaction and SqlConnection from its Execution Context

4. Commit transaction. It will save the record into Business Transaction entity (but we do not care).

5. As now plugin SqlConnection is free we can enlist it in another transaction. So Start TransactionScope and make it Enterprise. 

Enlist plugin connection in new scope

6. Do all custom business logic with multiple system updates. 

7. Important: do ALL CRM CHANGES through Organisation Service provided by plugin context. This will ensure that all changes done to CRM will go through Webservice and all CRM logic will be executed. At that time we know that Webservice will use the same SqlConnection which we enlisted in our transaction.

8. Complete enterprise transaction. This will save oll changes including CRM changes.

9. Using  plugin SqlConnection start new transaction by calling BeginTransaction()

10. Put a reference to new transaction to the same place where we took it at step 3.

11. Let CRM do the rest.

At step 11 CRM will commit transaction without knowing that it is not the same instance as it was originally.

 


Prerequisites:

1. CRM 2011 instance with custom entities and fields. CRM automatically adds Name field into custom entity:

 Business Transaction (new_businesstransaction)

 BusinessProcessName (new_businessprocessname) text(100)

 Data (new_data) text(100)

Completed (new_completed) Two Optionsii.

  Test Entity 1 (new_testentity1)

When creating  custom entities specify that they are visible in Workspase area.

Also change related forms so new fields become visible for editing.

 

2. SQL Server with TestDB database and Table1 in it

USE [TestDB]GOCREATE TABLE [dbo].[Table1]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO


3. Enable snapshot isolation on all involved databases including CRM. This is not required but will allow using table scans without locks to verify that prototype works.Kill all connections before running this command. 

alter database [Database Name] set READ_COMMITTED_SNAPSHOT  ON

Plugin registration

Register assembly using Plugin Registration Tool and then create a Step as:

 

TESTING

There are several ways of verifying that plugin is working as expected

1. Create new Business Transaction record into our custom entity in CRM. Specify unique name, BusinessProcessName = "TestProcessEntityAndDB" (which is business process class name from the plugin), Data = "error". Data is custom string passed into Business process and adds some control on execution process. When Data contains word "error" code with throw an exception at the very end so all changes will be rolled back. If Data contains "sleep" code will sleep the thread for 20 seconds. By specifying control works we can monitor execution process.

Next script executed in DB will show us internals of different databases while plugin executes its code:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select COUNT(1) from TestDB.dbo.Table1 
select COUNT(1) from SYNERGY_MSCRM.dbo.new_testentity1 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select COUNT(1) from TestDB.dbo.Table1 
select COUNT(1) from SYNERGY_MSCRM.dbo.new_testentity1

By saving new Business Transaction record with Data = "sleeperror" script will show extra rows created in different system for 20 seconds then they will disappear as process will generate an exception.

Business transaction record will stay in CRM for audit purpose containing Data passed into it. Completed field will = "No"

By saving Business Transaction record without "error" in Data field all changes will be committed and Transaction Completed value will be = "Yes"

Last edited Jan 28, 2013 at 6:46 AM by vluzhko, version 4