public void CloseReader() { reader.Close(); reader.Dispose(); ReCloseTimes++; HdisCommon.Log.Logging("{0}", "CloseReader()" + " ReCloseTimes:" + ReCloseTimes); }
public virtual object getmodel(object key, string alias) { OrmAnalysis ormAnalysis; EFWCoreLib.CoreFrame.DbProvider.AbstractDatabase _oleDb = GetDb(); ICacheManager _cache = GetCache(); string _pluginName = GetPluginName(); ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType); ormAnalysis.Alias = alias; ormAnalysis.Db = _oleDb; ormAnalysis.entityAttrList = (List <EntityAttributeInfo>)EntityManager.GetAttributeInfo(_cache, _pluginName); object value = null; object keyVal = key == null?ormAnalysis.GetEntityDataKeyValue(this) : key; string strsql = ormAnalysis.GetEntitySQL(this.GetType(), keyVal); System.Data.IDataReader result = _oleDb.GetDataReader(strsql); if (result.Read()) { value = ToObject(result, this, alias); } result.Close(); result.Dispose(); return(value); }
/// <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); }
private static int LoadFromDbase(Shotgun.Database.IBaseDataClass2 dBase) { var sql = "SELECT cpls.id,cpls.cp_pool_id,tn.sp_trone_id,tod.trone_id,trone_order_id,priority," + " ( cpls.status=1 and stn.status=1 and tn.status=1 and tod.disable=0 and stn.is_on_api=1) status" + " FROM daily_config.tbl_cp_pool_set cpls " + "left join tbl_cp_pool cpl on cpl.id= cpls.cp_pool_id " + "left join tbl_trone_order tod on cpls.trone_order_id= tod.id " + "left join tbl_trone tn on tn.id= tod.trone_id " + "left join tbl_sp_trone stn on stn.id= tn.sp_trone_id " + "where cpl.status=1 "; var cmd = dBase.Command(); cmd.CommandText = sql; System.Data.IDataReader rd = null; int c = 0; try { rd = dBase.ExecuteReader(cmd); lock (cache.SyncRoot) { cache.ClearCache(); while (rd.Read()) { PoolSetModel pm = new PoolSetModel(); pm.id = rd.GetInt32(0); pm.cp_pool_id = rd.GetInt32(1); pm.sp_trone_id = rd.GetInt32(2); pm.trone_id = rd.GetInt32(3); pm.trone_order_id = rd.GetInt32(4); pm.priority = rd.GetInt32(5); pm.status = rd.GetInt32(6); cache.InsertItem(pm); c++; } } } finally { if (rd != null) { rd.Dispose(); } cmd.Dispose(); } return(c); }
/// <summary> /// Returns the total sample group records count in table /// </summary> /// <returns>No of Sample group configured</returns> public int GetRowCount() { string Function_Name = "GetRowCount"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); string localSQL; int nRowCount = 0; localSQL = "SELECT COUNT(NAME) AS MAXROWS FROM DATALOG_DP_GROUP_TREND"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); if (LocationClauseStr.Length != 0) { localSQL += " WHERE" + LocationClauseStr; } System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { if (drReader != null && drReader.Read()) { if (!drReader.IsDBNull(0)) { LogHelper.Debug(CLASS_NAME, Function_Name, "Function_Exited"); nRowCount = Convert.ToInt32(drReader["MAXROWS"]); } } } 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(nRowCount); }
/// <summary> /// Get all the historical datapoint configuration names /// </summary> /// <returns></returns> public List <string> GetAllHistDPGrpNames() { string Function_Name = "GetAllHistDPGrpNames"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <string> dpGrpNames = new List <string>(); string localSQL = " SELECT DISTINCT CONFIG_NAME FROM TRENDVIEWER_HIST_CONFIG order by config_name "; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { if (!drReader.IsDBNull(0)) { dpGrpNames.Add(DAOHelper.convert8859P1ToGB2312(drReader.GetString(0).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(dpGrpNames); }
/// <summary> /// Returns a list object of AvatarArchives. This is being used for wiredux /// </summary> /// <param name="Public"></param> /// <returns></returns> public List <AvatarArchive> GetAvatarArchives(bool isPublic) { List <AvatarArchive> returnValue = new List <AvatarArchive>(); try { System.Data.IDataReader RetVal = GD.QueryData("where IsPublic = 1", "avatararchives", "Name, Snapshot, IsPublic"); while (RetVal.Read()) { AvatarArchive Archive = new AvatarArchive(); Archive.Name = RetVal["Name"].ToString(); Archive.Snapshot = RetVal["Snapshot"].ToString(); Archive.IsPublic = int.Parse(RetVal["IsPublic"].ToString()); returnValue.Add(Archive); } RetVal.Close(); RetVal.Dispose(); } catch { } GD.CloseDatabase(); return(returnValue); }
/// <summary> /// Inserts new sample group into database /// </summary> /// <param name="etyOPCGrp">new sample group entity</param> public bool InsertOPCGrpData(EtyDataLogDPGroupTrend etyDPGroupTrend) { string Function_Name = "InsertOPCGrpData"; LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - SampleGroup Name = {0}", etyDPGroupTrend.SampleGrpName)); SimpleDatabase.GetInstance().BeginTransaction(); // Get The max pkey from the database first and then insert the group with pkey value. // To avoid having incorrect pkey value between stations , hence avoid concurrency problem SqlStatements sqlQueries = new SqlStatements(); sqlQueries.OracleSql = DAOHelper.ORACLE_SEQ_FUNC_SQL; sqlQueries.MySqlStr = DAOHelper.MYSQL_SEQ_FUNC_SQL; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQueryWithSqls(sqlQueries); double nextPkey = 1; if (drReader != null && drReader.Read()) { if (drReader.FieldCount == 1) { nextPkey = Convert.ToDouble(drReader[0]); } } if (drReader != null) { drReader.Close(); drReader.Dispose(); } // // string enabled = DAOHelper.GetEnabledStr(DAOHelper.ChangeBoolToStr(etyDPGroupTrend.Disabled)); string startTime = ""; if (etyDPGroupTrend.StartTime.Length != 0) { startTime = DateTime.Parse(etyDPGroupTrend.StartTime).ToString("dd/MM/yyyy HH:mm:ss"); } string sample_grp_name = DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPGroupTrend.SampleGrpName); List <string> sqlStrings = new List <string>(); for (int i = 0; i < 2; i++) { string date_func = (i == 0) ? DAOHelper.ORACLE_DATE_FUNCTION : DAOHelper.MYSQL_DATE_FUNCTION; string date_format = (i == 0) ? DAOHelper.ORACLE_DATE_FORMAT : DAOHelper.MYSQL_DATE_FORMAT; if (startTime.Trim().Length != 0) { string sql = "INSERT INTO DATALOG_DP_GROUP_TREND " + " (PKEY,NAME,DESCRIPTION,SAMPLE_INTERVAL,START_TIME,DELTA_VALUE,ENABLED,INTERVAL_TYPE,LOCATION_KEY)" + " VALUES" + " (" + nextPkey + ",'" + sample_grp_name + "'" + ",'" + DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPGroupTrend.SampleGrpDescription) + "'" + "," + etyDPGroupTrend.Interval + "," + date_func + "('" + startTime + "','" + date_format + "')," + etyDPGroupTrend.DeltaValue + ",'" + enabled + "'" + ",'" + etyDPGroupTrend.IntervalType + "'," + LocationKeyHelper.GetInstance().LocationKey.ToString() + ")"; sqlStrings.Add(sql); } else { string sql = "INSERT INTO DATALOG_DP_GROUP_TREND " + " (PKEY,NAME,DESCRIPTION,SAMPLE_INTERVAL,DELTA_VALUE,ENABLED,INTERVAL_TYPE,LOCATION_KEY)" + " VALUES" + " (" + nextPkey + ",'" + sample_grp_name + "'" + ",'" + DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPGroupTrend.SampleGrpDescription) + "'" + "," + etyDPGroupTrend.Interval + "," + etyDPGroupTrend.DeltaValue + ",'" + enabled + "'" + ",'" + etyDPGroupTrend.IntervalType + "'," + LocationKeyHelper.GetInstance().LocationKey.ToString() + ")"; sqlStrings.Add(sql); } } List <SqlParameter> parameters = DAOHelper.CreateEnqueneParameters(sqlStrings); bool rRes = true; rRes = SimpleDatabase.GetInstance().ExecuteEnqueneProcedure(parameters); if (rRes) { rRes = DatalogConfigSettingsDAO.GetInstance().UpdateTrendVersionNum(); } if (!rRes) { SimpleDatabase.GetInstance().RollbackTransaction(); } else { SimpleDatabase.GetInstance().CommitTransaction(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(rRes); }
/// <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); }
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> // /// insert a record into TRENDVIEWER_LOG // /// </summary> // /// <param name="etyTrendLog">a record of trending log</param> // /// <returns>true/false(successful/failed)</returns> // public bool InsertTrendViewerLog(EtyTrendLog etyTrendLog) // { // string Function_Name = "InsertTrendViewerLog"; // LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); // bool result = false; // /*string localSQL = " INSERT INTO TRENDVIEWER_LOG(DATA_PT_HOST," + // "DATA_PT_SERVER,DATA_PT_NAME,DATA_PT_VALUE,DATA_PT_DATE) " + // " VALUES( '" + etyTrendLog.Data_PT_Host + "'" + // " ,'" + etyTrendLog.Data_PT_Server + "'" + // " ,'" + etyTrendLog.Data_PT_Name + "'" + // " ,'" + etyTrendLog.Data_PT_Value + "'" + // " , to_date('" + etyTrendLog.Data_PT_Time.ToString("yyyyMMddHHmmss") + "','YYYYMMDDHH24MISS') " + // " )"; // result = SimpleDatabase.getInstance().ExecuteAndReturnNonQuery(localSQL);*/ // string localSQL = " INSERT INTO TRENDVIEWER_LOG(DATA_PT_HOST," + // "DATA_PT_SERVER,DATA_PT_NAME,DATA_PT_VALUE,DATA_PT_DATE) " + // " VALUES( '" + etyTrendLog.Data_PT_Host + "'" + // " ,'" + etyTrendLog.Data_PT_Server + "'" + // " ,'" + etyTrendLog.Data_PT_Name + "'" + // " ,'" + etyTrendLog.Data_PT_Value + "'" + // " ," + SimpleDatabase.GetInstance().GetParameterDelimiter() + "DateValue)"; // SqlParameter parameter = new SqlParameter(); // parameter.ParameterName = "DateValue"; // parameter.DbType = DbType.DateTime; // parameter.Value = etyTrendLog.Data_PT_Time; // parameter.Direction = System.Data.ParameterDirection.Input; // List<SqlParameter> parameters = new List<SqlParameter>(); // parameters.Add(parameter); // result = SimpleDatabase.GetInstance().ExecuteNonQueryWithParams(localSQL, parameters); // // LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); // return result; // } /// <summary> /// get dp log list for a historical dp to plot /// </summary> /// <param name="histDP">the given historical dp for charting</param> /// <param name="startTime">start time</param> /// <param name="endTime">end time</param> /// <returns>trend log list</returns> public List <EtyDataLogDPLogTrend> GetHistDPLogList(EtyHistDataPoint histDP, DateTime startTime, DateTime endTime) { string Function_Name = "GetHistDPLogList"; List <EtyDataLogDPLogTrend> res = new List <EtyDataLogDPLogTrend>(); string localSQL; //changed by luxiangmei, to make the logic for historical mode the same as mixed mode. // if (usedInMixMode) //used in mixed mode // { localSQL = " SELECT CREATETIME,VALUE " + " FROM DATALOG_DP_LOG_TREND WHERE ENTITY_KEY = " + histDP.EntityKey + " AND CREATETIME >= @StartDateValue" + " AND CREATETIME <= @EndDateValue" + " ORDER BY CREATETIME"; SqlParameter parameter1 = new SqlParameter(); parameter1.ParameterName = "StartDateValue"; parameter1.DbType = DbType.DateTime; DateTime dtStart = new DateTime(histDP.DPStartDateTime.Year, histDP.DPStartDateTime.Month, histDP.DPStartDateTime.Day, startTime.Hour, startTime.Minute, startTime.Second); parameter1.Value = dtStart; parameter1.Direction = System.Data.ParameterDirection.Input; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(parameter1); SqlParameter parameter2 = new SqlParameter(); parameter2.ParameterName = "EndDateValue"; parameter2.DbType = DbType.DateTime; DateTime dtEnd = new DateTime(histDP.DPEndDateTime.Year, histDP.DPEndDateTime.Month, histDP.DPEndDateTime.Day, endTime.Hour, endTime.Minute, endTime.Second); parameter2.Value = dtEnd; parameter2.Direction = System.Data.ParameterDirection.Input; parameters.Add(parameter2); System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQueryWithParams(localSQL, parameters); try { while (drReader != null && drReader.Read()) { EtyDataLogDPLogTrend entity = new EtyDataLogDPLogTrend(); if (!drReader.IsDBNull(drReader.GetOrdinal("CREATETIME"))) { DateTime dt; if (DateTime.TryParse(drReader["CREATETIME"].ToString(), out dt)) { entity.CreateTime = dt; } } if (!drReader.IsDBNull(drReader.GetOrdinal("VALUE"))) { entity.Value = Convert.ToDouble(drReader["VALUE"]); } res.Add(entity); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } return(res); }
/// <summary> /// get train timetable data /// </summary> /// <param name="train id">train id</param> /// <param name="isPlannedData">is planned data</param> /// <returns>train time table</returns> public List <TrainTimeTableData> GetTrainTimeTableData(bool isPlanned, int TrainServiceId, DateTime Date) { string FUNCTION_NAME = "GetTrainTimeTableData"; LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(), EDebugLevelManaged.DebugInfo, "Function Entered"); List <TrainTimeTableData> res; res = GetCacheTrainTimeTableData(isPlanned, TrainServiceId); if (res != null) { LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(), EDebugLevelManaged.DebugInfo, "Returning Cache Data"); return(res); } //if data returned is null, get data from database res = new List <TrainTimeTableData>(); string localSQL; if (isPlanned) { localSQL = " SELECT SERVICE_ID,ORDER_ID as STATION_ORDER_ID,A_TIME,D_TIME, DISPLAY_NAME " + " FROM ATS_PLANNED_TRAIN_DATA, LOCATION WHERE " + " SERVICE_ID = @TrainServiceId" + " AND D_TIME >= @StartDateValue" + " AND A_TIME <= @EndDateValue" + " AND STATION_ID = PKEY" + " AND STATION_ID NOT IN ( " + m_ExcludeStationList + " ) ORDER BY SERVICE_ID, A_TIME"; } else { localSQL = " SELECT SERVICE_ID,ORDER_ID as STATION_ORDER_ID,A_TIME,D_TIME, DISPLAY_NAME" + " FROM ATS_PRACTICAL_TRAIN_DATA, LOCATION WHERE " + " SERVICE_ID = @TrainServiceId" + " AND D_TIME >= @StartDateValue" + " AND A_TIME <= @EndDateValue" + " AND STATION_ID = PKEY" + " AND STATION_ID NOT IN ( " + m_ExcludeStationList + " ) ORDER BY SERVICE_ID, A_TIME"; } SqlParameter parameter1 = new SqlParameter(); parameter1.ParameterName = "TrainServiceId"; parameter1.DbType = DbType.Int32; parameter1.Value = TrainServiceId; parameter1.Direction = System.Data.ParameterDirection.Input; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(parameter1); SqlParameter parameter2 = new SqlParameter(); parameter2.ParameterName = "StartDateValue"; parameter2.DbType = DbType.DateTime; parameter2.Value = Date; parameter2.Direction = System.Data.ParameterDirection.Input; parameters.Add(parameter2); SqlParameter parameter3 = new SqlParameter(); parameter3.ParameterName = "EndDateValue"; parameter3.DbType = DbType.DateTime; parameter3.Value = Date.AddHours(23).AddMinutes(59).AddSeconds(59); parameter3.Direction = System.Data.ParameterDirection.Input; parameters.Add(parameter3); System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQueryWithParams(localSQL, parameters); try { while (drReader != null && drReader.Read()) { TrainTimeTableData timeTableData = new TrainTimeTableData(); if (!drReader.IsDBNull(drReader.GetOrdinal("A_TIME"))) { DateTime dt; if (DateTime.TryParse(drReader["A_TIME"].ToString(), out dt)) { timeTableData.ArrTime = dt; } } if (!drReader.IsDBNull(drReader.GetOrdinal("D_TIME"))) { DateTime dt; if (DateTime.TryParse(drReader["D_TIME"].ToString(), out dt)) { timeTableData.DeptTime = dt; } } if (!drReader.IsDBNull(drReader.GetOrdinal("SERVICE_ID"))) { timeTableData.TrainServiceId = Convert.ToInt32(drReader["SERVICE_ID"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("STATION_ORDER_ID"))) { timeTableData.StationID = Convert.ToInt32(drReader["STATION_ORDER_ID"]); } if (!drReader.IsDBNull(drReader.GetOrdinal("DISPLAY_NAME"))) { timeTableData.StationName = Convert.ToString(drReader["DISPLAY_NAME"]); } res.Add(timeTableData); } } catch (System.Exception ex) { LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(), EDebugLevelManaged.DebugInfo, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(), EDebugLevelManaged.DebugInfo, "Function Entered"); return(res); }
/// <summary> /// get dp log list for a historical dp to plot /// </summary> /// <param name="histDP">the given historical dp for charting</param> /// <param name="startTime">start time</param> /// <param name="endTime">end time</param> /// <returns>trend log list</returns> public List <EtyTrendLog> GetHistDPLogList(EtyHistDataPoint histDP, DateTime startTime, DateTime endTime) { string Function_Name = "GetHistDPLogList"; List <EtyTrendLog> res = new List <EtyTrendLog>(); string localSQL; //changed by luxiangmei, to make the logic for historical mode the same as mixed mode. // if (usedInMixMode) //used in mixed mode // { localSQL = " SELECT DATA_PT_DATE,DATA_PT_VALUE " + " FROM TRENDVIEWER_LOG WHERE DATA_PT_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(histDP.DPName) + "' " + " AND DATA_PT_SERVER = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(histDP.DPServer) + "' " + " AND DATA_PT_HOST = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(histDP.DPHost) + "' " + " AND DATA_PT_DATE >= @StartDateValue" + " AND DATA_PT_DATE <= @EndDateValue" + " ORDER BY DATA_PT_DATE"; // } // else // used in history mode, will ignore "startTime" and "endTime" // { // localSQL = " SELECT DATA_PT_DATE,DATA_PT_VALUE " + // " FROM TRENDVIEWER_LOG WHERE DATA_PT_NAME = '" + DAOHelper.convertGB2312To8859P1(histDP.DPName) + "' " + // " AND DATA_PT_SERVER = '" + DAOHelper.convertGB2312To8859P1(histDP.DPServer) + "' " + // " AND DATA_PT_HOST = '" + DAOHelper.convertGB2312To8859P1(histDP.DPHost) + "' " + // " AND DATA_PT_DATE >= to_date(" + histDP.DPStartDateTime.ToString("yyyyMMdd") + // "000000,'YYYYMMDDHH24MISS') " + // " AND DATA_PT_DATE <= to_date(" + histDP.DPEndDateTime.ToString("yyyyMMdd") + // "235959,'YYYYMMDDHH24MISS') " + // " ORDER BY DATA_PT_DATE"; // } SqlParameter parameter1 = new SqlParameter(); parameter1.ParameterName = "StartDateValue"; parameter1.DbType = DbType.DateTime; DateTime dtStart = new DateTime(histDP.DPStartDateTime.Year, histDP.DPStartDateTime.Month, histDP.DPStartDateTime.Day, startTime.Hour, startTime.Minute, startTime.Second); parameter1.Value = dtStart; parameter1.Direction = System.Data.ParameterDirection.Input; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(parameter1); SqlParameter parameter2 = new SqlParameter(); parameter2.ParameterName = "EndDateValue"; parameter2.DbType = DbType.DateTime; DateTime dtEnd = new DateTime(histDP.DPEndDateTime.Year, histDP.DPEndDateTime.Month, histDP.DPEndDateTime.Day, endTime.Hour, endTime.Minute, endTime.Second); parameter2.Value = dtEnd; parameter2.Direction = System.Data.ParameterDirection.Input; parameters.Add(parameter2); System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQueryWithParams(localSQL, parameters); try { while (drReader != null && drReader.Read()) { EtyTrendLog entity = new EtyTrendLog(); if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_DATE"))) { DateTime dt; if (DateTime.TryParse(drReader["DATA_PT_DATE"].ToString(), out dt)) { entity.Data_PT_Time = dt; } } if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_VALUE"))) { entity.Data_PT_Value = Convert.ToDouble(drReader["DATA_PT_VALUE"]); } res.Add(entity); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } 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); }
public void Dispose() { _innerReader.Dispose(); }