Creating Custom Integrations with Microsoft CRM without Microsoft BizTalk Server Partner Edition

Article
06/30/2006

Microsoft Corporation

February 2004

Applies to:
Microsoft® CRM version 1.2
Microsoft Visual Studio® .NET

Summary: Learn about custom systems integration between Microsoft CRM and other applications. (28 printed pages)

Introduction
Microsoft CRM Custom Integration Checklist
Introduction to Microsoft CRM Post-Callouts
Sample Microsoft CRM Integration with Northwind Database
Viewing Microsoft CRM Object XML Schemas
Viewing Northwind "Customers" Table Definition
Integration Mapping
Determining the Integration Physical Design
Microsoft CRM User Interface Customization
Developing Microsoft CRM Post-Callouts
Developing Transact-SQL Stored Procedures
Developing Windows Service Applications
Testing our Custom Integration
Other Integration Alternatives
Conclusion

Introduction

Microsoft® CRM exposes two paradigms for integrating data between systems. The first paradigm leverages Microsoft BizTalk® Server Partner Edition as the message-processing engine used to integrate Microsoft CRM with Microsoft Business Solutions-Great Plains 7.0 or 7.5. The second paradigm provides developers with the ability to execute custom integration logic in response to Microsoft CRM events and actions. Implemented as direct platform extensions of the core system objects, developers are provided with a flexible and robust integration platform based on Microsoft .NET and COM+.

This article can be used as a reference guide for developing custom integrations between Microsoft CRM and other business applications.

Developers reading this article should be familiar with the following development platforms and languages:

Microsoft Visual Studio® .NET, Microsoft CRM object model and software development kit (SDK), The C# programming language, Microsoft SQL Server™, ADO.NET, Microsoft Message Queuing (MSMQ), and Transact-SQL stored procedures.

Microsoft CRM Custom Integration Checklist

Much like the implementation of Customer Relationship Management (CRM) systems, custom systems integration development is like formulating and executing a new business strategy. Both require significant planning and often times require changes to operational procedures and policies.

Organizations developing custom integrations to and from Microsoft CRM may use the following checklist as a guide for structuring your integration project:

Establish a team of business analysts and information technology experts to drive appropriate measures of success.
Understand key business drivers and integration variables required for your custom integration project.
Perform comprehensive data gap analysis and entity mappings between all trading systems.
Assign appropriate time and resources to integration development and unit testing.
Create thorough test plans and maintenance procedure documents for maintaining the custom integration.

Introduction to Microsoft CRM Post-Callouts

Microsoft CRM exposes asynchronous COM-based "callout" hooks that fire in response to platform operations regarding data persistence. By using this model, it is possible to insert custom-developed integration logic that fires in response to Create, Update and Delete operations performed within the application. The callout model is shown in the following figure.

Figure 1. Business logic pipeline with Microsoft CRM v1.X post callout

Implemented through the ICRMCallout interface and registered within the Microsoft CRM application metadata, Post-Callouts can be developed and implemented using .NET development technologies such as Microsoft Visual C#® .NET and Visual Studio .NET.

Sample Microsoft CRM Integration with Northwind Database

This sample integrates Microsoft CRM and the Northwind database and assumes that Microsoft CRM is the system of origination for creating and updating customer records. Northwind will require access to new customer and updates to serve its own native function to the organization.

Opportunity Statement

The goal for this integration is to create a centralized customer data store between our two systems: Microsoft CRM and Northwind database. Functional requirements for this integration call for near real-time synchronization of customer information from Microsoft CRM to the Northwind database.

Key Integration Variables

Understanding and documenting key integration variables is an exercise that should not be ignored. Integration designers and developers must perform the following before architecting their desired approach:

Identify the common data elements.
Define the integration intervals and trigger actions.
Document record ownership and conflict resolutions.
Agree upon data entry and formatting requirements.
Account for security and data access privileges.
Define error handling and status notifications.

Although this list can be used as a starting point, each custom development initiative will have its own list of variables that need to be determined while gathering requirements. For our sample integration, we'll assume the following integration parameters have been captured:

This integration synchronizes Microsoft CRM accounts of type "Customer" with Northwind customers upon the Create, Update and Delete trigger operations in Microsoft CRM.
The synchronization interval uses a 30-second timer for processing new requests.
The integration can scale to meet system usage requirements.
The integration is developed in a manner supported by Microsoft.

Entity Mapping Between Microsoft CRM and Northwind Database

