计算机MAC识别是否可以登录

最新推荐文章于 2024-11-06 11:17:40 发布

kingwebo'sZone

最新推荐文章于 2024-11-06 11:17:40 发布

阅读量90

点赞数

分类专栏：关于程序文章标签：数据库 sql java

本文链接：https://blog.csdn.net/weixin_40029679/article/details/129198194

版权

关于程序专栏收录该内容

422 篇文章 12 订阅

订阅专栏

文章展示了创建一个名为RegisterPC的数据库表，用于存储计算机注册信息，包括MAC地址、最后登录时间和创建日期等。客户端程序使用Dapper查询和更新表中的数据，检查MAC地址是否存在并更新最后登录时间。此外，还包含一个存储过程RegeitPC，用于插入或更新PC网络数据。

摘要由CSDN通过智能技术生成

数据库结构：

 USE [HengliReportWord]
GO

/****** Object:  Table [dbo].[RegisterPC]    Script Date: 2023/2/24 12:09:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RegisterPC](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PcName] [nvarchar](100) NULL,
	[PcMac] [nvarchar](50) NULL,
	[Remark] [nvarchar](300) NULL,
	[ClientIp] [nvarchar](30) NULL,
	[LastLogin] [datetime] NULL,
	[CreateDate] [datetime] NULL
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册计算机名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RegisterPC', @level2type=N'COLUMN',@level2name=N'PcName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册计算机Mac' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RegisterPC', @level2type=N'COLUMN',@level2name=N'PcMac'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RegisterPC', @level2type=N'COLUMN',@level2name=N'Remark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后一次登录时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RegisterPC', @level2type=N'COLUMN',@level2name=N'LastLogin'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RegisterPC', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO

//客户端程序DAL的方法

/// <summary>
        /// 判断是否存在于本机的mac列表，如果存在则是把最后一次访问时间更新，如果不存在则返回空值
        /// </summary>
        /// <param name="MacArr"></param>
        /// <returns></returns>
        public ResultMsg IsInMacTabel(string[] MacArr)
        {
            string AllStr = string.Join(",",MacArr);
            ResultMsg msg = new ResultMsg();
            RegisterPC one = new RegisterPC();
            string sql = string.Format("SELECT *   FROM RegisterPC where pcmac in ({0})", AllStr);
            one = DapperDbHelper.Query<RegisterPC>(sql).FirstOrDefault();
            if (one == null)
            {
                msg.Success = false;
                msg.ReturnObject = null;
            }
            else
            {
                msg.Success = true;
                msg.ReturnObject = one;
                one.LastLogin = DateTime.Now;
                InsertAndUpdate(one);//插入登录信息
            }
            return msg;
        }

//客户端程序获取MAC的方法

/// <summary>
        /// 判断是否存在于本机的mac列表，如果存在则是把最后一次访问时间更新，如果不存在则返回空值
        /// </summary>
        /// <param name="MacArr"></param>
        /// <returns></returns>
        public ResultMsg IsInMacTabel(string[] MacArr)
        {
            string AllStr = string.Join(",",MacArr);
            ResultMsg msg = new ResultMsg();
            RegisterPC one = new RegisterPC();
            string sql = string.Format("SELECT *   FROM RegisterPC where pcmac in ({0})", AllStr);
            one = DapperDbHelper.Query<RegisterPC>(sql).FirstOrDefault();
            if (one == null)
            {
                msg.Success = false;
                msg.ReturnObject = null;
            }
            else
            {
                msg.Success = true;
                msg.ReturnObject = one;
                one.LastLogin = DateTime.Now;
                InsertAndUpdate(one);//插入登录信息
            }
            return msg;
        }

存储过程获取相关pC网络数据

USE [HengliReportWord]
GO
/****** Object:  StoredProcedure [dbo].[RegeitPC]    Script Date: 2023/2/24 12:13:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[RegeitPC]
@PCName varchar(100),
@PCMac varchar(50),
@Remark varchar(300),
@LastLogin datetime,
@OutInt int output

as
begin
 declare @SetIp varchar(30) 
 select @SetIp =client_net_address   from sys.dm_exec_connections where Session_id=@@spid
 if exists (select * from RegisterPC where PcMac = @PCMac )
 begin
	set @OutInt = 0
	update RegisterPC set PcName = @PCName,Remark =@Remark,LastLogin =@LastLogin, ClientIp = @SetIp where PcMac = @PCMac
 end
 else
 begin
     set @OutInt = 1
     insert into RegisterPC(PcName,PcMac,Remark,LastLogin,CreateDate,ClientIp)   values(@PCName,@PCMac,@Remark,@LastLogin,GETDATE(),@SetIp)
 end
  select @OutInt
end

获取PC网络数据调用

 /// <summary>
        /// 插入数据或者更新
        /// </summary>
        /// <param name="modle"></param>
        /// <returns></returns>
        public ResultMsg InsertAndUpdate(RegisterPC modle)
        {
            var para = new DynamicParameters(); 
            para.Add("@PcName",modle.PcName,DbType.String);
            para.Add("@PcMac",modle.PcMac,DbType.String);
            para.Add("@Remark",modle.Remark,DbType.String); 
            para.Add("@LastLogin",modle.LastLogin,DbType.DateTime);
            para.Add("@OutInt",0,System.Data.DbType.Int32, ParameterDirection.Output);
            DapperDbHelper.Execute("RegeitPC",para,null,null,CommandType.StoredProcedure);
            msg.ReturnInt = para.Get<int>("@OutInt");
            return msg;
        }