Success Stories of Software Engineer: Update Bulk Records including creation and deactivation from .csv File to CRM

1. Save Excel File in your Drive

2. 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 is an example command to create 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 global assembly cache, when you run this command. The first command shown is for an on-premises organization. The second command is for a CRM Online organization. Both commands should be executed as a single command line with no line breaks.

CrmSvcUtil.exe

/codeCustomization:"Microsoft.Xrm.Client.CodeGeneration.CodeCustomization, Microsoft.Xrm.Client.CodeGeneration"

/out:Xrm.cs /url:https://<mydomain>.api.crm.dynamics.com/XRMServices/2011/Organization.svc

/username:<myusername>@<mydomain>.onmicrosoft.com /password:<mypassword> /namespace:Xrm /serviceContextName:XrmServiceContext

3. Create a Console Application project in Visual studio 2012.

4. Add the following references from the SDK\bin folder. You can skip this step and the next by simply installing the Microsoft.CrmSdk.Extensions NuGet package.

AntiXSSLibrary.dll

Microsoft.Crm.Sdk.Proxy.dll

Microsoft.Xrm.Client.dll

Microsoft.Xrm.Portal.dll

Microsoft.Xrm.Portal.Files.dll

Microsoft.Xrm.Sdk.dll

5. Add the following references from .NET.

System.IdentityModel.dll

Microsoft.ServiceBus.dll

System.Data.Services.dll

System.Data.Services.Client.dll

System.Runtime.Serialization.dll

6. Make sure the console project specifies .NET Framework 4.5.2 as the target framework and not .NET Framework 4.5.2 Client Profile. Check the properties of the project by selecting the ConsoleApplication1 project in Solution Explorer and clicking Alt-Enter.

Right Click ConsoleApplication1 -> Properties

7. 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.

Right-click your project again, click Add, and then click New Item.

Select Application Configuration File from the options and then click Add.

Edit the App.config configuration file with your specific connection string.

XML

<?xml version="1.0"?>

</configSections>

<microsoft.xrm.client>

</contexts>

</microsoft.xrm.client>

</startup>

</configuration>

8. Add a using statement to the namespace that you used in Step 1 when you created the project (for this example “Xrm”).

using System;

using System.Linq;

using System.Threading.Tasks;

using System.ServiceModel;

using System.ServiceModel.Description;

using System.Collections.Generic;

using System.Text;

using System.Text.RegularExpressions;

using System.Xml;

using System.Xml.Linq;

using System.Xml.XPath;

using System.Xml;

using System.IO;

using System.Linq;

//earlybound types

using Xrm;

// These namespaces are found in the Microsoft.Xrm.Sdk.dll assembly

// found in the SDK\bin folder.

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Client;

using Microsoft.Xrm.Sdk.Query;

using Microsoft.Xrm.Sdk.Discovery;

// These namespaces are found in the Microsoft.Sharepoint.Client.dll assembly

// found in the SDK\bin folder.

using Microsoft.Win32;

using Microsoft.SharePoint.Client;

// This namespace is found in Microsoft.Crm.Sdk.Proxy.dll and Microsoft.Crm.Sdk.Workflow.dll assembly

// found in the SDK\bin folder.

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Metadata;

using Microsoft.Xrm.Sdk.Workflow;

using Microsoft.Crm.Sdk.Messages;

// These namespaces are found in the System.Data assembly

// found in the .NET

using System.Data;

using System.Data.Services;

using System.Diagnostics;

using System.Globalization;

using System.Reflection;

using System.Net;

// add the following OleDb statement to connect with Excel

using System.Data.OleDb;

using System.Data.SqlClient;

using Microsoft.VisualBasic.FileIO;

9. Physical Design

In the Code below I am using a DataTable and LINQ to retrieve records from Excel and CRM then later performing the Join operation. SBI is unique identifier to identify records in CRM and csv File.

