SharePoint 2010 Service Application Development 101 – MailChimp Integration Example

In this post, we will leverage the custom service application infrastructure and solution we put together in the last 4 posts to enhance the service application with new features and capabilities. The sample feature we will add is an integration between SharePoint and MailChimp, enabling sites to synchronize a contacts list to a MailChimp list. The focus in this series has been to show how the SharePoint Service Application Framework (SAF) can be a very useful framework for adding capabilities to a SharePoint farm, in a centralized, reliable, and scalable way. Because SAF remains an integral part of SharePoint 2013, investments in SAF development remain viable for future versions of SharePoint.

This is part 5, the final post in a short series on developing service applications in SharePoint 2010:

• Part 1: Getting Started with SAF + Starter Solution (with Logging / Configuration / Service Locator)
• Part 2: Custom Service Application – Logical Components
• Part 3: Custom Service Application – Base Solution
• Part 4: Custom Service Application – Full Infrastructure (with Admin UI and PowerShell)
• Part 5 (This Post): Custom Service Application – Integrating features and capabilities into your service application (sample MailChimp integration)

Backgrounder and objective

In this post, we will leverage the custom service application infrastructure and solution we put together in the last 4 posts to enhance the service application with new features and capabilities. The focus in this series has been to show how the SharePoint Service Application Framework (SAF) can be a very useful framework for adding capabilities to a SharePoint farm, in a centralized, reliable, and scalable way. In addition, because SAF remains an integral part of SharePoint 2013, investments in SAF development remain viable for future versions of SharePoint; which is one of the reasons why I felt this series might be of interest.

As I was thinking about what I could do to showcase integrating new features and capabilities into a custom service application infrastructure, I felt that (going back to the 1st post in this series) showcasing an integration between an existing 3rd party system and SharePoint would be a good route to take. I landed on MailChimp, an email newsletter and publishing platform, while doing a google search last Thursday and started playing around with it a little. The next day, a client of mind asked me if I knew a good way to populate MailChimp from SharePoint, and this coincidence led me to selecting MailChimp for this final post, which I hope will tie up lose ends on the series.

I set about to define the following goals for this integration, the purpose of which is to leverage the essential components of the custom service application infrastructure we’ve built up to this point:

• Add custom database components to showcase enhancing a custom service application database
• Leverage SharePoint Service Jobs to showcase how a custom service application can make use of the built-in SharePoint Timer Job capabilities
• Enhance logging for new components being added to the custom service application
• Enhance the custom service application security capabilities to wrap custom application / product integration security scenarios
• Build back-end business logic, fronted by a WCF service layer
• Build client UIs and functionality that can leverage these WCF services (throw in a little AJAX)
• Build a management UI to manage the integration within SharePoint

I think this is pretty comprehensive, and can be adapted to lots of different integration scenarios. You tell me if this integration succeeds at this or not, you be the judge. I had a lot of fun throwing it together these past couple evenings.

MailChimp integration overview

Here are the use-cases for this integration that meet the above stated objective.

• Assume company (in our case our company is called “MyCorp”) is already using MailChimp as their campaign management and newsletter publishing platform, and is also using SharePoint heavily as an intranet and partner platform
• Synchronize lists from MailChimp to SharePoint and enable designated MailChimp administrators with the right to manage which MailChimp newsletters can be leveraged by SharePoint communities and subscribed to
• Allow SharePoint communities to setup contact lists, and publish these contact lists to one or more newsletters, such that users in these contact lists are opted in as newsletter subscribers
• Synchronize specified SharePoint contact lists with MailChimp newsletter subscriber lists

Here’s how I went about it, with screenshots and code snippets.

1. Adding schema components to the service application database

I decided to create a custom schema to house which MailChimp lists are enabled for community subscription, and also to track which contact lists are being published, and which newsletters they are being published to.

In order to add schema components to our existing database, all that we have to do in the infrastructure we built in the preceding posts is add SQL files to the SQL\MyCorp directory for them to be automatically processed during a service application update in the UI.

The file creates the tables with standard SQL syntax:

CREATE TABLE [MailChimpConfig](
  [ConfigKey] [nvarchar](50) NOT NULL,
  [ConfigValue] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_MailChimpConfig] PRIMARY KEY CLUSTERED
