Import Customers From Microsoft Dynamics CRM Online

Tuesday, October 30, 2012 by Karin Huber

In the following sample we want to show how to import customers from Microsoft Dynamics CRM Online into time cockpit using .NET (C#).

Access Time Cockpit in .NET

When you create a new Visual Studio Project you have to take the following steps to access time cockpit:

  • Add references to time cockpit
  • Add web service endpoint to app.config
  • Create DataContext

To skip these steps you can download our Visual Studio 2012 sample project (console application), that already contains these three steps. We use time cockpit version 1.9.1824.28 for the sample project. These assemblies work for all versions starting with 1.8 and 1.9. If you use an older or newer version of time cockpit you should replace all assemblies in folder TimeCockpitReferences with the assemblies from your time cockpit installation directory (C:\Program Files\software architects\time cockpit\time cockpit 2010).

Add References

To access time cockpit from .NET you have to add the following references to your project:

  • Antlr3.Runtime.dll
  • Antlr3.StringTemplate.dll
  • IronPython.dll
  • log4net.dll
  • Microsoft.Dynamic.dll
  • Microsoft.Scripting.dll
  • Newtonsoft.Json.Net35.dll
  • System.CoreEx.dll
  • System.Data.SqlServerCe.dll
  • System.Reactive.dll
  • TimeCockpit.Common.dll
  • TimeCockpit.Data.dll
  • TimeCockpit.Data.QueryLanguage.dll
  • TimeCockpit.Data.RoutingService.dll

You can find all assemblies in the time cockpit installation directory (C:\Program Files\software architects\time cockpit\time cockpit 2010).

Add Web Service Endpoint

Time cockpit uses a web service to locate the server data store for your time cockpit account. You have to define the web service endpoint in the config file of your application:

<?xml version="1.0" encoding="utf-8" ?>
            <endpoint name="" address="" binding="customBinding" 
                bindingConfiguration="CustomBinding_IManagementService" contract="WebManagementService.IManagementService" />

                <binding name="CustomBinding_IManagementService">
                    <security defaultAlgorithmSuite="Default" authenticationMode="UserNameOverTransport" requireDerivedKeys="true"
                        securityHeaderLayout="Strict" includeTimestamp="true" keyEntropyMode="CombinedEntropy"
                        <localClientSettings cacheCookies="true" detectReplays="false" replayCacheSize="900000" maxClockSkew="23:00:00"
                            maxCookieCachingTime="Infinite" replayWindow="00:05:00" sessionKeyRenewalInterval="10:00:00"
                            sessionKeyRolloverInterval="00:05:00" reconnectTransportOnFailure="true" timestampValidityDuration="00:05:00"
                        <localServiceSettings detectReplays="false" issuedCookieLifetime="10:00:00" maxStatefulNegotiations="128"
                            replayCacheSize="900000" maxClockSkew="23:00:00" negotiationTimeout="01:01:00" replayWindow="00:05:00"
                            inactivityTimeout="00:02:00" sessionKeyRenewalInterval="15:00:00" sessionKeyRolloverInterval="00:05:00"
                            reconnectTransportOnFailure="true" maxPendingSessions="128" maxCachedCookies="1000" timestampValidityDuration="00:05:00"/>
                    <textMessageEncoding maxReadPoolSize="64" maxWritePoolSize="16" messageVersion="Soap11" writeEncoding="utf-8">
                        <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384" maxBytesPerRead="4096" maxNameTableCharCount="16384"/>
                    <httpsTransport manualAddressing="false" maxBufferPoolSize="524288" maxReceivedMessageSize="65536" allowCookies="false"
                        authenticationScheme="Anonymous" bypassProxyOnLocal="false" decompressionEnabled="true" hostNameComparisonMode="StrongWildcard"
                        keepAliveEnabled="true" maxBufferSize="65536" proxyAuthenticationScheme="Anonymous" realm="" transferMode="Buffered"
                        unsafeConnectionNtlmAuthentication="false" useDefaultWebProxy="false" requireClientCertificate="false" />
                <remove name="WorkflowControlHttpsBinding"/>
                <binding name="WorkflowControlHttpsBinding" transactionFlow="true">
                    <security mode="Transport"/>
                <remove name="WorkflowControlHttpBinding"/>
                <binding name="WorkflowControlHttpBinding" transactionFlow="true"/>

Create DataContext

Now you can connect to time cockpit by creating a DataContext. Use the same username and password you used to configure time cockpit after starting it the first time.

using (var dataContext = DataContext.Create("username", "password"))

Please note, that your are connected to the server data store of time cockpit. We recommend to import data on the server, because it is easier for time cockpit to download many changes to the clients than to upload many changes to the server. When reading or writing data in the server data store, you will not see changes in your local time cockpit immediately. You can wait until the data is synced automatically (by default every 15 minutes), trigger sync manually, switch to server mode or view the data in Silverlight.

Download sample to access time cockpit (Console Application)

Connect to Microsoft Dynamics CRM

If you want to access Microsoft Dynamics CRM programmatically, you have the choice among different kind of programming models for Microsoft Dynamics CRM. We decided to use late-binding for our sample.

Download Microsoft Dynamics CRM SDK

The first step to get access to Microsoft Dynamics CRM is to download the SDK. It contains the assemblies you need to connect, documentation, several tools and samples.

Register Your Device

To connect to CRM you need a CRM connection string similar to Microsoft SQL Server. You can add the connection string to the connectionStrings section in app.config.

  <add name="Xrm" connectionString="Authentication Type=Passport;
    User ID=your-windowslive-id; Password=your-wlid-password;
    Device ID=your-device-id; Device Password=your-device-password"/>

To get the url for your CRM Server login to Microsoft Dynamics CRM and go to the Settings. Select the Customization group and select Customization. On the Customization page go to Developer Resources. There you will find the URL of your server.

If you want to authenticate with your Windows Live Id you have to specify a Device Id and a Device Password. The Microsoft Dynamics CRM SDK contains a tool that helps you to create a registration for your device in the folder tools/deviceregistration. Open the project in Visual Studio and build the project. Then you will find the DeviceRegistration.exe in the folder bin/debug. Use the following command to see if your device is already registered:

deviceregistration /operation:show

If your device is not registered, create a new device registration with the following command:

deviceregistration /operation:register

If you want to use one of the other authentication mechanisms for CRM you can skip this step.

Add Assemblies to Your Project

Take the assemblies microsoft.xrml.client.dll and microsoft.xrm.sdk.dll from the bin folder from the sdk. Then you should be able to connect to the Microsoft Dynamics CRM Server:

using (var crmContext = new CrmOrganizationServiceContext(new CrmConnection("Xrm")))

Copy Customers From Microsoft Dynamics CRM  Into Time Cockpit

To copy customers from CRM into time cockpit we read all customers from CRM and from time cockpit. Then we check for each CRM customer if it is available in timecockpit. If not, we create a new customer, then we update all fields for the customer. To identify CRM customers in time cockpit we added a new property to the Customer entity in time cockpit.

Add CrmUuid Property

CrmUuid Property

Now it is easy to match customers in time cockpit and CRM.

// Connect to time cockpit
Console.WriteLine("Connect to time cockpit ...");
using (var timeCockpitDataContext = DataContext.Create(ConfigurationManager.AppSettings["timecockpitUsername"], ConfigurationManager.AppSettings["timecockpitPassword"]))
	// Connect to crm online
	Console.WriteLine("Connect to crm online ...");
	using (var crmContext = new CrmOrganizationServiceContext(new CrmConnection("Xrm")))
		// Get customers from time cockpit
		Console.WriteLine("Get customers from time cockpit ...");
		var timeCockpitCustomers = timeCockpitDataContext.Select("From C In Customer Where C.CrmUuid <> Null Select C").ToDictionary(c => (Guid)c.GetMember("CrmUuid"));

		// Get countries from time cockpit
		Console.WriteLine("Get countries from time cockpit ...");
		var timeCockpitCountries = timeCockpitDataContext.Select("From C In Country Where C.CountryName <> Null Select C").ToDictionary(c => (string)c.GetMember("CountryName"));

		// Get customers from crm online
		Console.WriteLine("Get customers from crm online ...");
		var query = new QueryExpression();
		query.EntityName = "account";
		query.ColumnSet = new ColumnSet(new string[] { "accountid", "accountnumber", "name", "emailaddress1", "fax", "address1_telephone1", "address1_line1", "address1_city", "address1_postalcode", "address1_country" });
		var crmAccounts = crmContext.RetrieveMultiple(query);

		// Save customers
		Console.WriteLine("Save customers ...");
		var customerEntity = timeCockpitDataContext.Model.Entities["Customer"];

		foreach (var crmCustomer in crmAccounts.Entities)
				// Check if customer exists in time cockpit
				EntityObject timeCockpitCustomer = null;
				if (timeCockpitCustomers.ContainsKey(crmCustomer.Id))
					timeCockpitCustomer = timeCockpitCustomers[crmCustomer.Id];

				if (timeCockpitCustomer == null)
					// Add customer to time cockpit
					Console.WriteLine("\tAdd {0} - {1}", crmCustomer.Id, crmCustomer.Attributes["name"]);
					timeCockpitCustomer = customerEntity.CreateEntityObject<EntityObject>();
					timeCockpitCustomer.SetMember("CrmUuid", crmCustomer.Id);
					// Customer already exists
					Console.WriteLine("\tUpdate {0} - {1}", crmCustomer.Id, crmCustomer.Attributes["name"]);

				// Update properties of customer
				SetMember("name", "Code", crmCustomer, timeCockpitCustomer);
				SetMember("name", "CompanyName", crmCustomer, timeCockpitCustomer);
				SetMember("emailaddress1", "Email", crmCustomer, timeCockpitCustomer);
				SetMember("fax", "Fax", crmCustomer, timeCockpitCustomer);
				SetMember("address1_telephone1", "Phone", crmCustomer, timeCockpitCustomer);
				SetMember("address1_line1", "Street", crmCustomer, timeCockpitCustomer);
				SetMember("address1_city", "Town", crmCustomer, timeCockpitCustomer);
				SetMember("address1_postalcode", "ZipCode", crmCustomer, timeCockpitCustomer);
				SetRelation("address1_country", "Country", crmCustomer, timeCockpitCustomer, timeCockpitCountries);

			catch (Exception ex)
				Console.WriteLine("\tAccount {0} - {1} could not be saved: {2}", crmCustomer.Id, crmCustomer.Attributes["name"], ex.ToString());

The SetMember Method copies the value for a property from CRM into time cockpit.

static void SetMember(string crmName, string timeCockpitName, Entity crmEntity, EntityObject timeCockpitEntity)
	if (!string.IsNullOrEmpty(crmName) && crmEntity.Attributes.Contains(crmName))
		timeCockpitEntity.SetMember(timeCockpitName, crmEntity.Attributes[crmName]);
		timeCockpitEntity.SetMember(timeCockpitName, null);

static void SetRelation(string crmName, string timeCockpitName, Entity crmEntity, EntityObject timeCockpitEntity, Dictionary<string, EntityObject> timeCockpitValues)
	if (!string.IsNullOrEmpty(crmName) && crmEntity.Attributes.Contains(crmName))
		if (timeCockpitValues.ContainsKey((string)crmEntity.Attributes[crmName]))
			var value = timeCockpitValues[(string)crmEntity.Attributes[crmName]];
			timeCockpitEntity.SetMember(timeCockpitName, value);

	timeCockpitEntity.SetMember(timeCockpitName, null);

Download sample to copy customers from CRM into time cockpit (Console Application)

Additional Resources

comments powered by Disqus