JavaScript Required

We're sorry, but we doesn't work properly without JavaScript enabled.

C# - Load Microsoft Dynamics 365 CRM (CE) Records GUIDs to an SQL DB Table

Description

In most of the Data Migration and Integration Projects, retrieving GUIDs of 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 logical name of any of its fields (mainly name) as inputs in our C# Console Application and dump GUIDs of all its records in an SQL table.

Pre-requisites:

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, Guid, ReferenceField (lookup).

2. Open Visual Studio and create a new C# ConsoleApp where we will establish connection with 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.

sql connection
sql table
ms sql server
server exploer

You can verify the connection by clicking the ‘Test Connection’ button.

sql test connection

4. Now within the Server Explorer itself, go under Data Connections 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.

string connection

5. Make sure you add the XRM and SQL DLLs as Assembly References to your project first.

sql-dll

6. Write a C# function to establish a connection to your CRM instance. It will take Soap URL of your Microsoft Dynamics CRM Company, username and password as parameters. It will return Boolean values (true/false) based on whether the connection is established or not.

user connection

For SOAP Service URL, go to Settings → Customizations → Developer Resources and Copy the URL under Organization Service.

diynamic365

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

saop url

8. Once the connection to CE instance is established, accept the Entity’s logical name and its field’s logical name from the user and retrieve records from CRM.

dynamic365 CE

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.

consol

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.

11. Following is the output over the console. The Program has executed successfully.

cmd display

138 Account records are present in our CRM instance.

dynamic365 admin

12. GUIDs of all of them are successfully loaded in our SQL Table.

ce record
ce record found

Following is the complete C# Code. Provide your own values for Username, Password, SOAP Organization URL and SQL DB Connection String.

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.

You may like to read on Dynamics CRM Tips and Tricks

 

Some of our clients

team