// 添加新列 public void AddNewRow(CEntitySerialPort entity) { // 记录超过1000条,或者时间超过1分钟,就将当前的数据写入数据库 m_mutexDataTable.WaitOne(); //等待互斥量 DataRow row = m_tableDataAdded.NewRow(); row[CN_PortNumber] = entity.PortNumber; row[CN_TransType] = CEnumHelper.SerialTransTypeToDBStr(entity.TransType); row[CN_Baudrate] = entity.Baudrate; row[CN_Databit] = entity.DataBit; row[CN_Stopbit] = entity.StopBit; row[CN_Parity] = CEnumHelper.PortParityTypeToDBChar(entity.ParityType); row[CN_Stream] = CEnumHelper.SerialPortStreamTypeToDBStr(entity.Stream); row[CN_Break] = entity.Break; row[CN_Open] = entity.SwitchSatus; m_tableDataAdded.Rows.Add(row); if (m_tableDataAdded.Rows.Count >= CDBParams.GetInstance().AddBufferMax) { // 如果超过最大值,写入数据库 Task task = new Task(() => { AddDataToDB(); }); task.Start(); } else { // 没有超过缓存最大值,开启定时器进行检测,多次调用Start()会导致重新计数 m_addTimer.Start(); } m_mutexDataTable.ReleaseMutex(); }
private String GetDeleteSQL(CEntitySerialPort entity) { return(String.Format("DELETE FROM {0} WHERE [{1}]={2}", CT_TableName, CN_PortNumber, entity.PortNumber )); }
/// <summary> /// 获取所有分中心信息 /// </summary> public List <CEntitySerialPort> QueryAll() { var result = new List <CEntitySerialPort>(); var sqlConn = CDBManager.GetInstacne().GetConnection(); try { m_mutexWriteToDB.WaitOne(); // 取对数据库的唯一访问权 m_mutexDataTable.WaitOne(); // 获取内存表的访问权 sqlConn.Open(); // 建立数据库连接 String sqlStr = GetQuerySQL(); SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn); SqlDataReader reader = sqlCmd.ExecuteReader(); Debug.Assert(reader.FieldCount == CN_FIELD_COUNT, CT_TableName + "表与类" + CT_EntityName + "中定义字段不符合"); // 处理查询结果 while (reader.Read()) { try { var port = new CEntitySerialPort(); port.PortNumber = (Int32)reader[CN_PortNumber]; port.TransType = CEnumHelper.DBStrToSerialTransType((String)reader[CN_TransType]); port.Baudrate = (Int32)reader[CN_Baudrate]; port.DataBit = (Int32)reader[CN_Databit]; port.StopBit = (Int32)reader[CN_Stopbit]; port.ParityType = CEnumHelper.DBCharToPortParityType(((String)reader[CN_Parity])[0]); port.Stream = CEnumHelper.DBStrToSerialPortStreamType(reader[CN_Stream].ToString()); port.Break = (Boolean)reader[CN_Break]; port.SwitchSatus = (Boolean)reader[CN_Open]; result.Add(port); } catch (Exception exp) { Debug.WriteLine(exp.Message); } } } catch (Exception exp) { throw exp; } finally { sqlConn.Close(); // 关闭数据库连接 m_mutexDataTable.ReleaseMutex(); // 释放内存表的访问权 m_mutexWriteToDB.ReleaseMutex(); // 释放数据库的访问权 } return(result); }
private void GetUpdatedData() { // 标记为删除的就不需要添加的修改或者添加的分中心中了 List <int> listEditRows = new List <int>(); foreach (int item in base.m_listEditedRows) { if (!m_listMaskedDeletedRows.Contains(item)) { listEditRows.Add(item); } } // 将去重后的项赋给编辑项 base.m_listEditedRows = listEditRows; // 获取修改了的数据 foreach (var item in base.m_listEditedRows) { var port = new CEntitySerialPort(); port.PortNumber = Int32.Parse(base.Rows[item].Cells[CS_PortNumber].Value.ToString()); port.TransType = CEnumHelper.UIStrToTransType(base.Rows[item].Cells[CS_TransType].Value.ToString()); port.Baudrate = Int32.Parse(base.Rows[item].Cells[CS_Baudrate].Value.ToString()); port.DataBit = Int32.Parse(base.Rows[item].Cells[CS_DataBit].Value.ToString()); port.StopBit = Int32.Parse(base.Rows[item].Cells[CS_StopBit].Value.ToString()); port.ParityType = CEnumHelper.UIStrToParityType(base.Rows[item].Cells[CS_ParityType].Value.ToString()); port.Stream = CEnumHelper.UIStrToSerialPortStreamType(base.Rows[item].Cells[CS_Stream].Value.ToString()); port.Break = base.Rows[item].Cells[CS_Break].Value.ToString() == CS_Break_Enabled ? true : false; port.SwitchSatus = base.Rows[item].Cells[CS_Open].Value.ToString() == CS_SwitchStatus_Open ? true : false; int prePortNumber = Int32.Parse(base.Rows[item].Cells[CS_PrePortNumber].Value.ToString()); string tag = base.Rows[item].Cells[CS_PID].Value.ToString(); if (tag == "-1") { // 新增的记录 m_listAddedPort.Add(port); } else { // 修改的记录,判断如果串口号不一致,则变成删除以前的串口,添加一个新串口 if (port.PortNumber == prePortNumber) { m_listUpdatedPorts.Add(port); } else { // 新增一个记录,删除一个记录 m_listAddedPort.Add(port); m_listDeletedPorts.Add(prePortNumber); } } } m_listEditedRows.Clear(); }
private String GetUpdateSQL(CEntitySerialPort entity) { String strPortNum = entity.PortNumber.ToString(); String strTransType = CEnumHelper.SerialTransTypeToDBStr(entity.TransType); String strBaudrate = entity.Baudrate.ToString(); String strDataBit = entity.DataBit.ToString(); String strStopBit = entity.StopBit.ToString(); String strParityType = CEnumHelper.PortParityTypeToDBChar(entity.ParityType).ToString(); String strStream = CEnumHelper.SerialPortStreamTypeToDBStr(entity.Stream); String strBreak = "NULL"; if (entity.Break.HasValue) { strBreak = entity.Break.Value.ToString(); //if (entity.Break.Value) // strBreak = "1"; //else // strBreak = "0"; } String strOpen = "NULL"; if (entity.SwitchSatus.HasValue) { strOpen = entity.SwitchSatus.Value.ToString(); //if (entity.SwitchSatus.Value) // strOpen = "1"; //else // strOpen = "0"; } return(String.Format( "UPDATE {0} SET [{1}]={2},[{3}]={4},[{5}]={6},[{7}]={8},[{9}]={10},[{11}]={12},[{13}]={14},[{15}]={16} WHERE [{17}]={18}", CT_TableName, CN_TransType, strTransType, CN_Baudrate, strBaudrate, CN_Databit, strDataBit, CN_Stopbit, strStopBit, CN_Parity, strParityType, CN_Stream, strStream, CN_Break, strBreak, CN_Open, strOpen, CN_PortNumber, strPortNum )); }
private String GetInsertSQL(CEntitySerialPort entity) { String strPortNum = entity.PortNumber.ToString(); String strTransType = CEnumHelper.SerialTransTypeToDBStr(entity.TransType); String strBaudrate = entity.Baudrate.ToString(); String strDataBit = entity.DataBit.ToString(); String strStopBit = entity.StopBit.ToString(); String strParityType = CEnumHelper.PortParityTypeToDBChar(entity.ParityType).ToString(); String strStream = CEnumHelper.SerialPortStreamTypeToDBStr(entity.Stream); String strBreak = "NULL"; if (entity.Break.HasValue) { strBreak = entity.Break.Value.ToString(); //if (entity.Break.Value) // strBreak = "1"; //else // strBreak = "0"; } String strOpen = "NULL"; if (entity.SwitchSatus.HasValue) { strBreak = entity.SwitchSatus.Value.ToString(); //if (entity.SwitchSatus.Value) // strOpen = "1"; //else // strOpen = "0"; } return(String.Format( "INSERT INTO {0} ([{1}],[{2}],[{3}],[{4}],[{5}],[{6}],[{7}],[{8}],[{9}]) VALUES ({10},{11},{12},{13},{14},{15},{16},{17},{18})", CT_TableName, CN_PortNumber, CN_TransType, CN_Baudrate, CN_Databit, CN_Stopbit, CN_Parity, CN_Stream, CN_Break, CN_Open, strPortNum, strTransType, strBaudrate, strDataBit, strStopBit, strParityType, strStream, strBreak, strOpen )); }
/// <summary> /// 修改分中心信息 /// </summary> public void Update(CEntitySerialPort entity) { String sqlStr = GetUpdateSQL(entity); ExecuteNonQuery(sqlStr); }
/// <summary> /// 添加分中心 /// </summary> public void Add(CEntitySerialPort entity) { String sqlStr = GetInsertSQL(entity); ExecuteNonQuery(sqlStr); }