Migrate MS CRM 2011 OnPremise attached documents in notes entity to SharePoint 2013 OnPremise !!!

One of our client having problem with increasing database size very quickly and huge database size also becoming performance issue. Current database size 530GB. So what we and Microsoft suggest to migrate all attached files to SharePoint.

So, here I am going to demonstrate how we could achieve this. We have configured OOB CRM and SharePoint integration but challenging task was copy all existing attached CRM attachments to SharePoint 2013. We have written a console application to read all attached documents from case entity and copy over to SharePoint.

Let me explain how OOB CRM and SharePoint integration. I hope you know very well J. Let’s say we have configured List Component for CRM and SharePoint integration when we click on first time on Documents in left navigation it will ask to create folder in SharePoint and creates the folder (named as record primary field, there is no configuration OOB where we could change folder name other than primary field but we could write plugin to achieve this. Hope MS will provide OOB solution very soon) inside SharePoint and the process automatically create SharePoint document location entity record as well. So in the document migration process we have to follow the same process.

Please add the CRM SDK API’s and SharePoint client model API’s reference into your project.

Read all case entity records:

Note: Please implement paging if case records are more than 5000.

IOrganizationService _Service;

_Service = GetCRMService();

QueryExpression _Query = new QueryExpression

{

EntityName = "incident",

ColumnSet = new ColumnSet("title"),

Criteria = new FilterExpression

{

FilterOperator = LogicalOperator.Or,

Conditions =

{

new ConditionExpression

{

AttributeName = "statuscode",

Operator = ConditionOperator.Equal,

Values = { 1 }

},

}

}

};

EntityCollection EntityCollection = _Service.RetrieveMultiple(_Query);

if (EntityCollection.Entities.Count > 0)

{

foreach (Entity _Case in EntityCollection.Entities)

{

string _Title = _Case.Attributes["title"].ToString();

// Get all notes related to case

EntityCollection _Notes = GetAllNotes(_Case.Id, _Service);

//Check for return results

if (_Notes.Entities.Count > 0)

{

foreach (Entity _Note in _Notes.Entities)

{

if (_Note.Attributes.Contains("filename"))

{

string _FileName = _Note.Attributes["filename"].ToString();

string _DocumentBody = _Note.Attributes["documentbody"].ToString();

using (FileStream fileStream = new FileStream(_FileName, FileMode.OpenOrCreate))

{

byte[] fileContent = Convert.FromBase64String(_DocumentBody);

//fileStream.Write(fileContent, 0, fileContent.Length);

CopyToSharePoint2013(_Case.Id,_Service, _Title,fileContent, _FileName);

}

}

}

}

}

}

public static IOrganizationService GetCRMService()

{

IOrganizationService _Service=null;

try

{

//Authenticate using credentials of the logged in user;

ClientCredentials Credentials = new ClientCredentials();

string _USerName = @"DOMAIN\crmadmin";

Credentials.UserName.UserName = _USerName;

Credentials.UserName.Password = "pass@word1";

string _Server = System.Configuration.ConfigurationManager.AppSettings["CRMSever"].ToString();

string _OrgName = System.Configuration.ConfigurationManager.AppSettings["CRMOrgName"].ToString();

string _ServiceUrl = System.Configuration.ConfigurationManager.AppSettings["CRMSeviceURL"].ToString();

Uri OrganizationUri = new Uri(_Server+"/"+_OrgName+"/"+_ServiceUrl);

Uri HomeRealmUri = null;

//OrganizationServiceProxy serviceProxy;

OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null);

_Service = (IOrganizationService)serviceProxy;

}

catch (Exception Ex)

{

Console.Write(Ex.Message);

if (Ex.InnerException != null)

{

Console.WriteLine(Ex.InnerException.Message);

Console.ReadLine();

}

}

return _Service;

}

public static void CopyToSharePoint2013(Guid _CaseId,IOrganizationService _service, string _FolderName, byte[] fileContent, string filename)

