Receive Email Read Notification and Save It in Database

Introduction

This is my first attempt to write an article. This article will look at how to use HttpModule to record email read notifications in database.

Background

There are quite a few ways to get email read notifications for .NET based applications. We will look at one such option of using HttpModule and image tag to record the datetime of email read. Below is the step by step guide of how you can create this option for your application.

Using the Code

1. First we will create a database table called SystemEmailsAudit.
   SQL

   //
   CREATE TABLE [dbo].[SystemEmailsAudit]( [RecordId] [int] IDENTITY(1,1) NOT NULL, 
                                           [UserId] [varchar](20) NULL, 
                                           [EmailFor] [varchar](500) NULL, 
                                           [DateSent] [datetime] NULL, 
                                           [UniqueKey] [varchar](50) NULL, 
                                           [DateRead] [datetime] NULL ) ON [PRIMARY] 

   This table will keep track of all the emails that the system sends to users and we will register a record in the table when system sends out emails. For this purpose, I have created two stored procedures to register email audit when system sends out email and to update email reading date when user reads the email.

2. Create stored procedures in database to register and update SystemEmailsAudit table.
   SQL

       CREATE PROCEDURE register_system_email_audits
   	@UserId varchar(20),
   	@EmailFor varchar(500),
   	@DateSent datetime,
   	@UniqueKey varchar(20) output
   AS
   BEGIN
   	INSERT INTO [SystemEmailsAudit]
              ([UserId]
              ,[EmailFor]
              ,[DateSent]
              ,[UniqueKey]
              )
        VALUES
              (@UserId 
              ,@EmailFor
              ,@DateSent
              ,(SELECT CAST( CAST(RAND() * 100000000 AS int) as varchar(20)))
              );
        SELECT @UniqueKey=s.UniqueKey FROM [SystemEmailsAudit] s _
   		WHERE s.RecordId=@@IDENTITY;       
   END
   
   GO
   
   CREATE PROCEDURE update_system_emails_audit_read_date
   	@UniqueKey varchar(20),
   	@ReadDate datetime
   AS
   BEGIN
   	UPDATE SystemEmailsAudit
   	SET DateRead=@ReadDate
   	WHERE UniqueKey=@UniqueKey;
   END

3. Create a Common class that will call register stored procedure and send emails. I have a database utility class that has overloaded methods to deal with ExecuteNonQuery() method.
   SQL

   public class Common
   {
       public string RegisterSystemEmailAudit(string userId,
          string emailFor, DateTime sentDate)
          {
              SqlParameter uniqueKeyParam = new SqlParameter
              ("@UniqueKey", SqlDbType.VarChar);
              uniqueKeyParam.Direction = ParameterDirection.Output;
              uniqueKeyParam.Size = 255;
   
               public string conString = ConfigurationManager.ConnectionStrings
                  ["MyConnectionString"].ConnectionString;
               SqlConnection con = new SqlConnection(conString);
               DatabaseUtility.Connection = con;
   
              SqlParameter[] userParams = new SqlParameter[] {
                                          new SqlParameter("@UserId",userId),
                                          new SqlParameter("@EmailFor",emailFor),
                                          new SqlParameter("@DateSent",sentDate),
                                          uniqueKeyParam
                                          };
   
              con.Open();
              SqlCommand cmd = DatabaseUtility.ExecuteNonQuery(con,
              "register_system_email_audits",
          CommandType.StoredProcedure,true, userParams);
              con.Close();
              if (cmd !=null)
              {
                  return cmd.Parameters["@UniqueKey"].Value.ToString(); ;
              }
              else
              {
                  return "N/A";
              }
          }
   
           public static void SendMailMessage
          (string to, string subject, string message)
           {
              try
              {
   
                  MailMessage mailMsg = new MailMessage();
                  result = to.Split(new char[]{','},
              StringSplitOptions.RemoveEmptyEntries);
                  for (int count = 0; count < result.Length; count++)
                  {
                      mailMsg.To.Add(new MailAddress(result[count]));
                  }
                  mailMsg.Bcc.Add(ConfigurationManager.AppSettings
                  ["BCCEmail"].ToString().Trim());
                  mailMsg.From =  new MailAddress(ConfigurationManager.AppSettings
                  ["FromEmail"]);
                  mailMsg.Subject = subject;
                  mailMsg.Body = message;
                  mailMsg.IsBodyHtml=true;
   
                  //mailMsg.Headers.Add("Disposition-Notification-To",
                      "receiptto@email.com");
   
                  SmtpClient smtpClient = new SmtpClient();
   
                  smtpClient.EnableSsl = true;
                  //
   
                  smtpClient.Send(mailMsg);
              }
              catch (Exception exc)
              {
                // Deal with exception here
              }
          }
   }
   

4. Create a class library project in Visual Studio called ImageTracker and implement the IHttpModule interface. You have to add reference to System.Web namespace. This HttpModule will intercept all the requests in the ASP.NET pipeline and match to see if it is coming from an email by comparing the requested URL. In the email body, we will add a URL that matches with the pattern and adds unique key value to track it back to the exact user to whom the system sent email. In the below example, we add an event handler that looks at all the incoming requests and checks for the URL that matches "~/images/<keyvalue>.aspx" pattern. Please note that you don't require <keyvalue>.aspx file anywhere in your project as it is replaced by different image (footerFile) when we match the pattern.
   C#

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Web;
   using System.Text.RegularExpressions;
   using ImageTracker.DL;
   using System.Data;
   using System.Data.SqlClient;
   
   namespace ImageTracker
   {
       public class TrackRequest1 : IHttpModule
       {
           #region IHttpModule Members
           string footerFile = "~/images/footer.png";
           public void Dispose()
           {
              
           }
   
           public void Init(HttpApplication context)
           {
               context.BeginRequest += new System.EventHandler(GetImage_BeginRequest);
           }
   
           public void GetImage_BeginRequest(object sender, System.EventArgs args)
           {
               //cast the sender to a HttpApplication object
               System.Web.HttpApplication application = 
   				(System.Web.HttpApplication)sender;
   
               string url = application.Request.Path; //get the url path
               string pattern = @"/images/(?<key>.*)\.aspx";
   
               //create the regex to match for beacon images
               Regex r = new Regex
   		(pattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);
               if (r.IsMatch(url))
               {
                   MatchCollection mc = r.Matches(url);
                   if ((mc != null) && (mc.Count > 0))
                   {
                       string key = (mc[0].Groups["key"].Value);
                       UpdateSystemEmailAuditReadDate(key);
                   }
   
                   //now send the REAL image to the client
                   application.Response.ContentType = "image/gif";
                   application.Response.WriteFile
   			(application.Request.MapPath(footerFile));
   
                   //end the response
                   application.Response.End();
               }
           }
   
           public bool UpdateSystemEmailAuditReadDate(string uniqueKey)
           {
               public string conString = ConfigurationManager.ConnectionStrings
               	["MyConnectionString"].ConnectionString;
               SqlConnection con = new SqlConnection(conString);
               DatabaseUtility.Connection = con;
               
               SqlParameter[] commonParams = new SqlParameter[] {
                                             new SqlParameter("@UniqueKey",uniqueKey),
                                             new SqlParameter("@ReadDate",DateTime.Now)      
                                             };
               con.Open();
               
               int rowsAffected = DatabaseUtility.ExecuteNonQuery(con, 
               "update_system_emails_audit_read_date", 
   			CommandType.StoredProcedure, commonParams);
               con.Close();
               if (rowsAffected > 0)
               {
                   return true;
               }
               else
               {
                   return false;
               }
           }
   
           #endregion
       }
   }

5. In your application, add a reference to this ImageTracker project. In your application project, go to References and right click on it and select Add Reference and select browse tab and add reference to ImageTracker DLL file.
6. In your application's web.config file, add this HttpModule:
   XML

   <httpModules>
   	<add name="ScriptModule" type="System.Web.Handlers.ScriptModule, 
   	System.Web.Extensions, Version=3.5.0.0, Culture=neutral, 
   	PublicKeyToken=31BF3856AD364E35"/>
            <add type="ImageTracker.TrackRequest1,ImageTracker" name="ImageTracker" /> 
   </httpModules>

7. Create an email body template that has an image tag that matches the pattern we want to track.

   Dear User, 
   
        <br>  <br>                
        Thank you for your registration.....<br>Regards, <br>
        Administration 
        <br><br>
        <img src='http://localhost:4920/images/<keyvalue>.aspx'/>

   In the above example, note the <img src='http://localhost:49207/images/<keyvalue>.aspx'/> . When sending out an email, we will call register stored proc that we created in step 2 and receive the UniqueKey and we will replace <keyvalue> with that value. When user receives that email and reads it, the system will receive a request with that particular keyvalue and we can update the SystemEmailsAudit table record by registering read date against that unique key.
   Note: In your production system, you have to replace localhost:49207 with your actual domain name like www.mysite.com.

8. How to use the above email template.
   C#

   string subject = "This is test";    
   string message = @"use_step_7_email_body";
   string toEmail = "user@email.com";
   /*Note this addition*/                  
   string keyValue = common.RegisterSystemEmailAudit(userId, subject, DateTime.Now);
   message = message.Replace("<keyvalue>", keyValue);
   Common.SendMailMessage(toEmail, subject, message);

History

• 13^th October, 2011: Initial version