How to copy newly created CRM contact record to SQL table

0
votes

I have written a plugin that fires off when new record is created (post). This plugin simple runs a SSIS package that moves newly created record details in to SQL table. I'm having to deploy this plugin as none isolation mode.

What I want to know is that is there any better solution to live sync CRM records with SQL table?

I am working on CRM 2011 RU 18 On premise. 

                    string connectionString = "User Id=username; Password=password; Initial Catalog=db; Data Source=myServer;";
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        SqlCommand cmd = new SqlCommand("INSERT INTO testPerson (Firstname, Lastname, Emailaddress, CrmGuid) VALUES (@FN, @LN, @Email, @CrmGuid)");
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = connection;

                        cmd.Parameters.AddWithValue("@FN", "James");
                        cmd.Parameters.AddWithValue("@LN", "Bolton");
                        cmd.Parameters.AddWithValue("@Email", "[email protected]");
                        cmd.Parameters.AddWithValue("@CrmGuid", "C45843ED-45BC-E411-9450-00155D1467C5");

                        connection.Open();
                        cmd.ExecuteNonQuery();
                    }

                    //////// Direct DB Connection ////////////     <--- END 1
                }

                catch (Exception ex)
                {
                    tracingService.Trace("CRM to mWeb Sync Plugin: {0}", ex.ToString());
                    throw;
                }
3
sqlpluginsdynamics-crm-2011sync
Firing up an SSIS package each time a record is created is overkill and will either need to be Async unless the end-user is expected to wait. I've seen SSIS packages take a minute or more to start-up. – Nicknow
@Nicknow it is Async and given it is a single record, it will take less then a minutes. – kmria
Just to clarify, I wasn't saying the insert would take a minute - I was saying that the SISS package startup (which is independent of the work being done) can take a minute or more. This is one of the reasons that having to start an SSIS package for each operation can be a problematic architecture. – Nicknow
@Nicknow I agree, I have given up on the SSIS idea and now trying to follow up your link regarding web service. (something I have never done before so might be worth learning too!) – kmria

3 Answers

0
votes

As a possible alternative I see that plugin can use ADO Connection/Command e.t.c. to push data directly to external DB.

0
votes

If you want to run this in sandbox mode you will not be able to directly target SQL (or run an SSIS package.)

The correct solution would be to target a web service.

Create a standard .NET web service (WebApi, ASP.NET old school, WCF, etc. - pick your poison) to which you can post the fields. That web service should take the fields are write them to SQL. This is basic .NET - you can use a SqlCommand, EntityFramework, etc. Deploy this web service to IIS as you would any web service.

You can get a full tutorial on creating an ASP.NET WebApi Web Service here: http://www.codeproject.com/Articles/659131/Understanding-and-Implementing-ASPNET-WebAPI

Now, your plugin should call the web service you have created using HttpClient. So long as you have a FQDN (i.e., service.mydomain.com and not something such as localhost or 34.22.643.23) you will be able to run your plugin in sandbox mode (i.e., not isolation.)

You can secure your service using any IIS security feature or implement authorization in your WebAPI code.

0
votes

As per @Nicknow I finally learned(enough) and created my first WCF service and deployed it on the IIS. Yes, it was bit of work and learning but it works perfectly. From record creation to plugin completing to new rows created in my backend database, all within 2 minutes.

If anyone else in the same boat as me, I would suggest buying This book as it was massive help to me.

Thank you guys once again for pointing me in the right direction.