using Bro.Common.Helper; using Bro.Common.Interface; using Bro.M135.Common; using MySql.Data.MySqlClient; using Newtonsoft.Json; using Sunny.UI; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using Windows.Globalization.PhoneNumberFormatting; using static System.Windows.Forms.VisualStyles.VisualStyleElement.ListView; namespace Bro.M141.Process { public class M141Process_Mysql { string connStrAll = "server=localhost;port=3306;user id=root;password=123456;database=BroDB;charset=utf8"; string connStrLocal = "server=localhost;port=3306;user id=root;password=123456;database=BroDB;charset=utf8"; #region 基础操作 public void IniDBIP(string IP) { connStrAll = $"server={IP};port=3306;user id=root;password=123456;database=BroDB;charset=utf8"; } public void Operatoremysql(string sql, string connStr)//操作 { MySqlConnection con = new MySqlConnection(connStr); try { if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); MySqlCommand coon = new MySqlCommand(sql, con); coon.ExecuteNonQuery(); } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库操作异常 {sql} {e.ToString()}"); } con.Close(); } public DataTable selectdt(string str, string connStr)//查询表格 { MySqlConnection con = new MySqlConnection(connStr); DataTable dt = new DataTable(); try { if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); MySqlDataAdapter da = new MySqlDataAdapter(str, con); da.Fill(dt); } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询异常 {str} {e.ToString()}"); } con.Close(); return dt; } public static string selectmysql(string str, string connStr)//查询 { MySqlConnection con = new MySqlConnection(connStr); MySqlCommand sqlCmd = new MySqlCommand(); sqlCmd.Connection = con; sqlCmd.CommandText = str; string strr = ""; try { if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); strr = sqlCmd.ExecuteScalar().ToString(); } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询数据异常 {str} {e.ToString()}"); } con.Close(); return strr; } #endregion public void NewProduct(ProductModel pro) { try { //保留数据库最新的1000条数据 //string destr = "delete from forlocal where id not in (select id from (select id from forlocal order BY id desc limit 1000) as subquery)"; //Operatoremysql(destr, connStrAll); string str1 = "select id from forlocal order by id desc limit 1"; string index = selectmysql(str1, connStrLocal); string str2 = $"delete from forlocal where id < ({index}-1000)"; Operatoremysql(str2, connStrLocal); string mysqlstr = $"insert into forlocal (basketcode,pid,plcnum,sn,data,time) values('{pro.BasketCode}','{pro.PID}','{pro.SEQUENCE}','{pro.SN}','{JsonConvert.SerializeObject(pro)}','{DateTime.Now.ToString("yyyyMMddHHmmss")}')"; Operatoremysql(mysqlstr, connStrLocal); } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal插入数据异常 {e.ToString()}"); } } public void UpdateProduct(ProductModel pro) { try { string mysqlstr = $"update forlocal set data='{JsonConvert.SerializeObject(pro)}' where plcnum='{pro.SEQUENCE}'"; Operatoremysql(mysqlstr, connStrLocal); } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal更新数据异常 {e.ToString()}"); } } public void NewForAll(ProductModel pro, string devicestr) { Task.Run(() => { try { //保留数据库最新的1000条数据 //string destr = "delete from forall where id not in (select id from (select id from forall order BY id desc limit 1000) as subquery)"; //Operatoremysql(destr, connStrAll); string str1 = "select id from forall order by id desc limit 1"; string index = selectmysql(str1, connStrAll); string str2 = $"delete from forall where id < ({index}-1000)"; Operatoremysql(str2, connStrAll); string mysqlstr = $"insert into forall (basketcode,device,plcnum,sn,data,time) values('{pro.BasketCode}','{devicestr}','{pro.SEQUENCE}','{pro.SN}','{JsonConvert.SerializeObject(pro)}','{DateTime.Now.ToString("yyyyMMddHHmmss")}')"; Operatoremysql(mysqlstr, connStrAll); } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forall插入数据异常 {e.ToString()}"); } }); } public ProductModel GetProduct(string plcnum) { try { string mysqlstr = $"select data from forlocal where plcnum='{plcnum}' order by id desc limit 1"; string str = selectmysql(mysqlstr, connStrLocal); if (string.IsNullOrEmpty(str)) { return null; } else { ProductModel p = JsonConvert.DeserializeObject(str, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All }); return p; } } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal更新数据异常 {e.ToString()}"); } return null; } public string Getbasketcode(string plcnum) { try { string mysqlstr = $"select basketcode from forall where plcnum='{plcnum}' && device='S2' order by id desc limit 1"; string str = selectmysql(mysqlstr, connStrAll); if (!string.IsNullOrEmpty(str)) { return str; } } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal更新数据异常 {e.ToString()}"); } return "NoRead"; } public List GetProductList(string plcnum) { try { string mysqlstr = $"select data from forall where plcnum='{plcnum}'"; var dt = selectdt(mysqlstr, connStrAll); if (dt == null) { return null; } else { List plist = new List(); for (int i = 0; i < dt.Rows.Count; i++) { ProductModel p = JsonConvert.DeserializeObject(dt.Rows[i][0].ToString(), new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All }); //JsonConvert.DeserializeObject(_configBackupStr, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All }); plist.Add(p); } return plist; } } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forall数据异常 {e.ToString()}"); } return null; } } }