Part 3 - Finishing the Account Import/UpdateIn 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 MatchGo 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.
Add Record to CRMNext, 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.
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.
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.
Updating Records in CRMThis 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.
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!
Part 1 - Getting Started
Part 2 - Beginning the Import Code
Part 4 - Reconciling More than One Match - Merging Accounts