Click here to Skip to main content
Click here to Skip to main content

Tagged as

Receive Email Read Notification and Save It in Database

, 14 Oct 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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

  2. 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
  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.
     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.
    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:
    <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.
    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

  • 13th October, 2011: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

virang_21
Software Developer (Senior)
Australia Australia
I am currently working as a Software Developer in .NET technologies.
 
Languages & Technologies I have worked on so far:
 
Pascal ... My first programming language..baby steps..
8085 Assembler ... Amazing how it all works internally in computer brain... "intel inside"
C... missing my point(er)....
C++... Its all about classes ...
LISP ... Did I missed any parenthesis ?
VB... Oh dear... Just like a war stories.. those are the days
Java... I learn to program this in notepad...
C#... Oh what a smooth sailing...
ASP.NET... web is the future ...
Oracle ... Not the one from "The Matrix"...
MSSQL... select Programming from Universe where StartTime >='BigBang'
Wireless Sensor Networks ... Look who is talking too ?

Comments and Discussions

 
QuestionPlease Send mi complete source PinmemberMember 1087572211-Jun-14 1:49 
Questionfull code source PinmemberMember 1067421216-Mar-14 4:58 
AnswerRe: full code source Pinmembervirang_2116-Mar-14 12:30 
QuestionPlease send me the working source Pinmembermaksha15-Mar-14 3:05 
AnswerRe: Please send me the working source Pinmembervirang_2116-Mar-14 12:30 
QuestionGood Artical PinmemberMember 1005581231-Jan-14 20:51 
QuestionAmazing Pinmemberboy_way16-Sep-13 0:42 
Questionfantastic Pinmembersys300028-May-13 1:16 
Questionregarding source code of this project Pinmemberpanivir12-Apr-13 0:48 
QuestionAttach Working source Code Pinmemberajay5432117-Mar-13 0:33 
Questionhi Pinmemberrajjon30-Sep-12 2:06 
GeneralGood idea Pinmemberstanino12-Nov-11 8:20 
GeneralOnly valid for HTML mail PinmemberMr. Mox18-Oct-11 0:06 
GeneralRe: Only valid for HTML mail PinmemberRichard Deeming19-Oct-11 9:06 
QuestionNice Article very helpful PinmemberTushar Patil,Pune14-Oct-11 21:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 14 Oct 2011
Article Copyright 2011 by virang_21
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid