27Jun, 2025
Walking Through the Send Batch Upload and Merge Module Code
In my last article I introduced you to the Send Batch Upload and Merge Module, and why you might need it. We looked at the PowerShell script that calls the module, and today I'll walk you through the supporting code to get the work done.
There's a bit of skipping around in this article since there's helpers, a global config item, etc. I'll get to all of it starting with the gateway, then the task, then the rest.
Send API Gateway
The script you saw in the previous article had a few calls to some custom code, which leveraged Sitecore Send's API. I expect using Send will expand beyond this single use case, and likely used by other team members. For that reason, I want all interaction with this API to be central and configurable from one place, so I made a gateway for this.
Let's start with the constructor and some properties. Here, you can see the config item is loaded based on a Guid stored in constants. It also gets the Master or Web database depending on where this is installed by using a Role Definition helper. For this module it will only be used in Master, but like I said we will be using this for more things in the future.
public class SendGateway : IDisposable
{
private readonly HttpClient _httpClient;
internal Item GlobalConfigItem { get; set; }
public SendGateway()
{
_httpClient = new HttpClient();
var configItemID = Items.IntegrationSettings.ID;
var databaseName = Helpers.RoleDefinition.MasterOrWebDatabase();
if (string.IsNullOrWhiteSpace(databaseName))
{
Log.Error("Database name is not set based on role. Cannot initialize SendGateway.", this);
throw new InvalidOperationException("Database name is not set based on role. Cannot initialize SendGateway.");
}
var contextDb = Factory.GetDatabase(databaseName);
GlobalConfigItem = contextDb.Items.GetItem(configItemID);
if (GlobalConfigItem == null)
{
Log.Error($"Configuration item with ID {configItemID} not found.", this);
throw new InvalidOperationException($"Configuration item with ID {configItemID} not found.");
}
}
Before I forget, the gateway finishes by disposing:
public void Dispose()
{
Log.Debug("Disposing resources.", this);
_httpClient?.Dispose();
}
Getting All Active Mailing Lists
The following method returns an instance of the AllMailingLists object, which you can see in the Models folder of the solution. This was used in the script to provide a select list to the User, so they can choose where they want to upload.
public async Task<AllMailingLists> GetAllActiveMailingListsAsync()
{
var endpointFormatter = new GatewayUtility.EndpointFormatter(_httpClient, GlobalConfigItem, Templates.IntegrationSettings.Send.Fields.GetAllActiveEmailLists);
var endpoint = endpointFormatter.Process();
if (string.IsNullOrWhiteSpace(endpoint))
{
Log.Error("Endpoint is not configured.", this);
throw new InvalidOperationException("Endpoint is not configured.");
}
var response = await _httpClient.GetAsync(endpoint);
if (!response.IsSuccessStatusCode)
{
Log.Error($"Failed to get email lists. Status code: {response.StatusCode}, Reason: {response.ReasonPhrase}", this);
throw new HttpRequestException($"Failed to get email lists. Status code: {response.StatusCode}, Reason: {response.ReasonPhrase}");
}
var jsonSerializerSettings = new JsonSerializerSettings
{
MissingMemberHandling = MissingMemberHandling.Ignore
};
AllMailingLists mailingLists;
try
{
var responseContent = await response.Content.ReadAsStringAsync();
mailingLists = JsonConvert.DeserializeObject<AllMailingLists>(responseContent, jsonSerializerSettings);
}
catch (Exception ex)
{
Log.Error("Error deserializing email lists.", ex, this);
throw;
}
if (mailingLists == null)
{
Log.Error("Deserialization of email lists returned null.", this);
throw new InvalidOperationException("Deserialization of email lists returned null.");
}
if (mailingLists.Code != 0)
{
Log.Error($"Error in response: {mailingLists.Error}", this);
throw new InvalidOperationException($"Error in response: {mailingLists.Error}");
}
return mailingLists;
}
Getting the Subscribers of an Existing Mailing List
Like above, this method returns MailingListSubscribers which gives the task all existing recipients. This is going to be needed so we know who exists and who needs to be added.
public async Task<MailingListSubscribers> GetAllSubscribersOfMailingListAsync(string mailingListID, MailingListMemberStatus status)
{
var endpointFormatter = new GatewayUtility.EndpointFormatter(_httpClient, GlobalConfigItem, Templates.IntegrationSettings.Send.Fields.GetAllSubscribersofEmailList);
endpointFormatter.AddToken("mailingListID", mailingListID);
endpointFormatter.AddToken("Status", status.ToString());
var endpoint = endpointFormatter.Process();
if (string.IsNullOrWhiteSpace(endpoint))
{
Log.Error("Endpoint is not configured.", this);
throw new InvalidOperationException("Endpoint is not configured.");
}
var response = await _httpClient.GetAsync(endpoint);
if (!response.IsSuccessStatusCode)
{
Log.Error($"Failed to get email list subscribers. Status code: {response.StatusCode}, Reason: {response.ReasonPhrase}", this);
throw new HttpRequestException($"Failed to get email list subscribers. Status code: {response.StatusCode}, Reason: {response.ReasonPhrase}");
}
var jsonSerializerSettings = new JsonSerializerSettings
{
MissingMemberHandling = MissingMemberHandling.Ignore
};
MailingListSubscribers subscribers;
try
{
var responseContent = await response.Content.ReadAsStringAsync();
subscribers = JsonConvert.DeserializeObject<MailingListSubscribers>(responseContent, jsonSerializerSettings);
}
catch (Exception ex)
{
Log.Error("Error deserializing subscribers.", ex, this);
throw;
}
if (subscribers == null)
{
Log.Error("Deserialization of subscribers returned null.", this);
throw new InvalidOperationException("Deserialization of subscribers returned null.");
}
if (subscribers.Code != 0)
{
Log.Error($"Error in response: {subscribers.Error}", this);
throw new InvalidOperationException($"Error in response: {subscribers.Error}");
}
return subscribers;
}
Add Multiple Subscribers
The most interesting method in the gateway. Originally, I planned to use an update method for each recipient, but the rate limit is about 10 records every 2 seconds, which is incredibly slow. The AddMultipleSubscribers method from Send's API allows 1000 records every 2 seconds, AND it will update if a recipient already exists, which is what I want.
public async Task<bool> AddMultipleSubscribersAsync(string mailingListID, AddMultipleSubscribers addMultipleSubscribers)
{
if (addMultipleSubscribers == null
|| addMultipleSubscribers.Subscribers == null
|| addMultipleSubscribers.Subscribers.Count == 0)
return false;
var endpointFormatter = new GatewayUtility.EndpointFormatter(_httpClient, GlobalConfigItem, Templates.IntegrationSettings.Send.Fields.AddMultipleSubscribers);
endpointFormatter.AddToken("mailingListID", mailingListID);
var endpoint = endpointFormatter.Process();
if (string.IsNullOrWhiteSpace(endpoint))
{
Log.Error("Endpoint is not configured.", this);
throw new InvalidOperationException("Endpoint is not configured.");
}
var json = JsonConvert.SerializeObject(addMultipleSubscribers);
var content = new StringContent(json, Encoding.UTF8, "application/json");
var response = await _httpClient.PostAsync(endpoint, content);
if (!response.IsSuccessStatusCode)
{
Log.Error($"Failed to post subscribers. Status code: {response.StatusCode}, Reason: {response.ReasonPhrase}", this);
return false;
}
return true;
}
The Endpoint Formatter
I'm not showing all helpers, but this one is pivotal to good operations. It's given the current http client, global config item and endpoint field, which I'll explain. To start, there's some properties for the items passed to it, and a dictionary for values to be replaced:
internal class EndpointFormatter
{
private HttpClient HttpClient { get; set; }
private Item GlobalConfigItem { get; set; }
private string EndpointField { get; set; }
private readonly Dictionary<string, string> _tokens = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
public EndpointFormatter(HttpClient httpClient, Item globalConfigItem, string endpointField)
{
HttpClient = httpClient;
GlobalConfigItem = globalConfigItem;
EndpointField = endpointField;
}
There's also a method to add tokens so they're formatted in a way we'll expect:
public void AddToken(string name, string value)
{
if (string.IsNullOrWhiteSpace(name) || string.IsNullOrWhiteSpace(value))
return;
if (!name.StartsWith("{"))
name = "{" + name;
if (!name.EndsWith("{"))
name += "}";
if (_tokens.ContainsKey(name))
{
_tokens[name] = value;
return;
}
_tokens[name] = value;
}
And finally, Process will return a formatted string which can be used for calling Sitecore's Send API. You can see it will:
- Get the endpoint field's value from the global config item based on what was passed to the EndpointField property.
- Add the base address and API key from the global config item.
- replace all tokens in the result string with what's been put into the dictionary.
public string Process()
{
if (HttpClient == null || GlobalConfigItem == null || string.IsNullOrWhiteSpace(EndpointField))
return "";
var result = GlobalConfigItem.Fields[EndpointField].Value;
if (string.IsNullOrWhiteSpace(result))
return "";
AddToken("baseAddress", GlobalConfigItem.Fields[Templates.IntegrationSettings.Send.Fields.BaseAddress].Value);
AddToken("apiKey", GlobalConfigItem.Fields[Templates.IntegrationSettings.Send.Fields.ApiKey].Value);
AddToken("format", "json");
result = _tokens.Aggregate(result, (current, value) =>
System.Text.RegularExpressions.Regex.Replace(
current,
System.Text.RegularExpressions.Regex.Escape(value.Key),
value.Value ?? string.Empty,
System.Text.RegularExpressions.RegexOptions.IgnoreCase
)
);
return result;
}
The Global Config Item
Ok, before we get into the main task, I just want to cover the global config item, which allows administrators to update API details without the need of a release. The module looks for this item, so it's important not to delete it since recreating it will generate a different ID.
The fields are all set based on current documentation, with the API key being the only missing value. This you will have to set from your own instance of Sitecore Send.
You can see above there's a rate limit and max records per call, which is going to be highlighted in the following code and is important to follow.
What You've Been Waiting For - The Task
At this point I've covered creating a menu item, the PowerShell script, the gateway and helpers. There's nothing left except to use it all in a task. Like before, I'm going to cover code in order of how it's used in an execution.
Get Email Lists for Selection
This returns a Hashtable that PowerShell can use to present a selection list for email lists. Because it can't be ordered you can see in the PowerShell script itself that it does the ordering there.
public async Task<Hashtable> GetEmailListsForSelectionAsync()
{
var result = new Hashtable();
using (var sendGateway = new Gateways.SendGateway())
{
AllMailingLists allMailingLists = null;
try
{
allMailingLists = await sendGateway.GetAllActiveMailingListsAsync();
foreach (var mailingList in allMailingLists.Context.MailingLists)
{
result.Add(mailingList.Name, mailingList.Id.ToString());
}
}
catch (Exception ex)
{
Log.Info($"Error getting email lists. ({ex.Message})", this);
return result;
}
}
result.Add(string.Empty, string.Empty);
return result;
}
And a revisit of the ordering:
$orderedEmailLists = [ordered]@{}
$emailLists.GetEnumerator() | Sort-Object Key | ForEach-Object {
$orderedEmailLists[$_.Key] = $_.Value
}
Running the Task
I'll interrupt here and there to explain what's going on in this method, which returns a Tuple<bool, string>.
The Boolean indicates if there was an error, and the string is the output to the Sitecore client you saw in the last article. This also begins with some checks to ensure all variables are what we need them to be.
As a side note, all messaging is also logged so there's a record beyond the client.
public async Task<Tuple<bool, string>> BatchEmailListUpdateAsync(string fileLocation, string mailingListID, string mode)
{
var message = "";
var sb = new StringBuilder();
var added = 0;
var skipped = 0;
var skippedUnsubscribed = 0;
var updated = 0;
if (fileLocation == "cancel")
{
message = $"File selection was cancelled.";
sb.AppendLine(message);
Log.Info(message, this);
return new Tuple<bool, string>(false, sb.ToString());
}
else if (string.IsNullOrWhiteSpace(mailingListID))
{
message = $"No mailing list was selected.";
sb.AppendLine(message);
Log.Info(message, this);
return new Tuple<bool, string>(false, sb.ToString());
}
else if (string.IsNullOrWhiteSpace(mode))
{
message = $"Run mode selection was cancelled.";
sb.AppendLine(message);
Log.Info(message, this);
return new Tuple<bool, string>(false, sb.ToString());
}
The process begins with getting all current subscribers to the mailing list the User selected, both subscribed and unsubscribed.
using (var sendGateway = new Gateways.SendGateway())
{
message = $"Getting email list: {mailingListID}";
sb.AppendLine(message);
Log.Info(message, this);
MailingListSubscribers allSubscribed = null;
try
{
allSubscribed = await sendGateway.GetAllSubscribersOfMailingListAsync(mailingListID, MailingListMemberStatus.Subscribed);
}
catch (Exception ex)
{
message = $"Error retrieving email list. It is likely the list is not found. ({ex.Message})";
sb.AppendLine(message);
Log.Info(message, this);
return new Tuple<bool, string>(false, sb.ToString());
}
message = $"There are {allSubscribed.Context.Subscribers.Count} subscribed people in the current email list.";
sb.AppendLine(message);
Log.Info(message, this);
MailingListSubscribers allUnsubscribed = null;
try
{
allUnsubscribed = await sendGateway.GetAllSubscribersOfMailingListAsync(mailingListID, MailingListMemberStatus.Unsubscribed);
}
catch (Exception ex)
{
message = $"Error retrieving email list. It is likely the list is not found. ({ex.Message})";
sb.AppendLine(message);
Log.Info(message, this);
return new Tuple<bool, string>(false, sb.ToString());
}
message = $"There are {allUnsubscribed.Context.Subscribers.Count} unsubscribed people in the current email list.";
sb.AppendLine(message);
Log.Info(message, this);
Now that the current data is retrieved from Send, the CSV file is parsed and checked. The rows in the CSV are then added to a list of csvSubscribers, which has standard and custom field flags, so they're formatted properly into the API operation further on.
message = $"Loading CSV file {fileLocation}.";
Log.Info(message, this);
var dt = DataTableFromCsv(fileLocation);
var columnCount = dt.Columns.Count;
var rowCount = dt.Rows.Count;
BatchEmailListCsvColumn[] columns = new BatchEmailListCsvColumn[columnCount];
if (rowCount < 1)
{
message = "CSV file does not contain any subscribers.";
sb.AppendLine(message);
Log.Info(message, this);
return new Tuple<bool, string>(false, sb.ToString());
}
var csvSubscribers = new List<AddToListSubscriber>();
int i = 0;
foreach (DataColumn column in dt.Columns)
{
string columnName = column.ColumnName;
var columnType = Lists.BatchEmailListCsvColumns().FirstOrDefault(c => c.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase));
if (columnType == null)
{
columnType = Lists.BatchEmailListCsvColumns().FirstOrDefault(c => c.IsCustom);
columnType.ColumnName = columnName;
}
columns[i] = columnType;
i++;
}
for (int m = 0; m < rowCount; m++)
{
var subscriber = new AddToListSubscriber();
for (i = 0; i < columnCount; i++)
{
var columnType = columns[i];
if (columnType.IsSkipped)
continue;
var columnValue = dt.Rows[m][i].ToString();
if (columnType.IsStatus && columnValue.ToLower() != "active")
{
skipped++;
continue;
}
if (columnType.IsStandard)
{
if (columnType.ColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase))
{
subscriber.Name = columnValue;
}
else if (columnType.ColumnName.Equals("Email", StringComparison.OrdinalIgnoreCase))
{
subscriber.Email = columnValue;
}
else if (columnType.ColumnName.Equals("Mobile", StringComparison.OrdinalIgnoreCase))
{
subscriber.Mobile = columnValue;
}
}
else if (columnType.IsCustom)
{
subscriber.AddCustomField(columnType.ColumnName, columnValue);
}
else if (columnType.IsList)
{
if (!string.IsNullOrWhiteSpace(columnValue))
{
var tags = columnValue.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
.Select(tag => tag.Trim())
.ToList();
subscriber.Tags.AddRange(tags);
}
}
}
csvSubscribers.Add(subscriber);
}
message = $"There are {csvSubscribers.Count()} records in the CSV file.";
sb.AppendLine(message);
Log.Info(message, this);
At this point all subscribers that are being uploaded are ready, so the final preparatory step is to merge tags on a subscriber if they already exist, and unsubscribed subscribers are skipped.
foreach (var subscriber in csvSubscribers)
{
var subscribedUser = allSubscribed.Context.Subscribers.FirstOrDefault(s => s.Email.Equals(subscriber.Email, StringComparison.OrdinalIgnoreCase));
if (subscribedUser != null)
{
if (subscribedUser.Tags.Any())
{
subscriber.Tags.AddRange(subscribedUser.Tags);
subscriber.Tags = subscriber.Tags.Distinct(StringComparer.OrdinalIgnoreCase).ToList();
}
updated++;
}
else
{
var unsubscribedUser = allUnsubscribed.Context.Subscribers.FirstOrDefault(s => s.Email.Equals(subscriber.Email, StringComparison.OrdinalIgnoreCase));
if (unsubscribedUser != null)
{
skippedUnsubscribed++;
}
else
{
added++;
}
}
}
var resultCounts = $"Added {added}, Updated {updated}, Skipped (unsubscribed): {skippedUnsubscribed}, Skipped (not active) {skipped}.";
If the module is in report mode the method returns what has been discovered so far.
if (mode != "execute")
{
message = $"When complete, the result will be:";
sb.AppendLine(message);
Log.Info(message, this);
sb.AppendLine(resultCounts);
Log.Info(resultCounts, this);
message = $"Program is in reporting mode. Exiting.";
sb.AppendLine(message);
Log.Info(message, this);
return new Tuple<bool, string>(true, sb.ToString());
}
If we've gotten this far it means the module is in execute mode, and some simple math is used to determine if the upload has to happen in batches.
var implementWait = false;
int rateLimit;
var rateLimitField = sendGateway.GlobalConfigItem.Fields[Templates.IntegrationSettings.Send.Fields.AddMultipleSubscribersRateLimit];
if (!int.TryParse(rateLimitField?.Value, out rateLimit) || rateLimit < 1)
rateLimit = 2;
int maxRecordsPerCall;
var maxRecordsPerCallField = sendGateway.GlobalConfigItem.Fields[Templates.IntegrationSettings.Send.Fields.AddMultipleSubscribersMaxRecordsPerCall];
if (!int.TryParse(maxRecordsPerCallField?.Value, out maxRecordsPerCall) || maxRecordsPerCall < 1)
maxRecordsPerCall = 1000;
var numberOfCalls = (int)Math.Ceiling((double)csvSubscribers.Count / maxRecordsPerCall);
if (csvSubscribers.Count > maxRecordsPerCall)
{
implementWait = true;
message = $"There are more than {maxRecordsPerCall} so the API must be used in increments (total of {numberOfCalls}).";
sb.AppendLine(message);
Log.Info(message, this);
}
So close to the end! Here, batchSubscribers represents the piece of the upload for this call. It will run, wait 2 seconds, and run again until complete.
for (int j = 0; j < numberOfCalls; j++)
{
var batchSubscribers = csvSubscribers.Skip(j * maxRecordsPerCall).Take(maxRecordsPerCall).ToList();
if (await sendGateway.AddMultipleSubscribersAsync(mailingListID, batchSubscribers))
{
message = $"Successfully processed {batchSubscribers.Count} subscribers in batch {j + 1} of {numberOfCalls}.";
sb.AppendLine(message);
Log.Info(message, this);
}
else
{
message = $"Failed to update mailing list due to error at Send API in batch {j + 1}.";
sb.AppendLine(message);
Log.Info(message, this);
return new Tuple<bool, string>(false, sb.ToString());
}
if (implementWait)
{
message = $"Waiting {rateLimit} seconds between calls.";
sb.AppendLine(message);
Log.Info(message, this);
Thread.Sleep(rateLimit * 1000);
}
}
Nothing left to do but return the results with final counts.
message = $"Job finished. Processed {csvSubscribers.Count} subscribers.";
sb.AppendLine(message);
Log.Info(message, this);
sb.AppendLine(resultCounts);
Log.Info(resultCounts, this);
}
return new Tuple<bool, string>(true, sb.ToString());
}
Get Data Table From Provided CSV
In the above task we needed a data table, so this private method was created.
private DataTable DataTableFromCsv(string fileLocation)
{
var table = new DataTable();
using (var parser = new TextFieldParser(fileLocation))
{
parser.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited;
parser.SetDelimiters(";");
var isHeader = true;
while (!parser.EndOfData)
{
string[] fields = parser.ReadFields();
if (isHeader)
{
foreach (string field in fields)
table.Columns.Add(field);
isHeader = false;
}
else
{
var row = table.NewRow();
for (int i = 0; i < fields.Length; i++)
row[i] = fields[i];
table.Rows.Add(row);
}
}
}
return table;
}
Getting the Module
I've posted the source and a couple packages for this module for you to use. Thanks for reading!
Too Technical?
Tomorrow I'll be posting a User's Guide to for this module so non-developers know exactly how install and use it.
