Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM: Creating a CLR assembly and working with CRM web methods

Before we get started, there are a few rules: a) I assume you have good working knowledge of both Microsoft Dynamics GP and Microsoft CRM and that you know enough about the Item Master in GP and the Product entity in CRM, b) you are familiar with Microsoft Visual Studio and can create class libraries and create Web references, c) you have done some coding with either VB.NET and/or C#, and d) you will not ask me if I have the same code snippets in VB.NET. As I have said in multiple occassions -- no offense to VB developers -- when I work on commercial grade code I will choose C# over VB.NET any day of the week.

A bit of a reminder of the objective of today's code: a) we will create our CLR methods that will serve as bridge to the Microsoft CRM web methods. The resulting assembly will be registered on SQL Server with the CLR methods exposed as stored procedures that can be called from a trigger, and b) we will create the code that will allow us to establish a connection to Microsoft CRM and in turn insert a new or update an existing Product in CRM.

We begin by creating a class library project and renaming our default class library file to clrProcedures.cs. Once this is done, we can start declaring all namespaces to help us control the scope of class and method names that we will be using throughout the project. In particular, SQL Server CLR methods will benefit from using the Microsoft.SqlServer.Server namespace contained in the System.Data.dll assembly.

clrProcedures.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web.Services.Protocols;
using Microsoft.SqlServer.Server;
using Crm.Integration;

Also note that in the above code, I have declared the Crm.Integration namespace. This namespace will be created as a new Class Library file (Crm.Integration.cs) within our project further on in this article.

We must now implement the clrProcedures class. One note about CLR methods is that they are not encapsulated within a namespace and rather begin with the class declaration. This behavior is by design. Within our clrProcedures class, we will create a method, CreateProduct, that can be registered as a stored procedure in SQL Server. We will declare all the parameters that will be passed to the stored procedure. I believe these are pretty self-explanatory, but if you have any questions please follow up with a comment on the article.

public class clrProcedures
{
    [SqlProcedure]
    public static void CreateProduct(
          string itemNumber
        , string itemDescription
        , string vendorName
        , string vendorItem
        , decimal itemShipWeight
        , string defaultUnitOfMeasure
        , string defaultUnitOfMeasureSched
        , string defaultPriceLevel
        , string currencyID
        , int decimalsSupported
        , decimal quantityOnHand
        , decimal unitPrice
        , decimal priceLevelPrice
        , decimal standardcost
        , decimal currentCost
        , int productTypeCode
    )

Now we will proceed to create a few local variables, particularly the CRM server name, the CRM server port, and CRM Organization Name. These will be passed to our connection method to, well, open a connection to CRM. These values are read from a custom SQL table, dbo.crmInfo, in our company database. You may ask, why not create these values in a configuration file? One of the goals for my client was to provide easy access to database administrators to quickly reconfigure CRM server names and organization names without having to bother the network administrators, so it was easier to store this information in a table. In turn, our configuration file would be left to the network adminstrators to configure the address of the CRM web services as needed. My client is a public company and required segregation of duties between database admins and network admins.

    {
        string crmServerName, CrmServerPort, CrmOrgName;
        string sSQL = "SELECT CrmServerName, CrmServerPort, CrmOrgName FROM crmInfo";

        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(sSQL, connection);
            SqlDataReader r = command.ExecuteReader();

            r.Read();
            crmServerName = Convert.ToString(r["CrmServerName"]);
            CrmServerPort = Convert.ToString(r["CrmServerPort"]);
            CrmOrgName = Convert.ToString(r["CrmOrgName"]);
        }

Now that we have queried our CRM server settings, we can establish a connection to CRM. Our CRM authentication is done via Active Directory. This is important to know when using CLR methods as the SQL Server service startup credentials will be passed to the CRM connection. Hence, the SQL Server service account must exist in the CRM users and be associated to a role that has access to create Products in CRM. Suffice to say, we will be expanding on the crmIntegration class later and the crmConnection() and crmInsertProduct() methods.

