MBS Guru

23 October, 2013

Connecting to a Microsoft CRM 2013 Internet Facing Deployment with C#

Integrating and retrieving data from a Microsoft CRM 2013 instance is a common request, and I have found myself working on a number of these solutions lately. I encountered a recent example where a client was using an Internet Facing Deployment of CRM 2013.

In this case, I needed to connect to the Microsoft CRM 2013 server to retrieve data to be integrated into Dynamics GP.

Here is a quick sample of code you may use to connect to an internet facing deployment of Microsoft CRM 2013 using claims based authentication.

// Get the endpoint of the CRM organization service.

Uri organizationServiceUri = new Uri(@"CrmOrganizationServiceEndpointUri"); // In production code, abstract this to configuration.

// Get the service configuration.

IServiceConfiguration<IOrganizationService> serviceConfiguration = ServiceConfigurationFactory.CreateConfiguration<IOrganizationService>(organizationServiceUri);

// Set up the credentials.

ClientCredentials clientCredentials = new ClientCredentials();

clientCredentials.UserName.UserName = "CrmUserName"; // In production code, abstract this to configuration.

clientCredentials.UserName.Password = "CrmPassword"; // In production code, abstract this to configuration.

using (OrganizationServiceProxy organizationServiceProxy = new OrganizationServiceProxy(serviceConfiguration, clientCredentials))

     // Perform business logic here.

03 September, 2013

Executing SQL Queries via SharePoint Web Services

Can you really execute native SQL Queries from SharePoint Web Services?

Microsoft SharePoint is great for building enterprise systems tying together various data sources. If the information you are looking for is in a SharePoint List or Document Library, it is straightforward to call the built-in Web Services to query or manipulate that data. Through custom Web Parts, you can run server-side code and easily retrieve data that lives outside SharePoint.

But what if you don’t have the access to run Server-Side code? How can you get to the data that lives in SQL Server from your client-side web application? You can’t call external XML web services because of the Same Origin Policy Restrictions. True, you can work around this if you have access to a JSONP web service, and some browsers and servers are starting to support CORS to allow limited cross-site access. But if you don’t have access to the server, can’t control the browser environment and no JSONP web services are available, you aren't out of luck. I'll show you how to get SharePoint to execute the SQL Queries on your behalf and return the results to your web browser. With a few tweaks, this same technique can also be used to access arbitrary XML Web Services. In a later article, I'll expand this example to do just that.

A Word of Warning:

There is a downside to this approach. Since you will be using the SharePoint Server as a proxy, the SQL logs will show the connection coming from the SharePoint Server. Also, you must either pass in a username/password with the web service call or use a guest SQL account. You will have to consider the security impact of either approach carefully.

How does it work?

You will be using the WebPartPages.GetDataFromDataSourceControl method. This method is intended to be used by SharePoint Designer to render data during page design and is very sparsely documented. According to MSDN, it takes two string parameters: dscXml and contextUrl. That is the extent of the MSDN documentation.

In order to make it easier to work with, I created a helper function called SqlQuery that accepts a Server name, Database name, User name, password, Sql Query and a callback function. Pass in the proper parameters and your callback will be executed with the results. You can paste the source code below into the HTML source of a Content Editor Web Part on a page on your SharePoint Server to test. Note, I am using JQuery to simplify the AJAX calls and form interaction.