With the understanding that Microsoft CRM accounts are to be synchronized with Northwind customers, the first task to be accomplished is to define a detailed entity mapping between the two connection points.

Viewing Microsoft CRM Object XML Schemas

Microsoft CRM object definitions and schema references can be found within the Microsoft CRM SDK. Familiarize yourself with the Account and Contact schemas and data elements as they will be used within our sample integration.

Viewing Northwind "Customers" Table Definition

The Northwind Customers table definition can be viewed with SQL Server Enterprise Manager or using SQL Server Query Analyzer.

Viewing Table Definitions with SQL Server Enterprise Manager

SQL Server Enterprise Manager is a Microsoft Management Console (MMC) application that can be used to manage your SQL Server. To view the Northwind database table definitions, perform the following:

Launch Microsoft SQL Server Enterprise Manager.
Expand the Microsoft SQL Servers tree node.
Expand the SQL Server Group tree node.
Expand the tree node for the server where the Northwind database resides.
Expand the Databases node.
Expand the node for the Northwind database.
Click the Tables icon.
Right-click the Customers table.
Select Design Table.

The Design Table screen displays all fields, data types, and attributes of the Customers table.

Note These steps may vary depending on the Tree View node configurations within SQL Server.

Viewing Table Definitions Using SQL Server Query Analyzer

SQL Server Query Analyzer allows for Transact-SQL queries and stored procedures to be executed against a database. To view the Northwind database table definitions, perform the following:

Launch SQL Server Enterprise Manager.
Expand the SQL Servers tree node.
Expand the SQL Server Group tree node.
Expand the tree node for the server where the Northwind database resides.
Expand the Databases node.
Expand the node for the Northwind database.
With the Northwind database selected, click Tools, SQL Query Analyzer.
Type in the command sp_help Customers and hit F5. This will execute the sp_help system stored procedure and display the table definition in the results window.

Integration Mapping

The following entity and field mappings have been identified and documented for our sample integration:

Table 1. Microsoft CRM account to Northwind customer field mappings

Microsoft CRM Object	Microsoft CRM Schema Field	Northwind Table	Northwind Field
Account	accountnumber	Customers	CustomerID
Account	name	Customers	CompanyName
Contact	fullname	Customers	ContactName
Contact	jobtitle	Customers	ContactTitle
Account	address1_line1	Customers	Address
Account	address1_city	Customers	City
Account	address1_stateorprovince	Customers	Region
Account	address1_postalcode	Customers	PostalCode
Account	address1_country	Customers	Country
Account	telephone1	Customers	Phone
Account	fax	Customers	Fax

Determining the Integration Physical Design

To accomplish this integration we will develop the following components:

Microsoft CRM user interface changes to enforce data integrity.
Microsoft CRM Post Callout to write messages to an integration message queue.
Microsoft Windows® Service to monitor the message queue for actions to be pushed into Northwind database.
Transact-SQL stored procedures to perform the Create, Update and Retrieve actions against the Northwind database.

The integration design is shown in the following figure.

Figure 2. The integration design

Microsoft CRM User Interface Customization

To ensure that our integration maintains consistent data between systems, we'll use the following procedure to configure the Microsoft CRM Account form to require that the accountnumber schema field is set to "Business required" when selecting a customertypecode of "Customer". This will help enforce data integrity between the Microsoft CRM and Northwind customer records.

In Microsoft CRM, open the Account Customization / Customize Form screen.
Move the customertypecode schema field (displayed as Relationship Type) so that it is directly under the name schema field (displayed as Account Name.)
Select the customertypecode schema field and click Change Properties.
Click the Schema tab and make sure the Required Level attribute is set to "Business Required."
Implement a rule to enforce our data integrity requirement of requiring a customer ID for each Account of type "Customer" using Microsoft JScript® code for script automation. To perform this task, select the Events tab and edit the OnChange() event for this field.
Add the following JScript code to the OnChange() event and ensure that you enable the event by checking the "Event enabled" check box:

Figure 3. OnChange dialog
Click the Dependencies tab and add a dependency to the Account Number field.
Click OK.
Publish the changes using the Deployment Manager and restart IIS.
Once the IIS server has restarted, validate that your changes have been made to the Account form.

Developing Microsoft CRM Post-Callouts

To begin developing your Microsoft CRM Post-Callout, follow the steps indicated below:

