// 添加新列
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);
}
