1.DB源代码
USE E_MainBoard
GO
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='MB_FourYardsUnity')
DROP TABLE MB_FourYardsUnity
GO
CREATE TABLE MB_FourYardsUnity
(
Serial varchar(12) PRIMARY KEY, -------------序号
Mac_Address1 varchar(12) not null,------------Mac1
Mac_Address2 varchar(12) not null,------------Mac2
Mac_Address3 varchar(12) not null,------------Mac3
IsEnabled bit not null------------------------是否有效
)
GO
USE E_MainBoard
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='Proc_QueryAndUpdateFourYardsUnityData')
DROP PROC Proc_QueryAndUpdateFourYardsUnityData
GO
CREATE PROC Proc_QueryAndUpdateFourYardsUnityData
@Serial varchar(12),
@Mac_Address1 varchar(12),
@Mac_Address2 varchar(12),
@Mac_Address3 varchar(12),
@rs int output
AS
---开启事务
--begin transaction
--rollback transaction --回滚事务
--commit transaction ----提交事务
BEGIN
if exists(select * from MB_FourYardsUnity where Serial=@Serial and IsEnabled=1)
begin
if exists(select * from MB_FourYardsUnity where Serial=@Serial and
Mac_Address1=@Mac_Address1 and Mac_Address2=@Mac_Address2 and Mac_Address3=@Mac_Address3)
begin
update MB_FourYardsUnity set IsEnabled=0 where Serial=@Serial
if(@@error<1)
set @rs=0
end
else
begin
set @rs=4
end
end
else
begin
set @rs=5
end
return @rs
END
USE E_MainBoard
IF Exists(select * from sys.objects where name='Proc_InsertFourYardsUnityData')
DROP PROC Proc_InsertFourYardsUnityData
GO
CREATE PROC Proc_InsertFourYardsUnityData
@Serial varchar(12),
@Mac_Address1 varchar(12),
@Mac_Address2 varchar(12),
@Mac_Address3 varchar(12),
@rs int output
AS
BEGIN
INSERT MB_FourYardsUnity VALUES(@Serial,@Mac_Address1,@Mac_Address2,@Mac_Address3,1)
set @rs=@@ERROR
return @rs
END
GO
2.代码
2.1.MB_FourYardsUnity.Model
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MB_FourYardsUnity.Model
{
public class MB_FourYardsUnityEntity
{
/// <summary>
/// 序号
/// </summary>
public string Serial { get; set; }
/// <summary>
/// Mac1
/// </summary>
public string Mac_Address1 { get; set; }
/// <summary>
/// Mac2
/// </summary>
public string Mac_Address2 { get; set; }
/// <summary>
/// Mac3
/// </summary>
public string Mac_Address3 { get; set; }
}
}
2.2.MB_FourYardsUnity.DAL
using MB_FourYardsUnity.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
namespace MB_FourYardsUnity.DAL
{
public class TextProcessService
{
private string FileNamem = string.Empty;
private string FileNames = string.Empty;
public List<MB_FourYardsUnityEntity> DsData = null;
public TextProcessService(string FileNamem,string FileNames)
{
this.FileNamem = FileNamem;
this.FileNames = FileNames;
ReadAllTextData();
}
/// <summary>
/// 生成数据集合类
/// </summary>
/// <returns></returns>
private bool ReadAllTextData()
{
List <string>Tempm= new List<string>(ReadText(this.FileNamem));//网卡数据
List<string> Temps = new List<string>(ReadText(this.FileNames));//序列号数据
DsData = new List<MB_FourYardsUnityEntity>();
int i = 0;
foreach (string ts in Temps)
{
DsData.Add(new MB_FourYardsUnityEntity()
{
Serial = ts,
Mac_Address1 = Tempm[i],
Mac_Address2=Tempm[i+1],
Mac_Address3=Tempm[i+2]
});
i += 3;
}
return DsData.Count>0;
}
/// <summary>
/// 读取文本
/// </summary>
/// <param name="FileName">文件名</param>
/// <returns></returns>
private List<string> ReadText(string FileName)
{
List<string> Temp = new List<string>();
using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read))
{
using (StreamReader sr = new StreamReader(fs, Encoding.Default))
{
string str = string.Empty;
while ((str = sr.ReadLine()) != null)
{
Temp.Add(str.Trim());
}
}
}
return Temp;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.NetworkInformation;
using System.Text;
using System.Threading.Tasks;
namespace MB_FourYardsUnity.DAL
{
public class NetWorkConnectionService
{
public Boolean TestNetConnectity(string strIP)//判断网段是否可连接
{
// Windows L2TP VPN和非Windows VPN使用ping VPN服务端的方式获取是否可以连通
Ping pingSender = new Ping();
PingOptions options = new PingOptions();
// Use the default Ttl value which is 128,
// but change the fragmentation behavior.
options.DontFragment = true;
// Create a buffer of 32 bytes of data to be transmitted.
string data = "testtesttesttesttesttesttesttest";
byte[] buffer = Encoding.ASCII.GetBytes(data);
int timeout = 120;
PingReply reply = pingSender.Send(strIP, timeout, buffer, options);
return (reply.Status == IPStatus.Success);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using Dapper;
using MB_FourYardsUnity.Model;
using System.Data.SqlClient;
namespace MB_FourYardsUnity.DAL
{
public class FYUDataBaseService
{
private string sqlcon = string.Empty;
public FYUDataBaseService()
{
NetWorkConnectionService netWorkConnectionService = new NetWorkConnectionService();
if(netWorkConnectionService.TestNetConnectity(ConfigurationManager.ConnectionStrings["IP1"].ConnectionString))
sqlcon = ConfigurationManager.ConnectionStrings["sqlcon"].ConnectionString;
else
sqlcon= ConfigurationManager.ConnectionStrings["sqlconi"].ConnectionString;
//Console.WriteLine(sqlcon);
}
/// <summary>
/// 查询与更新实体类
/// </summary>
/// <param name="mB_FourYardsUnityEntity"></param>
/// <returns></returns>
public int QueryingAndUpdateSourceData(MB_FourYardsUnityEntity mB_FourYardsUnityEntity,out string outerr)
{
outerr = null;
using (SqlConnection conn = new SqlConnection(this.sqlcon))
{
try
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@Serial", mB_FourYardsUnityEntity.Serial);
paras.Add("@Mac_Address1", mB_FourYardsUnityEntity.Mac_Address1);
paras.Add("@Mac_Address2", mB_FourYardsUnityEntity.Mac_Address2);
paras.Add("@Mac_Address3", mB_FourYardsUnityEntity.Mac_Address3);
paras.Add("@rs", 255, System.Data.DbType.Int32, System.Data.ParameterDirection.Output);//输出参数
conn.Query<int>("Proc_QueryAndUpdateFourYardsUnityData", paras, commandType: System.Data.CommandType.StoredProcedure);//存储过程
return paras.Get<int>("@rs");
}
catch (Exception ex)
{
outerr = ex.Message;
return 255;
}
}
}
/// <summary>
/// 上传源始数据
/// </summary>
/// <param name=""></param>
/// <returns></returns>
public int UploadingSourceData(List<MB_FourYardsUnityEntity>dt,out string outerr)
{
int result = 0;
outerr = null;
using (SqlConnection conn = new SqlConnection(this.sqlcon))
{
foreach (MB_FourYardsUnityEntity mB_FourYardsUnityEntity in dt)
{
try
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@Serial", mB_FourYardsUnityEntity.Serial);
paras.Add("@Mac_Address1", mB_FourYardsUnityEntity.Mac_Address1);
paras.Add("@Mac_Address2", mB_FourYardsUnityEntity.Mac_Address2);
paras.Add("@Mac_Address3", mB_FourYardsUnityEntity.Mac_Address3);
paras.Add("@rs", 255, System.Data.DbType.Int32, System.Data.ParameterDirection.Output);//输出参数
conn.Query<int>("Proc_InsertFourYardsUnityData", paras, commandType: System.Data.CommandType.StoredProcedure);//存储过程
result += paras.Get<int>("@rs");
}
catch (Exception ex)
{
outerr = ex.Message;
return 255;
}
}
}
return result;
}
}
}
2.3.MB_FourYardsUnity.BLL
using MB_FourYardsUnity.DAL;
using MB_FourYardsUnity.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MB_FourYardsUnity.BLL
{
public class MB_FourYardsUnityManager
{
/// <summary>
/// 上传数据源
/// </summary>
/// <param name="FileNamem"></param>
/// <param name="FileNames"></param>
/// <returns></returns>
public bool UploadingSourceData(string FileNamem, string FileNames,out string errout)
{
errout = null;
TextProcessService textProcessService = new TextProcessService(FileNamem,FileNames);
if (textProcessService.DsData.Count > 0)
{
FYUDataBaseService fYUDataBaseService = new FYUDataBaseService();
if (fYUDataBaseService.UploadingSourceData(textProcessService.DsData,out errout) == 0)
{
return true;
}
else
return false;
}
else
{
return false;
}
}
/// <summary>
/// 核对序号和MAC捆绑
/// </summary>
/// <param name="mB_FourYardsUnityEntity"></param>
/// <returns></returns>
public int CheckSerialAndMacBinding(MB_FourYardsUnityEntity mB_FourYardsUnityEntity,out string errout)
{
errout = null;
FYUDataBaseService fYUDataBaseService = new FYUDataBaseService();
return fYUDataBaseService.QueryingAndUpdateSourceData(mB_FourYardsUnityEntity,out errout);
}
}
}
3.UI层
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="sqlcon" connectionString="server=xxxx\SERVER2;database=E_MainBoard;pwd=adminsystem;uid=sa" />
<add name="sqlconi" connectionString="server=xxxx\SERVER2;database=E_MainBoard;pwd=adminsystem;uid=sa" />
<add name="IP1" connectionString="xxxx" />
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
</configuration>
using MB_FourYardsUnity.BLL;
using MB_FourYardsUnity.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Reflection;
using System.Net.NetworkInformation;
namespace MB_FourYardsUnity
{
public class Program
{
static int Main(string[] args)
{
Console.Clear();
if (args.Length > 0)
{
string errout;
if (args.Length == 3 && args[0].ToUpper() == @"/IUP")//上传源数据,args[1]=mac.txt,args[2]=序号.txt
{
MB_FourYardsUnityManager mB_FourYardsUnityManager = new MB_FourYardsUnityManager();
if (mB_FourYardsUnityManager.UploadingSourceData(args[1], args[2], out errout))
return ShowInfo(ConsoleColor.Green, "Upload Source Data Succeed!!", true);
else
return ShowInfo(ConsoleColor.Red, errout == null ? $"Read {args[1]} And {args[2]} File Data Err!!" : errout, false);
}
else if (args.Length == 1 && args[0].ToUpper() == @"/CHK")//核对绑定数据
{
MB_FourYardsUnityEntity mB_FourYardsUnityEntity = new MB_FourYardsUnityEntity()
{
Serial = InputText("Please Scan Serial Number"),
Mac_Address1 = InputText("Please Scan Mac Address1"),
Mac_Address2 = InputText("Please Scan Mac Address2"),
Mac_Address3 = InputText("Please Scan Mac Address3")
};
//判断集合类是否为空
//TextIsEmpty<MB_FourYardsUnityEntity>(mB_FourYardsUnityEntity);
MB_FourYardsUnityManager mB_FourYardsUnityManager = new MB_FourYardsUnityManager();
int i = mB_FourYardsUnityManager.CheckSerialAndMacBinding(mB_FourYardsUnityEntity, out errout);
if (i == 5)
return ShowInfo(ConsoleColor.Red, errout == null ? $"Check Serial:{mB_FourYardsUnityEntity.Serial} And State Fail!!" : errout, false);
else if (i == 4)
return ShowInfo(ConsoleColor.Red, errout == null ? $"Check Serial:{mB_FourYardsUnityEntity.Serial} And Mac1:{mB_FourYardsUnityEntity.Mac_Address1} or" +
$" Mac2:{mB_FourYardsUnityEntity.Mac_Address2} or Mac3:{mB_FourYardsUnityEntity.Mac_Address3} Fail!!" : errout, false);
else if (i > 0)
return ShowInfo(ConsoleColor.Red, errout == null ? $"Update Serial:{mB_FourYardsUnityEntity.Serial} State Fail!!" : errout, false);
else
return ShowInfo(ConsoleColor.Green, $"Check Serial:{mB_FourYardsUnityEntity.Serial} And Mac1:{mB_FourYardsUnityEntity.Mac_Address1} or" +
$" Mac2:{mB_FourYardsUnityEntity.Mac_Address2} or Mac3:{mB_FourYardsUnityEntity.Mac_Address3} Pass!!", true);
}
else if (args.Length == 4 && args[0].ToUpper() == @"/CHKAM")
{
MB_FourYardsUnityEntity mB_FourYardsUnityEntity = new MB_FourYardsUnityEntity()
{
Serial = InputText("Please Scan Serial Number"),
Mac_Address1 = ReadText(args[1]),
Mac_Address2 = ReadText(args[2]),
Mac_Address3 = ReadText(args[3]),
};
//判断集合类是否为空
//TextIsEmpty<MB_FourYardsUnityEntity>(mB_FourYardsUnityEntity);
MB_FourYardsUnityManager mB_FourYardsUnityManager = new MB_FourYardsUnityManager();
int i = mB_FourYardsUnityManager.CheckSerialAndMacBinding(mB_FourYardsUnityEntity, out errout);
if (i == 5)
return ShowInfo(ConsoleColor.Red, errout == null ? $"Check Serial:{mB_FourYardsUnityEntity.Serial} And State Fail!!" : errout, false);
else if (i == 4)
return ShowInfo(ConsoleColor.Red, errout == null ? $"Check Serial:{mB_FourYardsUnityEntity.Serial} And Mac1:{mB_FourYardsUnityEntity.Mac_Address1} or" +
$" Mac2:{mB_FourYardsUnityEntity.Mac_Address2} or Mac3:{mB_FourYardsUnityEntity.Mac_Address3} Fail!!" : errout, false);
else if (i > 0)
return ShowInfo(ConsoleColor.Red, errout == null ? $"Update Serial:{mB_FourYardsUnityEntity.Serial} State Fail!!" : errout, false);
else
return ShowInfo(ConsoleColor.Green, $"Check Serial:{mB_FourYardsUnityEntity.Serial} And Mac1:{mB_FourYardsUnityEntity.Mac_Address1} or" +
$" Mac2:{mB_FourYardsUnityEntity.Mac_Address2} or Mac3:{mB_FourYardsUnityEntity.Mac_Address3} Pass!!", true);
}
}
Help();
return 1;
}
/// <summary>
/// 显示信息并退出
/// </summary>
/// <param name="color"></param>
/// <param name="ShowInfo"></param>
/// <param name="IsOk"></param>
public static int ShowInfo(ConsoleColor color, string ShowInfo,bool IsOk)
{
Console.ForegroundColor = color;
Console.WriteLine(ShowInfo);
Console.ResetColor();
if (IsOk)
return 0;
else
return 1;
}
/// <summary>
/// 判断数据类是否为空
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="DS"></param>
public static void TextIsEmpty<T>(T DS)
{
Type type = typeof(T);//类型的声明
T t = (T)Activator.CreateInstance(type);//创建实例
//通过返射去遍历属性
foreach (PropertyInfo info in type.GetProperties())
{
string temp = string.Empty;
info.GetValue(temp);
if (temp == null && temp == string.Empty)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine($"Read {info.Name} Item Values Is Empty!!");
Console.ResetColor();
System.Environment.Exit(1);
}
}
}
/// <summary>
/// 读取文本文件
/// </summary>
/// <param name="FileName"></param>
/// <returns></returns>
public static string ReadText(string FileName)
{
string TextStr = null;
using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read))
{
using (StreamReader sr = new StreamReader(fs, Encoding.Default))
{
TextStr = sr.ReadLine();
}
}
return TextStr;
}
/// <summary>
/// 扫描录入信息
/// </summary>
/// <param name="PromptInfo">提示信息</param>
/// <returns></returns>
public static string InputText(string PromptInfo)
{
Console.Clear();
Console.WriteLine("=============================================================================");
Console.ForegroundColor = ConsoleColor.Green;
Console.Write($"{PromptInfo}:");
Console.ResetColor();
string str = Console.ReadLine();
return str;
}
/// <summary>
/// 帮助信息
/// </summary>
public static void Help()
{
/*
* /IUP [FileName1] [FileName2] ==>上传数据源 FileName1:Mac文件,FileName2:序号文件
* /CHK ==>扫描录入序列号+Mac1、Mac2、Mac3比较
* /CHKAM [Mac1FileName] [Mac2FileName] [Mac3FileNmae] ==>录入扫描序号自动读取设置MAC文件并进行比较
*/
Console.Clear();//清屏
Console.WriteLine("Program Version.V1.00 Create Date:2021-05-22");
Console.WriteLine("Property in Copyright Ferex Shenbo.");
Console.WriteLine("-----------------------------------------------------------------------------------");
Console.ForegroundColor = ConsoleColor.Cyan;
Console.WriteLine(@"/IUP [FileName1] [FileName2] Uploading Data Source.");
Console.WriteLine(@"/CHK Check Serial And MacAddress1 And MacAddress2 And MacAddress3.");
Console.WriteLine(@"/CHKAM [Mac1FileName] [Mac2FileName] [Mac3FileNmae] Check Serial And Auto Read MacAddress1 And MacAddress2 And MacAddress3.");
Console.ResetColor();
}
}
}