(
  [ConfigKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [MailChimpNewsletters](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](256) NOT NULL,
  [MailChimpListId] [nvarchar](256) NOT NULL,
  [EnableSubscriptions] [bit] NOT NULL,
 CONSTRAINT [PK_MailChimpNewsletters] PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_MailChimpNewsletters] UNIQUE NONCLUSTERED
(
  [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [MailChimpNewsletters] ADD  CONSTRAINT [DF_MailChimpNewsletters_EnableSubscriptions]  DEFAULT ((0)) FOR [EnableSubscriptions]
GO

CREATE TABLE [MailChimpSubscriptions](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [SPSiteId] [uniqueidentifier] NOT NULL,
  [SPWebId] [uniqueidentifier] NOT NULL,
  [SPListId] [uniqueidentifier] NOT NULL,
  [SPListUrl] [nvarchar](256) NOT NULL,
  [MailChimpNewsletterId] [int] NOT NULL,
  [LastSync] [datetime] NOT NULL,
  [LastSyncUserCount] [int] NOT NULL,
 CONSTRAINT [PK_MailChimpSubscriptions] PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_MailChimpSubscriptions] UNIQUE NONCLUSTERED
(
  [SPListId] ASC,
  [MailChimpNewsletterId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [MailChimpSubscriptions]  WITH CHECK ADD  CONSTRAINT [FK_MailChimpSubscriptions_MailChimpNewsletters] FOREIGN KEY([MailChimpNewsletterId])
REFERENCES [MailChimpNewsletters] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [MailChimpSubscriptions] CHECK CONSTRAINT [FK_MailChimpSubscriptions_MailChimpNewsletters]
GO

2. Added custom diagnostic logging category in the service application database for easy debugging

I added a custom diagnostic category called “MailChimp” to our “MyCorp” logging area, and I’ll use this everywhere I do logging. Logging to this category is very simple as you can see in the next snippet:

public void DisableNewsletterSubscriptions(int[] newsletterIds)
{
    try
    {
        using (var sqlConnection = new SqlConnection(base.DatabaseConnectionString))
        {
            sqlConnection.Open();
            sqlConnection.Execute(SqlUpdateNewslettersStatement, new { EnableSubscriptions = false, ID = newsletterIds });
        }
    }
    catch (Exception ex)
    {
        Log.Error(LogCategory.MailChimp,
                  "DisableNewsletterSubscriptions Exception: {0} by {1}".FormatWith(ex.Message, base.UserLogin));
        throw new MCServiceException(ex.Message, ex);
    }
}

Then, using ULS Viewer, you can easily filter as seen below:

2. Added some security rights specific to this MailChimp integration within our service application

Custom Central Admin Rights	Custom Application Rights

Note, if a user is given the “Manage MailChimp” central admin right, that user automatically gets the “Manage Subscriptions” right. We do this by leveraging the override capabilities already built in the previous posts. Farm administrators also automatically get all rights in our service application.

We do this override using the following code:

// Specify if there are situations where central admin application rights can override application rights (user service application uses this method)
internal static MCServiceApplicationCentralAdminRights GetAdminOverrideRights(
    MCServiceApplicationRights applicationRights)
{
    var none = MCServiceApplicationCentralAdminRights.None;
    if ((applicationRights & (MCServiceApplicationRights.None | MCServiceApplicationRights.UseUtilities)) ==
        (MCServiceApplicationRights.None | MCServiceApplicationRights.UseUtilities))
    {
        // Users that can Manage Utilities can always Use Utilities
        none |= MCServiceApplicationCentralAdminRights.None |
                MCServiceApplicationCentralAdminRights.ManageUtilities;
    }
    if ((applicationRights & (MCServiceApplicationRights.None | MCServiceApplicationRights.ManageMailChimpSubscriptions)) ==
        (MCServiceApplicationRights.None | MCServiceApplicationRights.ManageMailChimpSubscriptions))
    {
        // Users that can Manage the MailChimp Account can always Manage Any Subscription
        none |= MCServiceApplicationCentralAdminRights.None |
                MCServiceApplicationCentralAdminRights.ManageMailChimpAccount;
    }
    return none;
}

3. Created a custom WCF service layer, with a custom “mailchimp.svc” endpoint

namespace MyCorp.SP.ServiceApplication.ServiceContracts
{
    [ServiceContract(Namespace = Constants.ServiceApplicationNamespace),
     SharePointPermission(SecurityAction.InheritanceDemand, ObjectModel = true),
     SharePointPermission(SecurityAction.LinkDemand, ObjectModel = true)]
    public interface IMailChimpService
    {
        [FaultContract(typeof (MCServiceFault)), OperationContract]
        NewsletterDto[] GetAvailableNewsletters();

        [FaultContract(typeof(MCServiceFault)), OperationContract]
        NewsletterDto[] GetSubscriptions(Guid spListId);

        [FaultContract(typeof(MCServiceFault)), OperationContract]
        void AddSubscriptions(Guid spSiteId, Guid spWebId, Guid spListId, string spListUrl, int[] newsletterIds);

        [FaultContract(typeof(MCServiceFault)), OperationContract]
        void RemoveSubscriptions(Guid spListId, int[] newsletterIds);

        [FaultContract(typeof(MCServiceFault)), OperationContract]
        bool HasAccountManagementRights();

        [FaultContract(typeof(MCServiceFault)), OperationContract]
        void SaveAccountInfo(string apiKey, string apiEndpoint);

        [FaultContract(typeof (MCServiceFault)), OperationContract]
        MailChimpConfigDto GetAccountInfo();

        [FaultContract(typeof(MCServiceFault)), OperationContract]
        void SyncNewsletterLists();

        [FaultContract(typeof(MCServiceFault)), OperationContract]
        void EnableNewsletterSubscriptions(int[] newsletterIds);

        [FaultContract(typeof(MCServiceFault)), OperationContract]
        void DisableNewsletterSubscriptions(int[] newsletterIds);

        [FaultContract(typeof(MCServiceFault)), OperationContract]
        MailChimpNewsletterDto[] GetNewsletterLists();
    }
}

The following is a sample for one of the methods. As you can see the implementation of the service talks directly to our custom schema we created earlier and also requires the proper security rights be met.

public void RemoveSubscriptions(Guid spListId, int[] newsletterIds)
{
    // only mailchimp subscription managers can add/remove subscriptions to a list
    base.DemandAccess(MCServiceApplicationRights.ManageMailChimpSubscriptions);

    try
    {
        using (var sqlConnection = new SqlConnection(base.DatabaseConnectionString))
        {
            sqlConnection.Open();
            using (var sqlTransaction = sqlConnection.BeginTransaction())
            {
                try
                {
                    foreach (var newsletterId in newsletterIds)
                    {
                        sqlConnection.Execute(SqlDeleteSubscriptionStatement,
                                              new {SPListId = spListId, NewsletterId = newsletterId},
                                              sqlTransaction);
                    }
                    sqlTransaction.Commit();
                }
                catch (Exception)
                {
                    sqlTransaction.Rollback();
                    throw;
                }
            }
        }
    }
    catch (Exception ex)
    {
        Log.Error(LogCategory.MailChimp,
                  "RemoveSubscription Exception: {0} by {1}".FormatWith(ex.Message, base.UserLogin));
        throw new MCServiceException(ex.Message, ex);
    }
}

The conversation between SharePoint and MailChimp is done using RESTful calls with JSON. I use the ServiceStack.Text library in this example to do the client work. I actually added a couple methods to the open source library and compiled it usint .NET 3.5. The compiled DLL is included as a download at the end of this post.

Here’s the method that synchronizes the MailChimp lists over to SharePoint:

public void SyncNewsletterLists()
{
    var accountInfo = GetAccountInfo();
    var endpointFormat = string.Format("{0}/?{{0}}", accountInfo.ApiEndpoint.NullSafe().TrimEnd('/'));

    var url = endpointFormat.FormatWith(
        string.Concat(
            ApiQsApiKeyFormat.FormatWith(accountInfo.ApiKey.NullSafe()), "&",
            ApiQsMethodFormat.FormatWith("lists"), "&",
            ApiQsOutputFormat.FormatWith("json"), "&",
            "start={0}".FormatWith(0), "&",
            "limit={0}".FormatWith(100) /* the max */
            ));

    var json = GetJsonFromUrl(url);
    var jsonObject = JsonObject.Parse(json);
    var total = jsonObject.JsonTo("total");
    var lists = jsonObject.ArrayObjects("data");
    var mailChimpLists = lists.Select(list => JsonSerializer.DeserializeFromString(list.SerializeToString())).ToList();

    try
    {
        using (var sqlConnection = new SqlConnection(base.DatabaseConnectionString))
        {
            sqlConnection.Open();
            using(var sqlTransaction = sqlConnection.BeginTransaction())
            {
                try
                {
                    // Delete lists that no longer exist in MailChimp (cascade delete in database will remove subscriptions)
                    sqlConnection.Execute(SqlDeleteNewslettersStatement,
                                          new {MailChimpListId = mailChimpLists.Select(l => l.Id).ToArray()},
                                          sqlTransaction);

                    // Insert lists in database if missing
                    foreach (var list in mailChimpLists)
                    {
                        var count =
                            sqlConnection.Query(SqlCountNewslettersStatement,
                                                     new {MailChimpListId = list.Id},
                                                     sqlTransaction).Single();
                        if (count == 0)
                        {
                            sqlConnection.Execute(SqlInsertNewslettersStatement,
                                                  new
                                                      {
                                                          Name = list.Name,
                                                          MailChimpListId = list.Id,
                                                          EnableSubscriptions = false
                                                      }, sqlTransaction);
                        }
                    }
                    sqlTransaction.Commit();
                }
                catch (Exception)
                {
                    sqlTransaction.Rollback();
                    throw;
                }
            }
        }
    }
    catch (Exception ex)
    {
        Log.Error(LogCategory.MailChimp,
                  "SyncNewsletterLists Exception: {0} by {1}".FormatWith(ex.Message, base.UserLogin));
        throw new MCServiceException(ex.Message, ex);
    }
}

4. Built a management UI to manage the Integration

Added a new MailChimp section in our custom service application management page, as well as a Timer Jobs section which links directly to a filtered view of all the jobs that are part of the service application.

When clicking on the “Account Information” link, this gives the MailChimp administrator the ability to enter the API key and endpoint information needed to do the communication back and forth between MailChimp and SharePoint. This data is stored in the database.

When clicking on the Manage Subscriptions page, I built a custom layout page to manage the MailChimp list subscriptions.

When you click on the “Sync Lists” button, the MailChimp lists are synchronized over to our database, and the page is populated:

Enabling and disabling the lists here allows administrators to control which lists Site administrators will be able to publish their contacts lists to:

 

5. Contacts List integration

Now that the admin UI is done. I built a front-end UI that Site administrators can use to easily publish their Contacts list to MailChimp with. As a Web feature, I added a MailChimp Tab to the Contacts Lists:

When the site admin clicks on the Manage Subscriptions button, he/she is presented with the option to pick from the “Enabled” newsletters in Central Admin. This UI is all done in AJAX, no postbacks.

Here’s the javascript code (using jQuery and a custom $.pageMethod extension) that I used that calls page methods in the code behind class when moving items from left to right, and back. Also, shoutout to Justin Mead over at www.meadmiracle.com for his nice dual listbox plugin that I’ve used on many projects.

var addSubscriptions = function (options) {
    var ids = [];
    $.each(options, function () {
        ids.push($(this).val());
    });
    $.log("Adding ids: " + ids);
    $.pageMethod('AddSubscriptions', { listId: $("#spListId").text(), newsletterIds: ids }, true, onPageMethodSuccess, onPageMethodFailure);
};

var removeSubscriptions = function (options) {
    var ids = [];
    $.each(options, function () {
        ids.push($(this).val());
    });
    $.log("Removing ids: " + ids);
    $.pageMethod('RemoveSubscriptions', { listId: $("#spListId").text(), newsletterIds: ids }, true, onPageMethodSuccess, onPageMethodFailure);
};

As Contacts lists are published to MailChimp newsletters, the admin UI reflects the subscriptions:

6. Synchronizing Contact Lists over to MailChimp

The process of synchronizing contacts lists over to MailChimp is done using a Job Definition. Back on the service application management page, click on “Review Job Definitions”.

Now, select the MailChimp Synchronization Job and click on “Run Now”.

The ULS logs will show the progress.

Now when we browse into MailChimp, we see that the lists have been populated with the users from the subscribed Contacts Lists:

Summary

In this post, I attempted to show how once you have a custom service application infrastructure built, you can extend it easily by building add-on capabilities and features that can be managed centrally within your SharePoint farm, taking full advantage of what SAF has to offer. This MailChimp integration showcases the use of a custom service application, SharePoint jobs, a custom service application database, custom security, WCF, AJAX, scalability, and hopefully it paints a realistic picture of what can be achieved.

Thanks for sticking with me through this series.

Feel free to download the code and use it at your own risk , and please let me know what you think in the comments.

[button link=”https://skydrive.live.com/redir?resid=30FD0C7F694C1B3F!293&authkey=!AJ9mKTemY0vGR_4″ color=”primary” target=”_blank” size=”large” title=”Code Download” icon_before=”download”]Code Download[/button]

Cheers!

.net, C#, MailChimp, mycorp, saf, service application, sharepoint, wcf