Uncategorized

Sending Emails from SQL Server through a Testable and Configurable CLR Assembly

The stored procedure sp_send_dbmail is intended to send emails directly from SQL Server. One of benefits using sp_send_dbmail puts messages into a message queue rather than directly sending them to recipients, which is asynchronous. Therefore, I would recommend using the stored procedure where possible. In order to use the stored procedure, a profile to execute the stored procedure MUST have an enough privilege. However, it might be impossible (or at least hard) to get your profile privileged because of various reasons including security concerns. In this case, an alternative way should be necessary. SQL Mailer can be a good option for it. In this article, I’ll introduce how SQL Mailer achieves the goal.

NOTE: This code can be found at https://github.com/aliencube/SQL-Mailer.

Creating CLR Assembly

This is the typical CLR assembly logic. Nothing special can be found here.

namespace DevKimchi
{
  public class SqlMailer
  {
    [SqlProcedure]
    public static void SendMail(SqlString sender, SqlString recipients, SqlString subject, SqlString body)
    {
      using (var smtp = new SmtpClient())
      using (var message = new MailMessage())
      {
        var from = new MailAddress(sender.Value);
        var to = new MailAddress(recipients.Value);

        message.From = from;
        message.To.Add(to);
        message.Subject = subject.Value;
        message.Body = body.Value;

        message.SubjectEncoding = Encoding.UTF8;
        message.BodyEncoding = Encoding.UTF8;
        message.IsBodyHtml = true;

        smtp.Host = "localhost";
        smtp.Port = 25;
        smtp.EnableSsl = false;
        smtp.UseDefaultCredentials = true;
        smtp.Send(message);
      }
    }
  }
}

`</pre>

Copile it and place it to your preferred location. Assuming it is copied to `C:\Temp\SqlMailer` and its filename is `SqlMailer.dll`.

## Registering CLR Assembly

Now, the CLR assembly is ready for registration. Let's have a go for the following SQL script.

<pre>`USE [MyDatabase]
GO

CREATE ASSEMBLY [SqlMailer]
  FROM 'C:\Temp\SqlMailer\SqlMailer.dll'   
  WITH PERMISSION_SET = EXTERNAL_ACCESS   
GO
`</pre>

If the permission is set to `EXTERNAL_ACCESS`, you might face a permission error. In this case, you can run the following query to allow libraries outside SQL Server.

<pre>`-- Change the database to access libraries outside SQL Server.
ALTER DATABASE [MyDatabase]
  SET TRUSTWORHTY ON
  WITH ROLLBACK IMMEDIATE
GO
`</pre>

Once CLR assembiy is registered, a stored procedure, `usp_SendMail` can be created. Make sure that the `EXTERNAL NAME` MUST follow the format of **[Registered Assembly Name].[Namespace &amp; Class Name].[Method Name]**.

<pre>`CREATE PROCEDURE [dbo].[usp_SendMail]
  @sender     AS NVARCHAR(256),
  @recipients AS NVARCHAR(256),
  @subject    AS NVARCHAR(256),
  @body       AS NVARCHAR(MAX)
AS
EXTERNAL NAME [SqlMailer].[DevKimchi.SqlMailer].[SendMail]
GO
`</pre>

If you find an exception related to `System.Net.Mail`, you should probably run the following script first and run the script above again.

<pre>`-- Change the database owner to any account having the system administrator privilege.
EXEC sp_changedbowner 'devkimchi'
GO
`</pre>

Now, the CLR assembly has been registered. Let's send an email through this.

<pre>`EXEC usp_SendMail 'from@email.com', 'to@email.com', 'email subject', 'email body'
`</pre>

If an SMTP server is properly setup, you can receive the email. If you can't use an SMTP server from your local machine during the development, [Papercut](http://papercut.codeplex.com) will be a good option for your testing purpose.

It's all done. At least, we have a working assembly. However, the code is not suitable for test as it's a static method. Now, we're going to refactor the `SendMail` method to be more testable.

## Refactoring for Testability

First of all, the `SqlMailer` class should implement the interface `ISqlMailer` which contains only one method `Send`.

<pre>`public interface ISqlMailer : IDisposable
{
  bool Send(string sender, string recipients, string subject, string body);
}
`</pre>

With this interface, `SqlMailer` should be modified:

<pre>`public class SqlMailer : ISqlMailer
{
  private bool _disposed;

  [SqlProcedure]
  public static void SendMail(SqlString sender, SqlString recipients, SqlString subject, SqlString body)
  {
    using (var mail = new SendMail())
    {
      mail.Send(sender.Value, recipients.Value, subject.Value, body.Value);
    }
  }

  public bool Send(string sender, string recipients, string subject, string body)
  {
    using (var smtp = new SmtpClient())
    using (var message = new MailMessage())
    {
      var from = new MailAddress(sender.Value);
      var to = new MailAddress(recipients.Value);

      message.From = from;
      message.To.Add(to);
      message.Subject = subject.Value;
      message.Body = body.Value;

      message.SubjectEncoding = Encoding.UTF8;
      message.BodyEncoding = Encoding.UTF8;
      message.IsBodyHtml = true;

      smtp.Host = "localhost";
      smtp.Port = 25;
      smtp.EnableSsl = false;
      smtp.UseDefaultCredentials = true;
      smtp.Send(message);
    }
  }

  public void Dispose()
  {
    if (this._disposed)
    {
      return;
    }
    this._disposed = true;
  }
}
`</pre>

As you can see above, the static method exposed to SQL Server only passes the values to the actual `SqlMailer` instance. Hence, `SqlMailer` is now testable. A test code can be written, with [`NUnit`](http://nunit.org) and [`FluentAssertions`](http://www.fluentassertions.com).

<pre>`[TestFixture]
public class SqlMailerTest
{
  private ISqlMailer _mailer;

