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)
{
System.Web.HttpApplication application =
(System.Web.HttpApplication)sender;
string url = application.Request.Path; string pattern = @"/images/(?<key>.*)\.aspx";
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);
}
application.Response.ContentType = "image/gif";
application.Response.WriteFile
(application.Request.MapPath(footerFile));
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,
<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.
- How to use the above email template.
string subject = "This is test";
string message = @"use_step_7_email_body";
string toEmail = "user@email.com";
string keyValue = common.RegisterSystemEmailAudit(userId, subject, DateTime.Now);
message = message.Replace("<keyvalue>", keyValue);
Common.SendMailMessage(toEmail, subject, message);
History
- 13th October, 2011: Initial version
