Category Archives: Github

Synchronizing recipients with MailChimp lists

MailChimp is an online service for sending professional looking email newsletters. They have a built in template designer but you can simply import your own designs from file or URL. For small organizations like sport clubs, hobby groups and so one they have a free plan with a maximum of 2000 subscribers and 12000 emails per month. More than enough for what I needed.

Manual synchronizing

MailChimp can have multiple lists to split up your contacts (per company or age group or …). Per list you can use segments to divide that one list. The only down side is that you only can add an email address once. This will give problems if you want multiple members of one family into one list under the same email address (parents email address for example).

People can be added through a registration form that you can alter to follow your website design and MailChimp offers signup by embedded code or by links or by a QR code, … When you’re already working with a contact database you can import these contacts in your list. MailChimp is very strict about importing people in their lists. You can’t use 3rd party lists, no lists you scraped from websites, … Users should be able to opt-in for the newsletter service and should always have a way to unsubscribe. If you don’t follow their guidelines your account will be blocked very quickly. Not without reason of course, they want to avoid that spammers misuse their service.

Importing

MailChimp offers imports from a range of services like Salesforce, Google Contacts, Google docs, Eventbrite, … Next to these services you can upload from file of copy paste from an excel file. And the latter was my manual synchronization every time before I would send out a new newsletter. I’ve created a stored procedure in my SQL database that would return all my contacts with their email addresses, first and last name. After exporting to excel, I could manually copy the 3 columns to the MailChimp web interface. If you’re only sending a newsletter every 3 months, this is doable. But we’re now speeding up on our newsletters and sending at least one per month and during the holidays multiple newsletters per month.

Time to look into the MailChimp API. I didn’t wanted to write a wrapper from scratch and looked into the list of existing wrappers. The first I found was the PerceptiveMCAPI created by Perceptive Logic. I haven’t looked into the other wrappers. I’m not saying the PerceptiveMCAPI is the best, the following is just an explanation how to incorporate the PerceptiveMCAPI into a .NET application.

PerceptiveMCAPI

You can download the compiled code from the codeplex page. Don’t expect a lot of documentation, I could hardly find any blogs or other information how to use their wrapper. I ended up downloading their source code to find all options available. The examples below just alters subscriptions to a list. The rest of the wrapper is similarly build so you can implement the other actions following the examples below.

Add the compiled source code to a folder in your application and add a reference in your project to the DLL. You don’t have to reference the CookComputing.XmlRpcV2.dll that is in the same download.

Updating the Web.config

Before we can use the wrapper we have to add a reference in the config file (web.config for web application, app.config for desktop applications). First of all, add a ‘<section>’ under the ‘<configSections>’ tag. If you don’t have a ‘<configSections>’ tag already add it directly under the ‘configuration’ opening tag. Don’t forget to add a closing ‘</configSections>’ tag.

&lt;/pre&gt;
&lt;section&gt;&lt;/section&gt;
&lt;pre&gt;

After we added the ‘<section>’ tag we can add the ‘<MailChimpAPIconfig>’ tag. There we can enter default values like the API key from MailChimp, the datacenter we want to use and other settings. See chapter MailChimp API key for more information how to get your API key.



Retrieving all our lists from MailChimp

Now we added our default values in the config file we can start to make our first call to the MailChimp API. In this first call we’ll retrieve all the lists we have created in MailChimp. We will need the list ID to add or remove subscribers from that list. For demo purposes I will add the necessary code in the home controller class. In real world application this would be ideally be placed in a separate manager class.

First add the using statement on top of our controller class.

using System.Web.Mvc;
using PerceptiveMCAPI;

The same workflow is used for every API call:

  • Create the correct main object from the Methods namespace
  • Create the correct input type object from the Types namespace
  • Call Execute on the main object
  • Catch the result in the correct output type object from the Types namespace

We need to access the MailChimp lists so we create a ‘PerceptiveMCAPI.Methods.lists’ object as our main object. The corresponding input type object will then be the ‘PerceptiveMCAPI.Types.listsInput’ class.

using System.Web.Mvc;
using PerceptiveMCAPI;

namespace MailChimpAPIDemo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = &quot;Modify this template to jump-start your ASP.NET MVC application.&quot;;

            var lists = new PerceptiveMCAPI.Methods.lists();
            var input = new PerceptiveMCAPI.Types.listsInput();

            return View();
        }
    }
}

To retrieve all the lists we don’t need extra input parameters so we can give the input object as it is to the Execute call. The return type do we catch in a ‘PerceptiveMCAPI.Types.listsOutput’ class.

using System.Web.Mvc;
using PerceptiveMCAPI;

namespace MailChimpAPIDemo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = &quot;Modify this template to jump-start your ASP.NET MVC application.&quot;;

            var lists = new PerceptiveMCAPI.Methods.lists();
            var input = new PerceptiveMCAPI.Types.listsInput();

            PerceptiveMCAPI.Types.listsOutput output = lists.Execute(input);

            return View();
        }
    }
}

After a check if the result is not null we can loop over the result List parameter in the output type. I’ve added a custom MCList class to hold all the results that we can send to the view. There are more parameters in the result object that you can use like statistics etc. These parameters are not documented but generally you can look at the MailChimp API documentation and figure out what is available.

using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using PerceptiveMCAPI;

namespace MailChimpAPIDemo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = &quot;Modify this template to jump-start your ASP.NET MVC application.&quot;;

            var returnValue = new List();

            var lists = new PerceptiveMCAPI.Methods.lists();
            var input = new PerceptiveMCAPI.Types.listsInput();

            PerceptiveMCAPI.Types.listsOutput output = lists.Execute(input);
            if (output != null &amp;&amp; output.result.Any())
            {
                foreach (var listsResult in output.result)
                {
                    returnValue.Add(new McList()
                        {
                            ListId = listsResult.id,
                            ListName = listsResult.name,
                            MemberCount = listsResult.member_count,
                            UnsubscribeCount = listsResult.unsubscribe_count
                        });
                }
            }
            return View(returnValue);
        }
    }

    public class McList
    {
        public string ListId { get; set; }
        public string ListName { get; set; }
        public int MemberCount { get; set; }
        public int UnsubscribeCount { get; set; }
    }
}

When we start up the test application will get the correct results. We see the one list we declared in MailChimp and of course no subscribers yet. (I’ve altered the default view, you can find this changes in the source code)

MailChimp Web interface vs our own web interface


Adding subscribers to our list

Now we can fetch all our lists (just one in this demo) we can start adding subscribers to our list. This will be explained in the following blog post.

MailChimp API key

The API key you can find in the MailChimp web interface under Account – API keys & Authorized Apps.

Click the ‘Add A Key’ button to add a new API key

Source code

You can find all demo’s from this blog post on Github. You’re free to fork or download.

Extending NetBash

NetBash is a small utility u can use in MVC projects created by Luke Lowrey. It’s an alternative for endless administrative pages for every function you need. It’s nested in your view and styled as a real bash command window.


In one of my side projects I created my own commands to send text messages to cell phones, to activate a mass mailing and so on. As I manage the application I get regularly questions for custom database queries where no administration screen is created for. Luke Lowrey already provided the NetBash.Database Nuget package to query the database. After installing the package I wasn’t able to get the database queries to work.

Open source

Luke Lowrey was so nice to create a GitHub repository with the source code for NetBash and the NetBash.Database plugin. As a new Github user I forked the NetBash.Database repository and cloned the code locally. I added the projects to my projects solution and removed the existing references to the Nuget packages. After adding new references to the added projects I was ready to start debugging.

First thing I noticed was the option to add the connectionstring name to the NetBash sql command but no way to fetch all connectionstrings names from the config file.As the project has multiple connectionstrings for different database it would be a help to have a way to visualize them before starting to query. First job, add an option to show the connectionstring names.

New NetBash option

Luke used NDesk to define the options that can be used in the Database plugin. Adding an option is quite simple. In the SqlCommand class we alter the Process method.

