Tuesday, June 28, 2011

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))

            // 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:
            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;



        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);


Sush said...

Hi Brent,

Thanks for your posts,they are highly informative.
I am looking at sending emails from a web screen [aspx page] and I have configured the database mail in SQL Server 2005. Could you let me know whats the most optimal way of sending mail onclick of the page.

Some details of the page are as follows:
The screen has some input fields where the user will chose from dropdownlists and some fields where he will leave his comments,
there will be a option shown below if he would like to send all these details via mail to Approver , if yes he needs to key in the email id and click Submit. On clicking Submit the details entered by the user must be stored in the db and the same details must be sent to the approver by mail.

As i said earlier , Database mail is configured.

Let me know your thoughts.

Brent V said...

Hi Sush,

I'm not sure I understand your question. The code I provided here is the code you would call from your .aspx page. You just need to change the profile name to match what you have in SQL Server Database Mail.

While this does not send the mail immediately, it is pretty close to it.

I hope that helps.


Jonathan Shields said...

Thanks. This saved me some work.