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, Mar 14 '13 at 18:23
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, Mar 14 '13 at 18:51

Daryl · Answer 1 · 2013-03-14T18:45:22.797

3

If all you're after is the name of the entity that is an attribute on your currently executing plugin, the EntityReference object has a Name property that should contain that name. If it doesn't you you can query CRM with the id and logical name to get any value that you're looking for on the referenced entity.

Edit 1

If you're just moving the data, why even bother setting the referenced name? I'd removed those names from your database table, and just create a view that looks up the corresponding entity's name. It's what CRM is doing. It also makes your other database more normalized. IE. If you update the name of an entity that is referenced by another entity, you will have to search for and update all of those names...

edited Mar 14 '13 at 18:45

answered Mar 14 '13 at 17:57

Daryl

18,592
9
78
145

Thanks for your reply Daryl. I did a quick test to see what is passed to EntityReference object's Name property, it is empty. Even though the field was updated, the InputParameter does not have the Name passed to the plugin. – sb78 Mar 14 '13 at 18:21
localcontext.plugincontext.primaryentityname ? – Mike_Matthews_II Mar 14 '13 at 18:46
Daryl, in response to your Edit 1, We already have cross database queries, this is a project which is an attempt to move those queries to one database, with minimum impact, so the goal is to keep the schema intact. – sb78 Mar 14 '13 at 18:54
@sb78 Wouldn't creating a view that represents the exact schema work? Just have all of your queries be referencing the view that is doing the lookup for the name. – Daryl Mar 14 '13 at 19:08

score 1 · Accepted Answer · answered Mar 14 '13 at 18:14

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

Thanks for your input Stuart. I will look into it, maybe I can switch case on the LogicalName of the entity and specify the field name. The most common of all SystemUser entity has fullname where it gets the value from. And for custom entities, I always specify an appropriate name for the primary key field. The code is fine, I do update the base query with the statement: query = query + — sb78, Mar 14 '13 at 18:49
I ended up writing a function that returns the field name using the LogicalName of the entity and doing a switch case to get the field name. — sb78, Mar 15 '13 at 18:57

score 1 · Answer 3 · answered Mar 14 '13 at 18:51

If you're dead set on putting the related entity name in the primary entity table you can always grab it like this:

var entityEntityRef = (EntityReference)keyValuePair.Value;
var relatedEntity = service.Retrieve(entityRef.LogicalName, entityRef.Id, new ColumnSet(true));

Now relatedEntity as all the attributes available. You'll mostly be looking for the Name field, but some entities are different, like contact which uses the full name field I believe.

score 0 · Answer 4 · answered Mar 14 '13 at 22:34

You can, in fact, register a single plugin for all entities (checking, of course, that the one that's firing the message is in the list of treated ones).

IEnumerable<String> supportees = new String[]{ "account", "contact" };
if(!supportees.Any(element
  => element == targetLogicalName))
    return;

As for the linked entities, you have three choices.

Just skip them. Not full data sync but easies to implement.
Store the guids only. Data sync is instance-wide - limited but moderately easy.
Get all the linked data. Full information but a recursive PIA to develop.

Microsoft Dynamics CRM 2011 sync entities into an outside database table

4 Answers4

Edit 1