DBHelper.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385
  1. using CommonStorage.Model;
  2. using MySql.Data.MySqlClient;
  3. using Newtonsoft.Json;
  4. using System;
  5. using System.Collections.Generic;
  6. using System.Data;
  7. using System.IO;
  8. using System.Linq;
  9. using System.Runtime.ConstrainedExecution;
  10. using System.Runtime.InteropServices.ComTypes;
  11. using System.Runtime.Remoting.Metadata.W3cXsd2001;
  12. using System.Text;
  13. using static Google.Protobuf.Reflection.FieldOptions.Types;
  14. namespace CommonStorage
  15. {
  16. public class DBHelper
  17. {
  18. static string connectionString = "server=localhost;port=3306;database=jf200;uid=mysqltest;password=test1234";
  19. public static bool InsertIntoLog(string msgType, Model.LevelEnum msgLevel, string msg)
  20. {
  21. using (MySqlConnection connection = new MySqlConnection(connectionString))
  22. {
  23. connection.Open();
  24. // MySqlTransaction transaction = connection.BeginTransaction();
  25. try
  26. {
  27. using (var context = new MyDb(connection, false))
  28. {
  29. // Passing an existing transaction to the context
  30. // context.Database.UseTransaction(transaction);
  31. context.logs.Add(new Model.Log() { MsgType = msgType, MsgLevel = msgLevel, Message = msg, CreateTime = DateTime.Now });
  32. context.SaveChanges();
  33. }
  34. // transaction.Commit();
  35. }
  36. catch(Exception ex)
  37. {
  38. throw ex;
  39. }
  40. }
  41. return true;
  42. }//end InsertIntoLog
  43. public static User QueryUser(string userName)
  44. {
  45. using (MySqlConnection connection = new MySqlConnection(connectionString))
  46. {
  47. connection.Open();
  48. try
  49. {
  50. using (var context = new MyDb(connection, false))
  51. {
  52. var user = context.users.FirstOrDefault(x => x.UserName == userName);
  53. return user;
  54. }
  55. }
  56. catch (Exception ex)
  57. {
  58. throw ex;
  59. }
  60. }
  61. }
  62. /// <summary>
  63. /// 查询配置的测试项目
  64. /// </summary>
  65. /// <param name="ProductCode">产品代号</param>
  66. /// <returns></returns>
  67. public static List<List<ValueItem>> QueryTestPar(string ProductCode)
  68. {
  69. List<List<ValueItem>> TpsDatas = new List<List<ValueItem>>();
  70. using (MySqlConnection connection = new MySqlConnection(connectionString))
  71. {
  72. connection.Open();
  73. try
  74. {
  75. using (var context = new MyDb(connection, false))
  76. {
  77. var config = context.tpsConfigs.FirstOrDefault(x => x.ProductCode == ProductCode);
  78. if(string.IsNullOrEmpty(config.Values))
  79. {
  80. TpsDatas = JsonConvert.DeserializeObject<List<List<ValueItem>>>(config.Values);
  81. }
  82. }
  83. }
  84. catch (Exception ex)
  85. {
  86. throw ex;
  87. }
  88. }
  89. return TpsDatas;
  90. }
  91. /// <summary>
  92. /// 新增或者更新产品测试配置数据
  93. /// </summary>
  94. /// <param name="ProductCode"></param>
  95. /// <returns></returns>
  96. public static bool AddOrUpdateTestPar(string ProductCode, List<List<ValueItem>> dataItems)
  97. {
  98. using (MySqlConnection connection = new MySqlConnection(connectionString))
  99. {
  100. connection.Open();
  101. try
  102. {
  103. using (var context = new MyDb(connection, false))
  104. {
  105. var config = context.tpsConfigs.FirstOrDefault(x => x.ProductCode == ProductCode);
  106. if(config == null)
  107. {
  108. TpsConfig tpsConfig = new TpsConfig();
  109. tpsConfig.ProductCode = ProductCode;
  110. tpsConfig.TpsName = "JF200测试程序";
  111. tpsConfig.TestItem = "空载-负载-短路";
  112. tpsConfig.TestProject = "常温测试";
  113. if(dataItems != null)
  114. {
  115. tpsConfig.Values = JsonConvert.SerializeObject(dataItems);
  116. }
  117. context.tpsConfigs.Add(tpsConfig);
  118. }
  119. else
  120. {
  121. config.Values = "";
  122. if (dataItems != null)
  123. {
  124. config.Values = JsonConvert.SerializeObject(dataItems);
  125. }
  126. }
  127. context.SaveChanges();
  128. }
  129. }
  130. catch (Exception ex)
  131. {
  132. throw ex;
  133. }
  134. }
  135. return true;
  136. }
  137. //删除产品测试配置项
  138. public static bool DeleteTestPar(string ProductCode)
  139. {
  140. using (MySqlConnection connection = new MySqlConnection(connectionString))
  141. {
  142. connection.Open();
  143. try
  144. {
  145. using (var context = new MyDb(connection, false))
  146. {
  147. var config = context.tpsConfigs.FirstOrDefault(x => x.ProductCode == ProductCode);
  148. if (config != null)
  149. {
  150. context.tpsConfigs.Remove(config);
  151. }
  152. context.SaveChanges();
  153. }
  154. }
  155. catch (Exception ex)
  156. {
  157. throw ex;
  158. }
  159. }
  160. return true;
  161. }
  162. /// <summary>
  163. /// 根据筛选信息查询测试数据,不含Values,Values是表内容,以加快查询返回
  164. /// </summary>
  165. /// <param name="Serial">产品编号</param>
  166. /// <param name="ProductCode">产品代号</param>
  167. /// <param name="BarCode">产品批次</param>
  168. /// <param name="startTime">开始时间</param>
  169. /// <param name="stopTime">结束数据</param>
  170. /// <returns></returns>
  171. public static List<TestDataBase> QueryTestData(string Serial, string ProductCode, string BarCode, DateTime startTime, DateTime stopTime)
  172. {
  173. List<TestDataBase> datas = new List<TestDataBase>();
  174. using (MySqlConnection connection = new MySqlConnection(connectionString))
  175. {
  176. connection.Open();
  177. try
  178. {
  179. using (var context = new MyDb(connection, false))
  180. {
  181. //不含Values,Values是表内容,以加快查询返回
  182. //var query = from x in context.testDatas
  183. // where ((string.IsNullOrEmpty(Serial) || x.Serial.Contains(Serial)) &&
  184. // (string.IsNullOrEmpty(ProductCode) || x.ProductCode.Equals(ProductCode)) &&
  185. // (string.IsNullOrEmpty(BarCode) || x.BarCode.Equals(BarCode)) &&
  186. // x.TestTime >= startTime &&
  187. // x.TestTime <= stopTime)
  188. // select new TestDataBase
  189. // {
  190. // ID = x.ID,
  191. // BarCode = x.BarCode,
  192. // DataName = x.DataName,
  193. // TestTime = x.TestTime,
  194. // ProductCode = x.ProductCode,
  195. // ProductName = x.ProductName,
  196. // ProductNum = x.ProductNum,
  197. // Serial = x.Serial,
  198. // IsPass = x.IsPass,
  199. // Tester = x.Tester,
  200. // TestProject = x.TestProject
  201. // };
  202. var query = from x in context.testDatas
  203. where ((string.IsNullOrEmpty(Serial) || x.Serial.Contains(Serial)) &&
  204. (x.ProductCode.Equals(ProductCode)) &&
  205. (string.IsNullOrEmpty(BarCode) || x.BarCode.Equals(BarCode)) &&
  206. x.TestTime >= startTime &&
  207. x.TestTime <= stopTime)
  208. select new TestDataBase
  209. {
  210. ID = x.ID,
  211. BarCode = x.BarCode,
  212. DataName = x.DataName,
  213. TestTime = x.TestTime,
  214. ProductCode = x.ProductCode,
  215. ProductName = x.ProductName,
  216. ProductNum = x.ProductNum,
  217. Serial = x.Serial,
  218. IsPass = x.IsPass,
  219. Tester = x.Tester,
  220. TestProject = x.TestProject
  221. };
  222. datas = query.ToList();
  223. }
  224. }
  225. catch (Exception ex)
  226. {
  227. throw ex;
  228. }
  229. }
  230. return datas;
  231. }
  232. /// <summary>
  233. /// 查询测试数据,ID是QueryTestData查出来的数据ID
  234. /// 用于数据查询,带Values
  235. /// </summary>
  236. /// <param name="ID">数据ID</param>
  237. /// <returns></returns>
  238. public static TestData QueryTestData(long ID)
  239. {
  240. using (MySqlConnection connection = new MySqlConnection(connectionString))
  241. {
  242. connection.Open();
  243. try
  244. {
  245. using (var context = new MyDb(connection, false))
  246. {
  247. var query = context.testDatas.FirstOrDefault(x => x.ID == ID);
  248. return query;
  249. }
  250. }
  251. catch (Exception ex)
  252. {
  253. throw ex;
  254. }
  255. }
  256. return null;
  257. }
  258. /// <summary>
  259. /// 根据ID删除指定的测试数据
  260. /// </summary>
  261. /// <param name="ID"></param>
  262. /// <returns></returns>
  263. public static bool DeleteTestData(long ID)
  264. {
  265. List<long> ids = new List<long>();
  266. ids.Add(ID);
  267. return DeleteTestDatas(ids);
  268. }
  269. /// <summary>
  270. /// 根据ID删除指定的测试数据,可以一次删除多条
  271. /// </summary>
  272. /// <param name="ids"></param>
  273. public static bool DeleteTestDatas(List<long> ids)
  274. {
  275. if(ids == null)
  276. return false;
  277. using (MySqlConnection connection = new MySqlConnection(connectionString))
  278. {
  279. connection.Open();
  280. try
  281. {
  282. using (var context = new MyDb(connection, false))
  283. {
  284. foreach (var id in ids)
  285. {
  286. var query = context.testDatas.FirstOrDefault(x => x.ID == id);
  287. if(query != null)
  288. {
  289. context.testDatas.Remove(query);
  290. }
  291. }
  292. context.SaveChanges();
  293. }
  294. return true;
  295. }
  296. catch (Exception ex)
  297. {
  298. throw ex;
  299. }
  300. }
  301. }
  302. /// <summary>
  303. /// 添加测试项
  304. /// </summary>
  305. /// <param name="testData"></param>
  306. /// <returns></returns>
  307. public static bool AddTestData(TestData testData)
  308. {
  309. using (MySqlConnection connection = new MySqlConnection(connectionString))
  310. {
  311. connection.Open();
  312. try
  313. {
  314. using (var context = new MyDb(connection, false))
  315. {
  316. context.testDatas.Add(testData);
  317. context.SaveChanges();
  318. return true;
  319. }
  320. }
  321. catch (Exception ex)
  322. {
  323. throw ex;
  324. }
  325. }
  326. return true;
  327. }
  328. /// <summary>
  329. /// 查询日志
  330. /// </summary>
  331. /// <param name="workstation">工位号</param>
  332. /// <param name="startTime">开始时间</param>
  333. /// <param name="stopTime">截止时间</param>
  334. /// <returns></returns>
  335. public static List<Log> QueryLog(string workstation, DateTime startTime, DateTime stopTime)
  336. {
  337. List<Log> logItems = new List<Log>();
  338. using (MySqlConnection connection = new MySqlConnection(connectionString))
  339. {
  340. connection.Open();
  341. try
  342. {
  343. using (var context = new MyDb(connection, false))
  344. {
  345. //不含Values,Values是表内容,以加快查询返回
  346. var query = from x in context.logs
  347. where ((string.IsNullOrEmpty(workstation) || x.Workstation.Equals(workstation)) &&
  348. x.CreateTime >= startTime &&
  349. x.CreateTime <= stopTime) select x;
  350. logItems = query.ToList();
  351. }
  352. }
  353. catch (Exception ex)
  354. {
  355. throw ex;
  356. }
  357. }
  358. return logItems;
  359. }
  360. }//class DBHelper
  361. }