        //create an instance of the crm integration class
        crmIntegration crmInt = new crmIntegration();

        try
        {
            // Establish connection with CRM server
            crmInt.crmConnection(crmServerName, CrmServerPort, CrmOrgName);

            // Insert product
            crmInt.crmInsertProduct(
                    itemNumber.Trim()
                    , itemDescription.Trim()
                    , vendorName.Trim()
                    , vendorItem.Trim()
                    , itemShipWeight
                    , defaultUnitOfMeasure.Trim()
                    , defaultUnitOfMeasureSched.Trim()
                    , defaultPriceLevel.Trim()
                    , currencyID.Trim()
                    , decimalsSupported
                    , quantityOnHand
                    , unitPrice
                    , priceLevelPrice
                    , standardcost
                    , currentCost
                    , productTypeCode
            );
        }
        catch (System.Exception ex)
        {
            if (ex.InnerException != null)
            {
                SqlContext.Pipe.Send("Exception occurred: " + ex.InnerException.Message);

                SoapException se = ex.InnerException as SoapException;
                if (se != null)
                    SqlContext.Pipe.Send("Exception detail: " + se.Detail.InnerText);
            }
        }
        finally
        {
            //do something else here
        }
    }
}

Since one of the main concerns of the client was the ability to upgrade with each new release of CRM, we made use of the CRM web services provided by the Microsoft CRM 4.0 SDK. For this, we will add a new class library to our project and call it Crm.Integration.cs which will implement the Crm.Integration namespace and the CrmIntegration class. But first, we must create two web references: one for the CRM Service, contained under the CrmSdk namespace and one for the CRM Discovery Service, contained under the CrmDiscovery namespace.

Crm.Integration.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Web.Services.Protocols;
using CrmDiscovery;
using CrmSdk;

Now that we have declared our namespaces, we can proceed to implement the crmIntegration class. The first method to be implemented will be the connection method. This method contains all the code needed to use the Discovery service to obtain the correct URL of the CrmService Web service for your organization. The code then sends a WhoAmI request to the service to verify that the user has been successfully authenticated. This method consists of three specific operations: a) instantiate and configure the CRMDiscovery Web service, b) Retrieve the organization name and endpoint Url from the CrmDiscovery Web service, and c) create and configure an instance of the CrmService Web service.

namespace Crm.Integration
{
    public class crmIntegration
    {
        CrmService crmService;

        // Establishes a connection to CRM
        public void crmConnection(string hostName, string hostPort, string orgName)
        {
            try
            {
                // STEP 1: Instantiate and configure the CrmDiscoveryService Web service.

                CrmDiscoveryService discoveryService = new CrmDiscoveryService();
                discoveryService.UseDefaultCredentials = true;
                discoveryService.Url = String.Format(
                    "http://{0}:{1}/MSCRMServices/2007/{2}/CrmDiscoveryService.asmx",
                    hostName, hostPort, "AD");

                // STEP 2: Retrieve the organization name and endpoint Url from the
                // CrmDiscoveryService Web service.
                RetrieveOrganizationsRequest orgRequest = new RetrieveOrganizationsRequest();
                RetrieveOrganizationsResponse orgResponse =
                    (RetrieveOrganizationsResponse)discoveryService.Execute(orgRequest);

                String orgUniqueName = String.Empty;
                OrganizationDetail orgInfo = null;

                foreach (OrganizationDetail orgDetail in orgResponse.OrganizationDetails)
                {
                    if (orgDetail.FriendlyName.Equals(orgName))
                    {
                        orgInfo = orgDetail;
                        orgUniqueName = orgInfo.OrganizationName;
                        break;
                    }
                }

                if (orgInfo == null)
                    throw new Exception("The organization name is invalid.");

                // STEP 3: Create and configure an instance of the CrmService Web service.

                CrmAuthenticationToken token = new CrmAuthenticationToken();
                token.AuthenticationType = 0;
                token.OrganizationName = orgUniqueName;

                crmService = new CrmService();
                crmService.Url = orgInfo.CrmServiceUrl;
                crmService.CrmAuthenticationTokenValue = token;
                crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;

                // STEP 4: Invoke CrmService Web service methods.

                WhoAmIRequest whoRequest = new WhoAmIRequest();
                WhoAmIResponse whoResponse = (WhoAmIResponse)crmService.Execute(whoRequest);

            }
            // Handle any Web service exceptions that might be thrown.
            catch (SoapException ex)
            {
                throw new Exception("An error occurred while attempting to authenticate.", ex);
            }
        }

For more information on using the CRM Discovery service with Active Directory authentication, click here. Following the authentication process, we can now implement the method that will insert or update a product in the Product entity.