Familiarize yourself with the MSDN® article: Business Logic Extensions to Microsoft CRM: Post-Callouts.
Launch Visual Studio .NET and create a new Class Library project. Name your project CrmIntegration and select a location to save your project.
Add a reference to the System.EnterpriseServices and System.Messaging.dll namespaces. System.EnterpriseServices provides the infrastructure for developing enterprise applications and enables your project to be managed by COM+, while System.Messaging.dll enables you to leverage MSMQ within your custom integration.
Give your class file a meaningful name to reference the integration. In this sample, we use the name CalloutClass.

In the code view for CalloutClass, add the following code to your class file:

using System;
using System.EnterpriseServices;
using System.Runtime.InteropServices;

Directly below your reference to the namespaces to be used in your project, add the following lines of code:

[assembly: ApplicationName("Microsoft CRM Custom Integration")]
[assembly: ApplicationActivation(ActivationOption.Server)]

Providing this information to your project defines the manner in which your .NET assemblies will interact with COM+. The ApplicationName attribute provides the name of the application once it is managed within COM+ and the ApplicationActivation attribute specifies that this COM+ application is a server application- enabling the application to run in a separate process as the process that spawned it.

Microsoft CRM Post-Callouts operate as step operation firing after a Microsoft CRM platform event; to enable this execution flow, your project must implement the ICRMCallout interface. Add the following lines of code to your project to implement the ICRMCallout interface:

[GuidAttribute("F4233E5B-17DC-4661-9ABC-6707A9F99215")]
public interface ICRMCallout
{
      void PostCreate(int ObjectType, string ObjectId, string 
        OrigObjectXml);
      void PostUpdate(int ObjectType, string ObjectId, string 
        OrigObjectXml);
      void PostDelete(int ObjectType, string ObjectId, string 
        OrigObjectXml);
}

Declare a public class and the "is a" relationship supporting inheritance of the ServicedComponent and ICRMCallout objects. Add the following lines of code to your project:
```
public class CalloutClass: ServicedComponent, ICRMCallout
```

Define the global variables for the Post Callout project; one for the name of the message queue to be used in the integration, one for the server name, and the other for the message queue object.

// Microsoft CRM custom integration queue
public const string WIP_QUEUE_NAME = @".\private$\mscrmoutqueue";
public static string _server = @"https://localhost/MSCRMServices/";
public static System.Messaging.MessageQueue _queue;

Add a ClassInterface attribute that is an interface that exposes all public methods and properties on the .NET object.
```
[ClassInterface(ClassInterfaceType.AutoDispatch)]
```
Because the Post-Callout must be registered within the Microsoft CRM Metabase, you need to explicitly define and provide the class with a static GUID attribute. To perform this task, you may use the GuidGen utility to create a static GUID for your project. From inside Visual Studio .NET, click Tools, Create GUID, as shown in the following figure.

Figure 4. Create GUID
Ensure that the GUID Format option 4, Registry Format, is selected and click Copy. This adds the newly generated GUID to the clipboard and enables you to paste the GUID into the following line of code:
```
[GuidAttribute("6B4938DE-9B28-4398-8DA0-D731FABCF639")]
```

Specify the code to execute upon the PostCreate event inside of the Post-Callout. Here is some sample code for writing an event to a message queue:

