23 Apr 2011

SharePoint and CRM 2011 Integration: Part 2

by Dave

So, in Part 1 we discussed how to get our entity data from CRM, and into XML.  In this post, I will run through what we want the output to be for each entity.

This basically boils down to two classes per entity; an entity class and a service class.

Entity Class

The entity class is a container for the data in the entity - it essentially mirrors the CRM entity, including all of it's attributes:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CRM.Entities
{
    /// 
    /// This class contains the properties for Account. The properties keep the data for Account.
    /// If you want to rename the class, don't forget to rename the entity in the model xml as well.
    /// 
    public partial class Account
    {
        #region Attributes
        
        public System.Guid DefaultPriceLevelId { get; set; }
        public System.String new_HelpstarID { get; set; }
        public System.String new_ServiceManagerID { get; set; }
        public System.String Address1_ShippingMethodCode { get; set; }
        public System.String IndustryCode { get; set; }
        public System.String Address2_Fax { get; set; }
        public System.Boolean DoNotFax { get; set; }
        public System.String Description { get; set; }
        public System.String AccountRatingCode { get; set; }
        public System.String Address2_City { get; set; }
        public System.String Address2_UPSZone { get; set; }
   
.. other attributes ommitted for brievity

        #endregion

        #region Relationships
        
        //Relationship account_principalobjectattributeaccess
        //Relationship new_account_serviceappointment_Customer
        //Relationship new_account_new_timesheet_Customer
        //Relationship Account_Faxes
        //Relationship order_customer_accounts

.. other relationships ommitted for brievity

        #endregion
    }
}

 As you can see above, the entity class contains a load of automatic properties to store the relevant attribute data.

Service Class

The service class does the heavy lifting; it performs the CRUD operations which will be invoked by SharePoint's BCS:

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Aspire.CRM;
    using Aspire.CRM.CrmSdk;
    using Microsoft.Office.Server.Diagnostics;

    namespace CRM.Entities
    {
      /// <summary>
      /// All the methods for retrieving, updating and deleting data are implemented in this class file.
      /// The below shows the finder and specific finder method for Account.
      /// </summary>
      public class AccountService
      {
        /// <summary>
        /// This is a specific finder method for Account.
        /// </summary>
        /// <param name="ID" />
        /// <returns>Account</returns>
        public static Account ReadItem(System.Guid ID)
        {
          try
          {
            CrmService crmService = CRMServiceManager.GetCrmService("OrganisationName", "Account");

            ColumnSet cols = new ColumnSet();
            cols.Attributes = 
                new string[] { 
                    "defaultpricelevelid",
                    "new_helpstarid",
                    "new_servicemanagerid",
                    "address1_shippingmethodcode",
                    "industrycode",
                    "address2_fax",
                    "donotfax",
                    "description",
                    "accountratingcode",
                    "address2_city",
                    "address2_upszone",
                    
                .. other attributes ommitted
                };
        
            account entity = crmService.Retrieve("account", ID, cols) as account;

            Account oNewEntity = new Account()
            {
                DefaultPriceLevelId = entity.defaultpricelevelid != null ? (System.Guid) entity.defaultpricelevelid.Value : default(System.Guid),
                new_HelpstarID = entity.new_helpstarid != null ? (System.String) entity.new_helpstarid.Value : default(System.String),
                new_ServiceManagerID = entity.new_servicemanagerid != null ? (System.String) entity.new_servicemanagerid.Value : default(System.String),
                Address1_ShippingMethodCode = entity.address1_shippingmethodcode != null ? (System.String) entity.address1_shippingmethodcode.Value : default(System.String),
                IndustryCode = entity.industrycode != null ? (System.String) entity.industrycode.Value : default(System.String),
                Address2_Fax = entity.address2_fax != null ? (System.String) entity.address2_fax.Value : default(System.String),
                DoNotFax = entity.donotfax != null ? (System.Boolean) entity.donotfax.Value : default(System.Boolean),
                Description = entity.description != null ? (System.String) entity.description.Value : default(System.String),
                AccountRatingCode = entity.accountratingcode != null ? (System.String) entity.accountratingcode.Value : default(System.String),
                Address2_City = entity.address2_city != null ? (System.String) entity.address2_city.Value : default(System.String),
                Address2_UPSZone = entity.address2_upszone != null ? (System.String) entity.address2_upszone.Value : default(System.String),
            .. other attributes ommitted
            });

            return oNewEntity;
          }
          catch (Exception ex)
          {
            PortalLog.LogString("Account Model (Read Item): {0}", ex.Message);
            return null;
          }
        }
    
        /// <summary>
        /// This is a sample finder method for Account.
        /// If you want to delete or rename the method think about changing the xml in the BDC model file as well.
        /// </summary>
        /// <returns>IEnumerable of Account</returns>
        public static IEnumerable<Account> ReadList()
        {
          try
          {
            CrmService crmService = CRMServiceManager.GetCrmService("OrganisationName", "Account");

            ColumnSet cols = new ColumnSet();
            cols.Attributes = 
                new string[] { 
                    "defaultpricelevelid",
                    "new_helpstarid",
                    "new_servicemanagerid",
                    "address1_shippingmethodcode",
                    "industrycode",
                    "address2_fax",
                    "donotfax",
                    "description",
                    "accountratingcode",
                    "address2_city",
                    "address2_upszone",
                    
                .. other attributes ommitted
                };

            QueryExpression query = new QueryExpression();
            query.EntityName = "account";
            query.ColumnSet = cols;

            BusinessEntityCollection entities = crmService.RetrieveMultiple(query);

            List<Account> lstAccounts = new List<Account>();
            foreach (BusinessEntity be in entities.BusinessEntities)
            {
              account entity = be as account;
              lstAccounts.Add(new Account()
              {
                  DefaultPriceLevelId = entity.defaultpricelevelid != null ? (System.Guid) entity.defaultpricelevelid.Value : default(System.Guid),
                  new_HelpstarID = entity.new_helpstarid != null ? (System.String) entity.new_helpstarid.Value : default(System.String),
                  new_ServiceManagerID = entity.new_servicemanagerid != null ? (System.String) entity.new_servicemanagerid.Value : default(System.String),
                  Address1_ShippingMethodCode = entity.address1_shippingmethodcode != null ? (System.String) entity.address1_shippingmethodcode.Value : default(System.String),
                  IndustryCode = entity.industrycode != null ? (System.String) entity.industrycode.Value : default(System.String),
                  Address2_Fax = entity.address2_fax != null ? (System.String) entity.address2_fax.Value : default(System.String),
                  DoNotFax = entity.donotfax != null ? (System.Boolean) entity.donotfax.Value : default(System.Boolean),
                  Description = entity.description != null ? (System.String) entity.description.Value : default(System.String),
                  AccountRatingCode = entity.accountratingcode != null ? (System.String) entity.accountratingcode.Value : default(System.String),
                  Address2_City = entity.address2_city != null ? (System.String) entity.address2_city.Value : default(System.String),
                  Address2_UPSZone = entity.address2_upszone != null ? (System.String) entity.address2_upszone.Value : default(System.String),
              .. other attributes ommitted
              });
            }

            return lstAccounts;
          }
          catch (Exception ex)
          {
            PortalLog.LogString("Account Model (Read Item): {0}", ex.Message);
            return null;
          }
        }
      }
    }