  [SetUp]
  public void Init()
  {
    this._mailer = new SqlMailer();
  }

  [TearDown]
  public void Dispose()
  {
    if (this._mailer != null)
    {
      this._mailer.Dispose();
    }
  }

  [Test]
  [TestCase("sender@email.com", "recipients@email.com", "email subject", "email body", true)]
  public void SendEmails_GivenConditions_ReturnsResult(string sender, string recipients, string subject, string body, bool expected)
  {
    var sent = this._mailer.Send(sender, recipients, subject, body);
    sent.Should().Be(expected);
  }
}
`</pre>

So far, the `SqlMailer` class has become testable. However, the `SmtpClient` class still needs dependencies for SMTP server settings. If production server uses a different SMTP server settings, the code MUST be recompiled. For web, console, WinForm or WPF applications, this can be sorted out by using App.config or Web.config. However, as CLR assembly is not an application, App.config can't be used. Instead, the assembly should manually handle the configuration file.

## Refactoring for Configurability

In order to change the CLR assembly to be configurable, a new data container class deserialised from an XML file should be developed. Assume that we have an `SqlMailer.config` file for SMTP configuration.

<pre>`&lt;configuration&gt;
  &lt;smtp&gt;
    &lt;enableSsl&gt;false&lt;/enableSsl&gt;
    &lt;host&gt;localhost&lt;/host&gt;
    &lt;port&gt;25&lt;/port&gt;
    &lt;defaultCredentials&gt;true&lt;/defaultCredentials&gt;
  &lt;/smtp&gt;
&lt;/configuration&gt;
`</pre>

Here's the corresponding data container class with its interface.

<pre>`public interface ISqlMailerConfig : IDisposable
{
  Smtp Smtp { get; set; }
}

[Serializable]
[XmlRoot("configuration")]
public class SqlMailerConfig : ISqlMailerConfig
{
  private const string APPLICATION_PATH = "C:\Temp\SqlMailer";

  private bool _disposed;

  [XmlElement(ElementName = "smtp", Type = typeof(Smtp), IsNullable = false)]
  public Smtp Smtp { get; set; }

  public static ISqlMailerConfig CreateInstance(string filename = null)
  {
    if (String.IsNullOrWhiteSpace(filename))
    {
      filename = "SqlMailer.config";
    }

    ISqlMailerConfig config;
    using (var stream = new FileStream(Path.Combine(APPLICATION_PATH, filename), FileMode.Open))
    {
      var serialiser = new XmlSerializer(typeof(SqlMailerConfig));
      config = serialiser.Deserialize(stream) as ISqlMailerConfig;
    }
    return config;
  }

  public void Dispose()
  {
    if (this._disposed)
    {
      return;
    }

    this._disposed = true;
  }
}

[Serializable]
[XmlType(TypeName = "smtp")]
public class Smtp
{
  private bool? _enableSsl;

  [XmlElement(ElementName = "enableSsl", DataType = "boolean", IsNullable = true)]
  public bool? EnableSsl
  {
    get
    {
      if (!this._enableSsl.HasValue)
      {
        this._enableSsl = false;
      }
      return this._enableSsl;
    }
    set { this._enableSsl = value; }
  }

