In-Memory XML Serialization with eConnect 10

December 2, 2010

Over at MBS Guru, my friend Bryan Prince demonstrates a technique to perform in-memory XML serialization when working with eConnect. Bryan’s technique is very helpful especially when working in environments where disk access and/or disk permissions can become an issue.

If you ever needed a cool piece of code for your eConnect projects, this is it! On a personal note…I had a chance to work on a project briefly with Bryan and I won’t be surprised he will be publishing some other cool life saving techniques he used at our client.

Until next post!

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


How to change your Microsoft Dynamics GP 2010 Map Services

November 12, 2010

In recent weeks, the newsgroups have been flooded with questions about the Bing Maps service not working at the street level for addresses in Microsoft Dynamics GP 2010 – see Microsoft Dynamics GP 2010 map buttons not drilling down to the street level. While this issue is slated to be fixed in Microsoft Dynamics GP 2010 Service Pack 2, the bottom line is, it has left a bit of a bad taste in users relying on this functionality.

In addition, some users have questioned whether they can use a map service of their choice. There are a few well known services out there, for example Yahoo! Maps, Google Maps, or even some region specific services like Australia’s WhereIs.com.

The good news is the Microsoft Dynamics GP community is full of individuals who are willing to share their knowledge without any restrictions and my buddy Jon Eastman at Touchstone Group, a Microsoft partner in the United Kindom offers this Dexterity based solution.

The Dexterity solution implements an AFTER trigger on the FormatWebAddress() function of the of the syMapPoint form. The processing function, GenerateGoogleMapsURL(), overrides the formatted address returned by the FormatWebAddress() function based on the address parameters. The Google Maps URL is then masked with the correct parameters and returned by the function for Microsoft Dynamics GP call to the browser application.

GenerateGoogleMapsURL()

{ function GenerateGoogleMapsURL

Created by Jon Eastman, Touchstone Group
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
}
function returns string sWebAddress;

in string sAddress;
in string sCity;
in string sState;
in string sZip;
in string sCountry;

sWebAddress = "http:\\maps.google.com\maps?q=" + FormatSegment(sAddress) of form syMapPoint +
"," + FormatSegment(sCity) of form syMapPoint +
"," + FormatSegment(sState) of form syMapPoint +
"," + FormatSegment(sZip) of form syMapPoint +
"," + FormatSegment(sCountry) of form syMapPoint;

Finally, we need to register our function trigger in the Startup script for the Runtime Engine to recognize our integrating solution event.

Startup

{ Startup
Created by Jon Eastman, Touchstone Group
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
}
local integer l_result;

l_result = Trigger_RegisterFunction(function FormatWebAddress of form syMapPoint, TRIGGER_AFTER_ORIGINAL, function GenerateGoogleMapsURL);

So, I figured, this is way cool! So why not implement the Visual Studio Tools for Microsoft Dynamics GP version of it? This would give me a chance to showcase the new event registration methods for functions and procedures.

The following C# code shows the event registration using Visual Studio Tools, but this time using Yahoo! Maps:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Dexterity.Bridge;
using Microsoft.Dexterity.Applications;
using Microsoft.Dexterity.Applications.DynamicsDictionary;

namespace sampleMapService
{
public class GPAddIn : IDexterityAddIn
{
// IDexterityAddIn interface
SyMapPointForm mapService;

public void Initialize()
{
Dynamics.Forms.SyMapPoint.Functions.FormatWebAddress.InvokeAfterOriginal += new SyMapPointForm.FormatWebAddressFunction.InvokeEventHandler(FormatWebAddress_InvokeAfterOriginal);

}

void FormatWebAddress_InvokeAfterOriginal(object sender, SyMapPointForm.FormatWebAddressFunction.InvokeEventArgs e)
{
mapService = Dynamics.Forms.SyMapPoint;

e.result = "http:\\\\maps.yahoo.com\\map?q1=" + mapService.Functions.FormatSegment.Invoke(e.inParam1) +
"+" + mapService.Functions.FormatSegment.Invoke(e.inParam2) +
"+" + mapService.Functions.FormatSegment.Invoke(e.inParam3) +
"+" + mapService.Functions.FormatSegment.Invoke(e.inParam4) +
"+" + mapService.Functions.FormatSegment.Invoke(e.inParam5);

}
}
}

In the above code, we register an InvokeAfterOriginal event, which is very similar to the trigger registration created in Dexterity. Once the event is registered, the actual method, FormatWebAddress_InvokeAfterOriginal() is implemented by reformatting the web address using the event arguments passed by Microsoft Dynamics GP to our method.

Hope you find these two approaches useful. For more information on these development methods, please visit the Learning Resources page on this blog or visit Developing for Dynamics GP.

Downloads

VST Google Maps solution – Click here
VST Yahoo! Maps solution – Click here
VST Sample Map Service Source Code – Click here

Until next post!

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


The Open XML SDK 2.0 for Microsoft Office

October 1, 2010

Along with the introduction of Microsoft Dynamics GP 2010 Word Templates came a little known software development kit: Open XML SDK 2.0.

Open XML is an open ECMA 376 standard and is also approved as the ISO/IEC 29500 standard that defines a set of XML schemas for representing spreadsheets, charts, presentations, and word processing documents. Microsoft Office 2007 and Microsoft Office 2010 all use Open XML as the default file format for rendering spreadsheets, documents, and presentations.

The Open XML file formats are useful for developers because they use an open standard and are based on well-known technologies: ZIP and XML.

The Open XML SDK 2.0 for Microsoft Office is built on top of the System.IO.Packaging API and provides strongly typed part classes to manipulate Open XML documents. The SDK also uses the .NET Framework Language-Integrated Query (LINQ) technology to provide strongly typed object access to the XML content inside the parts of Open XML documents.

In the case of Microsoft Dynamics GP 2010, the assembly enables developers to easily read-from and write-to Microsoft Word documents using their favorite language in Microsoft Visual Studio. The following image depicts the assembly in the Global Assembly Cache (GAC).

You can find more examples and a cool tool available at the Open XML SDK 2.0 download site. Using this tool, Rob Wagner was able to compare the original template shipped with GP and the modified version I created, when troubleshooting some issues I was having when adding a new section to the original template – see Debugging Microsoft Dynamics GP 2010 Word Templates.
Using the Open XML to work with Word documents

The following C# console application demonstrates how to use OpenXML to add a new table to the end of a document (similar to the text added in the “terms and conditions” article).

//---------------------------------------------------------------------
//  This file is a Microsoft Dynamics GP Business Intelligence Code Sample.
//
//  Copyright (C) Microsoft Corporation.  All rights reserved.
//
//This source code is intended only as a supplement to Microsoft
//Development Tools and/or on-line documentation.  See these other
//materials for detailed information regarding Microsoft code samples.
//
//THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY
//KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
//IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//PARTICULAR PURPOSE.
//---------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using wp = DocumentFormat.OpenXml.Wordprocessing;