CSV Fille	CRM File	Action on Left Table	Action on Right Table
1	2	Create	Update
2	3	Update	Update
3	5	Update	Deactivate
4	6	Create	Update
6	7	Update	Deactivate

10. Declare the Main Method as below:-

static void Main(string[] args)

{

//Get csv file data in Data Table

DataTable CPHData = GetDataTableFromCSVFile(@"C:\temp\CHP_Import_File\Organisation.csv");

int totalCPHRecordsCPHData = CPHData.Rows.Count;

Console.WriteLine(String.Format("{0}", totalCPHRecordsCPHData + " records found in CSV file"));

// find duplicate records using Linq from datatable

var duplicatesSBICSV = CPHData.AsEnumerable().GroupBy(r => r[10]).Where(gr => gr.Count() > 1).ToList();

if (duplicatesSBICSV.Count == 0)

{

//Create a DataTable from account entity from CRM

//Take SBI and accountid columns

DataTable CRMData = GetDataTableFromCRM();

int totalCRMRecords = CRMData.Rows.Count;

Console.WriteLine(String.Format("{0}", totalCRMRecords + " records found in Account entity"));

var duplicatesCRM = CRMData.AsEnumerable().GroupBy(r => r[0]).Where(gr => gr.Count() > 1).ToList();

if (duplicatesCRM.Count == 0)

{

//Get Scenarios in Data Table

DataTable ScenarioCPHData = GetDataTableFromScenario();

int totalTeamRecordsScenario = ScenarioCPHData.Rows.Count;

Console.WriteLine(String.Format("{0}", totalTeamRecordsScenario + " records found in Scenarios"));

//Create thrid Data Table

DataTable CPHResultData = new DataTable();

//get the specific coloumn of datatable

CPHResultData.Columns.Add("MSBI", typeof(int));

CPHResultData.Columns.Add("ID", typeof(string));

foreach (DataColumn column in CPHData.Columns)

{

CPHResultData.Columns.Add(column.ColumnName);

}

//Match file data with CRM data based on SBI

//Left Join

var CPHJoin = from dataRows1 in CPHData.AsEnumerable().Distinct(DataRowComparer.Default)

join dataRows2 in CRMData.AsEnumerable().Distinct(DataRowComparer.Default)

on dataRows1.Field<int>("SBI") equals dataRows2.Field<int>("MSBI") into lj

from r in lj.DefaultIfEmpty()

select CPHResultData.LoadDataRow(new object[]

{r == null ? 0 : r.Field<int>("MSBI"),r == null ? null : r.Field<string>("ID"),dataRows1.Field<string>("Account Name"),dataRows1.Field<string>("Address 1: Name"),

dataRows1.Field<string>("Address 1: Street 1"),dataRows1.Field<string>("Address 1: Street 2"),

dataRows1.Field<string>("Address 1: City"),dataRows1.Field<string>("Address 1: State/Province"),

dataRows1.Field<string>("Address 1: Country/Region"),dataRows1.Field<string>("Address 1: ZIP/Postal Code"),

dataRows1.Field<string>("Main Phone"),dataRows1.Field<string>("Other Phone"),dataRows1.Field<int>("SBI"),

dataRows1.Field<string>("Scenario Type"),dataRows1.Field<string>("Case Initiation Date"),dataRows1.Field<string>("Owner"),

dataRows1.Field<string>("has LK CPH"),r == null ? "Create" : "Update", }, true);

//display result in third datatable

CPHResultData = CPHJoin.Distinct().CopyToDataTable();

DataRow[] CreateResult = CPHResultData.Select("Action = 'Create'");

//Get CRM service context from app.config

var xrm = new XrmServiceContext("Xrm");

if (CreateResult.Length >= 1)

{

//create a new datatable if it has one or more record to create

DataTable CreateResultData = CreateResult.Distinct().CopyToDataTable();

int totalCreateResultData = CreateResultData.Rows.Count;

Console.WriteLine(String.Format("{0}", totalCreateResultData + " records will be created in CRM"));

Console.WriteLine(String.Format("{0}", CreateResultData.Rows.Count + " records creation in progress...."));

//Creating each record.

foreach (DataRow row in CreateResultData.Rows)

{

try

{

//Map the each field from CRM to Csv file

string scenarioName = row.Field<string>("Scenario Type");

int ScenarioId = int.MinValue;

string SName = string.Empty;

foreach (DataRow trow in ScenarioCPHData.Rows)

{

if (trow["SNAME"].ToString() == scenarioName.ToLower())

{

ScenarioId = (int)trow["SID"];

}

Entity entity = new Entity();

entity.LogicalName = "account";

entity.Attributes["name"] = row.Field<string>("Account Name");

entity.Attributes["address1_name"] = row.Field<string>("Address 1: Name");

entity.Attributes["address1_line1"] = row.Field<string>("Address 1: Street 1");

entity.Attributes["address1_line2"] = row.Field<string>("Address 1: Street 2");

entity.Attributes["address1_city"] = row.Field<string>("Address 1: City");

entity.Attributes["address1_stateorprovince"] = row.Field<string>("Address 1: State/Province");

entity.Attributes["address1_country"] = row.Field<string>("Address 1: Country/Region");

entity.Attributes["address1_postalcode"] = row.Field<string>("Address 1: ZIP/Postal Code");

entity.Attributes["telephone1"] = row.Field<string>("Main Phone");

entity.Attributes["test_sbinumber"] = row.Field<string>("SBI");

entity.Attributes["telephone2"] = row.Field<string>("Other Phone");

if (ScenarioId != int.MinValue)

{

entity.Attributes["test_scenariotype"] = new OptionSetValue(ScenarioId);

}

if (row.Field<string>("Case Initiation Date") != null)

{

string caseID = row.Field<string>("Case Initiation Date");

DateTime caseInitiationDate = Convert.ToDateTime(caseID);

entity.Attributes["test_caseinitiationdate"] = caseInitiationDate;

}

string LKCPH = row.Field<string>("has LK CPH");

if (LKCPH == "Yes")

{

entity.Attributes["test_haslkcph"] = true;

}

else

{

entity.Attributes["test_haslkcph"] = false;

}

xrm.Create(entity);

CreateLog(CreateResultData.Rows.IndexOf(row) + 1 + ", was created successfully");

Console.WriteLine(String.Format(CreateResultData.Rows.IndexOf(row) + 1 + ", was created successfully"));

}

catch (Exception ex)

{

Console.WriteLine(String.Format(CreateResultData.Rows.IndexOf(row) + 1 + ",Error: " + ex.Message));

CreateLog(CreateResultData.Rows.IndexOf(row) + 1 + ",Error: " + ex.Message);

}

Console.WriteLine(String.Format("{0}", CreateResultData.Rows.Count + " records were successfully created"));

}

else

{

Console.WriteLine(String.Format("{0}", "No record for creation."));

}

//UPDATING.....

DataRow[] UpdateResult = CPHResultData.Select("Action = 'Update'");

if (UpdateResult.Length >= 1)

{

DataTable UpdateResultData = UpdateResult.Distinct().CopyToDataTable();

int totalUpdateResultData = UpdateResultData.Rows.Count;

Console.WriteLine(String.Format("{0}", UpdateResultData.Rows.Count + " records updating in progress...."));

//Update each record.

foreach (DataRow row in UpdateResultData.Rows)

{

try

{

string scenarioName = row.Field<string>("Scenario Type");

int ScenarioId = int.MinValue;

string SName = string.Empty;

foreach (DataRow trow in ScenarioCPHData.Rows)

{

if (trow["SNAME"].ToString() == scenarioName.ToLower())

{

ScenarioId = (int)trow["SID"];

}

Entity entity = new Entity();

entity.Id = new Guid(row.Field<string>("ID"));