        // Insert product method
        public void crmInsertProduct(
            string productNumber,
            string productName,
            string productVendorName,
            string productVendorItem,
            decimal productWeight,
            string defaultUnitOfMeasure,
            string defaultUnitOfMeasureSched,
            string defaultPriceLevel,
            string currencyID,
            int decimalsSupported,
            decimal quantityOnHand,
            decimal listPrice,
            decimal priceLevelPrice,
            decimal standardCost,
            decimal currentCost,
            int productTypeCode)
        {
            try
            {
                string strProductId;
                product crmProduct = new product();

                bool found = crmGetProduct(productNumber, out strProductId);
                if (!found)
                {
                    // is a new product, create
                    crmProduct.productnumber = productNumber;
                    crmProduct.name = productName;

                    // quantity decimal places
                    crmProduct.quantitydecimal = new CrmNumber();
                    crmProduct.quantitydecimal.Value = decimalsSupported;

                    // quantity on hand
                    crmProduct.quantityonhand = new CrmDecimal();
                    crmProduct.quantityonhand.Value = quantityOnHand;

                    // unit price
                    crmProduct.price = new CrmMoney();
                    crmProduct.price.Value = listPrice;

                    // standard cost
                    crmProduct.standardcost = new CrmMoney();
                    crmProduct.standardcost.Value = standardCost;

                    // Current cost
                    crmProduct.currentcost = new CrmMoney();
                    crmProduct.currentcost.Value = currentCost;

                    // Vendor Name
                    crmProduct.vendorname = productVendorName;

                    // Vendor Item
                    crmProduct.vendorpartnumber = productVendorItem;

                    // Shipping Weight
                    crmProduct.stockweight = new CrmDecimal();
                    crmProduct.stockweight.Value = productWeight;

                    //------------------------------------------------//
                    // Product type code                              //
                    //------------------------------------------------//
                    crmProduct.producttypecode = new Picklist();
                    if (productTypeCode != 0)
                        crmProduct.producttypecode.Value = productTypeCode;
                    else
                        crmProduct.producttypecode.IsNull = true;


                    // retrieve guid's for the default unit of measure
                    string strUofM;
                    string strUofMSched;

                    bool isUofM = crmGetUofM(defaultUnitOfMeasure, out strUofM, out strUofMSched);
                    if (isUofM)
                    {
                        crmProduct.defaultuomid = new Lookup();
                        crmProduct.defaultuomid.Value = new Guid(strUofM);
                        crmProduct.defaultuomid.type = EntityName.uom.ToString();

                        crmProduct.defaultuomscheduleid = new Lookup();
                        crmProduct.defaultuomscheduleid.Value = new Guid(strUofMSched);
                        crmProduct.defaultuomscheduleid.type = EntityName.uomschedule.ToString();
                    }

                    // create the product
                    Guid productId = crmService.Create(crmProduct);

                    // create pricelist
                    crmInsertProductPricelist(productNumber, defaultUnitOfMeasure, defaultUnitOfMeasureSched, defaultPriceLevel, currencyID, 1, priceLevelPrice, 0);

                    // Create the column set object that indicates the fields to be retrieved.
                    ColumnSet columns = new ColumnSet();
                    columns.Attributes = new string[] { "productid", "pricelevelid" };

                    // Retrieve the product from Microsoft Dynamics CRM
                    // using the ID of the record that was retrieved.
                    // The EntityName indicates the EntityType of the object being retrieved.
                    product updatedProduct = (product)crmService.Retrieve(EntityName.product.ToString(), productId, columns);
                    updatedProduct.pricelevelid = new Lookup();

                    string guidPriceLevel;
                    bool isPricelevel = crmGetPriceLevel(defaultPriceLevel.ToUpper(), out guidPriceLevel);
                    if (isPricelevel)
                    {
                        updatedProduct.pricelevelid = new Lookup();
                        updatedProduct.pricelevelid.Value = new Guid(guidPriceLevel);
                        updatedProduct.pricelevelid.type = EntityName.pricelevel.ToString();

                    }

                    // update the record
                    crmService.Update(updatedProduct);
                }
                else
                {
                    // Create the column set object that indicates the fields to be retrieved.
                    ColumnSet columns = new ColumnSet();
                    columns.Attributes = new string[] { "productid", "name", "quantityonhand", "price", "standardcost", "currentcost", "defaultuomid", "defaultuomscheduleid" };

                    // Retrieve the product from Microsoft Dynamics CRM
                    // using the ID of the record that was retrieved.
                    // The EntityName indicates the EntityType of the object being retrieved.
                    Guid _productGuid = new Guid(strProductId);
                    product updatedProduct = (product)crmService.Retrieve(EntityName.product.ToString(), _productGuid, columns);

                    updatedProduct.name = productName;

                    // quantity decimal places
                    updatedProduct.quantitydecimal = new CrmNumber();
                    updatedProduct.quantitydecimal.Value = decimalsSupported;

                    // quantity on hand
                    updatedProduct.quantityonhand = new CrmDecimal();
                    updatedProduct.quantityonhand.Value = quantityOnHand;

                    // unit price
                    updatedProduct.price = new CrmMoney();
                    updatedProduct.price.Value = listPrice;

                    // standard cost
                    updatedProduct.standardcost = new CrmMoney();
                    updatedProduct.standardcost.Value = standardCost;

                    // Current cost
                    updatedProduct.currentcost = new CrmMoney();
                    updatedProduct.currentcost.Value = currentCost;

                    // Vendor Name
                    updatedProduct.vendorname = productVendorName;

                    // Vendor Item
                    updatedProduct.vendorpartnumber = productVendorItem;

                    // Shipping Weight
                    updatedProduct.stockweight = new CrmDecimal();
                    updatedProduct.stockweight.Value = productWeight;

                    //------------------------------------------------//
                    // Product type code                              //
                    //------------------------------------------------//
                    updatedProduct.producttypecode = new Picklist();
                    if (productTypeCode != 0)
                        updatedProduct.producttypecode.Value = productTypeCode;
                    else
                        updatedProduct.producttypecode.IsNull = true;

                    // retrieve guid's for the default unit of measure
                    string strUofM;
                    string strUofMSched;

                    bool isUofM = crmGetUofM(defaultUnitOfMeasure, out strUofM, out strUofMSched);
                    if (isUofM)
                    {
                        updatedProduct.defaultuomid = new Lookup();
                        updatedProduct.defaultuomid.Value = new Guid(strUofM);
                        updatedProduct.defaultuomid.type = EntityName.uom.ToString();

                        updatedProduct.defaultuomscheduleid = new Lookup();
                        updatedProduct.defaultuomscheduleid.Value = new Guid(strUofMSched);
                        updatedProduct.defaultuomscheduleid.type = EntityName.uomschedule.ToString();
                    }

                    string guidPriceLevel;
                    bool isPricelevel = crmGetPriceLevel(defaultPriceLevel.ToUpper(), out guidPriceLevel);
                    if (isPricelevel)
                    {
                        updatedProduct.pricelevelid = new Lookup();
                        updatedProduct.pricelevelid.Value = new Guid(guidPriceLevel);
                        updatedProduct.pricelevelid.type = EntityName.pricelevel.ToString();

                    }

                    // create pricelist
                    crmInsertProductPricelist(productNumber, defaultUnitOfMeasure, defaultUnitOfMeasureSched, defaultPriceLevel, currencyID, 1, priceLevelPrice, 0);

                    // update the record
                    crmService.Update(updatedProduct);
                }
            }
            catch (SoapException ex)
            {
                throw new Exception("An error occurred while attempting to insert a record in the CRM product entity.", ex);
            }
        }

In order to establish whether a product should be inserted or updated in the Product entity, you must first lookup the product. That's accomplished by invoking the crmGetProduct() method (to be implemented below). If the product is not found in the catalog, we can proceed to setup all the attributes to be inserted, then call the crmService.Create() method.

If the product is found, then we can just retrieve all the columns that will be subsequently updated, then invoke the crmService.Update() method to commit the changes.

Finally, the crmGetProduct() method is shown below:

