Part 3 - Creating a Custom Data Import with Microsoft CRM SDK

Part 3 - Finishing the Account Import/Update

In part 2 of this series, we set up the shell that will connect to the database and retrieve records from your staging table.  Now we want to loop through those records and add them to the database, if they don't already exist.  I will also show how to update a CRM record if the program finds a match in the system.

Query CRM Records for a Match

Go to your code, inside the "while" block and after the assignment of the variable "sAcct".  We are going to query the CRM service context to see if our account already exists in an active state in CRM, and then do a count of the results.  We are using LINQ (Language Integrated Query) for this purpose.  The next line of code will instantiate an integer variable and assign it the value of the total number of records returned by the query.  Figure 15 shows an example of the code.

Figure 16

Add Record to CRM

Next, we will check the variable "i" and if it is equal to 0, we will add the account.  Remember we created the "act" variable to hold the  CRM Account entity.  You can use IntelliSense to see that the variable holds all the attributes of the Account from your CRM system.  Assign the "act" variable to a new instance of the Account, and then assign the attributes the values from your SQL record. 

Figure 17

Notice that when we assign the Owner ID, it's not as simple as the other attributes.  This is because the Owner ID is actually a relationship between the Account and the SystemUser.  First, you have to create an EntityReference.  Then assign it an ID and tell it what the logical name (from CRM) should be.  You can manually type it in, however best practices are to use the EntityLogicalName attribute from the Entity itself.  Finally, assign this EntityReference to the OwnerID attribute on the Account.

The last line of code above calls the Create method on the CRM service, and adds the new Account to CRM.

There are a couple of more things we need to do before we can test the importer.  Outside of the "if" statement code block, assign the variable sLastAcctId the value of sAcct, and increment the value of iCount by 1.  This will help us keep track in the event something breaks.  

Figure 18

Lastly, go back to your Main function and add code to assign the variable fConn the value of your connection string from the config file.  Call the AccountImport function you just wrote, and add a few lines to show the count of records entered.  Your Main function should look something like figure 19.

Figure 19

You are now ready to test your program.  Be sure to add a few records to your tmpAccountImport table first.  How did it go?  Did your program run the first time?  If not, were you able to find your errors?  I would love to hear your questions and issues, if you're having any.  If your code ran without error, your command window should look something like this:

Figure 20

Updating Records in CRM

This is great, you might say, but what if the records already exist in CRM and you just need to make changes to them?  Even better, you have both types of records in your staging table and want the program to decide whether to do an import or an update.  This is very simple to do, and is only slightly different from adding a record.  Here's how.

Navigate to the part of your code in the AccountImport function, just after the "if" statement when you are checking if i == 0.  Add and else if statement and check if i equals 1.  If it does, there is only one match and you can update this record.  First, you need to retrieve the specifics of this account from CRM.  You are going to query the service context to pull the Guid of the record with your AcctID, then retrieve the entire Account from the CRM system.  

After that, assigning the values for the attributes is the same as when you are adding a record, with the exception of assigning the record to a new Owner.  Assign the other attributes first, then execute the Account update against the server.  Now you can initiate an AssignRequest variable, set the Assignee to the System User and the Target to the EntityLogicalName of the Account entity.  Lastly, execute the AssignRequest against the CRM service.  Figure 21 demonstrates this code.

Figure 21

Running your program again should update your existing records instead of adding them.  If you are a stickler for reusable code (and you should be!), you will want to take the attribute assignments out of the "if" statements and put them in a separate procedure, that is called from both the add and update parts of the "if" statement.  

We have one more scenario for discussion, and that is what happens when your program finds more than one match.  If you have decided that the AccountNumber is your unique identifier of your source system, then if there are more than one of these in your CRM system they are duplicates.  I'll cover these in Part 4 of this series.

Until then, happy coding!

Related Links
Part 1 - Getting Started 
Part 2 - Beginning the Import Code
Part 4 - Reconciling More than One Match - Merging Accounts