public void PostCreate(int ObjectType, string ObjectId, string 
  OrigObjectXml)
{
   string custTypeCode = String.Empty; 

   try
   {
      // Account Post-Callout logic for customers only
      if (ObjectType == 1)
      {
         custTypeCode = GetCustomerTypeCode(ObjectId);

         if (custTypeCode == "3")
         {
            if 
              (!(System.Messaging.MessageQueue.Exists(WIP_QUEUE_NAME)))
            {
                  System.Messaging.MessageQueue.Create(WIP_QUEUE_NAME);
                  _queue = new 
                    System.Messaging.MessageQueue(WIP_QUEUE_NAME);
            }
            else
            {
               queue = new 
                 System.Messaging.MessageQueue(WIP_QUEUE_NAME);
            }

            queue.DefaultPropertiesToSend.Recoverable = true;
            queue.Send(OrigObjectXml, "2|" + ObjectId + "|" + 
              ObjectType);
            queue.Close();
            queue.Dispose();
         }
      }
   }
   catch(System.Runtime.InteropServices.COMException ex)
   {
      WriteToEventLog(ex.Message);
   }
}

   public static string GetCustomerTypeCode(string accountid)
   {
      string resultXml = String.Empty;
      string result;
      try
      {
         Microsoft.Crm.Platform.Proxy.BizUser bizUser = new BizUser();
         bizUser.Credentials = 
           System.Net.CredentialCache.DefaultCredentials;

         bizUser.Url = _server + "BizUser.srf";

         Microsoft.Crm.Platform.Proxy.CUserAuth userAuth = 
           bizUser.WhoAmI();

         Microsoft.Crm.Platform.Proxy.CRMAccount account = new 
           CRMAccount();
         account.Url = AccountClass.SoapUrl + "CRMAccount.srf"; 
         account.Credentials = 
           System.Net.CredentialCache.DefaultCredentials;

         resultXml = account.Retrieve(userAuth, accountid, "");

         result = GetNodeValue(resultXml, "customertypecode");

         return result;
      }
      catch(System.Web.Services.Protocols.SoapException ex)
      {
         WriteToEventLog(ex.Detail.InnerText);
         return String.Empty;
      }
   }

   public static string GetNodeValue(string input, string searchName)
   {
      if (input == null || searchName == null)
      {
         return String.Empty;
      }

      int rootStart = input.IndexOf('<' + searchName + '>');
      if (rootStart == -1)
      {
         // Could be a node with attributes
         rootStart = input.IndexOf('<' + searchName + ' ');
         if (rootStart == -1)
         {
            return String.Empty;
         }
      }
      int startIndex = input.IndexOf(">", rootStart) + 1;
      int endIndex = input.IndexOf("</" + searchName + '>', 
        startIndex);
      if (endIndex == -1)
      {
         // Empty node
         return String.Empty;
      }
      string rVal = input.Substring(startIndex, endIndex - startIndex);
      // XML also encodes ' to &apos;, which HTML does not.
      // Normalize it here.
      return rVal.Replace( "&apos;", "'" );
   }

Add similar detail for the PostUpdate and PostDelete methods of the ICRMCallout interface to handle actions from update and delete events.
In order to ensure that your assembly is unique and to protect the versioning of the assembly, you need to generate a strong name Key for your project. To perform this task, open the Visual Studio .NET command line utility and type sn -k CrmIntegration.snk. Once the key is generated, you need to add it to the AssemblyInfo.cs file that is part of your project, as shown in the following code:
```
[assembly: AssemblyKeyFile("..\\..\\CrmIntegration.snk")]
```
Build your project by pressing CTRL+SHIFT+B and be sure to copy your project to the Microsoft CRM application server if you haven't already.
From the Visual Studio command line utility on the Microsoft CRM server, open a new command prompt, browse to the bin directory where your project has been built and type RegSvcs CrmIntegration.dll. RegSvcs is a .NET utility that will create the COM+ application referencing the application name specified in your project. Once your COM+ application has been created, you need to make sure that your Post-Callout runs under a context of a valid Microsoft CRM user.
Run the SQL command specified in the MSDN article for Post-Callouts to register your custom object into the EntityEventSubscribers table of the Microsoft CRM Metabase.
Restart IIS and make sure your COM+ application has been started. You should now see messages written to the .\private\mscrmoutqueue message queue on the Microsoft CRM server when creating new account records of type "Customer".

Developing Transact-SQL Stored Procedures

Transact-SQL stored procedures are a compiled set of SQL commands that can be executed against a database. They can be used to encapsulate business logic into a database component and can be executed based on business need. This sample integration uses stored procedures to perform the Create, Update and Retrieve operations against the Northwind database Customers table. Perform the following steps to create the p_insertCustomer stored procedure:

Launch SQL Server Query Analyzer on the database server where the Northwind database resides.
Open a new query (CTRL+N) if one does not exist.
Ensure that the current database is set to the Northwind database.

Enter the following syntax into the query window:

Stored procedure p_iInsertCustomer

IF exists(
SELECT * from SysObjects where name = 'p_insertCustomer' and type='P'
)
BEGIN
  drop proc p_insertCustomer
END
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE p_insertCustomer 
         @CustomerID    nchar(5),
         @CompanyName    nvarchar(40),
         @ContactName    nvarchar(30)   = null,
         @ContactTitle   nvarchar(30)   = null,
         @Address   nvarchar(60)   = null,
         @City      nvarchar(15)   = null,
         @Region      nvarchar(15)   = null,
         @PostalCode   nvarchar(10)   = null,
         @Country   nvarchar(15)   = null,
         @Phone      nvarchar(24)   = null,
         @Fax       nvarchar(25)   = null   