Source Code:

    <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.2.min.js" type="text/javascript"></script>

    <script type="text/javascript">
        $(document).ready(function () {

            //SqlQuery function - proxies a SQL Server call through sharepoint web services and executes your callback with the results.
            function SqlQuery(server, database, user, password, query, callback) {
                var soapMessage = ["<?xml version='1.0' encoding='utf-8'?>"];
                soapMessage.push("<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>");
                soapMessage.push("  <soap:Body>");
                soapMessage.push("      <GetDataFromDataSourceControl xmlns='http://microsoft.com/sharepoint/webpartpages'>");
                soapMessage.push("          <dscXml>&lt;asp:SqlDataSource runat=&quot;server&quot;  __designer:commandsync=&quot;true&quot; ProviderName=&quot;System.Data.SqlClient&quot;  ConnectionString=&quot;");
                soapMessage.push("Data Source=" + server + ";User ID=" + user + ";Password=" + password + ";Initial Catalog=" + database + ";&quot; ");
                soapMessage.push(" SelectCommand=&quot;" + query + " &quot;/&gt;</dscXml>");
                soapMessage.push("          <contextUrl></contextUrl>");
                soapMessage.push("      </GetDataFromDataSourceControl>");
                soapMessage.push("  </soap:Body>");
                soapMessage.push("</soap:Envelope>");
                var message = soapMessage.join("");
                var thisSite = window.location.href.split(window.location.pathname).shift(); 
                $.ajax({
                    url: thisSite + "/_vti_bin/webpartpages.asmx",
                    beforeSend: function (xhr) { xhr.setRequestHeader("SOAPAction", "http://microsoft.com/sharepoint/webpartpages/GetDataFromDataSourceControl"); },
                    type: "POST",
                    dataType: "xml",
                    contentType: "text/xml; charset=\"utf-8\"",
                    data: message,
                    complete: function (xData, status) {
                        if (status == "success") {
                            var rows = $($.parseXML($(xData.responseXML).find("GetDataFromDataSourceControlResult").prop("text"))).find("Row");
                            callback(rows, status);
                        } else {
                            callback(xData, status);
                        }
                    }
                });
            };
            //Set up some variables to interact with the form.
            var executeButton = $("#executeQuery"), serverInput = $("#server"), databaseInput = $("#database"), userNameInput = $("#userName"), passwordInput = $("#password"), queryInput = $("#query");


            //When the user clicks the button, execute the query, parse and display the results.
            executeButton.click(function () {
                window.status = "calling SQL Query";

                SqlQuery(serverInput.val(), databaseInput.val(), userNameInput.val(), passwordInput.val(), queryInput.val(), function (rows, status) {
                    window.status = status;
                    if (status == "success") {
                        var output = ["<ol>"];

                       //rows is now a jquery object with the response from the sql query
                        rows.each(function (i, element) {
                            var row = [];
                            for (var j = 0; j < element.attributes.length; j++) {
                                var attribute = element.attributes[j];
                                row.push("'" + attribute.nodeName + "'='" + attribute.value + "'");
                            }
                            output.push("<li>" + row.join() + "</li>");
                        });
                        $("#output").html(output.join("") + "</ol>");
                    } else {
                        $("#output").html($(rows.responseXML).text());
                    }
                });
            });
        });
    
    </script>

<!-- And the form to let the user interact with the SqlQuery function. -->

    <h1>
        SQL Query Demo</h1>
    <label for='userName'>
        User Name:</label>
    <input type='text' id='userName' value='SQLUser' />
    <label for='password'>
        Password:</label>
    <input type='password' id='password' value='********' />
    <label for='server'>
        Server:</label>
    <input type='text' id='server' value='SERVERNAME' />
    <label for='database'>
        Database:</label>
    <input type='text' id='database' value='Food' />
    <label for='query'>
        Query:</label>
    <textarea id='query' rows='5' cols='80'>SELECT TOP 10 [NDB_No] ,[Seq] ,[Amount] ,[Msre_Desc] ,[Gm_Wgt] FROM [Food].[dbo].[WEIGHT]</textarea>
    <input type='button' id='executeQuery' value='Execute Query' />
    <h1>
        Output</h1>
    <div id='output'>
    </div>

Example:

17 March, 2013

Will Your Existing GP Customizations Work in GP 2013‌?

With the recent release of Microsoft Dynamics GP 2013 comes an exciting new feature: the Web Client. We've all longed for the Web Client, dreaming of a day when we could access our system from any web browser without being tied to our desktops and office networks, and now it’s finally here! Before we all begin to jump for joy, there is one thing to realize before making the switch to GP 2013—some of the customizations that you've made over the years to your existing Dynamics GP installation may not be fully compatible in the new Web Client.

Before going further, let’s take a look at the different ways that GP can be customized, which of those ways will present an issue moving over to the new GP 2013 Web Client, and the next steps to make the transition.

4 Types of Dynamics GP Customizations

There are four ways that Dynamics GP software can be modified or customized:

Modifier - Allows changes to be made to the user interface (UI). Examples include adding new fields, moving fields around, and changing field labels, etc. Customizations made to GP using Modifier will be compatible with both the GP 2013 desktop version as well as the Web Client.
Dexterity -This is the programming language in which Dynamics GP is written and customizations to the code level require a Dexterity developer. Example of changes to Dexterity code include things like adding new business logic or capturing business domain specific data through alternate forms or window modifications. The possibilities are endless. Customizations made to GP using Dexterity will be compatible with both the GP 2013 desktop version as well as the Web Client.
VBA - Is most often used to add additional functionality to fields such as disabling a field or automatically populate fields from a database, etc. Although customizations made to GP using VBA will be compatible with the desktop version of GP 2013, at this time they are not compatible with the GP 2013 Web Client.
VST - Allows the same type of changes that would be made with Dexterity but with the .NET programming language. Although customizations made to GP using VST will be compatible with the desktop version of GP 2013, at this time they are not compatible with the GP 2013 Web Client without additional modifications.