The service class above has two methods; ReadList and ReadItem.  These map to the Finder and SpecificFinder methods respectively for the Account entity.  I have not yet implemented the other CRUD operations, since this is just a prototype at this stage - I will update the post when I have.

Doing this will allow you to create External Content Types (ECTs) for each entity within your CRM installation, including any custom ones, which can then be exposed to SharePoint search, used in lists and libraries, and all sorts of other fun things!

I have uploaded both source-code and binaries for this tool, here: SharePoint and CRM 2011 Integration: BCS Generator Tool.

Comments (5) -

Dan Japan
5/6/2011 9:19:41 AM #

Hi Dave,
Great stuff. I have been looking for a tool like this for litterally days.
Im having some trouble with the RetrieveOrganizationsRequest thuogh. My On Premise system seems to be returning 0. And i get an error saying the user account provided does not belong to any organizations.
Can you think of any reason why this would be the case? Any help would be appreciated.

Thanks

Dan Japan
5/7/2011 5:11:30 AM #

Had a failed organization on my side. Created a new one and it worked fine.

Dave United Kingdom
5/7/2011 3:04:25 PM #

That's great news Dan.  Do let me know how you get on integrating it with your SharePoint 2010 installation.

Cheers,

Dave

Osvaldo Sousa Ireland
5/16/2011 2:13:26 PM #

Hi Dave,

Thanks for sharing your BCS generator tool. I am currently having some difficulties using this tool.
I am able to generate the model but when I import it to a SharePoint solution it cannot compile throwing errors such as missing identifiers name in the BCS model xml  to missing assemblies (Aspire.SharePoint.CRMBDCModel, Aspire.SharePoint.CRMBDCModel.CrmSdk). I am missing anything? Do you have any walk-through  for using your tool

Gary McAllister United Kingdom
5/23/2011 8:44:00 PM #

Hey,

Thank you for contributing this to the community.

Just wondered how you are handling picklists between the two systems?

Thanks

Gary

About me

I am a software developer in the UK, and specialise in Microsoft technologies.

Starting as a web developer working on the British Airways website at Leighton, I progressed to .net development at Aspire Technology Solutions.  A lot of my work has involved SharePoint, and I have implemented numerous solutions using the platform.  Recently I have been involved in a large-scale enterprise application utilising WCF services, and also a large BI databse using SQL Server technologies.

Please download my CV.

MCTS

Month List