Program.cs
3.46 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
using FreeSql;
using HHECS.Tool.Utils;
using HHECS.DAQShared.Models;
using HHECS.BllModel;
internal class Program
{
/// <summary>
/// 数据库连接字符串
/// </summary>
private static readonly string connectionString = "Data Source=172.16.29.88;Initial Catalog=IoTUAT;Persist Security Info=True;User ID=sa;Password=1qaz!QAZ;Trust Server Certificate=True";
private static void Main(string[] args)
{
try
{
//文件目录，将需要导入的Excel文件放至此目录
var path = $@"{Directory.GetCurrentDirectory()}\\Template";
var root = new DirectoryInfo(path);
var files = root.GetFiles("*.xlsx");
foreach (var file in files)
{
var result = NpoiHelper.GetEquipmentData(file.FullName);
if (!result.Success)
{
Console.WriteLine($"读取Excel数据异常：{result.Msg}");
return;
}
var handleResult = DataHandle(result.Data);
if (!handleResult.Success)
{
Console.WriteLine($"导入数据异常：{handleResult.Msg}");
return;
}
}
}
catch (Exception ex)
{
Console.WriteLine($"程序异常：{ex.Message}");
}
}
private static BllResult DataHandle(List<Equipment> equipments)
{
try
{
var freeSql = new FreeSqlBuilder().UseConnectionString(DataType.SqlServer, connectionString)
.UseAutoSyncStructure(false).Build();
var equipmentTypeIds = equipments.Select(x => x.EquipmentTypeId).Distinct().ToList();
var equipmentTypes = freeSql.Queryable<EquipmentType>().Where(x => equipmentTypeIds.Contains(x.Id)).ToList();
//事务操作
freeSql.Transaction(() =>
{
foreach (var equipment in equipments)
{
var oldEquipment = freeSql.Queryable<Equipment>().Where(x => x.Code == equipment.Code).First();
if (oldEquipment == null)
{
equipment.Created = DateTime.Now;
equipment.Id = (int)freeSql.Insert(equipment).ExecuteIdentity();
}
else
{
equipment.Id = oldEquipment.Id;
}
var oldEquipmentPropCodes = freeSql.Queryable<EquipmentProp>().Where(x => x.EquipmentId == equipment.Id).ToList(x => x.Code);
var temps = new List<EquipmentProp>();
foreach (var equipmentProp in equipment.EquipmentProps)
{
if (oldEquipmentPropCodes.Contains(equipmentProp.Code))
{
continue;
}
equipmentProp.EquipmentId = equipment.Id;
equipmentProp.Created = DateTime.Now;
temps.Add(equipmentProp);
}
var total = freeSql.Insert(temps).ExecuteAffrows();
Console.WriteLine($"新增设备{equipment.Code}，属性：{total}");
}
});
return BllResultFactory.Success();
}
catch (Exception ex)
{
return BllResultFactory.Error(ex.Message);
}
}
}