Type of Customization	Compatible with GP 2013 Desktop Version	Compatible with GP 2013 Web Client	How to tell if you have this customization
Modifier	Yes	Yes	The page title of the window will have a dot in front of it
Dexterity	Yes	Yes	The dexterity modification will be listed in the Dynamics.set file: C:\Program Files\Microsoft Dynamics\GP (or the application directory where you installed GP)
VBA (Visual Basic for Applications)	Yes	No	The page title of the window will have a dot at the end of it
VST (Visual Studios Tools)	Yes	No	All customizations are stored in the AddIn subfolder of the Microsoft Dynamics\GP folder.

How to Tell What Kind of Customizations You Have

There are a few simple ways to tell what type of customizations you have in Dynamics GP.

1. Modifier - The Customization Maintenance window will list modified forms, modified reports, and custom VBA code and references that are active on the workstation.
Tools -> Customize -> Customization Maintenance

If you have made customizations to GP using Modifier, there will be a dot in front of the window’s page title.

2. Dexterity - The Customization Status window will list Dexterity customizations, as well as the Dynamics GP modules and Dexterity-based ISV solutions that are installed and active.
Tools -> Customize -> Customization Status

3. VBA - The Customization Maintenance window will list modified forms, modified reports, and custom VBA code and references that are active on the workstation.
Tools -> Customize -> Customization Maintenance

If you have made customizations to GP using VBA, there will be a dot at the end of the window’s page title.

4. VST - All customizations made to GP using VST are stored in a folder called AddIns. If you have VST customizations, you will find them inside this folder located AddIn subfolder of the Dynamics GP client installation. This is typically C:\Program Files (x86)\Microsoft Dynamics\GP\AddIns.

Next Steps

If GP customizations exist that may not be compatible with the new GP 2013 Web Client here are a few suggestions to preserve your customization’s functionality.

Business Process Analysis
Is this functionality still needed‌? Can it be improved‌?

Determine if the customization is still needed. New releases are packed with new functionality and the task that the old customization helped accomplish may now be handled out-of-the-box in the new version. Or, your business may have changed and it is simply not needed any longer! A business process analysis against the new version will help identify which customizations are no longer needed. After reviewing the new release’s capabilities, you may find that there are many other new features offered that you hadn’t even thought of before.

It’s like trading in your old 1994 Honda Accord for the 2013 model—you were excited about the built in navigation system (no more Garmin attached to your windshield) and heated seats but you had no idea that it had blind spot detectors and could park itself! So when you’re ready to make the switch, make a point to learn about all the new features of the software, not just the ones you already know about and you may be pleasantly surprised. Sometimes you don’t know what you don’t know!

Spend Time on Design

If a customization is going to be re-implemented it makes sense to spend a little extra time on the design of the customization. Ask yourself if there are any features that have been lacking that need to be added or can the functionality be changed to make it more user friendly. By making sure you have got the most value out of your customization it will pay off in the long run when you don’t have to continuously make changes to it again and again in the future.

Use Case Documentation

As with all customizations, it is a best practice to document, document, and document. Before sitting down with a developer take screen shots of the areas you want to change and document the business process flow. This will provide clear communication between you and your developer and help to ensure there aren’t any discrepancies between what you are trying to achieve and what the system ends up doing. It’s great to keep them on record as well!

Hire a Professional Dexterity Developer

Now that you are ready to move forward with your customizations into GP 2013’s Web Client, you’ll need to hire a Dexterity developer. Dexterity developers aren’t as easy to come by as other programming languages, but the good news is that if you've found one, they probably know their stuff (this isn't something your neighbor’s nephew in high school can do for you on the weekends). It’s a sophisticated programming language that takes years of experience to learn.

After having completed all of the leg work; conducting your business process analysis and providing your developer with use case documentation, the dexterity changes should be a breeze!

Here are a few things to look for in a Dexterity developer:
·         Is the programmer certified in Dexterity or Microsoft Dynamics GP‌?
·         How many or how long has the programmer been working with Dexterity‌?
·         Will the programmer provide documentation of their changes‌?
·         Will the programmer, or company they work for be able to support you in the years to come‌?

02 November, 2012

CSS Consolidator jiu-jitsu

CSS Consolidator

Sometimes you inherit a huge pile of CSS and want to make a few changes. Sometimes there are huge groups of duplicated rules. Sometimes, you just want to see everywhere Comic Sans is used in your stylesheets. (Hopefully, nowhere.)

Paste in your Source CSS below and click Consolidate CSS. Rules and selectors will be consolidated in the next textbox. All processing is done client-side in JavaScript.

Below that, you will see a breakdown of your styles, arranged by Selector, Attribute and Values.

Why did I do this? Why not?

Source CSS (No @media tags please)

Consolidated CSS

Selectors	Attributes	Values

Capturing (and using) raw SOAP messages in WCF

WCF is great for building web services. It's also great for interacting with existing web services. Visual Studio makes it so easy . . . add a service reference, point to the WSDL of the service and just like that, you have a set of classes to handle the service and data contracts.

Unfortunately, sometimes web services don't live up to their contracts. Recently, I was interacting with a web service and found that sometimes the response would be null. I fired up Fiddler and looked at the SOAP messages. Sure enough, an error had occurred on the remote server and the response, while valid XML, looked nothing like the promised Data Contract. It did however provide a useful description of the error.

There was no SOAP fault . . . no exception thrown (unless I tried to use the null response without checking), just a null response object by the time WCF handed it to me.

I searched the web and found several suggestions.

Use Fiddler . . . tried that but it doesn't help when you are trying to get your service into production.
Build a SOAP Extension . . . that's great if you are building a legacy asp.net XML Web Service and just want to log or tweak the messages. I'm doing WCF and needed to make the messages available to my service in real time.
Edit the Visual Studio Generated classes to wrap the response in XML and deserialize it yourself . . . that might work but you throw away so much niceness that's built for you already.

I ended up building a custom Endpoint behavior and applying it to the generated SOAP Client object.

First, you need a class to hold the raw Request and Response:

        public class InspectedSOAPMessages
        {
            public string Request { get; set; }

            public string Response { get; set; }
        }

Create an instance of the class and pass it into the new Endpoint Behavior:

        InspectedSOAPMessages soapMessages = new InspectedSOAPMessages();
        batchClient.Endpoint.Behaviors.Add(new CapturingEndpointBehavior(soapMessages));
        BatchService.batchResponseType response = batchClient.BatchOperation(batchRequest);
        batchClient.Close();
           //response will be null if the contract was violated . . . 
        if (response == null)
        {
           results.Message = soapMessages.Response;    } else { //process normal response

Now, all we need is the set of classes to handle the Endpoint Behavior:

    using System.ServiceModel;
    using System.ServiceModel.Channels;
    using System.ServiceModel.Description;
    using System.ServiceModel.Dispatcher;

    namespace MBSGuru
    {
    /// <summary>
        /// Allows capturing of raw SOAP Messages
        /// </summary>
        public class CapturingEndpointBehavior : IEndpointBehavior
        {
            /// <summary>
            /// Holds the messages
            /// </summary>
            public InspectedSOAPMessages SoapMessages { get; set; }

            public CapturingEndpointBehavior(InspectedSOAPMessages soapMessages)
            {
                this.SoapMessages = soapMessages;
            }

            /// <summary>
            /// Required by IEndpointBehavior
            /// </summary>
            /// <param name="endpoint"></param>
            /// <param name="bindingParameters"></param>
            public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters) { return; }

            /// <summary>
            /// Required by IEndpointBehavior
            /// </summary>
            /// <param name="endpoint"></param>
            /// <param name="clientRuntime"></param>
            public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
            {
                clientRuntime.MessageInspectors.Add(new CapturingMessageInspector(this.SoapMessages));
            }

            /// <summary>
            /// Required by IEndpointBehavior
            /// </summary>
            /// <param name="endpoint"></param>
            /// <param name="endpointDispatcher"></param>
            public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher) { return; }

            /// <summary>
            /// Required by IEndpointBehavior
            /// </summary>
            /// <param name="endpoint"></param>
            public void Validate(ServiceEndpoint endpoint) { return; }
        }

        /// <summary>
        /// Actual inspector that captures the messages
        /// </summary>
        public class CapturingMessageInspector : IClientMessageInspector
        {
            /// <summary>
            /// Holds the messages
            /// </summary>
            public InspectedSOAPMessages SoapMessages { get; set; }

            public CapturingMessageInspector(InspectedSOAPMessages soapMessages)
            {
                this.SoapMessages = soapMessages;
            }

            /// <summary>
            /// Called after the web service call completes.  Allows capturing of raw response.
            /// </summary>
            /// <param name="reply"></param>
            /// <param name="correlationState"></param>
            public void AfterReceiveReply(ref System.ServiceModel.Channels.Message reply, object correlationState)
            {
                this.SoapMessages.Response = reply.ToString();
            }

            /// <summary>
            /// Called before the web service is invoked.  Allows capturing of raw request.
            /// </summary>
            /// <param name="request"></param>
            /// <param name="channel"></param>
            /// <returns></returns>
            public object BeforeSendRequest(ref System.ServiceModel.Channels.Message request, IClientChannel channel)
            {
                this.SoapMessages.Request = request.ToString();
                return null;
            }
        }
    }

And just like that, you have a copy of the request and response. How you handle the violation of the contract is up to you. At least now you can report something more informative than "Error Occurred"

10 October, 2012

Create a Customer in Dynamics GP 2010 using eConnect

A few days ago I created a simple example to create a vendor in Dynamics GP using eConnect. I received a few requests to show a sample integration to create new customers. Creating new customers in Dynamics GP via eConnect is a common request and usually proceeds the creation of other receivables documents.

The sample below creates a new customer using the eConnect .NET assembly and in-memory serialization.

To run the following code on your machine:

Install the latest version of the eConnect 11 SDK.
Create a new Console Application in Microsoft Visual Studio.

Add references to these dynamic link libraries which are located by default in C:\Program Files (x86)\Microsoft Dynamics\eConnect 11.0\API\. (Ignore the x86 if you are using a 32-bit system.)

Microsoft.Dynamics.GP.eConnect.dll
Microsoft.Dynamics.GP.eConnect.Serialization.dll

Replace the Program.cs class in the project for the new class below.

using System;

using System.IO;

using System.Xml;

using System.Xml.Serialization;

using Microsoft.Dynamics.GP.eConnect;

using Microsoft.Dynamics.GP.eConnect.Serialization;

class Program

    static void Main(string[] args)

        Console.WriteLine("Beginning integration test.");

        using (eConnectMethods eConnectMethods = new eConnectMethods())

try

                Console.WriteLine("Creating a new customer.");

                // Modify the connection string for your environment.

                string connectionString = @"data source=localhost; initial catalog=TWO; integrated security=SSPI";

                // Create the customer.

                taUpdateCreateCustomerRcd customer = new taUpdateCreateCustomerRcd();

                customer.CUSTNMBR = "TEST001";

                customer.CUSTNAME = "Test Customer";

                // Assign the customer to a new master customer type.

                RMCustomerMasterType customerMasterType = new RMCustomerMasterType();

                customerMasterType.taUpdateCreateCustomerRcd = customer;

                // Assign the master customer type to a new collection of master customer types.

                RMCustomerMasterType[] customerMasterTypes = { customerMasterType };

                // Serialize the master vendor type in memory.

                eConnectType eConnectType = new eConnectType();

                MemoryStream memoryStream = new MemoryStream();

                XmlSerializer xmlSerializer = new XmlSerializer(eConnectType.GetType());

                // Assign the master customer types to the eConnectType.

                eConnectType.RMCustomerMasterType = customerMasterTypes;

                // Serialize the eConnectType.

                xmlSerializer.Serialize(memoryStream, eConnectType);

                // Reset the position of the memory stream to the start.

                memoryStream.Position = 0;

                // Create an XmlDocument from the serialized eConnectType in memory.

                XmlDocument xmlDocument = new XmlDocument();

                xmlDocument.Load(memoryStream);

                memoryStream.Close();

                // Call eConnect to process the XmlDocument.

                eConnectMethods.CreateEntity(connectionString, xmlDocument.OuterXml);

                Console.WriteLine("Successfully created customer {0}.", customer.CUSTNMBR);

            catch (Exception ex)

                Console.WriteLine("Exception occured: " + ex.Message);

            finally

                eConnectMethods.Dispose();

        Console.WriteLine("Integration test complete." +

                           Environment.NewLine +

                           Environment.NewLine);

        Console.WriteLine("Press <Enter> to continue...");

        Console.ReadLine();

Execute the project and you should see the following output:

And you should see the vendor created in Dynamics GP:

05 October, 2012

Create a Vendor in Dynamics GP 2010 with eConnect using In Memory Serialization

Developing several integrations between Microsoft Dynamics GP 2010 and various third-party systems the last few weeks reminded me to update my previous article on In Memory Serialization for eConnect 10.

Microsoft Dynamics GP 2010 uses eConnect version 11 which includes significant updates. Notably, the COM+ component has been changed to a WCF service.

In this example, I am creating a new vendor record in Dynamics GP using the same in memory serialization technique. Why write a file to disk unnecessarily?

To run the following code on your machine:

Install the latest version of the eConnect 11 SDK.
Create a new Console Application in Microsoft Visual Studio.