|
|
|
|
This article will show you how to build a Reusable User Control that can display site visitors by country. I have explained this in a few simple and easy steps. It is assumed that the reader has basic knowledge of ASP.net, SQL Server and Stored Procedures.
To build this user control we will go through the following steps:
1) Create tables to store IP to Country mapping and Visitors detail.
2) Load IP->Country mapping Records into the table using DTS.
3) Create a function to Convert IP string from w.x.y.z to IP Number so that we can search into the table by just one number instead of complex w.x.y.z IP string.
4) Create a trigger on SessionLog table so on each new session we can map Visitor IP to Country and Update Country field in SessionLog table.
5) Create a Stored Procedure to get actual output to display Visitors By Country
6) Write logic to insert new record in Session_Start event
7) Build a UserControl which can display Visitors By country in Bar Graph using DataList Control.
8) Test output of UserControl
Step-1 : Create tables to store IP to Country mapping and Visitors detail
You can create a separate database to store IP->Country mapping or add new tables to your existing database. If you have just created the database you will also need to create a few Tables. Run the following script in query analyzer and make sure that you select proper database in the database dropdown in query analyzer. |
 Click here to copy the following block |
CREATE TABLE GeoIPCountryWhois
(
begin_ip varchar (50) NULL,
end_ip varchar(50) NULL,
begin_num bigint NULL,
end_num bigint NULL,
country varchar(50) NULL,
country_name varchar(100) NULL
)
GO
CREATE TABLE CountryInfo
(
Country varchar(100) NULL ,
Country_name varchar(100) NULL
)
GO
CREATE TABLE SessionLog
(
SessionId int IDENTITY(1, 1) NOT NULL ,
RemoteIP varchar(50) NULL ,
CDate datetime NULL CONSTRAINT DF_SessionLog_EntryDate DEFAULT (getdate()),
Country varchar(50) NULL ,
CONSTRAINT PK_VisitorLog PRIMARY KEY CLUSTERED (SessionId)
)
GO |
Step-2 : Loading IP->Country mapping Records into the table using DTS.
This is very important step to analyze your site traffic by country. We need to store some sort of information which can tell us that IP w.x.y.z belongs to this country. There is a free database available for IPtoCountry lookup and which is provided by MaxMind. You can download this database in either Binary Format or CSV (Comma Separate Values) format. CSV format is very easy to load into SQL server so we will use CSV format only for this project. Download GeoIPCountryWhois.csv file from the following URL.
http://www.maxmind.com/app/geoip_country
To load CSV file's data go through the following steps.
1) Open Enterprise manager
2) Expand your server (in my case it was localhost)
3) Click and Expand the "Databases" folder
4) Click and Expand your database where you want to store IP to Country information
5) Right Click on the table icon.
6) Select All tasks->import data from popup menu
7) Now it will display DTS import data wizard screen shown as below
8) Click Next. On the next screen select "Text File" as your datasource and specify the location of your downloaded CSV file.
9) Click Next. On the next screen only change "Row Delimiter" to {LF} which indicates that CSV file uses line feed character to separate records.
10) Click Next. This screen will show some sample records.
11) Click Next. On this screen specify your destination database where you have created GeoIPCountryWhois table to store IP to country mapping records.
12) Click Next. On this screen Make sure that you select your newly Created table in Destination Column otherwise wizard will create new table automatically for you and the you cal modify table design lateron.
13) Click Next ....Next ... and Finish to Run your Data Import Wizard.
14) Once you complete data loading. Run following SQL Statement to create separate table for Country information. |
Step-3 : Build a function to Convert IP string from w.x.y.z to IP Number
Beginning IP Number and Ending IP Number are calculated as follows:
ipnum = 16777216*w + 65536*x + 256*y + z (1)
where
IP Address = w.x.y.z
The reverse of this formula is
w = int ( ipnum / 16777216 ) % 256;
x = int ( ipnum / 65536 ) % 256;
y = int ( ipnum / 256 ) % 256;
z = int ( ipnum ) % 256;
Where % is the mod operator.
It is useful to have the IP Number if you are performing IP Address lookups using a database. For example the following queries will find the country based on IP Address 24.24.24.24:
SQL Query |
| Here we used the formula (1) to compute the IP Number based on 24.24.24.24 |
Very important thing is to convert w.x.y.z into ipnumber so we can search in to the database.
To convert IP to Number I have created a function (Functions are Only supported in SQL2k). Run the following script in query analyzer and make sure that you select proper database in the database dropdown in query analyzer. |
| Click here to copy the following block |
CREATE FUNCTION GetCountryFromIP(@IP varchar(20))
RETURNS varchar(50)
AS
BEGIN
declare @ipnum bigint
declare @tmpIp varchar(20)
declare @w bigint
declare @x bigint
declare @y bigint
declare @z bigint
declare @country varchar(50)
set @tmpIp=@ip
set @w=cast(left(@ip, charindex('.',@ip,1)-1) as int)
set @ip=Right(@ip,len(@ip)-charindex('.',@ip,1))
set @x=cast(left(@ip, charindex('.',@ip,1)-1) as int)
set @ip=Right(@ip,len(@ip)-charindex('.',@ip,1))
set @y=cast(left(@ip, charindex('.',@ip,1)-1) as int)
set @ip=Right(@ip,len(@ip)-charindex('.',@ip,1))
set @z=cast(@ip as int)
set @ipnum = 16777216* @w + 65536* @x + 256*@y + @z
select top 1 @country=country from dbo.GeoIPCountryWhois where @ipnum between begin_num and end_num
RETURN IsNULL(@Country,'UNKNOWN')
END
GO |
Step-4 : Build a trigger and stored proc for SessionLog table
Trigger is required to update country field in sessionlog table so that we dont have to lookup the GeoIPCountryWhois table each time to find visitor country from his/her IP. Run the following script in query analyzer and make sure that you select proper database in the database dropdown in query analyzer. |
| Click here to copy the following block | Create trigger tr_UpdateCountry on dbo.SessionLog for insert,update as
if exists(select * from inserted)
UPDATE SessionLog
Set Country=dbo.GetCountryFromIP(i.RemoteIp)
From inserted i
WHERE i.SessionId=SessionLog.SessionId
GO |
| Now we will create a Stored Procedure to Insert data into SessionLog table. |
| Click here to copy the following block | Create Proc usp_AddNewSessionEntry
(@RemoteIP varchar(50),
@MemberId int=NULL
)
as
SET NOCOUNT ON
INSERT INTO dbo.SessionLog(RemoteIP,MemberId)
VALUES(@RemoteIP,@MemberId)
select @@IDENTITY as SessionId
GO |
Step-5 : Create a Stored Procedure to get actual output to display Visitors By Country
Now finally we need to create a stored procedure which will show you Visitors by country. Run the following script in query analyzer and make sure that you select proper database in the database dropdown in query analyzer. |
| Click here to copy the following block | create proc usp_VisitorsByCountry
as
declare @totalsitevisitors int
select top 100 PERCENT count(*) TotalCountryVisitors
,s.country
,c.country_name
into #tmp
from SessionLog s
join CountryInfo c
on c.Country=s.country
group by s.country,c.country_name
order by count(*) desc
select @totalsitevisitors=sum(TotalCountryVisitors) from #tmp
select t.*,TotalSiteVisitors=@totalsitevisitors from #tmp t
GO |
Now we are done with SQL Server side....
Step-6 : Write logic in Session_Start event to find visitor's IP
To store Visitor's IP we nned to write some code in Session_Start Event |
| Click here to copy the following block | Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
Dim SessionId As Integer
SessionId = AddNewSessionEntry(Request.ServerVariables("REMOTE_ADDR"))
Session("SessionId") = SessionId
End Sub |
| In session_Start Event we call AddNewSessionEntry function to Insert New Session Entry into the database. AddNewSessionEntry function gets Connection string from Web.Config file using ConfigurationSettings.AppSettings("ConnectionString"). If you dont have connection string stored in Web.Config the you can use your connection string as below. |
| Here is the AddNewSessionEntry function. |
| Click here to copy the following block | Function AddNewSessionEntry(ByVal RemoteIP As String, ByVal MemberId As String) As Integer
Try
Dim cnn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As SqlCommand
Dim SessionId As Integer
cnn.Open()
cmd = New SqlCommand
cmd.Connection = cnn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "usp_AddNewSessionEntry"
cmd.Parameters.Add(New SqlClient.SqlParameter("@RemoteIP", RemoteIP))
SessionId = cmd.ExecuteScalar()
cnn.Close()
AddNewSessionEntry = SessionId
Catch ex As Exception
AddNewSessionEntry = 0
End Try
End Function |
Step-7 : Build a UserControl to display output in BarGraph
Write following code in UserControl code section |
| Click here to copy the following block | Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
dl.DataSource = GetVisitorsByCountry()
Page.DataBind()
End If
End Sub
Protected Function GetWidth(ByVal TotalCountryVisitors As Integer, ByVal TotalSiteVisitors As Integer) As String
Dim w
w = Math.Round(((TotalCountryVisitors / TotalSiteVisitors) * 100), 2)
GetWidth = "<span class=barGraph style=""width:" & w & "%;""></span>"
End Function
Public Function GetVisitorsByCountry() As DataTable
Dim dt As New DataTable
Dim da As New SqlDataAdapter("usp_VisitorsByCountry", ConfigurationSettings.AppSettings("ConnectionString"))
da.Fill(dt)
GetVisitorsByCountry = dt
End Function |
| Write folling HTML code in ascx file. I have not included following Usercontrol tag because it might vary according to your project nameSpace |
| Click here to copy the following block | | <%@ Control Language="vb" AutoEventWireup="false" Codebehind="VisitorsByCountry.ascx.vb" Inherits="Main.VisitorsByCountry" TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %> |
| Click here to copy the following block | <asp:datalist id="dl" Width="100%" EnableViewState="False" BorderWidth="1px" BorderColor="White"
ShowFooter="False" runat="server">
<HeaderTemplate>
<asp:Label id="Label1" runat="server" CssClass="Heading">Site Visitors By Country</asp:Label>
</HeaderTemplate>
<ItemStyle Width="100%"></ItemStyle>
<ItemTemplate>
<asp:Label
id="l1"
runat="server"
Width="170"
CssClass="NormalMsg">
<%# Container.DataItem("country_name") %> (<%# Container.DataItem("TotalCountryVisitors") %>)
</asp:Label>
<asp:Literal id=lblTotalVisitorsGraph runat="server"
Text='<%# GetWidth(Container.DataItem("TotalCountryVisitors"),Container.DataItem("TotalSiteVisitors"))%>'>
</asp:Literal>
</ItemTemplate>
<HeaderStyle BackColor="Navy"></HeaderStyle>
</asp:datalist> |
Step-8 : Test output of UserControl
To test our control we have to create a test aspx page and a CSS file. I have used following CSS classes for this control. you can just add these classes to your existing CSS file and call that file in your ASPX page or just create new CSS file for the test page. |
| Click here to copy the following block | .barGraph
{
border-right: blue 1px solid;
border-top: blue 1px solid;
font-size: 6pt;
border-left: blue 1px solid;
border-bottom: blue 1px solid;
background-color: red;
}
.NormalMsg
{
font-weight: bold;
font-size: 10pt;
color: blue;
font-family: tahoma;
text-decoration: none;
}
.Heading
{
font-weight: bold;
font-size: 10pt;
color: white;
font-family: Verdana;
} |
Now build test.aspx
Add following entry for CSS file in section |
Now Just place your user control on Test page and run the project.
Happy Programming!!! |
|
|
|
|
|
|
Submitted By :
Nayan Patel
(Member Since : 5/26/2004 12:23:06 PM)
|
 |
|
|
Job Description :
He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting. |
|
View all (893) submissions by this author
(Birth Date : 7/14/1981 ) |
|
|