Hit Counter for Logging Users with ASP.net and SQL Server when you don't have access to Windows Server IIS Log Files from Shared/Free Hosting
T-SQL Table for persistent storage and Stored Procedure to save visitor info
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Log](
LogID int not null identity(1,1) CONSTRAINT PK_Log PRIMARY KEY ([LogID]) ,
[Created] [datetime] NOT NULL ,
[Page] [varchar](255) NOT NULL,
[UserAgent] [varchar](255) NULL,
[IP] [varchar](15) NULL, --IPv4 only
[Referrer] [varchar](max) NULL,
[LangCult] [varchar](5) NULL
) ON [PRIMARY]
GO
ALTER PROCEDURE LogCreate
@Created smalldatetime,
@Page varchar(255) = null ,
@UserAgent varchar(255) = null,
@IP varchar(15) = null,
@Referrer varchar(max) = null,
@LangCult varchar(5) = null
AS
BEGIN
INSERT INTO my_Log
(Created, Page, UserAgent, IP, Referrer, [LangCult])
VALUES
(@Created, @Page, @UserAgent, @IP, @Referrer, @LangCult)
RETURN 0
END
GO
C# This code is called by other pages using
<%@ import namespace="System.Web"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
public void Page_Load(object sender, EventArgs e)
{
// to encrypt a connection string in web.config: aspnet_regiis -pe "connectionStrings" -app "/MyWebsite" -prov "DataProtectionConfigurationProvider"
string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["myConnectionStringfromWebConfig"].ConnectionString;
if (strConnString != null)
{
using (SqlConnection cn = new SqlConnection(strConnString))
{
using (SqlCommand cmd = new SqlCommand("LogCreate", cn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Created", SqlDbType.SmallDateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Page", SqlDbType.VarChar).Value = this.Request.Url.AbsolutePath; //Request.CurrentExecutionFilePath)
cmd.Parameters.Add("@UserAgent", SqlDbType.VarChar).Value = this.Request.UserAgent;
cmd.Parameters.Add("@IP", SqlDbType.VarChar).Value = HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"];
string sReferrer = string.Empty;
if (Request.UrlReferrer != null) sReferrer = Request.UrlReferrer.ToString();
cmd.Parameters.Add("@Referrer", SqlDbType.VarChar).Value = sReferrer;
string sUserLanguage = string.Empty;
if (Request.UserLanguages[0] != null) sUserLanguage = Request.UserLanguages[0];
cmd.Parameters.Add("@LangCult", SqlDbType.VarChar).Value = sUserLanguage;
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
}
This'll get you started to view the log. There's no filtering so if your site experiences a DDOS attack, each page hit will be recorded, making your SQL Server MDF file grow rapidly.
<%@ import namespace="System.Web"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
public void Page_Load(object sender, EventArgs e)
{
// to encrypt a connection string in web.config: aspnet_regiis -pe "connectionStrings" -app "/MyWebsite" -prov "DataProtectionConfigurationProvider"
string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["myConnectionStringfromWebConfig"].ConnectionString;
if (strConnString != null)
{
using (SqlConnection cn = new SqlConnection(strConnString))
{
using (SqlCommand cmd = new SqlCommand("LogCreate", cn))
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT DISTINCT ISNULL([LangCult],'') AS [LangCult], [IP], [UserAgent], ISNULL([Referrer],'') AS [Referrer] FROM [ezpl8_Log]";
// SqlDataReader is fast-forward, read only
SqlDataReader myReader = cmd.ExecuteReader();
if (myReader.Read())
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<table style=\"font-size:{0}pt;font-family:arial;\">", "8");
do
{
while (myReader.Read())
sb.AppendFormat("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>", myReader.GetString(0), myReader.GetString(1), myReader.GetString(2), myReader.GetString(3));
} while (myReader.NextResult());
sb.AppendFormat("</table>");
Response.Write(sb.ToString()); ;
}
}
}
}
}
Posts
