Amr-CommonStorage/DBHelper.cs

using CommonStorage.Model;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.ConstrainedExecution;
using System.Runtime.InteropServices.ComTypes;
using System.Runtime.Remoting.Metadata.W3cXsd2001;
using System.Text;
using static Google.Protobuf.Reflection.FieldOptions.Types;

namespace CommonStorage
{
    public class DBHelper
    {
        static string connectionString = "server=localhost;port=3306;database=jf200;uid=mysqltest;password=test1234";
        public static bool InsertIntoLog(string msgType, Model.LevelEnum msgLevel, string msg)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                //  MySqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        // Passing an existing transaction to the context
                        //         context.Database.UseTransaction(transaction);
                        context.logs.Add(new Model.Log() { MsgType = msgType, MsgLevel = msgLevel, Message = msg, CreateTime = DateTime.Now });
                        context.SaveChanges();
                    }
                    //    transaction.Commit();
                }
                catch(Exception ex)
                {
                    throw ex;
                }
            }

            return true;
        }//end InsertIntoLog

        public static User QueryUser(string userName)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        var user = context.users.FirstOrDefault(x => x.UserName == userName);
                        return user;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 查询配置的测试项目
        /// </summary>
        /// <param name="ProductCode">产品代号</param>
        /// <returns></returns>
        public static List<List<ValueItem>> QueryTestPar(string ProductCode)
        {
            List<List<ValueItem>> TpsDatas = new List<List<ValueItem>>();

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        var config = context.tpsConfigs.FirstOrDefault(x => x.ProductCode == ProductCode);
                        if(string.IsNullOrEmpty(config.Values))
                        {
                            TpsDatas = JsonConvert.DeserializeObject<List<List<ValueItem>>>(config.Values);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return TpsDatas;
        }

        /// <summary>
        /// 新增或者更新产品测试配置数据
        /// </summary>
        /// <param name="ProductCode"></param>
        /// <returns></returns>
        public static bool AddOrUpdateTestPar(string ProductCode, List<List<ValueItem>> dataItems)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        var config = context.tpsConfigs.FirstOrDefault(x => x.ProductCode == ProductCode);
                        if(config == null)
                        {
                            TpsConfig tpsConfig = new TpsConfig();
                            tpsConfig.ProductCode = ProductCode;
                            tpsConfig.TpsName = "JF200测试程序";
                            tpsConfig.TestItem = "空载-负载-短路";
                            tpsConfig.TestProject = "常温测试";
                            if(dataItems != null)
                            {
                                tpsConfig.Values = JsonConvert.SerializeObject(dataItems);
                            }
                            context.tpsConfigs.Add(tpsConfig);
                        }
                        else
                        {
                            config.Values = "";
                            if (dataItems != null)
                            {
                                config.Values = JsonConvert.SerializeObject(dataItems);
                            }
                        }
                        context.SaveChanges();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return true;
        }

        //删除产品测试配置项
        public static bool DeleteTestPar(string ProductCode)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        var config = context.tpsConfigs.FirstOrDefault(x => x.ProductCode == ProductCode);
                        if (config != null)
                        {
                            context.tpsConfigs.Remove(config);
                        }
                        
                        context.SaveChanges();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return true;
        }

        /// <summary>
        /// 根据筛选信息查询测试数据,不含Values,Values是表内容，以加快查询返回
        /// </summary>
        /// <param name="Serial">产品编号</param>
        /// <param name="ProductCode">产品代号</param>
        /// <param name="BarCode">产品批次</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="stopTime">结束数据</param>
        /// <returns></returns>
        public static List<TestDataBase> QueryTestData(string Serial, string ProductCode, string BarCode, DateTime startTime, DateTime stopTime)
        {
            List<TestDataBase> datas = new List<TestDataBase>();
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        //不含Values,Values是表内容，以加快查询返回
                        //var query = from x in context.testDatas
                        //            where ((string.IsNullOrEmpty(Serial) || x.Serial.Contains(Serial)) &&
                        //                            (string.IsNullOrEmpty(ProductCode) || x.ProductCode.Equals(ProductCode)) &&
                        //                            (string.IsNullOrEmpty(BarCode) || x.BarCode.Equals(BarCode)) &&
                        //                            x.TestTime >= startTime &&
                        //                            x.TestTime <= stopTime)
                        //            select new TestDataBase
                        //            {
                        //                ID = x.ID,
                        //                BarCode = x.BarCode,
                        //                DataName = x.DataName,
                        //                TestTime = x.TestTime,
                        //                ProductCode = x.ProductCode,
                        //                ProductName = x.ProductName,
                        //                ProductNum = x.ProductNum,
                        //                Serial = x.Serial,
                        //                IsPass = x.IsPass,
                        //                Tester = x.Tester,
                        //                TestProject = x.TestProject
                        //            };

                        var query = from x in context.testDatas
                                    where ((string.IsNullOrEmpty(Serial) || x.Serial.Contains(Serial)) &&
                                                    (x.ProductCode.Equals(ProductCode)) &&
                                                    (string.IsNullOrEmpty(BarCode) || x.BarCode.Equals(BarCode)) &&
                                                    x.TestTime >= startTime &&
                                                    x.TestTime <= stopTime)
                                    select new TestDataBase
                                    {
                                        ID = x.ID,
                                        BarCode = x.BarCode,
                                        DataName = x.DataName,
                                        TestTime = x.TestTime,
                                        ProductCode = x.ProductCode,
                                        ProductName = x.ProductName,
                                        ProductNum = x.ProductNum,
                                        Serial = x.Serial,
                                        IsPass = x.IsPass,
                                        Tester = x.Tester,
                                        TestProject = x.TestProject
                                    };
                        datas = query.ToList();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return datas;
        }

        /// <summary>
        /// 查询测试数据，ID是QueryTestData查出来的数据ID
        /// 用于数据查询,带Values
        /// </summary>
        /// <param name="ID">数据ID</param>
        /// <returns></returns>
        public static TestData QueryTestData(long ID)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        var query = context.testDatas.FirstOrDefault(x => x.ID == ID);
                        return query;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return null;
        }

        /// <summary>
        /// 根据ID删除指定的测试数据
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static bool DeleteTestData(long ID)
        {
            List<long> ids = new List<long>();
            ids.Add(ID);
            return DeleteTestDatas(ids);
        }

        /// <summary>
        /// 根据ID删除指定的测试数据，可以一次删除多条
        /// </summary>
        /// <param name="ids"></param>
        public static bool DeleteTestDatas(List<long> ids)
        {
            if(ids == null)
                return false;

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        foreach (var id in ids)
                        {
                            var query = context.testDatas.FirstOrDefault(x => x.ID == id);
                            if(query != null)
                            {
                                context.testDatas.Remove(query);
                            }
                        }

                        context.SaveChanges();
                    }

                    return true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 添加测试项
        /// </summary>
        /// <param name="testData"></param>
        /// <returns></returns>
        public static bool AddTestData(TestData testData)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        context.testDatas.Add(testData);
                        context.SaveChanges();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return true;
        }

        /// <summary>
        /// 查询日志
        /// </summary>
        /// <param name="workstation">工位号</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="stopTime">截止时间</param>
        /// <returns></returns>
        public static List<Log> QueryLog(string workstation, DateTime startTime, DateTime stopTime)
        {
            List<Log> logItems = new List<Log>();
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (var context = new MyDb(connection, false))
                    {
                        //不含Values,Values是表内容，以加快查询返回
                        var query = from x in context.logs
                                    where ((string.IsNullOrEmpty(workstation) || x.Workstation.Equals(workstation)) &&
                                                    x.CreateTime >= startTime &&
                                                    x.CreateTime <= stopTime) select x;

                        logItems = query.ToList();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return logItems;
        }

    }//class DBHelper
}