{

try

{

string _SharePointServer = System.Configuration.ConfigurationManager.AppSettings["SharePointServer"].ToString();

using (ClientContext _clientContext = new ClientContext(_SharePointServer))

{

_clientContext.Credentials = new System.Net.NetworkCredential("crmadmin", pass@word1", "domain");

//create web object

Web web = _clientContext.Web;

List oList = web.Lists.GetByTitle("Case");

if(oList!=null)

{

//Create Folder

var folders = oList.RootFolder.Folders;

_clientContext.Load(folders);

_clientContext.ExecuteQuery();

var newFolder = folders.Add(_FolderName);

_clientContext.ExecuteQuery();

//Copy file into created folder

FileCreationInformation oFileCreationInformation = new FileCreationInformation();

oFileCreationInformation.Content = fileContent;

oFileCreationInformation.Url =@_SharePointServer +"/case/"+ _FolderName +"/" + filename;

oFileCreationInformation.Overwrite = true;

oList.RootFolder.Files.Add(oFileCreationInformation);

_clientContext.Load(oList);

_clientContext.ExecuteQuery();

//Create SharePoint Location record

if (!AlreadyExistDocumentLocationRecord(_CaseId,_service))

{

CreateDocumentLocationRecord(_service, _FolderName, oFileCreationInformation.Url, _CaseId);

}

}

}

}

catch (Exception Ex)

{

Console.Write(Ex.Message);

if (Ex.InnerException != null)

{

Console.WriteLine(Ex.InnerException.Message);

Console.ReadLine();

}

}

}

private static void CreateDocumentLocationRecord(IOrganizationService service, string _locationName, string _locationURL, Guid _regarding)

{

try

{

// Instantiate an account object.

Entity DocumentLocation = new Entity("sharepointdocumentlocation");

//set the fields values

DocumentLocation["name"] = _locationName;

DocumentLocation["regardingobjectid"] = new EntityReference("incident", _regarding);

DocumentLocation["relativeurl"] = _locationName;

if((GetParentSiteUrl(service))!=Guid.Empty)

{

DocumentLocation["parentsiteorlocation"] = new EntityReference("sharepointdocumentlocation", GetParentSiteUrl(service));

}

//create the record

Guid DocumentLocationid = service.Create(DocumentLocation);

}

catch (Exception Ex)

{

Console.Write(Ex.Message);

if (Ex.InnerException != null)

{

Console.WriteLine(Ex.InnerException.Message);

Console.ReadLine();

}

}

}

private static Guid GetParentSiteUrl(IOrganizationService service)

{

Guid _Id = Guid.Empty;

try

{

QueryExpression _Query = new QueryExpression

{

EntityName = "sharepointdocumentlocation",

ColumnSet = new ColumnSet("relativeurl"),

Criteria = new FilterExpression

{

FilterOperator = LogicalOperator.And,

Conditions =

{

new ConditionExpression

{

AttributeName = "relativeurl",

Operator = ConditionOperator.Equal,

Values = { "incident" }

},

}

}

};

EntityCollection _EntityLocation = service.RetrieveMultiple(_Query);

if (_EntityLocation.Entities.Count > 0)

{

_Id= _EntityLocation.Entities[0].Id;

}

}

catch (Exception Ex)

{

Console.Write(Ex.Message);

if (Ex.InnerException != null)

{

Console.WriteLine(Ex.InnerException.Message);

Console.ReadLine();

}

}

return _Id;

}

private static bool AlreadyExistDocumentLocationRecord(Guid CaseId, IOrganizationService _service)

{

QueryExpression _Query = new QueryExpression

{

EntityName = "sharepointdocumentlocation",

ColumnSet = new ColumnSet("regardingobjectid"),

Criteria = new FilterExpression

{

FilterOperator = LogicalOperator.And,

Conditions =

{

new ConditionExpression

{

AttributeName = "regardingobjectid",

Operator = ConditionOperator.Equal,

Values = { CaseId }

},

}

}

};

EntityCollection _EntityLocation = _service.RetrieveMultiple(_Query);

if (_EntityLocation.Entities.Count > 0)

return true;

else

return false;

}