Integrating Accredo with Dynamics CRM 2011

Roshan Mehta, 22 March 2012

We have had the opportunity to build an integration tool between Accredo and Microsoft Dynamics CRM 2011 to synchronize customer, product, and order information. Data is automatically pumped from a website into Accredo and our integration tool queries the Accredo database every ten minutes to synchronize data with CRM. We are only interested in data that has been created or modified within the last ten minutes so we can be sure we are only synchronizing small chunks of data at a time.

For the purposes of this blog post, we will download a demo version of Accredo Saturn. The demo includes sample files that we can use for our integration.

We can query the Accredo database files via the OLDB/ODBC driver. Note that this driver is read-only so we won’t be able to push data back into Accredo. To get started, we need to ensure we have the correct connection string, for example:

Provider=AccredoOLEDB;Location=E:\AccredoSaturnDemo\;Data Source=MyAccredo;User Id=Admin;Password=Admin

The OleDbConnection .NET class makes the querying process easier. For example, if we want to grab a list of all of the customers in Accredo, we can use the following code snippet:

            using (OleDbConnection conn = new OleDbConnection(connString))
            {
            try
                {
                    conn.Open();
                    OleDbCommand command = conn.CreateCommand();
                    command.CommandText = "SELECT CustomerCode, CustomerName, EmailAddress FROM ARCUST";
                    OleDbDataReader reader = command.ExecuteReader();

                     while (reader.Read())
                    {
                        customers.Add(new Customer
                        {
                            CustomerCode = reader["CustomerCode"].ToString(),
                            CustomerName = reader["CustomerName"].ToString(),
                            PrimaryEmail = reader["EmailAddress"].ToString(),
                        });
                    }
                }
                catch (Exception ex)
                {

                 }
                finally
                {
                    conn.Close();
                }
            }

Now that we have the customer data, we can use methods described in the Dynamics CRM 2011 SDK to insert data into CRM.

To query Accredo Orders we have to deal with multiple tables – INHEAD and INLINE. It is a good idea to query all data from the INLINE table first and then grab data from the INHEAD table. This is because it is possible to encounter an OutOfMemoryException due to the number of records in each table and the joins between them.