Real天下

昨天突然有一个冲动，想写一个计数器来统计一下我们RadioMe网络电台的访问量，好知己知彼，做好下一步的计划。没有怎么参考人家那些计数器是怎么设计的，按照自己的思路就乱来了一通。现在把代码贴出来，请高手指点一二吧！

一、数据表

CREATE TABLE [tbCounter] (
 [CounterID] [int] IDENTITY (1, 1) NOT NULL ,
 [IP] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [Count] [int] NOT NULL DEFAULT (1),
 [URL] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [SetupTime] [datetime] NULL  DEFAULT (getdate()),
 [ModifyTime] [datetime] NULL
) ON [PRIMARY]

二、计数器

添加一个UserControl（*.ascx） 在Page_Load事件下写入以下代码

if(!IsPostBack)
   {
    SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConStr"]);

     //获取当前页面的地址          
    string url = Request.Url.ToString().Replace("http://","").Replace("www.","");
    url = url.Remove(0,url.IndexOf("/"));
    //获取客户端IP地址
    string ip = Request.UserHostAddress;
    string sql = "select CounterID from tbCounter where IP='http://www.blogchinese.com/"+ip+"' and SetupTime >getdate()-1";

    SqlCommand myCommand = new SqlCommand(sql,myConnection);
    myConnection.Open();
    object obj = myCommand.ExecuteScalar();
    string cid = "";
    //如果24小时内此IP未访问过网站，则插入新记录，否则就在原记录上累加
    if (obj != null )
    {
     cid = obj.ToString();
    }
    if(cid == "" )
    {
     sql = "insert into tbCounter(IP,URL,SetupTime,ModifyTime)Values('"+ip+"','"+url+"',getdate(),getdate())";
     myCommand = new SqlCommand(sql,myConnection);
     myCommand.ExecuteNonQuery();
    }
    else
    {     
     sql = "update tbCounter set URL='"+url+"',Count=Count+1,ModifyTime=getdate() where CounterID="+cid;
     myCommand = new SqlCommand(sql,myConnection);
     myCommand.ExecuteNonQuery();
    }
    myConnection.Close();
   }

三、统计数据

//统计函数
string StatNum(string sql)
  {
   try
   {
    SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConStr"]);
    SqlCommand myCommand = new SqlCommand(sql,myConnection);
    myConnection.Open();
    return myCommand.ExecuteScalar().ToString();
   }
   catch
   {
    return "0";
   }
  }

  public string result;//全局变量绑定在页面上
  private void Page_Load(object sender, System.EventArgs e)
  {
   // 在此处放置用户代码以初始化页面
   if(!IsPostBack)
   {
    result = "<strong>计数器</strong><hr><br>";

    //本日访问人次（24小时内）
    string sql = "select count(*) from tbCounter where SetupTime >getdate()-1";
    string num = StatNum(sql);
    result += "<br>本日访问人次（24小时内）："+num;

    //本日点击数（24小时内）
    sql = "select sum([Count]) from tbCounter where SetupTime >getdate()-1";
    num = StatNum(sql);
    result += "<br>本日点击数（24小时内）："+num;

    //总人访问次（2006-1-17 17:00开始）
    sql = "select count(*) from tbCounter";
    num = StatNum(sql);
    result += "<br>总访问人次（2006-1-17 17:00开始）："+num;

    //总点击数（2006-1-17 17:00开始）
    sql = "select sum([Count]) from tbCounter";
    num = StatNum(sql);
    result += "<br>总点击数（2006-1-17 17:00开始）："+num;

    DataBind();
   }
  }