public override DataTable QueryRecords(string clause, out string errMsg)
{
errMsg = string.Empty;
DataTable dt = null;
try
{
if (!OpenConnection(out errMsg))
{
return(null);
}
SqlCommand cmd = new SqlCommand(clause, (SqlConnection)base.m_dbConn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{
errMsg = "查询数据时出现错误：" + ex.Message;
OPCLog.Error(errMsg);
return(null);
}
return(dt);
}
protected bool OpenConnection(out string errMsg)
{
errMsg = string.Empty;
if (this.m_dbConn == null)
{
errMsg = "数据库连接未设置";
OPCLog.Error(errMsg);
return(false);
}
if (this.m_dbConn.State == System.Data.ConnectionState.Closed)
{
try
{
this.m_dbConn.Open();
}
catch (Exception ex)
{
errMsg = "打开数据库连接失败：" + ex.Message;
OPCLog.Error(errMsg);
return(false);
}
}
if (this.m_dbConn.State == System.Data.ConnectionState.Closed)
{
errMsg = "未连接到数据库";
OPCLog.Error(errMsg);
return(false);
}
return(true);
}
public bool InsertToHistoryDataTable(List <OPCItemData> itemDataList, out string errMsg)
{
errMsg = string.Empty;
#region 检查数据连接设置
if (ServerName.Trim() == string.Empty ||
UserName.Trim() == string.Empty ||
Password.Trim() == string.Empty ||
NewDataTableName.Trim() == string.Empty)
{
errMsg = "数据库连接设置无效";
OPCLog.Error("数据库连接设置无效");
return(false);
}
if (this.m_dal == null)
{
switch (DatabaseType)
{
case OPCUtils.DatabaseType.MSSQLServer:
this.m_dal = new OPCClientMSSQLDAL(ServerName, DatabaseName, UserName, Password);
break;
case OPCUtils.DatabaseType.Oracle:
this.m_dal = new OPCClientOracleDAL(ServerName, UserName, Password);
break;
default:
break;
}
if (!this.m_dal.CreateConnection(out errMsg))
{
return(false);
}
}
#endregion
//OPCUtils.LogDataChangeTime("数据刷新-插入历史数据-插入数据表-获取数据实体");
List <OPCClientItemEntity> itemEntities = GetEntities(itemDataList);
//OPCUtils.LogDataChangeTime("数据刷新-插入历史数据-插入数据表-开始插入");
foreach (OPCClientItemEntity itemEntity in itemEntities)
{
//OPCUtils.LogDataChangeTime("数据刷新-插入历史数据-插入数据表-插入实体");
string dalErr = string.Empty;
if (!this.m_dal.InsertItem(HistoryDataTableName, itemEntity, out dalErr))
{
errMsg = dalErr;
}
}
//OPCUtils.LogDataChangeTime("数据刷新-插入历史数据-插入数据表-结束插入");
return(true);
}
protected override bool InitializeConnection(string connStr, out string errMsg)
{
errMsg = string.Empty;
try
{
base.m_dbConn = new SqlConnection();
base.m_dbConn.ConnectionString = connStr;
return(true);
}
catch (Exception ex)
{
errMsg = "打开数据连接时出现错误：" + ex.Message;
OPCLog.Error(errMsg);
return(false);
}
}
public override bool CreateConnection(out string errMsg)
{
OracleConnectionStringBuilder oraConnBuilder = new OracleConnectionStringBuilder();
oraConnBuilder.DataSource = this.m_oraSvrName;
oraConnBuilder.UserID = this.m_userName;
oraConnBuilder.Password = this.m_password;
this.m_oraHelper = new OPCOracleSQLHelper(oraConnBuilder.ConnectionString, out errMsg);
try
{
this.m_oraHelper.Connected = true;
this.m_oraHelper.Connected = false;
return(true);
}
catch (Exception e)
{
errMsg = e.Message;
OPCLog.Error(string.Format("创建 Oracle 数据库连接时发生错误：{0}", errMsg));
return(false);
}
}
public override bool ExecuteNonQuery(string clause, out string errMsg)
{
errMsg = string.Empty;
try
{
if (!OpenConnection(out errMsg))
{
return(false);
}
SqlCommand cmd = new SqlCommand(clause, (SqlConnection)base.m_dbConn);
cmd.ExecuteNonQuery();
base.m_dbConn.Close();
return(true);
}
catch (Exception ex)
{
errMsg = ex.Message;
OPCLog.Error(string.Format("执行SQL语句\r\n{0}\r\n时发生错误：{1}", clause, ex.Message));
return(false);
}
}
public override bool CreateConnection(out string errMsg)
{
errMsg = string.Empty;
SqlConnectionStringBuilder sqlConnBuilder = new SqlConnectionStringBuilder();
sqlConnBuilder.DataSource = this.m_server;
sqlConnBuilder.InitialCatalog = this.m_database;
sqlConnBuilder.UserID = this.m_userName;
sqlConnBuilder.Password = this.m_password;
this.m_sqlHelper = new OPCMSSQLHelper(sqlConnBuilder.ConnectionString, out errMsg);
try
{
this.m_sqlHelper.Connected = true;
this.m_sqlHelper.Connected = false;
return(true);
}
catch (Exception ex)
{
errMsg = ex.Message;
OPCLog.Error(string.Format("创建 MS SQL Server 数据库连接时发生错误：{0}", errMsg));
return(false);
}
}
public bool UpdateNewDataTable(List <OPCItemData> itemDataList, out string errMsg)
{
//MessageBox.Show("NewDataTable ItemDataList: " + itemDataList.Count.ToString());
errMsg = string.Empty;
#region 检查数据连接设置
if (ServerName.Trim() == string.Empty ||
UserName.Trim() == string.Empty ||
Password.Trim() == string.Empty ||
NewDataTableName.Trim() == string.Empty)
{
errMsg = "数据库连接设置无效";
OPCLog.Error("数据库连接设置无效");
return(false);
}
if (this.m_dal == null)
{
switch (DatabaseType)
{
case OPCUtils.DatabaseType.MSSQLServer:
this.m_dal = new OPCClientMSSQLDAL(ServerName, DatabaseName, UserName, Password);
break;
case OPCUtils.DatabaseType.Oracle:
this.m_dal = new OPCClientOracleDAL(ServerName, UserName, Password);
break;
default:
break;
}
if (!this.m_dal.CreateConnection(out errMsg))
{
return(false);
}
}
#endregion
#region 清空原有实时数据
/*
* if (!this.m_dal.ClearItems(NewDataTableName, out errMsg))
* {
* return false;
* }
*/
#endregion
List <OPCClientItemEntity> itemEntities = GetEntities(itemDataList);
foreach (OPCClientItemEntity itemEntity in itemEntities)
{
string dalErr = string.Empty;
string status = this.m_dal.QueryItemDataStatus(NewDataTableName, itemEntity, out dalErr);
if (status != string.Empty)
{
SyncSensorStatus(itemEntity, status);
}
if (!this.m_dal.DeleteItem(NewDataTableName, itemEntity, out dalErr) ||
!this.m_dal.InsertItem(NewDataTableName, itemEntity, out dalErr))
{
errMsg = dalErr;
}
}
return(true);
}
private List <OPCClientItemEntity> GetEntities(List <OPCItemData> itemDataList)
{
List <OPCClientItemEntity> itemEntities = new List <OPCClientItemEntity>();
try
{
List <OPCClientDBFieldMapping> dbFieldMappingList;
foreach (OPCItemData itemData in itemDataList)
{
OPCClientItemEntity itemEntity = new OPCClientItemEntity();
//如果是特殊的OPC项，则使用配置的映射
OPCItemMappingInfo opcItemMappingInfo = null;
if (OPCItemMapping != null)
{
opcItemMappingInfo = OPCItemMapping.GetOPCItemMappingInfo(itemData.ItemID);
}
if (opcItemMappingInfo != null)
{
dbFieldMappingList = opcItemMappingInfo.DBFieldMappingList;
}
else
{
dbFieldMappingList = this.DBFieldMappings;
}
#region 设置数据项实体的各属性
string[] ItemIDCol = itemData.ItemID.Split('.');
foreach (OPCClientDBFieldMapping fieldMapping in dbFieldMappingList)
{
OPCClientItemProperty itemProp = new OPCClientItemProperty();
//字段名称
itemProp.Name = fieldMapping.FieldName;
//数据类型
itemProp.DataType = (OPCClientDBFieldMapping.EnumDataType)fieldMapping.DataType;
//从自定义数据源获取字段值
if (fieldMapping.SourceCustom.Trim() != string.Empty)
{
//以":"开头为变量，否则为常量
//当为变量时，以":[0]"开头，为系统变量，不依赖当前程序和数据，如:[0]:GUID，:[0]:DateTime等。
//以":[1]"开头，根据同一传感器的其他值按规则获取相应值
//以":[2]"开头，根据同一个站点的其他传感器的数据按规则获取相应值
string prefix = string.Empty;
string prefix2 = string.Empty;
string varStr = string.Empty;
if (fieldMapping.SourceCustom.Length >= 4)
{
prefix = fieldMapping.SourceCustom.Substring(0, 4);
varStr = fieldMapping.SourceCustom.Substring(4, fieldMapping.SourceCustom.Length - 4);
}
if (fieldMapping.SourceCustom.Length >= 1)
{
prefix2 = fieldMapping.SourceCustom.Substring(0, 1);
}
string itemValue = string.Empty;
switch (prefix)
{
case ":[0]": //如":[0]:GUID"可以获取系统生成的GUID，":[0]:NOW"可以获取当前时间。
itemValue = OPCUtils.GetCustomSystemValue(varStr);
break;
case ":[1]": //如":[1]:SENSOR_NAME"，可以从数据实体中获取属性"SENSOR_NAME"的值，然后根据规则返回所需的值。
string fieldName = varStr.Substring(1, varStr.Length - 1);
IEnumerable <OPCClientItemProperty> tempProps =
itemEntity.Properties.Where(x => x.Name.ToUpper() == fieldName.ToUpper());
if (tempProps != null && tempProps.Count() > 0)
{
itemValue = OPCUtils.GetCustomValueByItemProperty(fieldName, tempProps.First().Value);
}
break;
case ":[2]": //如":[2]:更新时间"，可以从数据列表中找到"local.x02.更新时间"，从中获取Item Value.
string itemName = varStr.Substring(1, varStr.Length - 1);
if (ItemIDCol.Length > 0)
{
string queryItemID = string.Empty;
for (int i = 0; i < ItemIDCol.Length - 1; i++)
{
if (queryItemID == string.Empty)
{
queryItemID = ItemIDCol[i];
}
else
{
queryItemID = queryItemID + "." + ItemIDCol[i];
}
}
if (queryItemID == string.Empty)
{
queryItemID = itemName;
}
else
{
queryItemID = queryItemID + "." + itemName;
}
IEnumerable <OPCItemData> tempItemDataList =
itemDataList.Where(x => x.ItemID.ToUpper() == queryItemID.ToUpper());
if (tempItemDataList != null && tempItemDataList.Count() > 0)
{
itemValue = tempItemDataList.First().Value;
}
}
break;
default:
if (prefix2 == ":")
{
//如果是变量，默认获取系统变量
itemValue = OPCUtils.GetCustomSystemValue(fieldMapping.SourceCustom);
}
else
{
//常量
itemValue = fieldMapping.SourceCustom;
}
break;
}
itemProp.Value = itemValue;
}
//从OPCItem属性获取字段值
if (fieldMapping.SourceOPCItem > 0 && (itemProp.Value == null || itemProp.Value.Trim() == string.Empty))
{
switch ((OPCClientDBFieldMapping.EnumOPCItem)fieldMapping.SourceOPCItem)
{
case OPCClientDBFieldMapping.EnumOPCItem.未设置:
itemProp.Value = string.Empty;
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_VALUE:
itemProp.Value = itemData.Value;
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_QUALITY:
itemProp.Value = itemData.Quality;
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_TIMESTAMP:
itemProp.Value = itemData.Timestamp;
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_SERVERHANDLE:
itemProp.Value = itemData.ServerHandle;
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_CLIENTHANDLE:
itemProp.Value = itemData.ClientHandle;
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_ID:
itemProp.Value = itemData.ItemID;
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_ID_0:
if (ItemIDCol.Length > 0)
{
itemProp.Value = ItemIDCol[0];
}
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_ID_1:
if (ItemIDCol.Length > 1)
{
itemProp.Value = ItemIDCol[1];
}
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_ID_2:
if (ItemIDCol.Length > 2)
{
itemProp.Value = ItemIDCol[2];
}
break;
case OPCClientDBFieldMapping.EnumOPCItem.ITEM_ID_3:
if (ItemIDCol.Length > 3)
{
itemProp.Value = ItemIDCol[3];
}
break;
default:
itemProp.Value = string.Empty;
break;
}
}
//取值序列
itemProp.SEQ_Name = fieldMapping.SeqName;
//是否自增
itemProp.AutoInc = Convert.ToBoolean(fieldMapping.AutoInc);
//是否标识，用于判断是否同一个站点、传感器
itemProp.IsEntityIdentity = Convert.ToBoolean(fieldMapping.IsEntityIdentity);
bool propIsValid = false;
switch (itemProp.DataType)
{
case OPCClientDBFieldMapping.EnumDataType.INT:
int checkInt;
propIsValid = int.TryParse(itemProp.Value, out checkInt);
break;
case OPCClientDBFieldMapping.EnumDataType.NUMERIC:
double checkDouble;
propIsValid = double.TryParse(itemProp.Value, out checkDouble);
break;
case OPCClientDBFieldMapping.EnumDataType.CHAR:
propIsValid = true;
break;
case OPCClientDBFieldMapping.EnumDataType.NCHAR:
propIsValid = true;
break;
case OPCClientDBFieldMapping.EnumDataType.DATETIME:
DateTime checkDateTime;
propIsValid = DateTime.TryParse(itemProp.Value, out checkDateTime);
break;
case OPCClientDBFieldMapping.EnumDataType.BINARY:
propIsValid = true;
break;
default:
propIsValid = false;
break;
}
if (!propIsValid)
{
itemEntity = null;
break;
}
else
{
itemEntity.Properties.Add(itemProp);
}
}
#endregion
if (itemEntity != null)
{
itemEntities.Add(itemEntity);
}
}
} catch (Exception e)
{
OPCLog.Error(string.Format("获取OPC数据项映射配置时发生错误：{0}", e.Message));
}
return(itemEntities);
}
public override bool InsertItem(string tableName, OPCClientItemEntity item, out string errMsg)
{
this.m_oraHelper.Connected = true;
if (!this.m_oraHelper.Connected)
{
errMsg = "未连接到数据库";
OPCLog.Error(errMsg);
return(false);
}
string insertStr = "INSERT INTO " + tableName + "(";
string cols = string.Empty;
foreach (OPCClientItemProperty itemProp in item.Properties)
{
if (!itemProp.AutoInc)
{
cols = cols == string.Empty
? itemProp.Name : cols + "," + itemProp.Name;
}
}
insertStr = insertStr + cols + ") VALUES(";
string values = string.Empty;
foreach (OPCClientItemProperty itemProp in item.Properties)
{
if (!itemProp.AutoInc)
{
switch (itemProp.DataType)
{
case OPCClientDBFieldMapping.EnumDataType.INT:
values = values == string.Empty
? itemProp.Value : values + "," + itemProp.Value;
break;
case OPCClientDBFieldMapping.EnumDataType.NUMERIC:
values = values == string.Empty
? itemProp.Value : values + "," + itemProp.Value;
break;
case OPCClientDBFieldMapping.EnumDataType.CHAR:
values = values == string.Empty
? "'" + itemProp.Value + "'" : values + ",'" + itemProp.Value + "'";
break;
case OPCClientDBFieldMapping.EnumDataType.NCHAR:
values = values == string.Empty
? "'" + itemProp.Value + "'" : values + ",'" + itemProp.Value + "'";
break;
case OPCClientDBFieldMapping.EnumDataType.DATETIME:
string date = "TO_DATE('" + itemProp.Value + "', 'yyyy-mm-dd hh24:mi:ss')";
values = values == string.Empty
? date : values + ", " + date;
break;
case OPCClientDBFieldMapping.EnumDataType.BINARY:
values = values == string.Empty
? itemProp.Value : values + "," + itemProp.Value;
break;
default:
values = values == string.Empty
? "'" + itemProp.Value + "'" : values + ",'" + itemProp.Value + "'";
break;
}
}
}
insertStr = insertStr + values + ")";
return(this.m_oraHelper.ExecuteNonQuery(insertStr, out errMsg));
}
public override bool DeleteItem(string tablename, OPCClientItemEntity item, out string errMsg)
{
errMsg = string.Empty;
this.m_oraHelper.Connected = true;
if (!this.m_oraHelper.Connected)
{
errMsg = "未连接到数据库";
OPCLog.Error(errMsg);
return(false);
}
string deleteStr = "DELETE FROM " + tablename + " WHERE ";
string whereClause = string.Empty;
foreach (OPCClientItemProperty itemProp in item.Properties)
{
if (itemProp.IsEntityIdentity)
{
switch (itemProp.DataType)
{
case OPCClientDBFieldMapping.EnumDataType.INT:
whereClause = whereClause == string.Empty
? itemProp.Name + "=" + itemProp.Value : whereClause + " AND " + itemProp.Name + "=" + itemProp.Value;
break;
case OPCClientDBFieldMapping.EnumDataType.NUMERIC:
whereClause = whereClause == string.Empty
? itemProp.Name + "=" + itemProp.Value : whereClause + " AND " + itemProp.Name + "=" + itemProp.Value;
break;
case OPCClientDBFieldMapping.EnumDataType.CHAR:
whereClause = whereClause == string.Empty
? itemProp.Name + "='" + itemProp.Value + "'" : whereClause + " AND " + itemProp.Name + "='" + itemProp.Value + "'";
break;
case OPCClientDBFieldMapping.EnumDataType.NCHAR:
whereClause = whereClause == string.Empty
? itemProp.Name + "='" + itemProp.Value + "'" : whereClause + " AND " + itemProp.Name + "='" + itemProp.Value + "'";
break;
case OPCClientDBFieldMapping.EnumDataType.DATETIME:
string datetime = "TO_DATE('" + itemProp.Value + "', 'yyyy-mm-dd hh24:mi:ss')";
whereClause = whereClause == string.Empty
? itemProp.Name + "=" + datetime : whereClause + " AND " + itemProp.Name + "=" + datetime;
break;
case OPCClientDBFieldMapping.EnumDataType.BINARY:
whereClause = whereClause == string.Empty
? itemProp.Name + "=" + itemProp.Value : whereClause + " AND " + itemProp.Name + "=" + itemProp.Value;
break;
default:
whereClause = whereClause == string.Empty
? itemProp.Name + "='" + itemProp.Value + "'" : whereClause + " AND " + itemProp.Name + "='" + itemProp.Value + "'";
break;
}
}
}
if (whereClause != string.Empty)
{
deleteStr = deleteStr + whereClause;
return(this.m_oraHelper.ExecuteNonQuery(deleteStr, out errMsg));
}
else
{
return(true);
}
}
