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); } }