var p = new OptionSet() {
{ "e|execute", "Executes an sql query",
	v => _command = Command.Execute },
//{ "i|info", "Shows database information",
//    v => _command = Command.Info },

{ "t|tables", "Lists tables and space used optional filter on provided table name",
	v => _command = Command.Tables },

{ "s|schema", "Display table schema for the provided table name",
	v => _command = Command.Schema },

{ "clear", "Removes all rows from database",
	v => _command = Command.Clear },

{ "cn|connectionstring","Returns the connectionstring name that will be used",
	v=>_command=Command.ConnectionStringName},

{ "lcn|listcn","Shows all connectionstring names found",
	v=>_command = Command.ListConnectionStrings},

{ "c=|conn=", "Name of connection string to use (defaults to first found)",
  v => _connectionName = v },

{ "h|help", "show this list of options",
	v => _command = Command.Help }
};

Here we added the ‘lcn’ command in the OptionSet (line 19). Before we can set the _command parameter we have to add our command to the Command enum.

private enum Command
{
	Execute,
	Info,
	Tables,
	Schema,
	Clear,
	Help,
	ConnectionStringName,
	ListConnectionStrings
}

All we need now is a private method where we loop over all the connectionstrings and add them to a StringBuilder (see getConnectionStringNames()).

private string getConnectionStringNames()
{
	var sb = new StringBuilder();
	for (int i = 0; i < ConfigurationManager.ConnectionStrings.Count; i++)
	{
		sb.AppendLine(ConfigurationManager.ConnectionStrings[i].Name);
	}
	return sb.ToString();
}

We then return the result to the Process method (line 21,22).

switch (_command)
{
case Command.Execute:
return execute(query);

case Command.Info:
return executeEmbedded("DbInfo.sql");

case Command.Tables:
return getTables(query);

case Command.Schema:
return showSchema(query);

case Command.Clear:
return clearRecords();

case Command.ConnectionStringName:
return getConnectionStringName();

case Command.ListConnectionStrings:
return getConnectionStringNames();

case Command.Help:
default:
return showHelp(p);
}

Now we can use the “SQL -lcn” command in the NetBash interface to list us all the connectionstrings that are in the config file.

Entity Framework formatted connectionstring support

Now we can show all connectionstring names I encountered the next problem. To query the database Luke makes use of a SqlCommand. This command needs a SqlConnection object that has the correct connection string.

In the project we use Entity Framework (EF) that needs his own type of formatting for the connectionstrings. The SqlConnection object can’t work with this types of strings. But the type of connectionstring that we have to use is embedded in the EF string. All we need to do is check if the connectionstring is the EF type and then parse it. In the System.Data.EntityClient namespace Microsoft added a EntityConnectionStringBuilder that we can use to parse the string.

private string CheckForEntityFrameworkConnectionString(string connString)
{
	if (connString.Contains("provider connection string="))
	{
		//Parse connectionstring from Entity connectionstring
		var entityBuilder = new EntityConnectionStringBuilder(connString);
		return entityBuilder.ProviderConnectionString;
	}
	return connString;
}

The EntityConnectionStringBuilder has a constructor that takes an EF connectionstring. After that we can access the ProviderConnectionString parameter that will contain the string we need. We add the CheckForEntityFrameworkConnectionString in the getConnectionString method and we’re ready to query our database.

private string getConnectionString()
{
	var connString = ConfigurationManager.ConnectionStrings[_connectionName] ??
					 ConfigurationManager.ConnectionStrings[0];

	string connectionString = CheckForEntityFrameworkConnectionString(connString.ConnectionString);
	return connectionString;
}

Github commit and Pull Request

Now we added the functionality we wanted we can commit it all to our Github repository that is a fork of the original repository. After committing we want to inform Luke that we have some changes that he maybe would like to incorporate in his code. Github has this functionality on board. You can issue a “Pull request”. With this request you can ask the manager of the original repository to include the changes you have made. The manager then has to choose the changes he wants to merge into his code. I’ve added my ‘Pull Request’ and hopefully Luke likes the changes I added and will merge them into his fantastic utility.