/// <summary> /// retrieve all the configuration name for real-time charting. /// </summary> /// <returns></returns> public List <string> GetAllDPGrpNames() { string Function_Name = "GetAllDPGrpNames"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <string> dpGrpNames = new List <string>(); string localSQL = " SELECT DISTINCT CONFIG_NAME FROM TRENDVIEWER_CONFIG order by config_name "; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); if (drReader != null) { try { while (drReader.Read()) { if (!drReader.IsDBNull(0)) { dpGrpNames.Add(DAOHelper.convert8859P1ToGB2312(drReader["CONFIG_NAME"].ToString())); } } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(dpGrpNames); }
public string GetLocationNameFromKey(double locationKey) { string Function_Name = "GetLocationNameFromKey"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); string locationName = ""; string localSQL = "SELECT NAME FROM LOCATION WHERE PKEY = " + locationKey.ToString(); System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { if (drReader != null && drReader.Read()) { if (!drReader.IsDBNull(drReader.GetOrdinal("NAME"))) { locationName = DAOHelper.convert8859P1ToGB2312(drReader["NAME"].ToString()); } } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(locationName); }
/// <summary> /// Returns Sample Group details of the specified Sample group Key. /// </summary> /// <param name="opcGrpID">Sample group key</param> /// <returns>Sample Group entity</returns> public EtyOPCSampleGroup GetOPCGrpByID(string opcGrpID) { string Function_Name = "GetOPCGrpByID"; LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0}", opcGrpID)); EtyOPCSampleGroup etyOPCSampleGrp = new EtyOPCSampleGroup(); string localSQL = " SELECT SAMPLE_GRP_NAME,SAMPLE_GRP_DESC,SAMPLE_INTERVAL,INTERVAL_TYPE,START_TIME,DELTA_VALUE,DISABLE " + " FROM OPC_DT_SAMPLE_GRP WHERE SAMPLE_GRP_ID = " + opcGrpID; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_NAME"))) { etyOPCSampleGrp.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_DESC"))) { etyOPCSampleGrp.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_DESC"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_INTERVAL"))) { etyOPCSampleGrp.Interval = Convert.ToInt32(drReader["SAMPLE_INTERVAL"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("INTERVAL_TYPE"))) { etyOPCSampleGrp.IntervalType = drReader["INTERVAL_TYPE"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("START_TIME"))) { etyOPCSampleGrp.StartTime = drReader["START_TIME"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DELTA_VALUE"))) { etyOPCSampleGrp.DeltaValue = Convert.ToDouble(drReader["DELTA_VALUE"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("DISABLE"))) { etyOPCSampleGrp.Disabled = DAOHelper.ChangeStrToBool(drReader["DISABLE"].ToString()); } } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyOPCSampleGrp); }
/// <summary> /// Returns the DataPoints based specified datapoint name substring. /// </summary> /// <param name="opcServerName">Datapoint server name</param> /// <param name="DataPointNameSubstr"> Datapoint name substring to be queried</param> /// <returns>Datapoints</returns> public List <EtyDataLogDPTrend> GetDataPointByName(string opcServerName, string DataPointNameSubstr) { const string Function_Name = "GetDataPointByName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyDataLogDPTrend> etyDPTrendList = new List <EtyDataLogDPTrend>(); string localSQL = "SELECT et.PKEY AS ENTITY_KEY,CONCAT(et.NAME,'.Value')as DP_NAME ,DP.DP_GRP_ID AS DP_GRP_ID FROM ENTITY et LEFT JOIN DATALOG_DP_TREND DP" + " ON et.PKEY = DP.ENTITY_KEY WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')" + " AND UPPER(et.NAME) LIKE '%" + DAOHelper.convertEscapeStringAndGB2312To8859P1(DataPointNameSubstr) + "%'"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_ENTITY_LOCATIONKEY); if (LocationClauseStr.Length != 0) { localSQL += " AND" + LocationClauseStr; } localSQL += " ORDER BY DP_NAME "; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); if (drReader != null) { try { while (drReader.Read()) { EtyDataLogDPTrend etyDPTrend = new EtyDataLogDPTrend(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENTITY_KEY))) { etyDPTrend.EntityKey = Convert.ToDouble(drReader[COLUMN_ENTITY_KEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { etyDPTrend.OPCDataPointName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_GRP_ID))) { etyDPTrend.OPCSampleGrpId = Convert.ToDouble(drReader[COLUMN_SAMPLE_GRP_ID]); } etyDPTrendList.Add(etyDPTrend); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyDPTrendList); }
/// <summary> /// Returns all the Root Data Nodes. /// </summary> /// <returns>Root Data Nodes</returns> public List <EtyOPCDataNode> GetAllOPCDataNode(string serverRootName, string opcServerName) { const string Function_Name = "GetAllOPCDataNode"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyOPCDataNode> OPCDataNodeList = new List <EtyOPCDataNode>(); string localSQL = " SELECT KEYID,DT_NODE_NAME,DT_NODE_DESC " + " FROM OPC_DT_NODE WHERE PKEYID IN (SELECT DISTINCT PKEY FROM ENTITY WHERE NAME = '" + serverRootName + "') AND DISABLE = 'N' AND DATA_PT_SERVER = '" + opcServerName + "' ORDER BY DT_NODE_NAME"; //sql for getting from entity table /*string localSQL = " SELECT PKEY,NAME,DESCRIPTION" + * " FROM ENTITY WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataNode')"+ * " AND PARENTKEY IN (SELECT DISTINCT PKEY FROM ENTITY WHERE TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'SCADAROOT'))"+ * " ORDER BY NAME";*/ System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); if (drReader != null) { try { while (drReader.Read()) { EtyOPCDataNode etyOPCDataNode = new EtyOPCDataNode(); if (!drReader.IsDBNull(drReader.GetOrdinal("KEYID"))) { etyOPCDataNode.OPCDataNodeId = Convert.ToDouble(drReader["KEYID"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("DT_NODE_NAME"))) { etyOPCDataNode.OPCDataNodeName = DAOHelper.convert8859P1ToGB2312(drReader["DT_NODE_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DT_NODE_DESC"))) { etyOPCDataNode.OPCDataNodeDesc = DAOHelper.convert8859P1ToGB2312(drReader["DT_NODE_DESC"].ToString()); } OPCDataNodeList.Add(etyOPCDataNode); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(OPCDataNodeList); }
/// <summary> /// Returns the DataPoints based on the filterstring for specified server name. /// e.g. for filterstring can include substring datapoint name to search. /// </summary> /// <param name="opcServerName">datapoint server name</param> /// <param name="filterString">Where clause query string</param> /// <returns>Datapoints</returns> public List <EtyDataLogDPTrend> GetOPCDataPoint(string opcServerName, string filterString) { const string Function_Name = "GetOPCDataPoint"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyDataLogDPTrend> etyDPTrendList = new List <EtyDataLogDPTrend>(); string localSQL = "SELECT et.PKEY AS ENTITY_KEY,CONCAT(et.NAME,'.Value') as DP_NAME ,DP.DP_GRP_ID as DP_GRP_ID FROM ENTITY et LEFT JOIN DATALOG_DP_TREND DP" + " ON et.PKEY = DP.ENTITY_KEY WHERE et.TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')"; if (filterString.Trim() != "") { localSQL += filterString; } localSQL += " ORDER BY DP_NAME "; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); if (drReader != null) { try { while (drReader.Read()) { EtyDataLogDPTrend etyDPTrend = new EtyDataLogDPTrend(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENTITY_KEY))) { etyDPTrend.EntityKey = Convert.ToDouble(drReader[COLUMN_ENTITY_KEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { etyDPTrend.OPCDataPointName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_GRP_ID))) { etyDPTrend.OPCSampleGrpId = Convert.ToDouble(drReader[COLUMN_SAMPLE_GRP_ID]); } etyDPTrendList.Add(etyDPTrend); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyDPTrendList); }
/// <summary> /// Returns all OPC Sample Group key and Name map /// </summary> /// <returns>Sample group key-Name map</returns> public Dictionary <string, string> GetAllOPCGrpIdAndName(bool checkLoc) { string Function_Name = "GetAllOPCGrpIdAndName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); Dictionary <string, string> opcGrpIDToNameDic = new Dictionary <string, string>(); string localSQL; localSQL = "SELECT PKEY, NAME FROM DATALOG_DP_GROUP_TREND"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); if (LocationClauseStr.Length != 0 && checkLoc) { localSQL += " WHERE" + LocationClauseStr; } System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { string grpID = "", grpName = ""; if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { grpID = drReader[COLUMN_PKEY].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { grpName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } opcGrpIDToNameDic.Add(grpID, grpName); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(opcGrpIDToNameDic); }
/// <summary> /// Returns all OPC Sample Group key and Name map /// </summary> /// <returns>Sample group key-Name map</returns> public Dictionary <string, string> GetAllOPCGrpIdAndName() { string Function_Name = "GetAllOPCGrpIdAndName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); Dictionary <string, string> opcGrpIDToNameDic = new Dictionary <string, string>(); string localSQL; localSQL = "SELECT SAMPLE_GRP_ID, SAMPLE_GRP_NAME FROM OPC_DT_SAMPLE_GRP"; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { string grpID = "", grpName = ""; if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_ID"))) { grpID = drReader["SAMPLE_GRP_ID"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_NAME"))) { grpName = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_NAME"].ToString()); } opcGrpIDToNameDic.Add(grpID, grpName); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(opcGrpIDToNameDic); }
public List <EtyDataLogDPGroupTrend> GetAllOPCGrpsBySort(string sortColumn, string sortOrder) { string Function_Name = "GetAllOPCGrpsBySort"; LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0},{1}", sortColumn, sortOrder)); List <EtyDataLogDPGroupTrend> etyDPGrpTrendList = new List <EtyDataLogDPGroupTrend>(); string localSQL; localSQL = " SELECT DP.PKEY,DP.NAME,DP.DESCRIPTION,DP.SAMPLE_INTERVAL,DP.INTERVAL_TYPE,DP.START_TIME,DP.DELTA_VALUE,DP.ENABLED,DP.LOCATION_KEY,LOC.NAME AS LOCATION_NAME " + " FROM DATALOG_DP_GROUP_TREND DP, LOCATION LOC WHERE DP.LOCATION_KEY = LOC.PKEY"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); if (LocationClauseStr.Length != 0) { localSQL += " AND" + LocationClauseStr; } localSQL += " ORDER BY " + sortColumn; sortOrder = sortOrder.ToUpper(); if (sortOrder.Equals("ASCENDING") || sortOrder.Equals("ASC")) { localSQL += " ASC "; } else { localSQL += " DESC "; } System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { EtyDataLogDPGroupTrend etyDPGroupTrend = new EtyDataLogDPGroupTrend(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { etyDPGroupTrend.SampleGrpID = Convert.ToDouble(drReader[COLUMN_PKEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { etyDPGroupTrend.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DESCRIPTION))) { etyDPGroupTrend.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_DESCRIPTION].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_INTERVAL))) { etyDPGroupTrend.Interval = Convert.ToDouble(drReader[COLUMN_SAMPLE_INTERVAL]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_INTERVAL_TYPE))) { etyDPGroupTrend.IntervalType = drReader[COLUMN_INTERVAL_TYPE].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_START_TIME))) { etyDPGroupTrend.StartTime = drReader[COLUMN_START_TIME].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DELTA_VALUE))) { etyDPGroupTrend.DeltaValue = Convert.ToDouble(drReader[COLUMN_DELTA_VALUE]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENABLED))) { if (drReader[COLUMN_ENABLED].ToString() == "Y") { etyDPGroupTrend.Disabled = false; } else { etyDPGroupTrend.Disabled = true; } } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_LOCATION_KEY))) { etyDPGroupTrend.LocationKey = Convert.ToDouble(drReader[COLUMN_LOCATION_KEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(LOCATION_NAME))) { etyDPGroupTrend.LocationName = DAOHelper.convert8859P1ToGB2312(drReader[LOCATION_NAME].ToString()); } etyDPGrpTrendList.Add(etyDPGroupTrend); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyDPGrpTrendList); }
/// <summary> /// Get All child data point by a given entity pkey /// </summary> /// <param name="pkey">the parent entity pkey</param> /// <returns></returns> public Dictionary <ulong, EtyEntity> GetDataPointByDNPkey(ulong pkey) { string Function_Name = "GetDataPointByDNPkey"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); Dictionary <ulong, EtyEntity> res = new Dictionary <ulong, EtyEntity>(); // localSQL = " SELECT KEYID,DATA_PT_NAME,DATA_PT_DESC,DISABLE " + // " FROM OPC_DT_PT " + // " WHERE PKEYID = " + parentKeyID + // " AND DATA_PT_HOST = '" + opcServerHostBox.Text.Trim() + "' AND DATA_PT_SERVER = '" + serverNameBox.Text.Trim() + "' " + // " ORDER BY DATA_PT_NAME"; string localSQL = " SELECT PKEY,CONCAT(NAME,'.Value') as LONGNAME,DESCRIPTION " + " FROM ENTITY " + " WHERE PARENTKEY = " + pkey.ToString() + " AND TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint' ) " + " AND DELETED = 0 "; /*if (checkLoc) * { * localSQL = string.Concat(localSQL, " AND LOCATIONKEY=", locKey); * }*/ string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATIONKEY); if (LocationClauseStr.Length != 0) { localSQL += " AND" + LocationClauseStr; } localSQL = localSQL + " ORDER BY NAME"; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { EtyEntity entity = new EtyEntity(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { entity.Pkey = Convert.ToUInt64(drReader[COLUMN_PKEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_LONGNAME))) { entity.Name = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_LONGNAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DESCRIPTION))) { entity.Description = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_DESCRIPTION].ToString()); } res.Add(entity.Pkey, entity); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(res); }
/// <summary> /// Get all the child data node by an entity pkey /// </summary> /// <param name="pkey">the parent entity pkey</param> /// <returns></returns> public Dictionary <ulong, EtyEntity> GetDataNodeChildrenByPkey(ulong pkey) { string Function_Name = "GetDataNodeChildrenByPkey"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); Dictionary <ulong, EtyEntity> res = new Dictionary <ulong, EtyEntity>(); string localSQL = " SELECT PKEY,NAME,DESCRIPTION " + " FROM ENTITY " + " WHERE PARENTKEY = " + pkey.ToString() + " AND TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataNode' ) " + " AND DELETED = 0 "; /* if (checkLoc) * { * //localSQL = localSQL + " AND LOCATIONKEY=" + locKey.ToString; * localSQL = string.Concat(localSQL, " AND LOCATIONKEY=", locKey); * }*/ string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATIONKEY); if (LocationClauseStr.Length != 0) { localSQL += " AND" + LocationClauseStr; } localSQL = localSQL + " ORDER BY NAME"; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { EtyEntity entity = new EtyEntity(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { entity.Pkey = Convert.ToUInt64(drReader[COLUMN_PKEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { entity.Name = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DESCRIPTION))) { entity.Description = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_DESCRIPTION].ToString()); } res.Add(entity.Pkey, entity); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(res); }
/// <summary> /// get historical datapoint configuration by config name /// </summary> /// <param name="configName">hist dp config name</param> /// <returns>hist dp list in one group</returns> public List <EtyHistDataPoint> GetHistDPByCfgName(string configName) { string Function_Name = "GetHistDPByCfgName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyHistDataPoint> histDPList = new List <EtyHistDataPoint>(); string localSQL = " SELECT DATA_PT_NAME,DATA_PT_TYPE,DATA_PT_COLOR," + " DATA_PT_SERVER,DATA_PT_ENABLED,DATA_PT_LBL_ENABLED,DATA_PT_LBL_NAME,DATA_PT_DT," + " DATA_PT_HOST,DATA_PT_DT_END ,ENTITYKEY " + " FROM TRENDVIEWER_HIST_CONFIG WHERE CONFIG_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(configName) + "'"; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); EtyHistDataPoint newEtyHistDP = null; try { while (drReader != null && drReader.Read()) { newEtyHistDP = new EtyHistDataPoint(); if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_NAME"))) { newEtyHistDP.DPName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_TYPE"))) { newEtyHistDP.DPType = DAOHelper.ConvertLineTypeToEnum(drReader["DATA_PT_TYPE"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_COLOR"))) { newEtyHistDP.DPColor = drReader["DATA_PT_COLOR"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_SERVER"))) { newEtyHistDP.DPServer = drReader["DATA_PT_SERVER"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_ENABLED"))) { newEtyHistDP.DPEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_ENABLED"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_ENABLED"))) { newEtyHistDP.DPLblEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_LBL_ENABLED"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_NAME"))) { newEtyHistDP.DPLblName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_LBL_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_DT"))) { DateTime dt; if (DateTime.TryParse(drReader["DATA_PT_DT"].ToString(), out dt)) { newEtyHistDP.DPStartDateTime = dt; } } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_HOST"))) { newEtyHistDP.DPHost = drReader["DATA_PT_HOST"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_DT_END"))) { DateTime dt; if (DateTime.TryParse(drReader["DATA_PT_DT_END"].ToString(), out dt)) { newEtyHistDP.DPEndDateTime = dt; } } if (!drReader.IsDBNull((drReader.GetOrdinal("ENTITYKEY")))) { newEtyHistDP.EntityKey = Convert.ToUInt64(drReader["ENTITYKEY"]); } histDPList.Add(newEtyHistDP); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(histDPList); }
/// <summary> /// get marker list by a group name /// </summary> /// <param name="configName">the marker group name</param> /// <returns>the marker list in the group</returns> public List <EtyMarker> GetMarkersByCfgName(string configName) { const string Function_Name = "GetMarkersByCfgName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyMarker> markerList = new List <EtyMarker>(); string localSQL = " SELECT CONFIG_NAME, MARKER_NAME,MARKER_WIDTH,MARKER_BCOLOR,MARKER_VALUE,MARKER_ENABLED,MARKER_FCOLOR" + " FROM TRENDVIEWER_MARKER WHERE CONFIG_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(configName) + "'"; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); EtyMarker newEtyMarker = null; try { while (drReader != null && drReader.Read()) { newEtyMarker = new EtyMarker(); if (!drReader.IsDBNull(drReader.GetOrdinal("CONFIG_NAME"))) { newEtyMarker.ConfigName = DAOHelper.convert8859P1ToGB2312(drReader["CONFIG_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_NAME"))) { newEtyMarker.MarkerName = DAOHelper.convert8859P1ToGB2312(drReader["MARKER_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_WIDTH"))) { newEtyMarker.MarkerWidth = Convert.ToDouble(drReader["MARKER_WIDTH"]); //need test } if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_BCOLOR"))) { newEtyMarker.MarkerBColor = drReader["MARKER_BCOLOR"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_VALUE"))) { newEtyMarker.MarkerValue = Convert.ToDouble(drReader["MARKER_VALUE"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_ENABLED"))) { string markerEnabled = drReader["MARKER_ENABLED"].ToString(); if (markerEnabled == "Y") { newEtyMarker.MarkerEnabled = true; } else { newEtyMarker.MarkerEnabled = false; } } if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_FCOLOR"))) { newEtyMarker.MarkerFColor = drReader["MARKER_FCOLOR"].ToString(); } markerList.Add(newEtyMarker); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(markerList); }
/// <summary> /// get all formulas by a configuration name. /// </summary> /// <param name="configName">formula configuration name</param> /// <returns>fomula list</returns> public List <EtyFormula> GetFormulasByCfgName(string configName) { string Function_Name = "GetFormulasByCfgName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyFormula> formulaList = new List <EtyFormula>(); string localSQL = " SELECT DATA_PT_EQUATION,DATA_PT_TYPE,DATA_PT_COLOR,DATA_PT_ENABLED,DATA_PT_LBL_ENABLED,DATA_PT_LBL_NAME " + " FROM TRENDVIEWER_FORMULA_CONFIG WHERE CONFIG_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(configName) + "'"; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); EtyFormula newEtyFormula = null; try { while (drReader != null && drReader.Read()) { newEtyFormula = new EtyFormula(); if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_EQUATION"))) { newEtyFormula.DPEquation = drReader["DATA_PT_EQUATION"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_TYPE"))) { newEtyFormula.DPType = DAOHelper.ConvertLineTypeToEnum(drReader["DATA_PT_TYPE"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_COLOR"))) { newEtyFormula.DPColor = drReader["DATA_PT_COLOR"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_ENABLED"))) { newEtyFormula.DPEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_ENABLED"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_ENABLED"))) { newEtyFormula.DPLblEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_LBL_ENABLED"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_NAME"))) { newEtyFormula.DPLblName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_LBL_NAME"].ToString()); } formulaList.Add(newEtyFormula); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(formulaList); }
/// <summary> /// retrieve a configuration from TRENDVIEWER_CONFIG by configuration name. /// </summary> /// <param name="configName">configuration name</param> /// <returns></returns> public List <EtyDataPoint> GetDPByCfgName(string configName) { string Function_Name = "GetDPByCfgName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyDataPoint> dpList = new List <EtyDataPoint>(); string localSQL = " SELECT DATA_PT_NAME,DATA_PT_TYPE,DATA_PT_COLOR,DATA_PT_SERVER,DATA_PT_ENABLED,DATA_PT_LBL_ENABLED,DATA_PT_LBL_NAME,DATA_PT_HOST,DATA_PT_VISIBLE " + " FROM TRENDVIEWER_CONFIG WHERE CONFIG_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(configName) + "'"; // string localSQL = " SELECT c.DATA_PT_NAME,c.DATA_PT_TYPE,c.DATA_PT_COLOR,c.DATA_PT_SERVER,c.DATA_PT_ENABLED,c.DATA_PT_LBL_ENABLED, c.DATA_PT_LBL_NAME, c.DATA_PT_HOST,c.DATA_PT_VISIBLE, " // + "o.keyid from trendviewer_config c, opc_dt_pt o where c.data_pt_name = o.data_pt_name " + // " and c.CONFIG_NAME = '" + DAOHelper.convertGB2312To8859P1(configName) + "'"; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); EtyDataPoint newEtyDP = null; try { while (drReader != null && drReader.Read()) { newEtyDP = new EtyDataPoint(); if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_NAME"))) { newEtyDP.DPName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_TYPE"))) { newEtyDP.DPType = DAOHelper.ConvertLineTypeToEnum(drReader["DATA_PT_TYPE"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_COLOR"))) { newEtyDP.DPColor = drReader["DATA_PT_COLOR"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_SERVER"))) { newEtyDP.DPServer = drReader["DATA_PT_SERVER"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_ENABLED"))) { newEtyDP.DPEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_ENABLED"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_ENABLED"))) { newEtyDP.DPLblEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_LBL_ENABLED"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_NAME"))) { newEtyDP.DPLblName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_LBL_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_HOST"))) { newEtyDP.DPHost = drReader["DATA_PT_HOST"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_VISIBLE"))) { newEtyDP.DPVisible = DAOHelper.ChangeStrToBool(drReader["DATA_PT_VISIBLE"].ToString()); } // if (!drOracle.IsDBNull(9)) // newEtyDP.PKey = (ulong)(drOracle.GetOracleDecimal(9).ToInt64()); dpList.Add(newEtyDP); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(dpList); }
public List <EtyOPCSampleGroup> GetAllOPCGrpsBySort(string sortColumn, string sortOrder) { string Function_Name = "GetAllOPCGrpsBySort"; LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0},{1}", sortColumn, sortOrder)); List <EtyOPCSampleGroup> etyOPCGrpList = new List <EtyOPCSampleGroup>(); string localSQL; localSQL = " SELECT SAMPLE_GRP_ID,SAMPLE_GRP_NAME,SAMPLE_GRP_DESC,SAMPLE_INTERVAL,INTERVAL_TYPE,START_TIME,DELTA_VALUE,DISABLE " + " FROM OPC_DT_SAMPLE_GRP"; localSQL += " ORDER BY " + sortColumn; sortOrder = sortOrder.ToUpper(); if (sortOrder.Equals("ASCENDING") || sortOrder.Equals("ASC")) { localSQL += " ASC "; } else { localSQL += " DESC "; } System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { EtyOPCSampleGroup etyOPCSampleGrp = new EtyOPCSampleGroup(); if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_ID"))) { etyOPCSampleGrp.SampleGrpID = Convert.ToDouble(drReader["SAMPLE_GRP_ID"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_NAME"))) { etyOPCSampleGrp.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_DESC"))) { etyOPCSampleGrp.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_DESC"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_INTERVAL"))) { etyOPCSampleGrp.Interval = Convert.ToDouble(drReader["SAMPLE_INTERVAL"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("INTERVAL_TYPE"))) { etyOPCSampleGrp.IntervalType = drReader["INTERVAL_TYPE"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("START_TIME"))) { etyOPCSampleGrp.StartTime = drReader["START_TIME"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DELTA_VALUE"))) { etyOPCSampleGrp.DeltaValue = Convert.ToDouble(drReader["DELTA_VALUE"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("DISABLE"))) { if (drReader["DISABLE"].ToString() == "Y") { etyOPCSampleGrp.Disabled = true; } else { etyOPCSampleGrp.Disabled = false; } } etyOPCGrpList.Add(etyOPCSampleGrp); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyOPCGrpList); }
/// <summary> /// Returns all Enabled Sample group Entities. /// </summary> /// <returns>Sample group entity list</returns> public List <EtyOPCSampleGroup> GetAllEnabledSampleGrp() { string Function_Name = "GetAllEnabledSampleGrp"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyOPCSampleGroup> etyOPCGrpList = new List <EtyOPCSampleGroup>(); string localSQL; localSQL = " SELECT SAMPLE_GRP_ID,SAMPLE_GRP_NAME,SAMPLE_GRP_DESC,SAMPLE_INTERVAL,INTERVAL_TYPE,START_TIME,DELTA_VALUE,DISABLE " + " FROM OPC_DT_SAMPLE_GRP WHERE DISABLE = 'N' "; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { EtyOPCSampleGroup etyOPCSampleGrp = new EtyOPCSampleGroup(); if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_ID"))) { etyOPCSampleGrp.SampleGrpID = Convert.ToDouble(drReader["SAMPLE_GRP_ID"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_NAME"))) { etyOPCSampleGrp.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_DESC"))) { etyOPCSampleGrp.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_DESC"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_INTERVAL"))) { etyOPCSampleGrp.Interval = Convert.ToDouble(drReader["SAMPLE_INTERVAL"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("INTERVAL_TYPE"))) { etyOPCSampleGrp.IntervalType = drReader["INTERVAL_TYPE"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("START_TIME"))) { etyOPCSampleGrp.StartTime = drReader["START_TIME"].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal("DELTA_VALUE"))) { etyOPCSampleGrp.DeltaValue = Convert.ToDouble(drReader["DELTA_VALUE"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("DISABLE"))) { if (drReader["DISABLE"].ToString() == "Y") { etyOPCSampleGrp.Disabled = true; //TODO CHECK ERROR } else { etyOPCSampleGrp.Disabled = false; } } etyOPCGrpList.Add(etyOPCSampleGrp); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyOPCGrpList); }
/// <summary> /// Returns Sample Group details of the specified Sample group Key. /// </summary> /// <param name="opcGrpID">Sample group key</param> /// <returns>Sample Group entity</returns> public EtyDataLogDPGroupTrend GetOPCGrpByID(double opcGrpID) { string Function_Name = "GetOPCGrpByID"; LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0}", opcGrpID)); EtyDataLogDPGroupTrend etyDpGroupTrend = new EtyDataLogDPGroupTrend(); string localSQL = " SELECT DP.NAME,DP.DESCRIPTION,DP.SAMPLE_INTERVAL,DP.INTERVAL_TYPE,DP.START_TIME,DP.DELTA_VALUE,DP.ENABLED,DP.LOCATION_KEY,LOC.NAME AS LOCATION_NAME" + " FROM DATALOG_DP_GROUP_TREND DP,LOCATION LOC WHERE DP.PKEY = " + opcGrpID.ToString() + "AND LOC.PKEY = DP.LOCATION_KEY"; /* string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); * if (LocationClauseStr.Length != 0) * { * localSQL += " AND" + LocationClauseStr; * }*/ System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { etyDpGroupTrend.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DESCRIPTION))) { etyDpGroupTrend.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_DESCRIPTION].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_INTERVAL))) { etyDpGroupTrend.Interval = Convert.ToInt32(drReader[COLUMN_SAMPLE_INTERVAL]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_INTERVAL_TYPE))) { etyDpGroupTrend.IntervalType = drReader[COLUMN_INTERVAL_TYPE].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_START_TIME))) { etyDpGroupTrend.StartTime = drReader[COLUMN_START_TIME].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DELTA_VALUE))) { etyDpGroupTrend.DeltaValue = Convert.ToDouble(drReader[COLUMN_DELTA_VALUE]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENABLED))) { etyDpGroupTrend.Disabled = !(DAOHelper.ChangeStrToBool(drReader[COLUMN_ENABLED].ToString())); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_LOCATION_KEY))) { etyDpGroupTrend.LocationKey = Convert.ToDouble(drReader[COLUMN_LOCATION_KEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(LOCATION_NAME))) { etyDpGroupTrend.LocationName = DAOHelper.convert8859P1ToGB2312(drReader[LOCATION_NAME].ToString()); } } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyDpGroupTrend); }
/// <summary> /// Returns the DataPoints based specified datapoint name substring. /// </summary> /// <param name="opcServerName">Datapoint server name</param> /// <param name="DataPointNameSubstr"> Datapoint name substring to be queried</param> /// <returns>Datapoints</returns> public List <EtyOPCDataPoint> GetDataPointByName(string opcServerName, string DataPointNameSubstr) { const string Function_Name = "GetDataPointByName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyOPCDataPoint> etyDataPointList = new List <EtyOPCDataPoint>(); /* string localSQL = " SELECT KEYID,PKEYID,DATA_PT_NAME,DATA_PT_DESC,SAMPLE_GRP_ID " + * " FROM OPC_DT_PT " + * " WHERE DATA_PT_SERVER = '" + TrendingHelper.convertGB2312To8859P1(opcServerName.Trim()) + "'" + * " And DATA_PT_NAME LIKE '%" + DAO.Trending.TrendingHelper.convertGB2312To8859P1(DataPointNameSubstr) + "%'"; * * localSQL += " ORDER BY DATA_PT_NAME ";*/ /*string localSQL = " SELECT et.PKEY,et.PARENTKEY,CONCAT(et.NAME,'.Value')as NAME ,et.DESCRIPTION, dt.SAMPLE_GRP_ID FROM ENTITY et, OPC_DT_PT dt" + * " WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')" + * " AND UPPER(NAME) LIKE '%" + DAOHelper.convertGB2312To8859P1(DataPointNameSubstr) +"%'"+ * " AND et.PKEY = dt.KEYID(+)";*/ string localSQL = " SELECT et.PKEY,et.PARENTKEY,CONCAT(et.NAME,'.Value')as NAME ,et.DESCRIPTION, dt.SAMPLE_GRP_ID FROM ENTITY et LEFT JOIN OPC_DT_PT dt" + " ON et.PKEY = dt.KEYID WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')" + " AND UPPER(NAME) LIKE '%" + DAOHelper.convertGB2312To8859P1(DataPointNameSubstr) + "%'"; localSQL += " ORDER BY NAME "; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); if (drReader != null) { try { while (drReader.Read()) { EtyOPCDataPoint etyDataPoint = new EtyOPCDataPoint(); if (!drReader.IsDBNull(drReader.GetOrdinal("PKEY"))) { etyDataPoint.OPCDataPointId = Convert.ToDouble(drReader["PKEY"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("PARENTKEY"))) { etyDataPoint.OPCParentDataPointId = Convert.ToDouble(drReader["PARENTKEY"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("NAME"))) { etyDataPoint.OPCDataPointName = DAOHelper.convert8859P1ToGB2312(drReader["NAME"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("DESCRIPTION"))) { etyDataPoint.OPCDataPointDesc = DAOHelper.convert8859P1ToGB2312(drReader["DESCRIPTION"].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_ID"))) { etyDataPoint.OPCSampleGrpId = Convert.ToDouble(drReader["SAMPLE_GRP_ID"]); } etyDataPointList.Add(etyDataPoint); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyDataPointList); }