VST: Rendering WinForms in Microsoft Dynamics GP web client

In my previous article I discussed applying Service Pack 2 to update your VST development environment. The SP2 update introduces the ability to render VST WinForms in the Web Client.

Background

Prior to the release of Microsoft Dynamics GP 2013 RTM, the application development and technical consulting communities got to preview the web client at the Microsoft Dynamics GP Technical Airlift 2012 in Fargo in a Web Client Jumpstart Training for partners. Following the training and attendance to some sessions, VST developers got the bad news: "Only Dexterity applications are fully compatible with the web client". That left a number of you with the proverbial (and probably literal) bad taste in the mouth and scrambling for answers. Microsoft came back with a consolation price announcement: "Only VST applications written without using WinForms will be supported in the web client". In retrospect, I feel the message could have been delivered differently, but I digress.

Fast forward to SP2 and Microsoft did what it does best: make things right! VST developers now have the ability to render WinForms in the web client... with a few caveats.

The Boring Theory

With the RTM release of Microsoft Dynamics GP 2013 web client, VST developers needed to make a slight change to the way assembly add-ins were exposed to the desktop client and/or web client. By default assemblies were not loaded by the Runtime Engine and Web Client Runtime engine. In contrast, VST assemblies placed under the traditional AddIns folder would load in the desktop client (Dexterity client) only.

Now, to have an integration load on the Microsoft Dynamics GP web client, you must add the SupportedDexPlatforms attribute to the class that implements the IDexterityAddIn interface to indicate that the integration works for the web client.

Once you have indicated what platform your application will be compatible with, it's time to address your WinForms. Microsoft has leveraged its rendering engine to support two different types of rendering for WinForms: dynamic and custom rendering.

To use dynamic rendering, your Visual Studio Tools form must use controls from the supported list. The following controls are automatically rendered:

• Button
• CheckBox
• ComboBox
• GroupBox
• Label
• ListBox
• RadioButton
• TextBox

If the WinForm uses controls that aren't included in this list, those controls won't be rendered on the form when it is displayed in the web client. In those cases, you must implement custom rendering.

 

Dynamic rendering is the simplest technique for implementing a Visual Studio Tools form that works on the Microsoft Dynamics GP web client. With dynamic rendering, the web client runtime does the necessary work to display the Visual Studio Tools form in the web client. It also handles the communication between the web client machine and the web client server.

To enable dynamic rendering for a form, the form must derive from DexUIForm class. You must add the WCCompliantWindow attribute to the class that defines the form. This attribute indicates that the form should be automatically rendered by the web client. If this attribute isn't found on the form, the form will not be displayed in the web client.

The fun part!

Let's take canned PostingSOPBatches2 example. The first task at hand is to determine what platform the VST application will support:

In the above, we have added the SupportedDexPlatforms attribute, which takes one parameter that indicates which platform or platforms are supported by the integration. In this example, the logical-or operation is used to indicate that both the desktop client and the web client are supported.

With the client defined, the next focus is on the WinForm itself.  The following is our WinForm layout:

Once you complete the layout of the form, double-click on it to open the associated code:

The WCCompliantWindow attribute takes one boolean parameter that indicates whether dynamic rendering is supported for the form.

That's it! Compile away and test, test, and test, your code to make sure it works and that no rendering issues occur. You should place the resulting assembly, in this case postingSopBatches2.dll and its config file in the AddIns folder under your Microsoft Dynamics GP 2013 root folder. Launch the Web Client and display the form. For this example, we have placed the form under the About Microsoft Dynamics GP box, but could have well written an event handler to display it under SOP Entry where it really belongs.

So what happens to things like grids and custom controls? Those will need to be rendered using Custom Rendering. In my next article I will discuss this method and some of the drawbacks you may face while doing so.

Thanks to Kevin Racer (Twitter: @kracer123) and Michael Hammond (Twitter: @mhamm_msft) for their valuable contributions to this article.

-//-

Remember, the best application development techniques usually involve a Hybrid Development approach. There's always a way to accomplish the end goal and ultimately, is up to you the developer to consider at all the options available.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Accessing Microsoft Dynamics GP Default Settings from Visual Studio Tools

Rarely you will hear the Dex.ini file being called the Microsoft Dynamics GP defaults file. However, the Dex.ini contains keys (and their associated values) that define how Microsoft Dynamics GP behaves in some cases. For example, SQLLastUser=sa stores the login of the last user who accessed the system on that specific instance of Microsoft Dynamics GP, in this case, 'sa'. Likewise, AutoInstallChunks=TRUE, allows Microsoft Dynamics GP to bypass prompting the user for new code (chunk file) to be installed at the time of launching the application. There are other settings controlling how certain features behave.

You can find a list of Dex.ini settings here.

Recently, I was helping a good friend of mine up in Maine who attended my Dexterity class in Boston and this time he wanted to know how to read the path for the Microsoft Dynamics GP help file. Of course, there's a simple (predefined) method for this as my good friend Patrick Roth with the Escalation Engineering team in Fargo pointed out, if you are familiar with some of Microsoft Dynamics GP form procedures:

string strPath = "";
strPath = Dynamics.Forms.MainMenu.Functions.GetDynamicsHelpPath.Invoke();
MessageBox.Show(strPath);

So, let's assume you had no clue this function existed. How would you address this issue? Frankly, I can think of at least 3 ways to do this:

1) Using a hybrid method whereby you create a Dexterity dictionary to wrap the Defaults_Read() Dexterity sanScript function into a user-defined global function. This method presents a challenge as you need to use the Dictionary Assembly Generator (DAG.EXE) to create an assembly you can reference from your VST project. In addition, you may or may not want to add an entirely new dictionary into the picture, when delivering your final solution. However, in case you are interested, this is the implementation:

GetDexIniSetting function

For more information on developing hybrid applications with Dexterity and Visual Studio Tools take a look at my articles:

Developing Microsoft Dynamics GP hybrid integrating applications
Hybrid development for the Managed Code developer
Hybrid development for the Managed Code developer (cont.)

2) You could use the Continuum API from Visual Studio Tools to access the Defaults_Read() sanScript function. The Continuum API is the Component Object Model (COM) API that is available for Microsoft Dynamics GP. The Continuum API documentation is available for download here. In this method, all you have to do is add a reference to the interop.Dynamics.dll COM library, which hosts the Continuum API.

// Created by Mariano Gomez, MVP
// This code is licensed under the Creative Commons 
// Attribution-NonCommercial-ShareAlike 3.0 Unported License.
// http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode

public string GetDexIniSetting(string searchKey)
{
   ParameterHandlerClass myParamHandler = new ParameterHandlerClass();
            
   try
   { Dynamics.Application gpApp = (Dynamics.Application)Activator.CreateInstance(Type.GetTypeFromProgID("Dynamics.Application"));
     if (gpApp == null) 
       return "";
     else
     {
       string passthrough_code = "";
       string compile_err;
       int error_code, result;

       result = gpApp.SetParamHandler(myParamHandler);
       myParamHandler.IN_Key = searchKey;

       passthrough_code += "local boolean err_val;";
       passthrough_code += "local string dex_key_val, dex_key;";
       passthrough_code += @"err_val = OLE_GetProperty(""IN_Key"", dex_key); "; 
       passthrough_code += "dex_key_val = Defaults_Read(dex_key);";
       passthrough_code += @"err_val = OLE_SetProperty(""OUT_KeyVal"", dex_key_val);";

       gpApp.CurrentProductID = 0; 

       error_code = gpApp.ExecuteSanscript(passthrough_code, out compile_err);
       return myParamHandler.OUT_KeyVal;
     }
   }
   catch
   { MessageBox.Show("Failed to initialize gpApp");
     return "";
   }
 }

public class ParameterHandlerClass
{
  public string IN_Key { get; set; }
  public string OUT_KeyVal { get; set; }
}

In the above code, we define a parameter handler class with couple properties for the key that will be read from the Dex.ini and the key value to be retrieved.

In our GetDexIniSettings method, we instantiate our class and use the Continuum SetParaHandler() method to load the IN_Key property value. We then setup our pass-through code and invoke sanScript's OLE_GetProperty() function to retrieve the value of the IN_Key from our VST application via some old fashioned OLE automation. The OLE_SetProperty() function then returns the value to our class property, OUT_KeyVal.

That's it!

3) The third method uses some classic C# reflection to get the path of the AddIns. It then converts this to the path of the Data folder and the dex.ini file, and then uses the Pinvoke() method to read the key in the dex.ini and return the value.

