Real Code
When a new user starts their session by visiting a page on our webserver, ASP.Net raises the Session_Start event, which is when we connect to the SQL Server database and add their details to the tbl_OnlineUsers table. A timing issue does arise, however, because we also want to record what page the user starts on in the tbl_OnlineUsers_VisitedURLs table, which requires that their particular Session ID have a row in the tbl_OnlineUsers table. Because the Application_Request event is raised before Session_Start, this would cause an error and cripple the whole application.
The solution is to use the Application_PreRequestHandlerExecute event to add the URL and timestamp to their table, because that event gets fired after Session_Start.
The third event used here is Session_End, where we remove the Session details from tbl_OnlineUsers. Sadly, ASP.Net doesn't seem to raise this event when every session times out, so we schedule an SQL task to run at a certain time every day to clean out the old records.
global.asax.cs
using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class Global : System.Web.HttpApplication
{
protected void Session_Start(Object sender, EventArgs e)
{
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["YourConnString"]);
SqlCommand myCommand = new SqlCommand("sp_AddOnlineUser", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@IP_Address", Request.ServerVariables["REMOTE_ADDR"]);
myCommand.Parameters.Add("@Session_ID", Session.SessionID.ToString());
myCommand.Parameters.Add("@user_Agent", Request.ServerVariables["HTTP_USER_AGENT"]);
myCommand.Parameters.Add("@Referer", Request.ServerVariables["HTTP_REFERER"]);
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
catch (Exception Err)
{
// do nothing but we really don't want to throw an
// exception here because that would likely crash the whole app
}
}
protected void Application_PreRequestHandlerExecute(Object sender, EventArgs e)
{
SqlConnection myConnection = new SqlConnection (ConfigurationSettings.AppSettings["YourConnString"]);
SqlCommand myCommand = new SqlCommand ("sp_AddVisitedURL", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@URL", Request.ServerVariables["SCRIPT_NAME"]);
myCommand.Parameters.Add("@Session_ID", Session.SessionID.ToString());
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
catch (Exception Err)
{
// do nothing but we really don't want to throw an
// exception here because that would likely crash the whole app
}
}
protected void Session_End(Object sender, EventArgs e)
{
SqlConnection myConnection = new SqlConnection (ConfigurationSettings.AppSettings["YourConnString"]);
SqlCommand myCommand = new SqlCommand ("sp_DeleteOnlineUser", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@Session_ID", Session.SessionID.ToString());
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
catch (Exception Err)
{
// do nothing but we really don't want to throw an
// exception here because that would likely crash the whole app
}
}
}
(click here for this page in VB.Net)
The above code puts the user's details and clickstream into the database, but exists in the global.asax code behind file and doesn't allow us to actually display that data. The following pages solve that problem:
OnlineUsers.aspx
<%@ Page Language="C#" Src="OnlineUsers.cs" Inherits="ViewOnlineUsers" %>
<html>
<head>
<title>
Online Users
</title>
</head>
<body>
<h1>Online Users</h1>
<ASP:Repeater id="rptOnlineUsers" runat="server">
<ItemTemplate>
User from <%# DataBinder.Eval(Container.DataItem, "IP_Address") %>
<br />
Session started at <%# DataBinder.Eval(Container.DataItem, "TimeStamp") %>
<br />
Using <%# DataBinder.Eval(Container.DataItem, "User_Agent") %>
<br />
<%# DataBinder.Eval(Container.DataItem, "Referer") %>
<br />
<a href="OnlineUsers_Clickstream.aspx?Session_ID=<%# DataBinder.Eval(Container.DataItem, "Session_ID") %>">Click
here</a> to view the user's clickstream.
<br />
</ItemTemplate>
</ASP:Repeater>
</body>
</html>
OnlineUsers.cs
using System;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class ViewOnlineUsers : System.Web.UI.Page
{
public Repeater rptOnlineUsers;
protected void Page_Load(Object sender, EventArgs e)
{
GetOnlineUsers();
}
private void GetOnlineUsers()
{
try
{
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["yourConnString"]);
SqlDataAdapter myCommand = new SqlDataAdapter("sp_GetOnlineUsers", myConnection);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "tbl_OnlineUsers");
rptOnlineUsers.DataSource = myDataSet.Tables["tbl_OnlineUsers"].DefaultView;
rptOnlineUsers.DataBind();
}
catch (Exception Err)
{
}
}
}
Click here for this page in VB.Net, or here to see it in action
This calls the stored procedure "sp_GetOnlineUsers" which returns every row of the tbl_OnlineUsers table, and then a Repeater is used to display the data. A hyperlink is provided to view the clickstream of every user, which leads to the OnlineUsers_Clickstream.aspx page and sends that particular user's Session_ID in a querystring.
OnlineUsers_Clickstream.aspx
<%@ Page Language="C#" Src="OnlineUsers.cs" Inherits="ViewOnlineUsers" %>
<html>
<head>
<title>
Online Users
</title>
</head>
<body>
<h1>Online Users</h1>
<ASP:Repeater id="rptOnlineUsers" runat="server">
<ItemTemplate>
User from <%# DataBinder.Eval(Container.DataItem, "IP_Address") %>
<br />
Session started at <%# DataBinder.Eval(Container.DataItem, "TimeStamp") %>
<br />
Using <%# DataBinder.Eval(Container.DataItem, "User_Agent") %>
<br />
<%# DataBinder.Eval(Container.DataItem, "Referer") %>
<br />
<a href="OnlineUsers_Clickstream.aspx?Session_ID=<%# DataBinder.Eval(Container.DataItem, "Session_ID") %>">Click
here</a> to view the user's clickstream.
<br />
</ItemTemplate>
</ASP:Repeater>
</body>
</html>
OnlineUsers_Clickstream.cs
using System;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class ViewOnlineUsers_ByClickstream : System.Web.UI.Page
{
public Repeater rptOnlineUsers;
public Repeater rptOnlineUsers_ClickStream;
protected void Page_Load(Object sender, EventArgs e)
{
GetOnlineUsers();
GetClickStream();
}
private void GetOnlineUsers()
{
try
{
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["YourConnString"]);
SqlDataAdapter myCommand = new SqlDataAdapter("sp_GetOnlineUsers_ByUser " + Request.QueryString["Session_ID"], myConnection);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "tbl_OnlineUsers");
rptOnlineUsers.DataSource = myDataSet.Tables["tbl_OnlineUsers"].DefaultView;
rptOnlineUsers.DataBind();
}
catch (Exception Err)
{
}
}
private void GetClickStream()
{
try
{
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["YourConnString"]);
string sSQL = "sp_GetOnlineUsers_Clickstream " + Request.QueryString["Session_ID"];
SqlDataAdapter myCommand = new SqlDataAdapter(sSQL, myConnection);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "tbl_OnlineUsers");
rptOnlineUsers_ClickStream.DataSource = myDataSet.Tables["tbl_OnlineUsers"].DefaultView;
rptOnlineUsers_ClickStream.DataBind();
}
catch (Exception Err)
{
}
}
}
Click here for this page in VB.Net.