AS
BEGIN
      Insert Customers
      SELECT    @CustomerId,
         @CompanyName,
         @ContactName,   
         @ContactTitle,
         @Address,
         @City,
         @Region,
         @PostalCode,
         @Country,
         @Phone,
         @Fax
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Press F5 or hit the run key to compile the stored procedure into the Northwind database.
Complete similar stored procedures for Update and Delete data persistence operations.

Developing Windows Service Applications

Microsoft Windows Service applications empower developers to create applications that run within their own Windows sessions. Because they do not contain a user interface and they are long-running executables that can be started when the computer starts, Windows Service applications are ideal for developing custom integration applications for Microsoft CRM.

When creating a Windows Service type application in Visual Studio .NET, the code designer provides a template for creating your application. This section focuses on the code used for integration rather than stepping through the code provided in the template. Follow this procedure to create a Windows Service application:

Open Microsoft Visual Studio .NET and create a new Windows Service application named CrmMessageMonitor. Select the location where you want your project to be saved.
Rename your class object to give it relevance to your integration. This scenario uses the name MonitorClass.
In Solution Explorer, access Design view so that you can add an installation component to your project.
Click anywhere within the designer's surface.
In the Description area of the Properties window, click the Add Installer link.
A new class, ProjectInstaller, and two installation components, serviceProcessInstaller1 and serviceInstaller1, are added to your project, and property values for the service are copied to the components.
Switch back to the Code view and add additional references to the following namespaces:
```
using System.Messaging;
using System.Timers;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.Crm.Platform.Proxy;
```
Note If you develop this sample integration with Microsoft CRM 1.0, the last namespace name will be Microsoft.CRM.Proxy.

To begin developing your Windows Service application, you need to define some global variables to be used throughout your custom integration project. Add the following constants to your project:

   private const string SOAP_URL ="https://localhost/MSCrmServices/";
   private const string MSCRM_WIP_QUEUE =@".\private$\mscrmwipqueue";
   private const string NWD_DB_PROVIDER = 
"Data Source=localhost;Initial Catalog=Northwind; user=sa; 
  password=mscrm;";
   private const int   INTERVAL= 30000;  // Milliseconds

Define the following variables to be used with your integration program:

Variable name	Description
_msMQ	A Message Queue object to read the integration queue within the Post Callout
_tTimer	A Timer object used to set the time intervals that will fire our integration code
_sqlServer	A SqlConnection object used to connect to the SQL Server where the Northwind database is stored
_sqlCmd	A SqlCommand object used to execute our integration stored procedures
_bHalted	A Boolean variable used to check whether our application has been stopped
_evtLog	An EventLog object used to keep track of the processing

   // Declare static variables
   private static System.Messaging.MessageQueue _msMQ;
   private static System.Timers.Timer _tTimer;
   private static System.Data.SqlClient.SqlConnection _sqlServer;
   private static System.Data.SqlClient.SqlCommand _sqlCmd;
   private static System.Diagnostics.EventLog _evtLog;
   public  static bool _bHalted = false;

Within the MonitorClass constructor, call the static method InitializeComponent() that will set attributes for your Windows Service application. Later, you will add detail to the InitializeComponent() method.
```
   public MonitorClass()
   {
      InitializeComponent();
   }
```

Examine the main entry point into your Windows service application—the static Main method. The Main method essentially creates an instance of MonitorClass and begins running the service as shown in the following code:

static void Main()
{
   System.ServiceProcess.ServiceBase[] ServicesToRun;
   ServicesToRun = new System.ServiceProcess.ServiceBase[]{new 
     MonitorClass()};
   System.ServiceProcess.ServiceBase.Run(ServicesToRun);
}

