What the...?

Something bad happened

We're not sure what, but we have a few guesses.

Problem: Object reference not set to an instance of an object.
Ticket: 7864830
Server: Web03
Receive Email Read Notification and Save It in Database - CodeProject Click here to Skip to main content
11,715,022 members (65,539 online)
Click here to Skip to main content

Tagged as

Receive Email Read Notification and Save It in Database

, 14 Oct 2011 CPOL 19.8K 32
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 ?

You may also be interested in...

Comments and Discussions

 
Questionnot redirect page to Imagetracker Pin
Member 107436915-Feb-15 0:54
memberMember 107436915-Feb-15 0:54 
Questionemail code on jyoti@shopsandhomes.com Pin
Member 107436914-Feb-15 19:53
memberMember 107436914-Feb-15 19:53 
QuestionPlease Send mi complete source Pin
Member 1087572211-Jun-14 0:49
memberMember 1087572211-Jun-14 0:49 
Questionfull code source Pin
Member 1067421216-Mar-14 3:58
memberMember 1067421216-Mar-14 3:58 
AnswerRe: full code source Pin
virang_2116-Mar-14 11:30
membervirang_2116-Mar-14 11:30 
QuestionPlease send me the working source Pin
maksha15-Mar-14 2:05
membermaksha15-Mar-14 2:05 
AnswerRe: Please send me the working source Pin
virang_2116-Mar-14 11:30
membervirang_2116-Mar-14 11:30 
QuestionGood Artical Pin
Member 1005581231-Jan-14 19:51
memberMember 1005581231-Jan-14 19:51 
QuestionAmazing Pin
boy_way15-Sep-13 23:42
memberboy_way15-Sep-13 23:42 
Questionfantastic Pin
sys300028-May-13 0:16
membersys300028-May-13 0:16 
Questionregarding source code of this project Pin
panivir11-Apr-13 23:48
memberpanivir11-Apr-13 23:48 
QuestionAttach Working source Code Pin
ajay5432116-Mar-13 23:33
memberajay5432116-Mar-13 23:33 
Questionhi Pin
rajjon30-Sep-12 1:06
memberrajjon30-Sep-12 1:06 
GeneralGood idea Pin
stanino12-Nov-11 7:20
memberstanino12-Nov-11 7:20 
GeneralOnly valid for HTML mail Pin
Mr. Mox17-Oct-11 23:06
memberMr. Mox17-Oct-11 23:06 
GeneralRe: Only valid for HTML mail Pin
Richard Deeming19-Oct-11 8:06
memberRichard Deeming19-Oct-11 8:06 
QuestionNice Article very helpful Pin
Tushar Patil,Pune14-Oct-11 20:01
memberTushar Patil,Pune14-Oct-11 20: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
Web03 | 2.8.150901.1 | Last Updated 14 Oct 2011
Article Copyright 2011 by virang_21
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid