首页手记记录用户登录日志

记录用户登录日志

标签：

SQL Server

我们可以在专案中，实现用户登录日志记录。简单实现方法，如下：

在数据中建立一张表，存储一些相关的信息。

代码

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LoginLog](
    [LoginLogId] [int] IDENTITY(1,1) NOT NULL,
    [UsersId] [int] NOT NULL,
    [IP] [nvarchar](20) NOT NULL,
    [ComputerName] [nvarchar](50) NULL,
    [LoginTime] [datetime] NOT NULL,
    [ActiveX] [bit] NOT NULL,
    [Cookies] [bit] NOT NULL,
    [CSS] [bit] NOT NULL,
    [Languages] [nvarchar](30) NULL,
    [Platform] [nvarchar](30) NULL,
    [UserAgent] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
    [LoginLogId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[LoginLog] ADD  DEFAULT (getdate()) FOR [LoginTime]
GO

ALTER TABLE [dbo].[LoginLog] ADD  DEFAULT ((0)) FOR [ActiveX]
GO

ALTER TABLE [dbo].[LoginLog] ADD  DEFAULT ((0)) FOR [Cookies]
GO

ALTER TABLE [dbo].[LoginLog] ADD  DEFAULT ((0)) FOR [CSS]
GO

然后，再建立一个存储过程，对表进行插入动作。

代码

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_LoginLog_Insert]
(
    @UsersId INT,
    @IP NVARCHAR(20),
    @ComputerName NVARCHAR(50),
    @ActiveX BIT,
    @Cookies BIT,
    @CSS BIT,
    @Languages NVARCHAR(30),
    @Platform NVARCHAR(30),
    @UserAgent NVARCHAR(300)
)
AS
    BEGIN TRANSACTION
    INSERT INTO [dbo].[LoginLog]([UsersId],[IP],[ComputerName],[LoginTime],[ActiveX],[Cookies],[CSS],[Languages],[Platform],[UserAgent]) VALUES (@UsersId,@IP,@ComputerName,GETDATE(),@ActiveX,@Cookies,@CSS,@Languages,@Platform,@UserAgent)

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION
    END
        COMMIT TRANSACTION

在aspx.cs中，当验证用户登录成功之后，再执行下面这个方法，把相关资讯存入LoginLog表中

代码

private void RecordLoginLog(string usersId)
    {
        string ip = objInsusNetUtility.GetClientIpAddress();
        string computerName = Dns.GetHostEntry(ip).HostName;
        bool activeX = Request.Browser.ActiveXControls;
        bool cookie = Request.Browser.Cookies;
        bool cSS = Request.Browser.SupportsCss;
        string language = Request.UserLanguages[0];
        string platform = Request.Browser.Platform;
        string userAgent =  Request.UserAgent ;
        objLoginLog.Insert(usersId, ip, computerName,activeX,cookie,cSS,language,platform,userAgent);
    }

上面LoginLog类别中，它有一个INSERT()方法

代码

public void Insert(string usersId, string ip, string computerName, bool activeX, bool cookies, bool css, string languages, string platform, string useragent)
        {
            Parameter[] parameter = {
                                        new Parameter("@UsersId",SqlDbType.Int,4,ConvertData.ToInt(usersId)),
                                        new Parameter("@IP",SqlDbType.NVarChar,20,ip),
                                        new Parameter("@ComputerName",SqlDbType.NVarChar,50,computerName),
                                        new Parameter("@ActiveX",SqlDbType.Bit,1,activeX),
                                        new Parameter("@Cookies",SqlDbType.Bit,1,cookies),
                                        new Parameter("@CSS",SqlDbType.Bit,1,css),
                                        new Parameter("@Languages",SqlDbType.NVarChar,30,languages),
                                        new Parameter("@Platform",SqlDbType.NVarChar,30,platform),
                                        new Parameter("@UserAgent",SqlDbType.NVarChar,300,useragent),
                                    };
            objBusinessBase.ExecuteProcedure("usp_LoginLog_Insert", parameter);
        }