Integrating SharePoint 2010 and CRM 2011
Part 3: Publishing CRM entities in SharePoint.
Hello SharePointers !!
I have been working with SharePoint for many years, but this is the first time I decided to blog about something.
The driving force behind choosing this particular topic is the two-fold: Firstly, there is not enough information available and secondly, due to the constant reminders by colleague Andy to help the larger community out there.
Andy, himself has published multi-part blog series on this topic and in essence this post forms the part 3 of his series.
Over here we will see how we can fetch and display complete set of entity information from CRM 2011 in a SharePoint Web part. That is in the SharePoint Web Part I will have a drop down list with the collection of all CRM ‘Entity’ and another drop down that will contain the associated ‘Views’ and finally the Entity Result set. But before we do that it’s important to understand why this is not straight forward!
Thought Process:
1) CRM 2011 provides SDK and I can always develop custom solutions. If I choose to refer to “Walkthrough: Build a Web Application That Connects to Microsoft Dynamics CRM 2011 Using Developer Extensions” I can easily display CRM 2011 entity information in a Web Application.
2) That means I can easily transform those custom solutions (like a Web Application) into a SharePoint 2010 web part, and bingo! We are all set!
Catch:
1) CRM 2011 is based on .Net framework 4.0 and SP 2010 is on .Net 3.5. So if you try to implement the step 2 you can’t even build your SharePoint Web Part forget about deploying it.
Resolution [Important Steps]:
1) Publish an intermediary WCF service with relevant methods to expose information from CRM 2011.
2) Create a SharePoint Web Part and make appropriate calls to the WCF service and then transform the result set as per your needs for the end user.
The Steps in detail:
1) We need to create and publish a WCF service to call in CRM 2011. This service can have multiple facets based on your needs; may be you intend to perform add/edit/delete apart from just “display” so create the WCF methods judiciously. For now I will stick to the display part.
IMP: The code blocks below doesn’t confirm to the coding best practices but does work, and I am sure you guys can transform it accordingly. I have ripped the pieces from from all over the web
a) Generate Early Bound Types:
Run the CrmSvcUtil.exe tool, with the Microsoft.Xrm.Client.CodeGeneration extension, to generate your entity classes and service contexts.
The following example command creates a file called “Xrm.cs” that points at an instance of Microsoft Dynamics CRM. Note that the Microsoft.Xrm.Client.CodeGeneration.dll file must be in the same directory as the CrmSvcUtil.exe file, or in the system GAC, when you run this command.
crmsvcutil.exe /codeCustomization:"Microsoft.Xrm.Client.CodeGeneration.CodeCustomization, Microsoft.Xrm.Client.CodeGeneration"
/out:Xrm\Xrm.cs /url:https://dpm2012/fabrikam/XRMServices/2011/Organization.svc /domain:CONTOSO /username:spfarm
/password:Welcome@123 /namespace:Xrm /serviceContextName:XrmServiceContext
b) Use Visual Studio 2010 to create a WCF Service Application (ensure .NET Framework 4).
c) Right-click the project in Visual Studio, click Add, and then click Existing Item. Select the “xrm.cs” file that you created when you generated the early bound types.
d) Right-click the project in Visual Studio, click Add, add a new class to the project. The Add New Item dialog box appears. Rename the class as Entities.cs. Similarly add one more class and name it as Views.cs
e) Add couple of properties in both these classes (please name it appropriately)
public string MyProperty { get; set; }
public string MyProperty1 { get; set; }
f) Add the references as shown below (for the CRM dll the Source will be SDK\bin, don’t search for C:\Shariq )
g) Now your Solution should look more like below (few more references might have been inadvertently added)
h) Right-click the project in Visual Studio and add the Service Reference for the Organization.svc
i) Open the interface IService1.cs and replace the ServiceContract with the code below:
[ServiceContract]
public interface IService1
{
[OperationContract]
string GetMessage();
[OperationContract]
List<Entities> GetEntityData();
[OperationContract]
List<Views> GetViewData(int SelectedEntity);
[OperationContract]
DataSet GetCompleteData(string _SavedQueryId);
}
j) Now we need to implement the Service1.svc, copy the below code (make appropriate changes for your OrganizationUri i.e. Organization.svc )
public class Service1 : IService1
{
public List<Entities> GetEntityData()
{
ClientCredentials Credentials = new ClientCredentials();
Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;
//This URL needs to be updated to match the servername and Organization for the environment.
Uri OrganizationUri = new Uri("https://DPM2012/TailspinToysDB/XRMServices/2011/Organization.svc");
List<Entities> EntityList = new List<Entities>();
Uri HomeRealmUri = null;
using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri,
Credentials, null))
{
Microsoft.Xrm.Sdk.IOrganizationService service = serviceProxy as Microsoft.Xrm.Sdk.IOrganizationService;
RetrieveAllEntitiesRequest request = new RetrieveAllEntitiesRequest()
{
EntityFilters = EntityFilters.Attributes,
RetrieveAsIfPublished = true
};
// Retrieve the MetaData.
RetrieveAllEntitiesResponse response = (RetrieveAllEntitiesResponse)serviceProxy.Execute(request);
foreach (EntityMetadata currentEntity in response.EntityMetadata)
{
if (currentEntity.IsCustomizable.Value == true)
{
foreach (AttributeMetadata currentAttribute in currentEntity.Attributes)
{
Entities e = new Entities();
e.MyProperty = currentEntity.LogicalName;
e.MyProperty1 = currentEntity.ObjectTypeCode.Value.ToString();
if (EntityList.Where(en => en.MyProperty.Equals(e.MyProperty) &&
en.MyProperty1.Equals(e.MyProperty1)).Count() == 0)
{
EntityList.Add(e);
}
}
}
}
}
return EntityList;
}
public List<Views> GetViewData(int paramSelectedEntity)
{
ClientCredentials Credentials = new ClientCredentials();
Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;
//This URL needs to be updated to match the servername and Organization for the environment.
Uri OrganizationUri = new Uri("https://dpm2012/TailspinToysDB/XRMServices/2011/Organization.svc");
Uri HomeRealmUri = null;
List<Views> ViewsList = new List<Views>();
using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri,
Credentials, null))
{
serviceProxy.EnableProxyTypes();
Microsoft.Xrm.Sdk.IOrganizationService service = (Microsoft.Xrm.Sdk.IOrganizationService)serviceProxy;
// Retrieve Views
QueryExpression mySavedQuery = new QueryExpression
{
ColumnSet = new ColumnSet("savedqueryid", "name", "querytype", "isdefault", "returnedtypecode",
"isquickfindquery"),
EntityName = SavedQuery.EntityLogicalName,
Criteria = new FilterExpression
{
Conditions =
{
new ConditionExpression
{
AttributeName = "returnedtypecode",
Operator = ConditionOperator.Equal,
Values = {paramSelectedEntity}
}
}
}
};
RetrieveMultipleRequest retrieveSavedQueriesRequest = new RetrieveMultipleRequest { Query = mySavedQuery };
RetrieveMultipleResponse retrieveSavedQueriesResponse =
(RetrieveMultipleResponse)serviceProxy.Execute(retrieveSavedQueriesRequest);
DataCollection<Entity> savedQueries = retrieveSavedQueriesResponse.EntityCollection.Entities;
foreach (Entity ent in savedQueries)
{
SavedQuery rsq = (SavedQuery)ent;
if (rsq.IsDefault == true)
{
Views e = new Views();
e.MyProperty = rsq.Name.ToString();
e.MyProperty1 = rsq.Id.ToString();
ViewsList.Add(e);
}
}
}
return ViewsList;
}
public DataTable convertEntityCollectionToDataTable(EntityCollection BEC)
{
DataTable dt = new DataTable();
int total = BEC.Entities.Count;
for (int i = 0; i < total; i++)
{
DataRow row = dt.NewRow();
Entity myEntity = (Entity)BEC.Entities[i];
var keys = myEntity.Attributes.Keys;
foreach (var item in keys)
{
string columnName = item;
string value = getValuefromAttribute(myEntity.Attributes[item]);
if (dt.Columns.IndexOf(columnName) == -1)
{
dt.Columns.Add(item, Type.GetType("System.String"));
}
row[columnName] = value;
}
dt.Rows.Add(row);
}
return dt;
}
private string getValuefromAttribute(object p)
{
if (p.ToString() == "Microsoft.Xrm.Sdk.EntityReference")
{
return ((EntityReference)p).Name;
}
if (p.ToString() == "Microsoft.Xrm.Sdk.OptionSetValue")
{
return ((OptionSetValue)p).Value.ToString();
}
if (p.ToString() == "Microsoft.Xrm.Sdk.Money")
{
return ((Money)p).Value.ToString();
}
if (p.ToString() == "Microsoft.Xrm.Sdk.AliasedValue")
{
return ((Microsoft.Xrm.Sdk.AliasedValue)p).Value.ToString();
}
else
{
return p.ToString();
}
}
public DataSet GetCompleteData(string _strSavedQueryId)
{
DataSet ds = new DataSet();
Guid _SavedQueryId = new Guid(_strSavedQueryId);
ClientCredentials Credentials = new ClientCredentials();
Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;
//This URL needs to be updated to match the servername and Organization for the environment.
Uri OrganizationUri = new Uri("https://dpm2012/TailspinToysDB/XRMServices/2011/Organization.svc");
Uri HomeRealmUri = null;
using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri,
Credentials, null))
{
serviceProxy.EnableProxyTypes();
Microsoft.Xrm.Sdk.IOrganizationService service = (Microsoft.Xrm.Sdk.IOrganizationService)serviceProxy;
ColumnSet cSet = new ColumnSet("savedqueryid", "fetchxml");
SavedQuery rsq = (SavedQuery)service.Retrieve(SavedQuery.EntityLogicalName, _SavedQueryId, cSet);
RetrieveMultipleRequest req = new RetrieveMultipleRequest();
FetchExpression fetch = new FetchExpression(rsq.FetchXml);
req.Query = fetch;
RetrieveMultipleResponse resp = (RetrieveMultipleResponse)service.Execute(req);
DataTable ObjDT = convertEntityCollectionToDataTable(resp.EntityCollection);
ds.Tables.Add(ObjDT);
}
return ds;
}
}
k) Now the CRM WCF Service is ready and we can publish it at IIS. The steps to publish any WCF service can be found easily over web. Starters can refer to https://debugmode.net/2010/09/07/walkthrough-on-creating-wcf-4-0-service-and-hosting-in-iis-7-5/. Skip the Service creation steps from this blog just refer to Publishing steps and also ensure to test it locally using a local Client.
If you have reached here successfully, the battle is almost won 
2) Now we just need to create a simple SharePoint 2010 Visual Web Part to consume this service as per need.
a) Use Visual Studio 2010 to create a new Visual Web Part project.
b) Right Click the project and Add the Service Reference for our CRM Service published in Step 1.
c) Add the following code to VisualWebPart1UserControl.ascx :
<asp:DropDownList ID="ddlEntityList" runat="server" AutoPostBack="True"
OnSelectedIndexChanged="ddlEntityList_SelectedIndexChanged"></asp:DropDownList>
<asp:DropDownList ID="ddlViewsList" runat="server" AutoPostBack="True"
OnSelectedIndexChanged="ddlViewsList_SelectedIndexChanged"></asp:DropDownList>
<asp:GridView ID="gdEntity" ViewStateMode="Disabled" runat="server" />
d) Add the following code to VisualWebPart1UserControl.ascx.cs:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Service1Client proxy = new Service1Client();
ddlEntityList.DataSource = proxy.GetEntityData();
ddlEntityList.DataTextField = "MyProperty";
ddlEntityList.DataValueField = "MyProperty1";
ddlEntityList.DataBind();
}
}
protected void ddlEntityList_SelectedIndexChanged(object sender, EventArgs e)
{
ddlViewsList.Items.Clear();
int index = Convert.ToInt32(ddlEntityList.SelectedValue);
Service1Client proxy = new Service1Client();
ddlViewsList.DataSource = proxy.GetViewData(index);
ddlViewsList.DataTextField = "MyProperty";
ddlViewsList.DataValueField = "MyProperty1";
ddlViewsList.DataBind();
}
protected void ddlViewsList_SelectedIndexChanged(object sender, EventArgs e)
{
string str = ddlViewsList.SelectedValue;
Service1Client proxy = new Service1Client();
gdEntity.DataSource = proxy.GetCompleteData(str);
gdEntity.DataBind();
}
e) Right click the Project and deploy to the SharePoint Site.
f) Go to the SharePoint 2010 Site and add the Visual Web Part to a page where you want to display the Web Part. And ‘Bingo’. You should see the something like this:
This SharePoint Web Part code above is pretty basic in nature for now, but I am sure you can improve upon this sample code – you know, things like:
- When not to show the ‘View’ Drop Down when the Entity is refreshed.
- Or when there is no result set we must show appropriate result rather than the Yellow screen of death.
- Or you want to filter the Grid by removing certain columns from the dataset or renaming the column Header etc.
I leave all it all up to you, as the sample is attached. Use it/Break it and Enjoy !!!
Food for thought
1) In principle this should work for MOSS 2007, for those who are still on it
2) In SharePoint 2013, we can merge the WCF code implementation within the SharePoint web part itself.
You can download the complete sample from here.
Code References
https://mscrmbi.blogspot.com/2012/02/crm-2011-convert-entitycollection-to.html
https://msdn.microsoft.com/en-us/library/gg695790.aspx
Hope this helps a needy soul! If it does, please leave your comment/feedback.
Cheers!
Comments
- Anonymous
January 31, 2013
good article, explains beautifully