namespace NewTableAdHocText
{
    class Program
    {
        public bool AddTableAdHocText(string WordDocPath, string TextPath)
        {
            try
            {
                //read the ad-hoc text and store it for later
                StreamReader srStreamReader = new StreamReader(TextPath);
                String sString = srStreamReader.ReadToEnd();

                //open the word document and it's main document part
                using (WordprocessingDocument wdWordDoc = WordprocessingDocument.Open(WordDocPath, true))
                {
                    MainDocumentPart mdpWordDocMainPart = wdWordDoc.MainDocumentPart;
                    //create then add a new paragraph
                    wp.Paragraph wppParagraph = new wp.Paragraph(new wp.ParagraphProperties(new wp.ParagraphStyleId() { Val = "NoSpacing" }));
                    mdpWordDocMainPart.Document.Body.Append(wppParagraph);
                    //create new markup for the table
                    Table wppTable = new Table(
                        new TableProperties(
                            new TableStyle() { Val = "TableGrid" },
                            new TableWidth() { Width = "0", Type = TableWidthUnitValues.Auto },
                        new wp.TableBorders(
                            new TopBorder() { Val = BorderValues.None },
                            new LeftBorder() { Val = BorderValues.None },
                            new BottomBorder() { Val = BorderValues.None },
                            new RightBorder() { Val = BorderValues.None, },
                            new InsideHorizontalBorder() { Val = wp.BorderValues.None },
                            new InsideVerticalBorder() { Val = wp.BorderValues.None }),
                        new TableLook() { Val = "04A0", FirstRow = true, LastRow = false, FirstColumn = true, LastColumn = false, NoHorizontalBand = false, NoVerticalBand = true }),
                        new TableGrid(
                            new GridColumn() { Width = "11016" }),
                        new TableRow(
                            new TableCell(
                                new TableCellProperties(
                                    new TableCellWidth() { Width = "11016", Type = wp.TableWidthUnitValues.Dxa }),
                                    new Paragraph(
                                        new ParagraphProperties(
                                            new ParagraphStyleId() { Val = "NoSpacing" },
                                            new KeepLines(),
                                            new PageBreakBefore(),
                                            new ParagraphMarkRunProperties(
                                                new RunFonts() { Ascii = "Trebuchet MS", HighAnsi = "Trebuchet MS" },
                                                new FontSize() { Val = "17" },
                                                new FontSizeComplexScript() { Val = "17" }),
                                            new LastRenderedPageBreak(),
                                            new Run(
                                                new RunProperties(
                                                    new RunFonts() { Ascii = "Trebuchet MS", HighAnsi = "Trebuchet MS" },
                                                    new FontSize() { Val = "17" },
                                                    new FontSizeComplexScript() { Val = "17" },
                                                new Text(sString)))))))); //text for the run comes from the adhoc text document
                    //add then save the table to the document
                    mdpWordDocMainPart.Document.Body.Append(wppTable);
                    mdpWordDocMainPart.Document.Save();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.InnerException);
                return false; //failure
            }
            return true;
        }

        static void Main(string[] args)
        {
            new Program().AddTableAdHocText(args[0], args[1]);
        }
    }
}

The above example shows how simple it is to:

1) Crack open a word document.
2) Append a table to the end with specific properties.
3) Insert some ad-hoc text into the table.

The major advantage is the strongly typed interface to office document. You don’t need COM interoperability to manipulate any document.

You can download the sample project at the bottom of this article.

Downloads

NewTableAdhocText.zip – C# command line application to demonstrate adding a table at the end of a Word Document.

Until next post!

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


My first post over at Dynamics Latam

September 29, 2010

My first post on COM interop and Dexterity is out over at Dynamics Latam! This will be an excellent venue for me to give back to Spanish speaking community Microsoft Dynamics GP in North, Central, and South America and will sure up other venues to spread the GP gospel.

Until next post!

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


Using SQL Server CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM: Configuring SQL Server and creating table triggers

August 21, 2010

In my previous article I outlined some of the steps and methods needed on the Visual Studio side to create an integration to Microsoft CRM. In particular, the article showed you how to create the SQL CLR methods and how these methods interact with the Microsoft CRM Web services to create or update an item in the Product entity.

Following along, once you have created the assemblies (integration assembly and XML serialization assembly), you must proceed to install these in the Framework directory — the assemblies were created targeting the .NET Framework 3.5, so they were installed there — and also register these in the Global Assembly Cache (GAC) under the %windir%\assembly folder.

Once the assemblies are GAC’ed you can now begin the process of registering these with Microsoft SQL Server 2005, 2008, or 2008 R2. To begin registering the assemblies with SQL Server, we must first define an Asymmetric Key from the signed assembly created in our previous project.

USE master;
GO

CREATE ASYMMETRIC KEY CrmKey
FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.dll'
GO

An asymmetric key is a securable entity at the database level. In its default form, this entity contains both a public key and a private key. When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file or imports a public key from an assembly. For additional information on asymmetric keys click here.

Next, you must define a SQL Server login that’s associated to the asymmetric key for code signing purposes. One of the characteristics of the .NET Framework is that all external resources being accessed will require a certain level of trust. SQL Server accomplishes this by using a login for code signing with specific permissions to the outside world.

USE master;
GO

CREATE LOGIN [crmlogin] FROM ASYMMETRIC KEY [CrmKey];
GO

GRANT UNSAFE ASSEMBLY TO crmlogin;
GO

For more information on granting permissions to assemblies click here.

Once we have created the asymmetric key, it’s now time to create the assemblies in your company database.

USE [CompanyDB];
GO

CREATE ASSEMBLY [Crm.Integration]
FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY [Crm.Integration.XmlSerializers]
FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.XmlSerializers.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

