Description
In most, Data Migration and Integration Projects, retrieving GUIDs of Dynamics CRM records is always challenging. The fields that are of lookup type need the GUIDs of the records to load. In this tutorial, we’ll learn how to retrieve GUIDs of CRM records and load them in an SQL table, which can further be used for Data Migration and Integration tasks. We will take the logical name of the entity and the logical name (primarily name) of any of its fields as input in our C # console application and dump the GUID of all its records in the SQL table.
Pre-requisites:
- A valid Dynamics-365 CRM Instance
- Microsoft SQL Server Management Studio
- An SQL Server & a DB with valid credentials
- MS Visual Studio (version 2017 or above)
Steps to load Microsoft Dynamics 365 CRM (CE) Records GUIDs to an SQL DB Table:
1. Open MS SQL Server Management Studio and create a table to store the GUIDs. Here we have created a table CE_RecordGuids_Sample” with the columns Entity, Name, Guide, and ReferenceField (lookup).
2. Open Visual Studio and create a new C# ConsoleApp where we will establish a connection with the CE instance first, retrieve the GUIDs after getting input from the user on the Console, establish the connection with SQL Server, and DB and then dump the GUIDs in the table CE_RecordGuids_Sample.
3. Go to View → Server Explorer → Data Connections → Add Connection. Select Microsoft SQL Server and then enter your Server Name. Then choose your SQL DB.
Verify connection
You can verify the connection by clicking the ‘Test Connection’ button.
4. Go under Data Connection in Server Explorer and right-click on your newly added DB connection.
Select Properties and Copy the Connection String. Keep it somewhere handy as we’ll need it in our code.
5. Make sure you add the XRM and SQL DLLs as Assembly References to your project.
6. Write a C# function to establish a connection to your CRM instance. It will take the Soap URL of your Dynamics CRM Company, username, and password as parameters. It will return Boolean values (true/false) based on whether the connection is established or not.
For SOAP Service URL, go to Settings → Customizations → Developer Resources and Copy the URL under Organization Service.
7. Let’s code our Main Function. We’ll have to call the ConnectToCE function that we defined above. We’ll pass our Organization SOAP service URL, username, and password.
8. Once the connection to the CE instance is established, accept the Entity’s logical name and its field’s logical name from the user and retrieve records from CRM.
9. Establish the connection to your SQL DB and insert records to the table CE_RecordGuids_Sample. Before inserting, check if there are already any records for the same entity in the table. If there are, delete them and then insert in the new ones.
So basically, it’s an UPSERT (INSERT + UPDATE) operation.
10. Let’s run our program now. We will input ‘account’ for the entity’s logical name and ‘name’ for the field’s logical name.
Output on console
The Program has executed successfully.
138 Account records are present in our CRM instance.
12. GUIDs of all of them are successfully loaded in our SQL Table.
Following is the complete C# Code. Provide your values for Username, Password, SOAP Organization URL, and SQL DB Connection String.
<![CDATA[
using System;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using System.ServiceModel.Description;
using System.Data.SqlClient;
using Microsoft.Crm.Sdk.Messages;
namespace CE_FetchCEGuids
{
internal class Program
{
private static void Main(string[] args)
{
string username = "<Your User name>"; //To Do – Provide your user name
string password = "<Password>"; //To Do – Provide your Password
string SoapUrl = "<SOAP Organization URL>"; //To Do – SOAP Organization URL
if (ConnectToCE(SoapUrl, username, password) == false)
{
Console.WriteLine("Invalid Credentials! Login Failed.");
}
else
{
Console.WriteLine("Dynamics 365 CE Connection Established!");
Uri organizationUri = new Uri(SoapUrl);
ClientCredentials credentials = new ClientCredentials();
credentials.UserName.UserName = username;
credentials.UserName.Password = password;
credentials.Windows.ClientCredential = new System.Net.NetworkCredential(username, password, string.Empty);
OrganizationServiceProxy service = new OrganizationServiceProxy(organizationUri, null, credentials, credentials);
SqlCommand cmd;
SqlConnection con;
string entity, referenceField;
Console.WriteLine("\nEnter the entity (logical name) you want the Records GUIDs for: ");
entity = Console.ReadLine();
Console.WriteLine("\nEnter the field (logical name) that you want to retrieve: ");
referenceField = Console.ReadLine();
string GuidField = entity + "id";
QueryExpression queryExpression = new QueryExpression(entity);
queryExpression.ColumnSet = new ColumnSet(referenceField, GuidField);
EntityCollection entityCollection = service.RetrieveMultiple(queryExpression);
Console.ForegroundColor = ConsoleColor.Blue;
Console.WriteLine("\nWriting " + entity + " GUIDs to the SQL Table . . .");
con = new SqlConnection("Data Source=<your server>;Initial Catalog=<you database>;Integrated Security=True"); //TO DO: Enter your Connection String
con.Open();
cmd = new SqlCommand("DELETE FROM [<your database>].[dbo].[CE_RecordGuids] WHERE Entity = @Entity", con); // DELETE PRE-EXISTING RECORDS FOR THE ENTITY IN THE SQL TABLE
cmd.Parameters.AddWithValue("@Entity", entity);
cmd.ExecuteNonQuery();
for (int i = 0; i < entityCollection.Entities.Count; i++) { string RecordName; string RecordGuid; RecordName=entityCollection.Entities[i].Attributes[referenceField].ToString(); RecordGuid=entityCollection.Entities[i].Attributes[GuidField].ToString(); // INSERT THE GUIDs IN THE TABLE cmd=new SqlCommand("INSERT INTO [HISOL_DM_STG].[dbo].[CE_RecordGuids_Sample] (Entity, Name, Guid, ReferenceField) VALUES (@Entity, @RecordName, @RecordGuid, @ReferenceField)", con); cmd.Parameters.AddWithValue("@RecordName", RecordName); cmd.Parameters.AddWithValue("@RecordGuid", RecordGuid); cmd.Parameters.AddWithValue("@Entity", entity); cmd.Parameters.AddWithValue("@ReferenceField", referenceField); cmd.ExecuteNonQuery(); } } Console.ForegroundColor=ConsoleColor.Green; Console.WriteLine("Done!"); Console.Read(); } // Function to connect to Microsoft Dynamics 365 CRM Instance public static bool ConnectToCE(string SoapUrl, string username, string password) { Uri organizationUri=new Uri(SoapUrl); ClientCredentials credentials=new ClientCredentials(); credentials.UserName.UserName=username; credentials.UserName.Password=password; credentials.Windows.ClientCredential=new System.Net.NetworkCredential(username, password, string.Empty); OrganizationServiceProxy service=new OrganizationServiceProxy(organizationUri, null, credentials, credentials); string userId=null; try { userId=((WhoAmIResponse)service.Execute(new WhoAmIRequest())).UserId.ToString(); } catch { } if (userId !=null) { return true; } return false; } } } ]]> Conclusion
With the above comprehensive tutorial, we have found a way to store the GUID values of CRM Entity Records in an SQL Database with UPSERT operation. With this major step, we can perform multiple complex Data Migration & Integration tasks by essentially resolving conflicts around lookup fields.