Locate the InitializeComponent() method that is called from within the MonitorClass constructor. This method sets attributes for the Windows Service application, as shown in the following code:
```
private void InitializeComponent()
   {
      this.CanPauseAndContinue = true;
      this.ServiceName = "CrmMessageMonitor";
      this.AutoLog = true;
   }
```
Windows Services expose methods that override methods within the base class. These methods fire based upon the state of the existing process and it is in these methods that you will define the constants for your integration process. The four methods that you will incorporate into your project are:
- onStart(): This method fires when the Windows Service application starts. It is in this method that you will initialize the program variables and begin processing the custom integration.
- onStop(): This method fires when the Windows Service application is stopped. Here you can perform application cleanup operations within your custom code.
- onPause(): This method fires when the Windows Service application is paused. Because this is a time-based integration process, you need to pause the Timer object used to manage the integration inside of this event.
- onContinue(): This method fires when the Windows Service application is resumed from a paused state. In the onPause() method, the Timer object used to maintain integration timing was paused, so you must resume the timer in the onContinue() method.
Locate the onStart() method of your Windows Service application. Add the following code to execute each time the CrmMessageMonitor application is started:
```
protected override void OnStart(string[] args)
   {
   if(!(System.Messaging.MessageQueue.Exists(MSCRM_WIP_QUEUE)))
   {
      System.Messaging.MessageQueue.Create(MSCRM_WIP_QUEUE);
   }

   // Process messages on elapsed timer events
   _tTimer = new System.Timers.Timer();
   _tTimer.Elapsed += new 
     System.Timers.ElapsedEventHandler(MonitorWorkQueue);
   _tTimer.Interval = INTERVAL;
   _tTimer.Enabled = true;
   _tTimer.Start();
}
```
This method checks for the existence of the Message Queue used for retrieving messages to be pushed into the Northwind database. In addition, it instantiates a new Timer object to be used throughout the Windows Service application. This timer control is the object that is used to trigger the integration processes. When the timer elapses, it fires ElapsedEventHandler that calls the main entry point into the integration: the MonitorWorkQueue() method.

Add the following code to the MonitorClass of your project:

private static void MonitorWorkQueue(object source, 
  System.Timers.ElapsedEventArgs args)
{
  try
  {
     // Check whether service has been halted; if yes, exit.
     if (_bHalted){return;}
     _tTimer.Stop();
     GetQueueMessages();
     _tTimer.Start();
   }
   catch(System.Exception ex)
   {
   _evtLog.WriteEntry("CrmMessage Monitor failed at method: 
     MonitorWorkQueue with exception: " + ex.Message, 
     EventLogEntryType.Error);
   }

}

public static void GetQueueMessages()
{
   _msMQ = new MessageQueue(MSCRM_WIP_QUEUE);
   System.TimeSpan tSpan = new TimeSpan(0, 0, 0, 0, 30);

   string xml = String.Empty;
   string customerId = String.Empty;
   string[] crmVals;

   try
   {
      foreach(System.Messaging.Message sMessage in 
        _msMQ.GetAllMessages())
      {
         if(_bHalted)
            {return;}

         crmVals = sMessage.Label.ToString().Split('|');   
         xml = ReturnFixedObjectXml(crmVals);   
         CommitToNorthwind(crmVals[0], xml);

         // Remove from MSMQ
         System.Messaging.Message msgTemp;
         msgTemp = _msMQ.ReceiveById(sMessage.Id);
      }
   }
   catch(System.Messaging.MessageQueueException ex)
   {
   _evtLog.WriteEntry("CrmMessage Monitor failed at method: 
     GetQueueMessages with exception: " + ex.Message, 
     EventLogEntryType.Error);
   }
}

The first method, MonitorWorkQueue(), is called upon the elapsed event handler of the Timer object. It calls another method, GetQueueMessages(), that polls the message queue and performs the majority of the integration logic.

GetQueueMessages() checks all of the messages in the message queue used in the integration project, parses the message label, and performs two critical steps:

First it calls the method ReturnFixedObjectXml(), passing in the array of values parsed in the message label.

Second it calls the method CommitToNorthwind(), passing in the action to be taken and the resulting XML string from the ReturnFixedObjectXml() method. The possible actions are: 2 = insert; 8 = update; 32 = delete.

Add the following code to the ReturnFixedObjectXml(), CommitToNorthwind() and GetNodeValue() methods:

public static string ReturnFixedObjectXml(string[] parms)
{
   string ActionCode = parms[0];
   string oId      = parms[1];
   string oType    = parms[2];

   Microsoft.Crm.Platform.Proxy.BizUser bizUser = new
                        Microsoft.Crm.Platform.Proxy.BizUser();
   bizUser.Credentials = System.Net.CredentialCache.DefaultCredentials;
   bizUser.Url = SOAP_URL + "BizUser.srf";

   string fetchXml = String.Empty;
   string resultXml = String.Empty;
   try
   {
      Microsoft.Crm.Platform.Proxy.CUserAuth userAuth = 
        bizUser.WhoAmI();

      Microsoft.Crm.Platform.Proxy.CRMQuery cQuery 
                         = new Microsoft.Crm.Platform.Proxy.CRMQuery();
      cQuery.Credentials = 
        System.Net.CredentialCache.DefaultCredentials;
      cQuery.Url = SOAP_URL + "CRMQuery.srf";

      fetchXml ="<fetch mapping='logical'><entity 
        name='account'><attribute name='name'/><attribute 
        name='accountnumber'/>";
      fetchXml +=  "<attribute name='address1_line1'/><attribute 
        name='address1_city'/><attribute 
        name='address1_stateorprovince'/><attribute 
        name='address1_postalcode'/><attribute 
        name='address1_country'/><attribute 
        name='address1_line1'/><attribute name='telephone1'/><attribute 
        name='fax'/>";
      fetchXml += "<attribute name='primarycontactid'/><link-entity 
        name='contact' to='primarycontactid'>";
      fetchXml += "<attribute name='fullname'/><attribute 
        name='jobtitle'/></link-entity><filter type='and'>";
      fetchXml += "<condition attribute='accountid' operator='eq' 
        value='" +  oId + "' /></filter></entity></fetch>";

      resultXml = cQuery.ExecuteQuery(userAuth, fetchXml);

      return resultXml;
   }
   catch(System.Web.Services.Protocols.SoapException ex)
   {

      _evtLog.WriteEntry(ex.Message, EventLogEntryType.Error);
      return "";
   }
}

public static void CommitToNorthwind(string actionCode, string 
  fixedXml)
{
   _sqlServer = new 
     System.Data.SqlClient.SqlConnection(NWD_DB_PROVIDER);
   _sqlServer.Open();

   string sproc = String.Empty;

   try
   {
      switch(actionCode)
      {
         case "2":
            sproc = "p_insertCustomer";
            break;
         case "8":
            sproc = "p_updateCustomer";
            break;
         case "32":
            sproc = "p_deleteCustomer";
            break;
         default:
            sproc = "";
            break;
      }

      if ((sproc != "") || (!_bHalted)) 
      {

         _sqlCmd = new System.Data.SqlClient.SqlCommand(sproc, 
           _sqlServer);
         _sqlCmd.CommandType = CommandType.StoredProcedure;
         _sqlCmd.Parameters.Add("@CustomerID", 
           System.Data.SqlDbType.NChar, 5);
         _sqlCmd.Parameters[0].Value = GetNodeValue(fixedXml, 
           "accountnumber");
         _sqlCmd.Parameters.Add("@CompanyName", 
           System.Data.SqlDbType.NVarChar, 40);
         _sqlCmd.Parameters[1].Value = GetNodeValue(fixedXml, "name");
         _sqlCmd.Parameters.Add("@ContactName", 
           System.Data.SqlDbType.NVarChar, 30);
         _sqlCmd.Parameters[2].Value = GetNodeValue(fixedXml, "");
         _sqlCmd.Parameters.Add("@ContactTitle", 
           System.Data.SqlDbType.NVarChar, 30);
         _sqlCmd.Parameters[3].Value = GetNodeValue(fixedXml, "");
         _sqlCmd.Parameters.Add("@Address", 
           System.Data.SqlDbType.NVarChar, 60);
         _sqlCmd.Parameters[4].Value = GetNodeValue(fixedXml, "");
         _sqlCmd.Parameters.Add("@City", 
           System.Data.SqlDbType.NVarChar, 15);
         _sqlCmd.Parameters[5].Value = GetNodeValue(fixedXml, "");
         _sqlCmd.Parameters.Add("@Region", 
           System.Data.SqlDbType.NVarChar, 15);
         _sqlCmd.Parameters[6].Value = GetNodeValue(fixedXml, "");
         _sqlCmd.Parameters.Add("@PostalCode", 
           System.Data.SqlDbType.NVarChar, 10);
         _sqlCmd.Parameters[7].Value = GetNodeValue(fixedXml, "");
         _sqlCmd.Parameters.Add("@Country", 
           System.Data.SqlDbType.NVarChar, 15);
         _sqlCmd.Parameters[8].Value = GetNodeValue(fixedXml, "");
         _sqlCmd.Parameters.Add("@Phone", 
           System.Data.SqlDbType.NVarChar, 24);
   _sqlCmd.Parameters[9].Value = GetNodeValue(fixedXml, "telephone1");
         _sqlCmd.Parameters.Add("@Fax", 
           System.Data.SqlDbType.NVarChar,24);
         _sqlCmd.Parameters[10].Value = GetNodeValue(fixedXml, "fax");

         _sqlCmd.ExecuteScalar();
         _sqlCmd.Dispose();
         _sqlServer.Close();
         _sqlServer.Dispose();

      }
      else
      {
         return;
      }
   }
   catch(System.Data.SqlClient.SqlException ex)
   {
      _sqlServer.Close();
      _sqlServer.Dispose();
      _evtLog.WriteEntry("CrmMessage Monitor failed at method: 
        CommitToNorthind with SqlException: " + ex.Message, 
        EventLogEntryType.Error);
   }
   catch(System.Exception ex)
   {
      _sqlServer.Close();
      _sqlServer.Dispose();
      _evtLog.WriteEntry("CrmMessage Monitor failed at method: 
        CommitToNorthwind with exception: " + ex.Message, 
        EventLogEntryType.Error);
   }
}
public static string GetNodeValue(string input, string searchName)
{
   if (input == null || searchName == null)
   {
      return String.Empty;
   }
   int rootStart = input.IndexOf('<' + searchName + '>');
   if (rootStart == -1)
   {
      // Could be a node with attributes
      rootStart = input.IndexOf('<' + searchName + ' ');
      if (rootStart == -1)
      {
         return String.Empty;
      }
   }
   int startIndex = input.IndexOf(">", rootStart) + 1;
   int endIndex = input.IndexOf("</" + searchName + '>', startIndex);
   if (endIndex == -1)
   {
      // Empty node
      return String.Empty;
   }
   string rVal = input.Substring(startIndex, endIndex - startIndex);
   // XML also encodes ' to &apos;, which HTML does not.  
   // Normalize it here.
   return rVal.Replace( "&apos;", "'" );
}

