Developing AJAX based popup notifications

Introduction

ASP.NET AJAX opens a wide range of possibilities to developers. One of them is periodic pinging of database and check for changes to the data. It is certainly not a new technique and developers achieved it in the past using JavaScript and XMLHTTP. However, with the help of ASP.NET AJAX the same task becomes much simple. In this article I will illustrate a possible approach for notifying the end user of any changes made to the database. The notifications are displayed in a popup to catch the attention of the user.

The Scenario

Let's assume that you have some data stored in a SQL server database. When data from a specific table from the database changes because of INSERT, UPDATE or DELETE operations you want to notify the user of this change. The user can then do the necessary action (say navigate to appropriate page of the site to process that data).

The Solution

One classic approach is to refresh the whole page periodically using META REFRESH tag. However, this approach is not an optimized one  because:

• It will refresh the whole page again and again just for a simple notification
• If the user is entering some data and the page refreshes then this very behavior can be annoying to the user

A better approach is to ping the server periodically without refreshing the whole page. This will save the bandwidth and will be quicker. Though it doesn't sole the second problem completely it will at least reduce it to a great extent.

In order to ping the server periodically one can use AJAX and web services. In this approach you will need to use setTimeout() JavaScript function to execte a script periodically. The script will call a web service that will tell you if the server data has changed or not. There is a still easy way - using UpdatePanel and Timer controls of ASP.NET AJAX. In this article we will see just that.

The Architecture

Have a look at the following figure carefully.

The above figure shows the architecture of our approach. It is assumed that you have a table named Employees in your database that you wish to monitor for any changes. We will write a trigger on the Employees table that adds an entry to another table called Notification table. The trigger will get executed upon INSERT, UPDATE and DELETE operations on the Employees table. The web form will periodically poll the notification table for any changes. If there are any changes it will display a popup notification to the user.

You might have guessed that this architecture is closely matching with that of SQL Server based cache dependencies.

Developing the application

Let's develop an application that implements the above architecture. Begin by creating a new AJAX enabled web site in Visual Studio.

Right click on the App_Data folder in the Solution Explorer and choose "Add New Item..." menu option. Add a new SQL Server database to your web site (you can create a new database in SQL Server directly if you so wish).

Then open Server Explorer and add a new table called Employees to the database. The structure of Employees table is shown below:

As you can see the Employees table consists of three columns viz. EmployeeID, Firstname and LastName. The EmployeeId column is the primary key.

Similarly add a table called Notifications to the database.

The notification table consists of three columns viz Id, TableName and ChangeId. The Tablename column stores names of the tables that you wish to monitor for any change. The ChangeId columns stores a GUID indicating a change identifier. This GUID is changed every time data in the table as indicated by TableName column changes.

Now create a new trigger named NotificationTrigger for the Employees table. The following T-SQL script shows you how:

CREATE TRIGGER [NotificationTriger] 
ON [dbo].[Employees] 
FOR INSERT, UPDATE, DELETE
AS
UPDATE Notifications 
WITH (ROWLOCK) 
SET changeId = NEWID() 
WHERE TableName = 'Employees'

The notification trigger updates the Notifications table whenever a row is added, modified or deleted from the Employees table.

This completes database level tasks. Now let's develop a web form that polls the notification table.

Open the default web form (that has ScriptManager control on it) and drag and drop an UpdatePanel on it. Also drag and drop a Timer control inside the UpdatePanel. Set the Interval property of the Timer to 5000 milliseconds.

Add a new style sheet to the web site and add two CSS classes to it as shown below:

.notificationPanel
{
    padding-right: 5px;
    padding-left: 5px;
    border-left-color: gold;
    left: 0px;
    border-bottom-color: gold;
    padding-bottom: 5px;
    width: 200px;
    border-top-style: ridge;
    border-top-color: gold;
    padding-top: 5px;
    border-right-style: ridge;
    border-left-style: ridge;
    position: absolute;
    top: 0px;
    height: 70px;
    background-color: lightyellow;
    border-right-color: gold;
    border-bottom-style: ridge;
    visibility: visible;
    margin: 5px;
}

.hiddenPanel
{
    visibility: hidden;
    display: none;
}

The class notificationPanel is applied to the notification popup when it is visible to the user whereas the class hiddenPanel is applied when the popup is hidden from the user. After creating the style sheet simply drag and drop it on the web form designer so as to generate the following markup:

<link href="StyleSheet.css" 
rel="stylesheet" type="text/css" />

Now drag and drop a Panel control inside the UpdatePanel. Also place a Label control inside this Panel. Set the Text property of the Label to "The data has been changed!". Add a LinkButton below the Label and set its Text property to Close.

Now comes the important part. Add a Load event handler for the UpdatePanel and write the following code inside:

protected void UpdatePanel1_Load(object sender, 
EventArgs e)
{
string strConn = @"Data Source=.\SQLEXPRESS;
AttachDbFilename=<website_path_here>\App_Data\
Database.mdf;Integrated Security=True;
User Instance=True";
SqlConnection cnn = new SqlConnection(strConn);
cnn.Open();
SqlCommand cmd = new SqlCommand("select changeid 
from notifications where tablename='Employees'",
cnn);
object obj = cmd.ExecuteScalar();
string changeid = obj.ToString();
cnn.Close();

if (Session["changeid"] == null)
{
Session["changeid"] = changeid;
Panel1.CssClass = "hiddenPanel";
Timer1.Enabled = true;
}
else
{
if (changeid != Session["changeid"].ToString())
{
Panel1.CssClass = "notificationPanel";
Timer1.Enabled = false;
}
else
{
Panel1.CssClass = "hiddenPanel";
Timer1.Enabled = true;
}

if (ScriptManager1.AsyncPostBackSourceElementID 
== "LinkButton1")
{
Session["changeid"] = changeid;
Panel1.CssClass = "hiddenPanel";
Timer1.Enabled = true;
}
}
}

The Load event of UpdatePanel control is raised each time the UpadtePanel is refreshed. Inside we connect with the database and get the value of ChangeId column of Employees table. We then store the retrieved changeid in a session variable. Initially the Panel is hidden by setting its CssClass property to hiddenPanel and Timer is enabled. Then onwards after every 5000 milliseconds only the "else" block will be executed. In the else block we check if the change id stored in the session and what is retrieved recently from the database is matching. If they are not matching it indicates that the Employee table has been changed. We then set the CssClass property of the Panel to notificationPanel. This will show the Panel to the user and user will feel as if a popup is being displayed. Till the time the Panel is being shown to the user the Timer is disabled.

The UpdatePanel may get refreshed because of two reasons:

• Timer refreshing the panel
• User clicking on the Close LLinkButton

The last "if" condition checks if the LinkButton has been clicked. This is done by using AsyncPostBackSourceElementID property of the ScriptManager. This property returns the ID of a control that triggered the post back. Inside we store the new change id in the session , hide the panel and enable the Timer again.

That's it. You are ready to test your web form. Just for the sake of testing you can use a DetailsView control placed inside another UpdatePanel. The DetailsView control will display all the records of Employees table and will allow you to add/update/delete them. The following figure shows a sample run of the web form.

Notice that if the UpdatePanel refreshes when the DetailsView is in edit mode the cursor disappears from the current textbox. This is expected (see our discussion earlier) and you may adjust the Timer interval to suit your scenario.

 

Posted On :	19 Aug 2007
Current Rating :
Rate This Article :	5 - Excellent 4 - Very Good 3 - Good 2 - Average 1 - Poor

About the Author

Bipin Joshi Bipin Joshi is the proprietor of BinaryIntellect Consulting where he conducts premier training programs on .NET technologies.