Showing posts with label Visual Studio 2008. Show all posts
Showing posts with label Visual Studio 2008. Show all posts

Thursday, December 2, 2010

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/

Friday, November 12, 2010

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/

Friday, October 1, 2010

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/

Friday, August 20, 2010

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/

Tuesday, August 17, 2010

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/

Sunday, August 15, 2010

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/

Sunday, January 10, 2010

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/

    Monday, October 19, 2009

    VST - Amount in Words on SOP Entry window

    Background

    Just recently, I came across a Microsoft Dynamics GP Partners forum question, requesting the ability to add the amount in words to the SOP Entry window and possibly other windows throughout the system. Certain requirements may seem very strange to some of us, but are based on actual customer requests elsewhere on this planet.

    The proposed solution

    As I have been lately talking about hybrid integrating applications development, I thought it would be more than appropriate for this occassion to show how this customization could be achieved with the use of Modifier and Visual Studio Tools for Microsoft Dynamics GP. The idea? Pretty simple! Add a text field to the SOP Entry window with Modifier, then build a forms dictionary application assembly with the Dictionary Assembly Generator that can be accessed from Visual Studio Tools. In Visual Studio, I would then create a Dynamics GP project that would reference the Application.Dynamics.ModifiedForms.dll to set the document amount in words to the text field added with Modifier, by calling the Report Writer function RW_ConvertToWordsAndNumbers. If this sounds all too complicated, I will show you how to build this customization in 4 steps.

    1. Modify the SOP Entry form to include a local text field. The following screenshot shows the modified window with the text field, '(L) Amount In Words'. Don't forget to grant yourself security to the modified window in Dynamics GP.





    2. Use the Dictionary Assembly Generator (DAG.EXE) tool provided with Visual Studio Tools to generate the Application.Dynamics.ModifiedForms.dll application assembly for the forms dictionary. Since DAG.EXE is a command line utility, go to the command prompt then go to the Visual Studio Tools SDK folder (typically under Program Files\Microsoft Dynamics\GP10 VS Tools SDK) to execute it, as follows:

    dag.exe 0 "C:\Program Files\Microsoft Dynamics\GP\Dynamics.set" /F /N:Dynamics

    3. Open Visual Studio and create a new Dynamics GP solution, SOPAmountInWords.



    Once the solution has been created, you can proceed to add a reference to the forms dictionary application assembly.



    Now you can proceed to add the following code in the editor:


    // Created by Mariano Gomez, MVP
    // No warranties conferred, express or implied
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Windows.Forms;
    using Microsoft.Dexterity.Bridge;
    using Microsoft.Dexterity.Applications;
    using Microsoft.Dexterity.Applications.DynamicsModifiedDictionary;

    namespace SOPAmountInWords
    {
    public class GPAddIn : IDexterityAddIn
    {
    // IDexterityAddIn interface
    const short FUNCTIONAL = 1;
    const short ORIGINATING = 2;

    SopEntryForm sopEntryMod;
    Microsoft.Dexterity.Applications.DynamicsDictionary.SopEntryForm sopEntry;

    public void Initialize()
    {

    // create overload method for changes in the document total field
    sopEntry = Dynamics.Forms.SopEntry;
    sopEntry.SopEntry.OriginatingDocumentAmount.Change += new EventHandler(OriginatingDocumentAmount_Change);

    }

    void OriginatingDocumentAmount_Change(object sender, EventArgs e)
    {
    string amountInWords;

    // retrieve amount in words
    if (sopEntry.SopEntry.CurrencyViewButton.Value == ORIGINATING)
    {
    amountInWords = Dynamics.Functions.RwConvertToWordsAndNumbers_.Invoke(
    sopEntry.SopEntry.OriginatingDocumentAmount.Value,
    sopEntry.SopEntry.CurrencyId.Value,
    0
    );
    }
    else
    {
    amountInWords = Dynamics.Functions.RwConvertToWordsAndNumbers_.Invoke(
    sopEntry.SopEntry.DocumentAmount.Value,
    sopEntry.SopEntry.CurrencyId.Value,
    0
    );
    }


    // assign value to custom text field on modified form
    try
    {
    sopEntryMod = DynamicsModified.Forms.SopEntry;
    sopEntryMod.SopEntry.LocalAmountInWords.Clear();

    sopEntryMod.SopEntry.LocalAmountInWords.Value = amountInWords;
    }
    catch (Exception ex)
    {
    MessageBox.Show("Error attempting to set modified form field value: {0}", ex.ToString());
    }

    }
    }
    }



    Code Explanation

    The first aspect of the code is to reference the namespace of the modified form applciation assembly. This will allow us to access the modified form object, SOP Entry. As a best practice, and to avoid working with extremely long object namespaces, I created two variables that reference the objects I need to work with. In addition, I defined two constants that will check whether the amount is being displayed in functional or originating currency as the wording will need to change accordingly.


    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Windows.Forms;
    using Microsoft.Dexterity.Bridge;
    using Microsoft.Dexterity.Applications;
    using Microsoft.Dexterity.Applications.DynamicsModifiedDictionary;

    namespace SOPAmountInWords
    {
    public class GPAddIn : IDexterityAddIn
    {
    // IDexterityAddIn interface
    const short FUNCTIONAL = 1;
    const short ORIGINATING = 2;

    SopEntryForm sopEntryMod;
    Microsoft.Dexterity.Applications.DynamicsDictionary.SopEntryForm sopEntry;


    In the Initialize() method, we will register a change event on the Originating Document Amount field, in turn Visual Studio will create the proper overload method that we will use to add the code to manage the display of the amount in words.


    public void Initialize()
    {

    // create overload method for changes in the document total field
    sopEntry = Dynamics.Forms.SopEntry;
    sopEntry.SopEntry.OriginatingDocumentAmount.Change += new EventHandler(OriginatingDocumentAmount_Change);

    }


    In the OriginatingDocumentAmount_Change() method, we now can add the code to manage the display of the amount in words by invoking the RwConvertToWordsAndNumbers_() function, exposed via the Microsoft.Dexterity.Applications namespace (Applications.Dynamics.dll application assembly)


    void OriginatingDocumentAmount_Change(object sender, EventArgs e)
    {
    string amountInWords;

    // retrieve amount in words
    if (sopEntry.SopEntry.CurrencyViewButton.Value == ORIGINATING)
    {
    amountInWords = Dynamics.Functions.RwConvertToWordsAndNumbers_.Invoke(
    sopEntry.SopEntry.OriginatingDocumentAmount.Value,
    sopEntry.SopEntry.CurrencyId.Value,
    0
    );
    }
    else
    {
    amountInWords = Dynamics.Functions.RwConvertToWordsAndNumbers_.Invoke(
    sopEntry.SopEntry.DocumentAmount.Value,
    sopEntry.SopEntry.CurrencyId.Value,
    0
    );
    }


    // assign value to custom text field on modified form
    try
    {
    sopEntryMod = DynamicsModified.Forms.SopEntry;
    sopEntryMod.SopEntry.LocalAmountInWords.Clear();

    sopEntryMod.SopEntry.LocalAmountInWords.Value = amountInWords;
    }
    catch (Exception ex)
    {
    MessageBox.Show("Error attempting to set modified form field value: {0}", ex.ToString());
    }

    }

    Note that the value of the CurrencyViewButton is checked to establish whether to display the amount in functional or originating, but also use the correct currency wording (dollars/cents, pounds/pensks, etc).

    The Report Writer function is then called with the required parameters. Then the result is assigned to our exposed text box field.

    4. Now we can build and deploy the solution. Copy the resulting application assembly to the AddIns folder under the GP installation folder. Launch Dynamics GP and go to the SOP Entry screen. You can enter a new document or browse through existing ones as the customization will fill in the text box appropriately.



    Hopefully you enjoyed this simple and useful customization and learned a bit more about developing hybrid applications.

    Downloads
    You may download the zip file containing the Visual Studio solution, application assembly and package file with the customization. To install, copy the Application.Dynamics.ModifiedForms.dll and the SOPAmountInWords.dll files to the AddIns folder under Dynamics GP. Import the package file and grant yourself security to the modified SOP Entry window.

    SOPAmountInWords.zipx - Click here to download

    Until next post!

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

    Sunday, September 20, 2009

    The Technology Corner: Windows 7 and Microsoft Dynamics GP 10.0

    For those of you who follow me on Facebook and Twitter, you probably already got first hand updates as I was going through rebuilding my laptop with the new Windows 7 and getting all my core applications installed. Most of you are aware that I am more of a technical guy, so hardware and applications performance is very critical.

    Let's start with my laptop... I currently own a Dell XPS M1710 running Intel Centrino Duo core and 2.5GB in RAM. I know, yes! I use a gaming notebook taking advantage of the processing capabilities and the massive 17" screen. A little outdated when compared to the new XPS, but it still gets the job done!

    The goal was to install the following software:

    • Windows 7 Enterprise
    • Microsoft Office 2007 Enterprise, including Microsoft Project 2007 and Microsoft Visio 2007
    • Microsoft SQL Server 2008 Standard with Service Pack 1
    • Microsoft Visual Studio 2008 Standard with Service Pack 1
    • Microsoft Dexterity 10 with Service Pack 4
    • Microsoft Dynamics GP 10 with Service Pack 4
    • Support Debugging Tool for Microsoft Dynamics GP 10
    • Visual Studio Tools with Service Pack 4 for Microsoft Dynamics GP
    In addition, I needed to install Microsoft Silverlight 3, Microsoft .NET RIA Services, Microsoft Virtual Earth Control CTP, and Microsoft Dynamics GP Integration Manager 10 with Service Pack 4.

    After all, I am also gearing up for the Microsoft Dynamics GP Technical Conference, so I really needed to showcase all the latest technology -- well, I would have loved to have Visual Studio 2010, but Visual Studio Tools is not compatible yet -- to you the developers out there.

    The prep work
    I started out by backing up my all files onto my home NAS and progressed from there with a checklist of the most important programs I needed after the Windows 7 installation was complete. My goal was not to have a straight upgrade from Windows Vista Service Pack 2, but rather a fresh install as mostly recommended by Microsoft anyways.

    Once backups were ready and the inventory of applications was completed came the actual installation of Windows 7.

    Windows 7 installation.
    I popped my copy of Windows 7 Enterprise into the DVD drive and rebooted my laptop. The first surprise was the now graphical installation interface, a long shot away from the old DOS interfaces that plagued previous Windows installations. The interface was pretty intuitive to navigate. I essentially started by reformatting my existing partion. This is where the second surprised come to play. In previous installations of Windows, formatting a partition could take long minutes. Windows 7 completed the reformatting of my 76GB partition in less than 10 seconds. From there on, copying the files and extracting them onto the hard drive was pretty simple. The footprint was minimal too, occupying less than 3GB.

    As a laptop user, my major concern is always drivers. Windows 7 did an excellent job recognizing all my laptop components including the wireless... this was awefully cool because, the OS could immediately access other components available online and perform some basic updates at the same time the installation was happening. The only dissapointment was my display driver. That I had to install from a pen drive that I prepared before reformatting just in case.

    Once the final reboot was complete, I run the Windows Update to make sure nothing else was missing... and yes, they are already a few Windows 7 updates available. End to end, the OS took approximately 30 minutes to install, including the updates. The third surprise came then... Windows 7 was booting up three-fold faster than Windows Vista. It went from a painful 1.5-minute boot up process to less than 10 seconds. My laptop seemed to have regained some life, though I was a bit skeptic since I really hadn't loaded anything yet.

    Programs installation

    SQL Server 2008 Standard with Service Pack 1
    Once the Windows 7 updates were installed, I moved on to SQL Server 2008 Standard installation. SQL Server installation was pretty straight forward, however, at the beginning of the install, Windows 7 warned me that this program was written for another version of Windows and presented me with a link as to where I could find the updates for SQL Server... now that's productivity. Windows 7 askmed me if I wanted to change the installer compatibility, and did so upon my acceptance of the message. The installation continued as usual and without any hiccups.
    I then went to the SQL Server site and downloaded and installed Service Pack 1. In less than 15 minutes I was up and running with SQL Server 2008 with Service Pack 1. I restarted the laptop to test the boot up and surprisingly, nothing had changed as far as performance.

    Microsoft Office 2007 Enterprise

    MS Office installation went uneventful. However, when the installation was completed and I let couple minutes pass by, Windows 7 had already downloaded Office 2007 Suite Service Pack 2 and the required security updates for everything else installed at this point. I thought this would be a drag, because they were more than 35 updates between SQL Server, Office, and other driver components on my machine that needed to be applied. Wrong! When I clicked on the shut down button. Everything got installed and applied in one pass! No more multipass service pack installation bootups! Between the installation of MS Office 2007 and the service packs, I spent another 30 to 40 minutes. I also activated all the products during this time.

    Microsoft Visual Studio 2008 Standard with Service Pack 1

    So I then put the VS2008 DVD in the drive and began the installation. Again, nothing much to report here. Once the installation was complete, I ran Windows Update. VS2008 SP1 was next in line. That got applied successfully without any issues and the whole experience took 20 minutes tops. Other security components were installed, but this took less than 5 minutes.

    Surprisingly enough at this point, my laptop's performance seem to hold steady...

    Microsoft Dexterity 10 with Service Pack 4
    Dexterity took less than 2 minutes to download, and 2 minutes to install... next!

    Microsoft Dynamics GP 10 with Service Pack 4
    Since the introduction of Feature Pack 1 with Service Pack 2, the GP installer had already incorporated changes to deal with .NET Framework and the newer operating systems. So I decided I would start my installation with the Feature Pack 1 with Service Pack 4 installer. Everything went fine until I launched Dynamics Utilities. It complained that it could not find BCP... hmm, then I remembered Vaidy's article on the subject when he was attempting to install on GP on Vista. It's easy to forget that Windows 7 also implements UAC -- not a sarcasm by the way. Once I ran Dynamics Utilities as administrator, between the creation of the DYNAMICS system database and the sample company, Fabrikam, some 20 minutes had passed.

    I have to admit that throughout this process, my laptop "seemed to had regained its focus" on delivering what it was designed to deliver: peak performance for demanding gaming applications. This was no different for my business applications either and that made me think that Windows 7 was doing a really good job at keeping a low memory and disk overhead, giving all other applications the room needed to perform adequately.

    Visual Studio Tools for Microsoft Dynamics GP
    Installing VST was a bit tricky. I started with the SDK download available from PartnerSource only to find out that it was asking for an existing installation of Visual Studio 2005 or greater. Since it said "greater" I assumed VS2008 would be just fine, but the installer did not seem to recognize I had VS2008 installed. I thought for one instance that this would be the wall that would stop me in my tracks. I ran a Windows 7 compatibility analysis on the MSI and Windows 7 suggested to run it in compatibility mode "Previous Windows version". So I did, still the same error.

    It turns out VST SP2 has an additional installer that checks for the existance of VS2008 and gives you the option to install the VST Templates for VS2005 or VS2008. Phew!! Sigh of releaf! The problem was not relaed to a compatibility issue with Windows 7. I moved on to download and install VST SP4 once the initial components were installed. Because of the issues I had, I wasted precious minutes in this step, so overall it took some 45 minutes and some swearing to get through.

    Support Debugging Tool
    I downloaded this baby from the Support Debugging Tool download page and attempted to extract directly into the Program Files\Microsoft Dynamics\GP folder and received a priviledge error -- UAC in action again. It was necessary to extract it into the My Documents folder then move to the GP folder.

    I booted up GP to add the code and got prompted as such. However, after acknowledging the message I got the following error:


    I have a feeling this error is Service Pack 4 related, rather than anything to do with Windows 7, but for now, SDT remains broken. I then had to rename the chunk file and move on.

    Integration Manager
    I began Integration Manager's installation very aware of Vaidy's findings with his initial installation test on Windows 7 RC, but I figured, I will once more test Windows 7 backward compatibility features. After all, this is one of the biggest selling points to customers to upgrade to Windows 7.

    Everything began just fine... space requirements computed, files transferred, components registered, registry keys created. Surprisingly enough, after launching IM -- expecting it to bumb out -- everything was fine! I entered my registration keys and opened an integration just fine. I installed IM from the Feature Pack 1 with Service Pack 4 DVD image. Not sure if this had anything to do with my success, but there it was, up and running! The process took less than 4 minutes to complete.

    All in all my laptop reinstallation took over 5 hours (including moving back files to the hard drive).

    Before deciding to perform a Windows 7 upgrade, take in to consideration all the factors and applications your business is currently running. Check with each one of the manufacturers to validate compatibility. However, as an early technology adapter, I am pretty please with the results of this installation and can give Windows 7 two thumbs up!

    I will certainly be performing more tests and will let you know of any issues I may encounter.

    Until next post!

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

    Monday, September 7, 2009

    The Technology Corner: Integrating Silverlight and Virtual Earth with Dynamics GP

    Starting today, I will introduce a weekly series dedicated to explore other Microsoft technologies that may seem totally unrelated to Dynamics GP. These articles are based on experiences acquired through pet projects. These are projects that have not made it beyond my computing lab and have become a true passion of mine. My intention is to present them to you the reader with only one goal in mind: show how the use of technology can enhance user interaction with data. The articles will present practical samples of these technologies and how they could perhaps be incorporated into your Dynamics GP development efforts.

    Part I: The Virtual Earth Silverlight Map Interface

    Today we will explore Silverlight and Microsoft Virtual Earth.

    Silverlight was originally introduced as a video streaming plug in, but has rapidly evolved into a feature-rich interactive web applications development framework. In turn, Microsoft Virtual Earth, now Bing Maps for Enterprise, is a set of controls and APIs that allow organizations to take advantage of the latest mapping technology to create unique customer experiences by delivering locally relevant information.

    The project

    As simple as it may sound, this project allows a user, say for example an account executive traveling to see a customer, to obtain not only geographical location information about the customer, but also view important account information on the map interface.

    Getting Started

    For this project you will need the following laundry list of development tools and technology components:
    That's it!

    For having no prior Silverlight or Virtual Earth development experience, this first part of the project went extremely smooth. Of first order, was the creation of the Silverlight Application project in Visual Studio.


    After entering the project name, and clicking Ok, I was presented with the option of hosting my Silverlight application in a new Web site and to define the project type of my new Web application. These seemed like logical choices, so I sticked to the defaults. Another option (topic of a future article) is the ability to enable .NET RIA services for my Silverlight application.


    Following Microsoft's definition:

    Microsoft .NET RIA Services simplifies the traditional n-tier application pattern by bringing together the ASP.NET and Silverlight platforms. The RIA Services provides a pattern to write application logic that runs on the mid-tier and controls access to data for queries, changes and custom operations. It also provides end-to-end support for common tasks such as data validation, authentication and roles by integrating with Silverlight components on the client and ASP.NET on the mid-tier.

    However, for this project, I was going to keep it simple and work with LINQ to SQL to provide data access to my Silverlight application.

    After clicking on the OK button, the solution was setup. In reviewing the Solution Explorer, I had a Silverlight client application and a web application as part of the overall solution.


    Silverlight client applications also make use of a XAML (pronounced "zamel") page to define and build user interface elements. XAML is an XML-based language that may be used to define graphical assets, user interfaces, behaviors, animations, and more. It was introduced by Microsoft as the markup language used in Windows Presentation Foundation, a desktop-oriented technology that is part of the .NET Framework 3.0. It was designed to help bridge the work between designers and developers in creating applications. In the case of the application, a MainPage.XAML was added.

    Of second order, I needed to add a reference to my Microsoft Virtual Earth Map Control. Since this is part of the UI, it was clear enough that a References was needed on the Silverlight client application.


    With a reference to the assembly, I could now add two lines of code -- literally -- to the XAML page to incorporate a map interface to my application: one to reference the map control assembly, and the other to give the map some startup parameters.

    MainPage.XAML


    <UserControl x:Class="VESilverlight.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:m="clr-namespace:Microsoft.VirtualEarth.MapControl;assembly=Microsoft.VirtualEarth.MapControl"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d" d:DesignWidth="640" d:DesignHeight="480">
    <Grid x:Name="LayoutRoot">
    <m:Map Name="custMap" Center="41.900632,-87.629631"></m:Map>
    </Grid>
    </UserControl>


    I then compiled the code and ran it in debug mode and to my surprise, I had a fully working map solution! Talking about productivity! Wow!


    But, while I could make use of the map, this was only the beginning of the work ahead. For now, enjoy this first part. Try it out and let me know what you think.

    I leave you with this MIX09 video presentation of Chris Pendleton, Virtual Earth Technical Evangelist at Microsoft, who explains the Virtual Earth Map Control in more detail.

    video


    Until next post!

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