These three methods perform the bulk of the integration processing.

ReturnFixedObjectXml() creates a Microsoft CRM query object and uses the FetchXml language to query the Account and Contact objects within Microsoft CRM. The method returns a pre-defined XML string that can be parsed in the CommitToNorthwind() method to commit data to the external database. Additional FetchXml references can be found in the Microsoft CRM SDK.

CommitToNorthwind() creates the SQLConnection and SQLCommand objects used to call the integration stored procedure created in the previous section. Within this method, the public method GetNodeValue() is called to parse the XML string and retrieve the node values received from the CRM object query. In addition, the CommitToNorthwind() method determines which operation to perform based upon the actionCode variable passed into the method. The operations supported by this method (Create = 2, Update = 8, Delete = 32) follow the same enumeration constants used in the Microsoft CRM platform.

GetNodeValue() is a lightweight method that parses an XML string and returns the node value of the search string variable. This method uses string parsing techniques as opposed to using XPath queries for returning the requested node values.

With the majority of the integration code provided in the onStart() event of your Windows Service integration, you can now add the following code to the remaining events so that the service is easily administered and responds to the actions enabled in the constructor:

   protected override void OnStop()
   {
      _bHalted = true;
      _tTimer.Stop();

   }
   protected override void OnPause()
   {
      _bHalted = true;
      _tTimer.Stop();

   }
   protected override void OnContinue()
   {
      _bHalted = false;
      _tTimer.Start();
   }

Within these methods, you are essentially setting the associated properties of the two global variables _bHalted and _tTimer so that they hold values that are congruent with the appropriate action.
Build your project by pressing CTRL+SHIFT+B and resolve any debugging errors that may have occurred in development.
Open the Visual Studio .NET command line utility, browse to the directory where your application executable is stored and type installutil CrmMessageMonitor.exe. InstallUtil.exe is a command line utility that lets you quickly install and uninstall your service application.
To ensure that your service has been installed successfully, you can launch the server explorer and see the CrmMessageMonitor Windows Service installed on your server.
Right-click the CrmMessageMonitor Windows Service and click Start. This will start the newly created application.

Testing our Custom Integration

To test the newly created integration, and prior to starting your integration service, ensure that all Northwind customers are loaded as Microsoft CRM Account records. Once this pre-requisite has been met, start the CrmMessageMonitor.exe service and watch as newly created and updated customer records are written from Microsoft CRM to the Northwind database.

Other Integration Alternatives

Using the same integration design methodology, integration developers could quickly alter the provided integration code to leverage the power of Microsoft BizTalk Server and add additional methods to the MonitorClass object to create bi-directional integration between the two systems.

Conclusion

Creating custom systems integration between Microsoft CRM and other applications requires appropriate design and dedicated resources to ensure that planned objectives are met. With tools such as a fully documented Software Development Kit (SDK), an application platform based on standard Microsoft .NET development technologies, and the ability to leverage additional Microsoft technologies as part of the architecture design, integration developers can quickly implement the required integration strategies to ensure that Microsoft CRM integration is a success.