        public bool crmGetProduct(string productNumber, out string pId)
        {
            pId = null;

            ConditionExpression condition1 = new ConditionExpression();
            condition1.AttributeName = "productnumber";
            condition1.Operator = ConditionOperator.Equal;
            condition1.Values = new string[] { productNumber };

            FilterExpression filter = new FilterExpression();
            filter.FilterOperator = LogicalOperator.And;
            filter.Conditions = new ConditionExpression[] { condition1 };

            ColumnSet resultSetColumns = new ColumnSet();
            resultSetColumns.Attributes = new string[] { "productid", "productnumber" };

            // Put everything together in an expression.
            QueryExpression qryExpression = new QueryExpression();
            qryExpression.ColumnSet = resultSetColumns;

            // set a filter to the query
            qryExpression.Criteria = filter;

            // Set the table to query.
            qryExpression.EntityName = EntityName.product.ToString();

            // Return distinct records.
            qryExpression.Distinct = true;

            // Execute the query.
            BusinessEntityCollection productResultSet = crmService.RetrieveMultiple(qryExpression);

            // Validate that an expected contact was returned.
            if (productResultSet.BusinessEntities.Length == 0)
                return false;
            else
            {
                bool productFound = false;
                foreach (product aProduct in productResultSet.BusinessEntities)
                {
                    if (aProduct.productnumber.ToUpper().Trim().Equals(productNumber.ToUpper()))
                    {
                        productFound = true;
                        pId = aProduct.productid.Value.ToString();
                        break;
                    }
                }

                return productFound;
            }
        }
    }
}

The beauty about Microsoft Dynamics CRM platform services is that it provides a number of methods and implementations that facilitate querying any piece of data stored in the platform. The above method shows the use of the ConditionExpression, FilterExpression and QueryExpression classes, that when combined together, form the basis of the query platform. Finally we can create a collection with the filtered Product entity and navigate to see if the product was found.

This completes the first part of our implementation, but here are some final notes and things that I discovered throughout the project:

1. Assemblies that will be registered against SQL Server require signing. You must create a strong name key file that will be used to sign your assembly. To do this, go to the project Properties and select the Signing tab.

2. You cannot simply register an assembly that references a Web service against SQL Server without creating an XML serialization assembly. Serialization assemblies improve the startup performance of the Web service calls. To do this, go the project Properties and select the Build tab. Select On from the Generate Serialization Assembly drop down list.

Keep in mind that the above code is only provided as a sample and that other implementations are required to deal with Unit of Measures and Price Schedules. The bottom line is, the crmGetProduct() method provides the basis for the implementation of the other methods not shown.

Friday, I will show you how to register the assemblies on Microsoft SQL Server and how to implement some basic triggers that will exploit the CLR stored procedures.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/