  private string _host;

  [XmlElement(ElementName = "host", DataType = "string", IsNullable = true)]
  public string Host
  {
    get
    {
      if (String.IsNullOrWhiteSpace(this._host))
      {
        this._host = "localhost";
      }
      return this._host;
    }
    set { this._host = value; }
  }

  private int? _port;

  [XmlElement(ElementName = "port", DataType = "int", IsNullable = true)]
  public int? Port
  {
    get
    {
      if (!this._port.HasValue)
      {
        this._port = 25;
      }
      return this._port;
    }
    set { this._port = value; }
  }

  private bool? _defaultCredentials;

  [XmlElement(ElementName = "defaultCredentials", DataType = "boolean", IsNullable = true)]
  public bool? DefaultCredentials
  {
    get
    {
      if (!this._defaultCredentials.HasValue)
      {
        this._defaultCredentials = true;
      }
      return this._defaultCredentials;
    }
    set { this._defaultCredentials = value; }
  }
}
`</pre>

The only caveat here is the `APPLICATION_PATH` constant. As CLR assemblies are registered into SQL Server in binary format, there is no way to get the real application path, even though there are still files located at `C:\Temp\SqlMailer`. If the `APPLICATION_PATH` is omitted, the CLR assemblies assume that the file is located at `C:\Windows\System32`. We can put the `SqlMailer.config` file directly into there, but this is highly not recommended. Therefore, we MUST explicitly hardcode the application path. Other than this, all will be fine.

Let's move onto the test methods. As `SqlMailerConfig` implements the interface `ISqlMailerConfig`, this is also testable. Therefore, a test code could be:

<pre>`[TestFixture]
public class SqlMailerConfigTest
{
  private ISqlMailerConfig _config;

  [SetUp]
  public void Init()
  {
  }

  [TearDown]
  public void Dispose()
  {
    if (this._config != null)
    {
      this._config.Dispose();
    }
  }

  [Test]
  [TestCase("SqlMailer.config")]
  public void GetDeserialisedObject_GivenConfig_ReturnDeserialisedObject(string filename)
  {
    this._config = SqlMailerConfig.CreateInstance(filename);
    this._config.Should().NotBeNull();

    var smtp = this._config.Smtp;
    smtp.Should().NotBeNull();
    smtp.EnableSsl.Value.Should().Be(false);
    smtp.Host.Should().Be("localhost");
    smtp.Port.Value.Should().Be(25);
    smtp.DefaultCredentials.Value.Should().Be(true);
  }
}
`</pre>

This code enables `SmtpClient` to be configurable, once `SqlMailer` imports the config settings. Let's modify the `SqlMailer` class again to accept this configurations.

<pre>`public class SqlMailer : ISqlMailer
{
  private readonly ISqlMailerConfig _config;

  private bool _disposed;

  public SqlMailer(ISqlMailerConfig config = null)
  {
    if (config = null)
    {
      config = SqlMailerConfig.CreateInstance();
    }
    this._config = config;
  }

  [SqlProcedure]
  public static void SendMail(SqlString sender, SqlString recipients, SqlString subject, SqlString body)
  {
    using (var mail = new SendMail())
    {
      mail.Send(sender.Value, recipients.Value, subject.Value, body.Value);
    }
  }

  public bool Send(string sender, string recipients, string subject, string body)
  {
    using (var smtp = new SmtpClient())
    using (var message = new MailMessage())
    {
      var from = new MailAddress(sender.Value);
      var to = new MailAddress(recipients.Value);

      message.From = from;
      message.To.Add(to);
      message.Subject = subject.Value;
      message.Body = body.Value;

      message.SubjectEncoding = Encoding.UTF8;
      message.BodyEncoding = Encoding.UTF8;
      message.IsBodyHtml = true;

      smtp.Host = this._config.Smtp.Host.Value;
      smtp.Port = this._config.Smtp.Port.Value;
      smtp.EnableSsl = this._config.Smtp.EnableSsl.Value;
      smtp.UseDefaultCredentials = this._config.Smtp.UseDefaultCredentials.Value;
      smtp.Send(message);
    }
  }

  public void Dispose()
  {
    if (this._disposed)
    {
      return;
    }
    this._disposed = true;
  }
}

We’ve so far completed refactoring the SqlMailer assembly to become testable and configurable. Register this assembly again and send emails directly from SQL Server through it. It should be fine.

Share