[DllImport("kernel32.dll", CharSet = CharSet.Unicode)]
 static extern uint GetPrivateProfileString(
 string lpAppName,
 string lpKeyName,
 string lpDefault,
 StringBuilder lpReturnedString,
 uint nSize,
 string lpFileName);

//get path which should be the addins folder
String strPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);

//look for \Addins path
if (strPath.EndsWith(@"\AddIns", true, System.Globalization.CultureInfo.InvariantCulture) == true)
{ //and if we find one replace with the data folder
  strPath = strPath.Replace(@"\Addins", @"\Data\Dex.ini");
}
else
{ //if not in addins then we must be in the root GP folder (like GP 2013 does with GP addins) so tack on \Data
  strPath = strPath + @"\Data\Dex.ini";
}
 
StringBuilder sb = new StringBuilder(100);
uint res = GetPrivateProfileString("General", "Pathname", "", sb, (uint)sb.Capacity, strPath);
MessageBox.Show(sb.ToString());

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the 
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

The above method comes courtesy of my good friend Patrick Roth with the Escalation Engineering team at Microsoft.

All the above methods are generic in nature and can read more than just a specific key. It's good to know that there's more than one way to do things. If you are going all hybrid, there's a code for that! If you want to be self-contained, there's a code for that! If you want to deviate completely from sanScript because you hate it or don't know it, there's a code for that! Whatever route you choose it must work to your benefit.

As a good Australian friend of mine would say, "The right solution is the one that works!".

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

In-Memory XML Serialization with eConnect 10

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/

Read More

How to change your Microsoft Dynamics GP 2010 Map Services

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/

Read More

The Open XML SDK 2.0 for Microsoft Office

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/

Read More

My first post over at Dynamics Latam

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/

Read More

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

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/

Read More

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/

Read More

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

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/

Read More

Building a COM Interop Assembly to use with Microsoft Dexterity

I am currently building some customizations for a customer of mine in the aerospace industry. My customer required a library of trigonometric functions that could be used to extend their Dexterity integrating applications.

To solve this problem, we turned to .NET to create COM interop assembly. The idea was to take advantage of the standard Math class methods available with the System namespace - System.Math . The following is an excerpt of the code we created:

TrigonometricFunctions.cs

//Created by Mariano Gomez, MVP
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;

namespace TrigonometricFunctions
{
    public class TrigonometricFunctions
    {
        [GuidAttribute("8268A95E-6FCB-4FB2-88A1-1E38F49F4FB8"), ClassInterface(ClassInterfaceType.AutoDual)]
        public class TrigFn
        {
            // dx in degrees
            public double fSin(double dx)
            {
                double angle = Math.PI * dx / 180.0;
                // returns sin(dx)
                return Math.Sin(angle);
            }

            // dx in degrees
            public double fCos(double dx)
            {
                double angle = Math.PI * dx / 180.0;
                // returns cos(dx)
                return Math.Cos(angle);
            }

            // dx in degrees
            public double fTan(double dx)
            {
                double angle = Math.PI * dx / 180.0;
                // returns tan(dx)
                return Math.Tan(angle);
            }

        }
    }
}

Once the functions in the TrigFn class were in place, we set up the assembly information in Visual Studio's marking the option to Make assembly COM visible.


So we did not have to register the assembly manually, we took advantage of Visual Studio's ability to register the assembly for COM interop under the Build settings. For purposes of demostration, I created a simple Dexterity form, as shown below:



The following is the code added to the '(L) Sine' button:

Sine button CHG script

{ script: l_Sine_CHG }

local TrigonometricFunctions oTrig;
local currency angle, sine;

oTrig = new TrigonometricFunctions.TrigFn();

'(L) Prompt' = "The Sine value is ";
'(L) Conversion' = oTrig.fSin('(L) Angle');

In the code above, TrigonometricFunctions is a data type created after adding the TrigonometricFunctions COM interop assembly as a library to our Dexterity application. The data type references the TrigonometricFunctions.TrigFn class.

References:

Using a .NET Assembly from a Dexterity-Based Application - Click here

Microsoft Dynamics GP 10.0 White Paper: Using a .NET Assembly from a Dexterity-based Application - Click here

Downloads:

TrigonometricFunctions .NET project - Click here
Dexterity Sample App - Click here

Until next post!

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

Read More