Receive Email Read Notification and Save It in Database

virang_21

4.00/5 (8 votes)

Oct 14, 2011

CPOL

2 min read

47384

This article will look at using HttpModule to record email read notifications.

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

First we will create a database table called SystemEmailsAudit.

//
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.

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

    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

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.

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

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.

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

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.

In your application's web.config file, add this HttpModule:

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

Create an email body template that has an image tag that matches the pattern we want to track.
```
 
Dear User, 

 
 Thank you for your registration..... Regards, 
 Administration 
 
 <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.

How to use the above email template.

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