Atlanta Custom Software Development 

   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

UserControl to display Site Visitors By Country in bar graph.

Total Hit ( 9012)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


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 To store IP to Country Mapping
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 To Lookup Country Name from 2 Character Country Code
CREATE TABLE CountryInfo
(
   Country varchar(100) NULL ,
   Country_name varchar(100) NULL
)
GO

--//Create Table To Store Visitor Session Log,
--//Using this table we will calculate Visitors By Country
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.

Click here to copy the following block
Select distinct Country, Country_name into CountryInfo from GeoIPCountryWhois

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

Click here to copy the following block
SELECT country FROM GeoIPCountryWhois WHERE 404232216 BETWEEN begin_num AND end_num

Here we used the formula (1) to compute the IP Number based on 24.24.24.24

Click here to copy the following block
404232216 = 16777216*24 + 65536*24 + 256*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
--Example: Select dbo.GetCountryFromIP('12.14.12.22')
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
   --// w.x.y.z
   
   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 @tmpip,@ipnum
   select top 1 @country=country from dbo.GeoIPCountryWhois where @ipnum between begin_num and end_num
--    select @country as Country

   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)
    --print 'Insert...'
    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)
   
--//Return New SesionId    
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)
  ' Fires when the session is started
  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.

Click here to copy the following block
Dim myConnectionStr="Database=test;uid=sa;trusted_connection=true"
Dim cnn As New SqlConnection(myConnectionStr)

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
  'Put user code to initialize the page here
  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

Click here to copy the following block
<link href="MyCSSFile.css" type="text/css" rel="stylesheet">

Now Just place your user control on Test page and run the project.

Happy Programming!!!


Audit, Notify, Deploy and Manage SSIS
Download the Free 30-day Trial Version...Learn More About This Product...

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 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  Blogs

© 2008 BinaryWorld LLC. All rights reserved.