C# 三层架构(主板序列号绑4网卡项目)

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();
        }
    }
}

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值