Reliable Sending of Email for.Net/C# using SQL Server

If you want more reliable sending of emails from your .Net code you may think it is too much work to implement logging, retrying if smtp server is unavailable, control returned to user while email is sending, etc. The truth is that if you try to do it all via ASP.NET this can involve a lot of work. Thankfully, there is an easy way to meet all these requirements and not have to do much work at all.

As it turns out SQL Server has a robust facility for sending emails called Database Mail. It meets all the above requirements and these requirements are configurable. I have posted many entries on Database Mail such as resending emails, reviewing logs, etc. Just search on my blog for them. Much of the configuration comes from the Profile and Account settings you configure when you setup this info for your app.

Below is a C# method that you can add to your project. Calling this will allow you to send email reliably from your app and not have to worry about the delivery of it. it assumes that you have a class called Config with two static methods YourConnectionString and AllowEmailsToBeSent. You can hard code those obviously. The two methods just pull the values from the Web.config, but it could be anywhere in theory. The first is just your database connection string and the second controls whether the contents of the email will be written to a hard coded path on your harddrive for testing or it will be connect to SQL Server and actually send the email. This is useful for development / testing when you don’t want to disturb real users.

/// <summary>
/// Calls SQL Server Send Mail procedure, or logs to log file
/// </summary>
/// <param name="fromEmailAddress"></param>
/// <param name="toEmailAddresses">semi-colon separated list of recipients</param>
/// <param name="subject"></param>
/// <param name="body"></param>
public void SendEmail(string fromEmailAddress, string toEmailAddresses, string subject, string body)
{
    // this should never happen, but just so I will know about it
    if (string.IsNullOrEmpty(toEmailAddresses))
    {
        toEmailAddresses = "test@yourdomain.com";
    }
    // this should never happen, but just so I will know about it
    if (string.IsNullOrEmpty(fromEmailAddress))
    {
        fromEmailAddress = "test@yourdomain.com";
    }

    if (Config.AllowEmailsToBeSent)
    {
        // send email here
        using (SqlConnection conn = new SqlConnection(Config.YourConnectionString))
        {
            conn.Open();

            // use sql server database mail instead of .net mail
            // so that we get retry, logging, automatic batching, etc for free. Better reliability and recover.
            //-- for all parameters: http://msdn2.microsoft.com/en-us/library/ms190307.aspx   
            SqlCommand cmd = new SqlCommand("msdb.dbo.sp_send_dbmail", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@profile_name", SqlDbType.VarChar).Value = "Your Profile name here";
            cmd.Parameters.Add("@recipients", SqlDbType.VarChar).Value = toEmailAddresses;
            cmd.Parameters.Add("@subject", SqlDbType.VarChar).Value = subject;
            cmd.Parameters.Add("@body", SqlDbType.VarChar).Value = body;

            cmd.ExecuteNonQuery();
        }

    }
    else
    {

        StreamWriter writer = new StreamWriter(@"c:\Email.log", true);
        using (writer)
        {
            writer.WriteLine("\r\n\r\nDate/Time Stamp: " + DateTime.Now.ToLocalTime());
            writer.WriteLine("From: " + fromEmailAddress);
            writer.WriteLine("To: " + toEmailAddresses);
            writer.WriteLine("Subject: " + subject);
            writer.WriteLine("Body: " + body);
        }
    }
}