For more information on creating assemblies, click here.

With the assemblies created, it’s now time to expose our CLR stored procedure to SQL Server. In order to register our CLR method, we use the standard CREATE PROCEDURE statement with a twist:

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[crmInsertProduct]
  @itemNumber       NVARCHAR(31),
  @itemDescription  NVARCHAR(100),
  @VendorName     NVARCHAR(65),
  @VendorItem       NVARCHAR(31),
  @ItemShipWeight   NUMERIC(19,5),
  @defaultUofM     NVARCHAR(20),
  @defaultUofMSched NVARCHAR(20),
  @defaultPriceList NVARCHAR(20),
  @currencyID       NVARCHAR(15),
  @decimals     INT,
  @quantityOnHand   NUMERIC(19,5),
  @listPrice        NUMERIC(19,5),
  @priceListPrice   NUMERIC(19,5),
  @standardcost     NUMERIC(19,5),
  @currentCost     NUMERIC(19,5),
  @productTypeCode  INT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Crm].[Integration].[clrProcedures].[CreateProduct]
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

GRANT EXECUTE ON [dbo].[crmInsertProduct] to [DYNGRP]
GO

Note that the stored procedure must be created with the same number of parameters as the CLR method.

Finally, we can create a trigger on the IV00101 table to call the stored procedure and pass in the parameters required.

Here are some final notes from and things I had to implement at the SQL Server configuration level to make all this work:

1. First, you must enable CLR integration on SQL Server to allow it to execute assemblies. To enable CLR integration, you must change the ‘CLR Enabled’ option in SQL Server configuration.

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE;
GO
EXEC sp_configure 'CLR Enabled', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced option', '0';
GO
RECONFIGURE;
GO

2. In order to recreate all the above objects, you must first drop the stored procedure, then drop the assemblies, then login, and finally the asymmetric key, this is, objects need to be dropped in reverse order to avoid dependency errors.

USE [CompanyDB]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[crmInsertProduct]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[crmInsertProduct]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Crm.Integration.XmlSerializers' and is_user_defined = 1)
DROP ASSEMBLY [Crm.Integration.XmlSerializers]

GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Crm.Integration' and is_user_defined = 1)
DROP ASSEMBLY [Crm.Integration]
GO

USE master;
GO

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'crmlogin')
DROP LOGIN [crmlogin]
GO

DROP ASYMMETRIC KEY CrmKey;
GO

I hope you had a good time reading this series. A lot of what you read here I had to learn on the fly, so a lot of reading and research went into building this integration approach. I am sure there are things that could be improved, but this is the down and dirty version.

Until next post!

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


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

August 18, 2010

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/


Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM

August 16, 2010

I have been involved for over the past 6 months with an extensive project requiring complex integrations between Microsoft Dynamics GP 10.0, Microsoft CRM 4.0 and other custom operational systems. In the process of designing and implementing these integrations the client requested a very easy to use interface that could be maintained without having to hire an army of developers or even specialized resources.

The mission: insert/update customer addresses and inventory items from Microsoft Dynamics GP into Microsoft CRM’s Product and Customer Address entities. The client also requested the integration be done using the Microsoft CRM web services in order to ensure upgrade support.

Background

Beginning with SQL Server 2005, the components required to develop basic CLR database objects are installed with SQL Server. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:

System.Data
System.Data.Sql
Microsoft.SqlServer.Server
System.Data.SqlTypes

You can find more information on SQL Server CLR integration over at MSDN. Be sure to check the following articles:

Overview of CLR Integration
CLR Stored Procedures

Solution

The solution can be broken down into two parts:

1. Creating the assembly with the CLR stored procedures that would in turn instantiate the CRM web methods to open a connection and insert or update the Product and Customer Address entity records.

2. Configuring Microsoft SQL Server and registering the assembly, creating the triggers on the RM Customer Address Master (RM00102) and Item Master (IV00101) tables that would invoke the CLR stored procedures to pass the Microsoft Dynamics GP records.

This week’s series will outline the solution with the code to achieve this. The following topics will become available on the day of their release:

08/18/2010 – Creating a CLR assembly and working with CRM web methods

08/20/2010 – Configuring SQL Server and creating table triggers

Until next post!

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