Microsoft Dynamics CRM 2011 sync entities into an outside database table

Question

I have a requirement to sync some entities (account, lead, contact etc) to a database table outside of the crm database but on the same server. I am looking for a supported way for doing this. Here's what I have tried, that don't work:

I first created table in the outside database that matches the schema from dbo.account (view). Then I wrote post create, post update, post assign and post delete plugins to create, update or delete the record in the outside table (using ADO.Net). I have written the plugin in the most generic way so that it can be registered for any entity with minimum changes to the plugin (by not hardcoding the field names). Doing it this way, the problem I am running into is with the fields that are foreign key to other tables. Eg. in dbo.account, there are fields like PrimaryContactId and PrimaryContactIdName, PreferredSystemUserId and PreferredSystemUserIdName, ParentAccountId and ParentAccountIdName etc. In the input parameters for the plugin, the xxxxId fields are available when they are updated, but not the 'xxxxIdName' fields. Because of which I am not able to 'sync' the table as is.

Is there a solution to make my plugin solution work? Is there a better supported way for having a sync table? Thanks in advance,

PS: 1. The data sync has to be in real time

PS: 2. Here is my function to get the query that does the update

private static string PrepareUpdateQuery(ITracingService tracingService, IEnumerable<KeyValuePair<string, object>> attributeCollection, string entityName, string entityIdName)
    {

        var query = "Update MainDb.MSCRM." + entityName + " set ";
        foreach (KeyValuePair<string, object> keyValuePair in attributeCollection)
        {
            tracingService.Trace("Key: {0}", keyValuePair.Key);
            if (keyValuePair.Key != entityIdName && keyValuePair.Key != "modifiedonbehalfby")
            {
                query = query + keyValuePair.Key + " = ";
                if (keyValuePair.Value == null)
                    query = query + "null, ";
                else
                {
                    var typeOfValue = keyValuePair.Value.GetType().Name;
                    tracingService.Trace("typeOfValue: {0}", typeOfValue);
                    switch (typeOfValue)
                    {
                        case "EntityReference":
                            query = query + "'" + ((EntityReference)keyValuePair.Value).Id + "', ";
                            break;
                        case "OptionSetValue":
                            query = query + ((OptionSetValue)keyValuePair.Value).Value + ", ";
                            break;
                        case "BooleanManagedProperty":
                            query = query + (((BooleanManagedProperty)keyValuePair.Value).Value ? "1" : "0") + ", ";
                            break;
                        default:
                            query = query + "'" + keyValuePair.Value + "', ";
                            break;
                    }
                }
            }
        }
        return query;
    }

I'm using Entity Framework (EF) to manage my connection to a nonCRM DB and populating the EF Entity in a CRM Plugin and then saving those through EF. That way, I can let object oriented C# handle my data without me having to create an update query dynamically. Also, since I go through EF, I can define any data structure I want on the nonCRM DB and still interact with it in a OO way. — Mike_Matthews_II
Entity Framework is new to me. I will however look into it and see if that works in my case. Thanks for the pointer. — sb78

Stuart Stuart · Accepted Answer · 2013-03-14T18:14:19

the xxxIdName fields are just a helper for the views really, you can easily figure out what they should contain.

For example, say you have an account 'some company' with a primary contact called 'bob bobson'. when processing the account entity the primarycontactId will be a guid and the primarycontactIdName will be 'bob bobson', the accountIdName will be 'some company'.

easiest way to do this in your plugin is to look up the related entity and get the value from there - 90% of the time it's just the name field.

you also need to consider however if you are doing the right thing in using the CRM schema, perhaps it would be better to copy only the fields you need and use your own schema for the sync table.

UPDATE: just saw your code, you are overwritting the value contained in query and not setting it back to the base query, so you will get odd results/errors on the second pass through the foreach

Microsoft Dynamics CRM 2011 sync entities into an outside database table

4 Answers

Edit 1