This article describes two ways of sending content of a custom table by e-mail. The Custom tables module allows users to create their own tables in the system database and manage data in them via the Kentico CMS user interface, without the need to use e.g. Microsoft SQL Server Management. This may come in handy in many scenarios, typically when you need to store a large number of structured data items, for which standard Kentico CMS documents are not efficient. Sometimes, you may want to send its content out of the system by e-mail, or even to do so periodically. Let’s see how to do it.
The first approach is based on using Kentico API together with some basic .NET API. The sample code below can be called from any ASPX page template within Kentico CMS project. It can be placed into a Portal engine template using standard ASCX user control, or you can build a simple web part on this idea and take advantage of the pre-defined “Custom table selector” form control. Regardless of the method you choose, the basic idea is based on these four parts.
1. Let’s define a sample custom table. In my example, it stores popular Czech pop singers and contains 4 columns [Display name (column name)]:
First name (firstName), Second name (secondName), Age (age) and Gender (gender). Let’s put some data into it and send it out.
2. First, we need to get the required data into a DataSet. Please note, you should link the following assemblies (
using [namespace]) to use code below as is:
CMS.UIControls, CMS.SiteProvider, CMS.CMSHelper, CMS.SettingsProvider, CMS.EmailEngine, CMS.GlobalHelper, System.Data and
System.Text.
// Create new Custom table item provider
CustomTableItemProvider customTableProvider = new CustomTableItemProvider(CMSContext.CurrentUser);
// Custom table's code-name
string customTableClassName = "customtable.TestCustomTable";
// Check if given Custom table exists
DataClassInfo customTable = DataClassInfoProvider.GetDataClass(customTableClassName);
if (customTable != null)
{
// list of required columns and topN property
string columns = "firstName,lastName,age,gender";
int topN = 100;
// Get the data set according to the parameters
DataSet dataSet = customTableProvider.GetItems(customTableClassName, null, null, topN, columns);
// If some data found, compose body text and send e-mail, otherwise 'no data found'
if (!DataHelper.DataSourceIsEmpty(dataSet))
{ // Compose message and send e-mail }
3. When we have received the data successfully (DataSet isn’t empty), we can compose the HTML body of our e-mail message. The StringBuilder class is more than suitable for this purpose, so let’s use this to our advantage and write the following method:
/// <summary>
/// Compose body HTML text using StringBuilder class.
/// </summary>
/// <param name="_ds">DataSet containing required data</param>
/// <param name="_customTable">DataClassInfo</param>
/// <returns></returns>
public static string ComposeBody(DataSet _ds, DataClassInfo _customTable)
{
StringBuilder sb = new StringBuilder();
// Message header
sb.AppendFormat("<h3>Content of {0} custom table.</h3>", _customTable.ClassDisplayName);
sb.AppendLine("");
sb.Append("<table border=\"2\"");
sb.Append("<tr><th>First name</th><th>Last name</th><th>Age</th><th>Gender</th></tr>");
foreach (DataRow dr in _ds.Tables[0].Rows)
{
// Example how to transfer true / false Boolean value to corresponding string expression
string sGender0 = dr["Gender"].ToString();
string sGender1 = (sGender0.ToLower() == "true") ? "male" : "female";
// Compose table structure with corresponding values
sb.AppendFormat("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>", dr["firstName"].ToString(), dr["lastName"].ToString(), dr["age"].ToString(), sGender1);
}
sb.Append("</table>");
sb.Append("</br></br>");
// Message footer
sb.AppendFormat("[Send from {0} by {1}]", CMSContext.CurrentSiteName, CMSContext.CurrentUser.FullName);
return sb.ToString();
}
Please notice, it is totally up to you what layout you define. This is just an example; you may have to adjust according to your needs.
4. When the body message is composed, the final step is to send our e-mail out:
/// <summary>
/// Send e-mail message
/// </summary>
/// <param name="_sb">Message body text</param>
/// <param name="_fromEmail">From e-mail address</param>
/// <param name="_toEmail">To e-mail address</param>
/// <param name="_subject">Message subject</param>
public static void SendEmail(string _sb, string _fromEmail, string _toEmail, string _subject)
{
EmailMessage msg = new CMS.EmailEngine.EmailMessage();
msg.From = _fromEmail; // use valid e-mail
msg.Recipients = _toEmail; // use valid e-mail
msg.Subject = _subject;
msg.Body = _sb;
CMS.EmailEngine.EmailSender.SendEmail(msg);
}
Here’s the result:
Another approach requiring no programming is to use dynamic newsletter functionality. You can create a special page with a sample layout, inheriting no content, and only containing the proper Custom table viewer web part. Then you can use this page as a dynamic newsletter template and send it out once, or periodically.
If you want to test this functionality and don’t want or cannot use standard SMTP settings, you can use the Papercut utility.