1 create table ActiveUsers
2 ( UserID varchar(50) not null,
3 IP varchar(50) not null,
4 LastHit datetime not null,
5 LastUrl varchar(256) not null,
6 constraint PK_ActiveSessions
7 primary key clustered ( UserID ) )
8 go
9
10
11 alter proc Hit_Add (@Url varchar(256), @UserID varchar(50),@IP varchar(50))
12 as
13
14 if (@UserID = '域名\系统管理员账号')
15 return
16
17 insert into Hits values
18 (@Url, @UserID,@IP,getdate())
19
20 delete ActiveUsers where UserID = @UserID or
21 datediff(mi, LastHit, getdate()) > 30 -- minutes
22
23 insert into ActiveUsers values
24 (@UserID, @IP,getdate(),@Url)
25
26
27 go
28
29
30 create proc ActiveUsers_Get
31 as
32 select UserID, LastHit, LastUrl,
33 datediff(mi, LastHit, getdate()) Age
34 from ActiveUsers
35 go 这样你会发现在你的数据库的可编程性里会多了2个存储过程
一个是Hit_Add一个是ActiveUsers_Get
这样其实整个过程就完成了。在hits表里存的就是点击改站点的所有用户的数据情况,其中信息由 用户名,ip地址和登录时间。我们可以通过不同的select语句来获得当月当日或者总的 用户登录数
1 select count(*) num from [UserOnline].[dbo].[Hits]
2 where datediff(day,timestamp,getdate())=0
上面的代码就是获得当天的用户登录数的语句
而在ActiveUsers_Get里你也许也会发现几条用户的数据,但是其实那并不是我要的真正的activeusers的列表。
我们需要建立一个显示当前用户在线列表的控件:
前台代码:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Data.SqlClient;
using Microsoft.SharePoint.Utilities;
using Ultrapower.Portal.Business;
namespace Ultrapower.Portal.UserControls
{
public partial class UserOnLine : System.Web.UI.UserControl
{
private void dowithgrd(string keyword, string name)
{
BoundField sendTimeField = new BoundField();
sendTimeField.DataField = keyword;
sendTimeField.HeaderText = name;
GridOnline.Columns.Add(sendTimeField);
}
private DataSet ds = new DataSet();
private DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
UserOnlineCollection useronlineclt = new UserOnlineCollection();
string sqlcmd = "ActiveUsers_Get";
ds = useronlineclt.GetActiveUserTable(sqlcmd, CommandType.StoredProcedure);
GridOnline.Columns.Clear();
dowithgrd("Username", "名称");
dowithgrd("UserID", "登录名");
dowithgrd("IP", "登录IP");
dowithgrd("LastHit", "登录时间");
GridOnline.AutoGenerateColumns = false;
dt = ds.Tables[0];
dt.Columns.Add("Username");
for (int i = 0; i < dt.Rows.Count; i++)
{