Ejemplo n.º 1
0
 public bool GetLastData(ref Nullable <Decimal> lastVoltage, ref Nullable <DateTime> lastDayTime, ref Nullable <EChannelType> lastChannelType, ref Nullable <EMessageType> lastMessageType, string stationId)
 {
     // 获取计算雨量值所需的数据
     try
     {
         // 获取最近一条的雨量值
         string sql = string.Format("select top 1 * from {0} where {1} = '{2}' order by {3} desc;",
                                    CT_TableName,
                                    CN_StationId, stationId,
                                    CN_DataTime);
         SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
         DataTable      dataTableTmp = new DataTable();
         adapter.Fill(dataTableTmp);
         if (dataTableTmp.Rows.Count > 0)
         {
             lastVoltage     = Decimal.Parse(dataTableTmp.Rows[0][CN_Voltage].ToString());
             lastDayTime     = DateTime.Parse(dataTableTmp.Rows[0][CN_DataTime].ToString());
             lastChannelType = CEnumHelper.DBStrToChannelType(dataTableTmp.Rows[0][CN_TransType].ToString());
             lastMessageType = CEnumHelper.DBStrToMessageType(dataTableTmp.Rows[0][CN_MsgType].ToString());
         }
         else
         {
             //      Debug.WriteLine(string.Format("查询电压表为空,站点{0}", stationId));
         }
         return(true);
     }
     catch (System.Exception ex)
     {
         Debug.WriteLine(ex.ToString());
         return(false);
     }
     finally
     {
     }
 }
Ejemplo n.º 2
0
        protected void ExecuteNonQuery(String sqlText)
        {
            var sqlConn = CDBManager.GetInstacne().GetConnection();

            try
            {
                m_mutexWriteToDB.WaitOne();         // 取对数据库的唯一访问权
                //m_mutexDataTable.WaitOne();         // 获取内存表的访问权
                sqlConn.Open();                     // 建立数据库连接

                //  查询
                SqlCommand sqlCmd = new SqlCommand(sqlText, sqlConn);
                sqlCmd.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                sqlConn.Close();                    //  关闭数据库连接
                m_mutexDataTable.ReleaseMutex();    //  释放内存表的访问权
                m_mutexWriteToDB.ReleaseMutex();    //  释放数据库的访问权
            }
        }
Ejemplo n.º 3
0
        public List <CEntityVoltage> QueryForRateMonthTable(CEntityStation station, DateTime startTime, DateTime endTime)
        {
            List <CEntityVoltage> results = new List <CEntityVoltage>();
            string stationID = station.StationID;

            try
            {
                CTF_TableName = "voltage" + startTime.Year.ToString() + startTime.Month.ToString() + (startTime.Day < 15 ? "A" : "B");
                CTT_TableName = "voltage" + endTime.Year.ToString() + endTime.Month.ToString() + (endTime.Day < 15 ? "A" : "B");
                string         sql          = "select * from " + CF_TableName + " where stationid = " + stationID + " and convert(VARCHAR, " + CN_DataTime + ", 120) LIKE '%00:00%' and datatime between '" + startTime + "' and '" + endTime + "' and messagetype = 8;";
                SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
                DataTable      dataTableTmp = new DataTable();
                adapter.Fill(dataTableTmp);
                for (int i = 0; i < dataTableTmp.Rows.Count; ++i)
                {
                    CEntityVoltage res = new CEntityVoltage();
                    res.TimeCollect = DateTime.Parse(dataTableTmp.Rows[i][CN_DataTime].ToString());
                    res.type        = dataTableTmp.Rows[i][CN_TransType].ToString();
                    results.Add(res);
                }
            }
            catch
            {
            }
            return(results);
        }
Ejemplo n.º 4
0
        // 执行当前的SQL命令
        protected bool ExecuteSQLCommand(string cmd)
        {
            // 获取对数据的的实例唯一访问权
            //m_mutexWriteToDB.WaitOne();
            SqlConnection conn = CDBManager.GetInstacne().GetConnection();

            try
            {
                conn.Open();
                SqlCommand command = conn.CreateCommand();
                command.CommandText = cmd;
                int lines = command.ExecuteNonQuery();
                //CDBLog.Instance.AddInfo("插入12条数据到水位表");
                Debug.WriteLine("{0}行收到影响", lines);
                return(true);
            }
            catch (System.Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }
            finally
            {
                //m_mutexWriteToDB.ReleaseMutex();
                conn.Close();
            }
            return(false);
        }
Ejemplo n.º 5
0
        // 将当前所有数据写入数据库
        protected override bool AddDataToDB()
        {
            // 先获取对数据库的唯一访问权
            m_mutexWriteToDB.WaitOne();

            // 然后获取内存表的访问权
            m_mutexDataTable.WaitOne();

            if (m_tableDataAdded.Rows.Count <= 0)
            {
                m_mutexDataTable.ReleaseMutex();
                m_mutexWriteToDB.ReleaseMutex();
                return(true);
            }
            //清空内存表的所有内容,把内容复制到临时表tmp中
            DataTable tmp = m_tableDataAdded.Copy();

            m_tableDataAdded.Rows.Clear();

            // 释放内存表的互斥量
            m_mutexDataTable.ReleaseMutex();

            try
            {
                //将临时表中的内容写入数据库
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(CDBManager.GetInstacne().GetConnectionString()))
                {
                    bulkCopy.DestinationTableName = CT_TableName;
                    bulkCopy.BatchSize            = 1;
                    bulkCopy.BulkCopyTimeout      = 1800;
                    //bulkCopy.ColumnMappings.Add(CN_RainID, CN_RainID);
                    bulkCopy.ColumnMappings.Add(CN_PortNumber, CN_PortNumber);
                    bulkCopy.ColumnMappings.Add(CN_TransType, CN_TransType);
                    bulkCopy.ColumnMappings.Add(CN_Baudrate, CN_Baudrate);
                    bulkCopy.ColumnMappings.Add(CN_Databit, CN_Databit);
                    bulkCopy.ColumnMappings.Add(CN_Stopbit, CN_Stopbit);
                    bulkCopy.ColumnMappings.Add(CN_Parity, CN_Parity);
                    bulkCopy.ColumnMappings.Add(CN_Stream, CN_Stream);
                    bulkCopy.ColumnMappings.Add(CN_Break, CN_Break);
                    bulkCopy.ColumnMappings.Add(CN_Open, CN_Open);
                    try
                    {
                        bulkCopy.WriteToServer(tmp);
                    }
                    catch (Exception e)
                    {
                        Debug.WriteLine(e.ToString());
                    }
                }
            }
            catch (System.Exception ex)
            {
                Debug.WriteLine(ex.ToString());
                m_mutexWriteToDB.ReleaseMutex();
                return(false);
            }
            Debug.WriteLine("###{0} :add {1} lines to serial port db", DateTime.Now, tmp.Rows.Count);
            m_mutexWriteToDB.ReleaseMutex();
            return(true);
        }
Ejemplo n.º 6
0
        //1009gm
        public List <CEntityVoltage> QueryForRateTable(CEntityStation station, DateTime date)
        {
            List <CEntityVoltage> results = new List <CEntityVoltage>();
            DateTime startTime            = date;
            DateTime endTime   = startTime.AddHours(23).AddMinutes(59).AddSeconds(59);
            string   stationID = station.StationID;

            try
            {
                string         sql          = "select * from " + CT_TableName + " where stationid = " + stationID + " and convert(VARCHAR, " + CN_DataTime + ", 120) LIKE '%00:00%' and datatime between '" + startTime + "' and '" + endTime + "'and messageType = 8;";
                SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
                DataTable      dataTableTmp = new DataTable();
                adapter.Fill(dataTableTmp);
                for (int i = 0; i < dataTableTmp.Rows.Count; ++i)
                {
                    CEntityVoltage res = new CEntityVoltage();
                    res.TimeCollect = DateTime.Parse(dataTableTmp.Rows[i][CN_DataTime].ToString());
                    res.type        = dataTableTmp.Rows[i][CN_TransType].ToString();
                    results.Add(res);
                }
            }
            catch
            {
            }
            return(results);
        }
Ejemplo n.º 7
0
        public List <CEntityEva> GetPageData(int pageIndex, bool irefresh)
        {
            if (pageIndex <= 0 || m_startTime == null || m_endTime == null || m_strStaionId == null)
            {
                return(new List <CEntityEva>());
            }
            // 获取某一页的数据,判断所需页面是否在内存中有值
            int startIndex = (pageIndex - 1) * CDBParams.GetInstance().UIPageRowCount + 1;
            int key        = (int)(startIndex / CDBParams.GetInstance().DBPageRowCount) + 1; //对应于数据库中的索引
            int startRow   = startIndex - (key - 1) * CDBParams.GetInstance().DBPageRowCount - 1;

            Debug.WriteLine("Eva startIndex;{0} key:{1} startRow:{2}", startIndex, key, startRow);
            // 判断MAP中是否有值
            if (m_mapDataTable.ContainsKey(key) && !irefresh)
            {
                // 从内存中读取
                return(CopyDataToList(key, startRow));
            }
            else if (m_mapDataTable.ContainsKey(key) && irefresh)
            {
                m_mapDataTable.Remove(key);
                // 从数据库中查询
                int    topcount = key * CDBParams.GetInstance().DBPageRowCount;
                int    rowidmim = topcount - CDBParams.GetInstance().DBPageRowCount;
                string sql      = " select * from ( " +
                                  "select top " + topcount.ToString() + " row_number() over( order by " + CN_DataTime + " ) as " + CN_RowId + ",* " +
                                  "from " + CT_TableName + " " +
                                  "where " + CN_StationId + "=" + m_strStaionId.ToString() + " " +
                                  "and " + TimeSelectString + CN_DataTime + " between " + DateTimeToDBStr(m_startTime) +
                                  "and " + DateTimeToDBStr(m_endTime) +
                                  ") as tmp1 " +
                                  "where " + CN_RowId + ">" + rowidmim.ToString() +
                                  " order by " + CN_DataTime + " DESC";
                SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
                DataTable      dataTableTmp = new DataTable();
                adapter.Fill(dataTableTmp);
                m_mapDataTable.Add(key, dataTableTmp);
                return(CopyDataToList(key, startRow));
            }
            else
            {
                // 从数据库中查询
                int    topcount = key * CDBParams.GetInstance().DBPageRowCount;
                int    rowidmim = topcount - CDBParams.GetInstance().DBPageRowCount;
                string sql      = " select * from ( " +
                                  "select top " + topcount.ToString() + " row_number() over( order by " + CN_DataTime + " ) as " + CN_RowId + ",* " +
                                  "from " + CT_TableName + " " +
                                  "where " + CN_StationId + "=" + m_strStaionId.ToString() + " " +
                                  "and " + TimeSelectString + CN_DataTime + " between " + DateTimeToDBStr(m_startTime) +
                                  "and " + DateTimeToDBStr(m_endTime) +
                                  ") as tmp1 " +
                                  "where " + CN_RowId + ">" + rowidmim.ToString() +
                                  " order by " + CN_DataTime + " DESC";
                SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
                DataTable      dataTableTmp = new DataTable();
                adapter.Fill(dataTableTmp);
                m_mapDataTable.Add(key, dataTableTmp);
                return(CopyDataToList(key, startRow));
            }
        }
Ejemplo n.º 8
0
        protected override bool AddDataToDB()
        {
            // 然后获取内存表的访问权
            m_mutexDataTable.WaitOne();

            if (m_tableDataAdded.Rows.Count <= 0)
            {
                m_mutexDataTable.ReleaseMutex();
                return(true);
            }
            //清空内存表的所有内容,把内容复制到临时表tmp中
            DataTable tmp = m_tableDataAdded.Copy();

            m_tableDataAdded.Rows.Clear();

            // 释放内存表的互斥量
            m_mutexDataTable.ReleaseMutex();

            // 先获取对数据库的唯一访问权
            //m_mutexWriteToDB.WaitOne();

            try
            {
                //将临时表中的内容写入数据库
                //SqlConnection conn = CDBManager.GetInstacne().GetConnection();
                //conn.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(CDBManager.GetInstacne().GetConnectionString()))
                {
                    // 雨量表有插入触发器,如果遇到重复记录,则更新为当前的最新记录
                    bulkCopy.BatchSize            = 1;
                    bulkCopy.BulkCopyTimeout      = 1800;
                    bulkCopy.DestinationTableName = CDBSQLTSRain.CT_TableName;
                    bulkCopy.ColumnMappings.Add(CN_StationId, CN_StationId);
                    bulkCopy.ColumnMappings.Add(CN_DataTime, CN_DataTime);
                    bulkCopy.ColumnMappings.Add(CN_TotalRain, CN_TotalRain);
                    bulkCopy.ColumnMappings.Add(CN_TransType, CN_TransType);
                    bulkCopy.ColumnMappings.Add(CN_MsgType, CN_MsgType);
                    bulkCopy.ColumnMappings.Add(CN_RecvDataTime, CN_RecvDataTime);
                    try
                    {
                        bulkCopy.WriteToServer(tmp);
                    }
                    catch (Exception e)
                    {
                        Debug.WriteLine(e.ToString());
                    }
                }
                //conn.Close();   //关闭连接
            }
            catch (System.Exception ex)
            {
                Debug.WriteLine(ex.ToString());
                //  m_mutexWriteToDB.ReleaseMutex();
                return(false);
            }
            Debug.WriteLine("###{0} :add {1} lines to rain db", DateTime.Now, tmp.Rows.Count);
            CDBLog.Instance.AddInfo(string.Format("添加{0}行到调试雨量表", tmp.Rows.Count));
            //  m_mutexWriteToDB.ReleaseMutex();
            return(true);
        }
Ejemplo n.º 9
0
        public List <DateTime> QueryRecordByStationIdAndPeriod(string stationId, DateTime startTime,
                                                               DateTime endTime)
        {
            List <DateTime> results = new List <DateTime>();

            try
            {
                string sql = string.Format("select {0} from {1} where {2} = '{3}' and {4} between {5} and {6}",
                                           CN_DataTime,
                                           CT_TableName,
                                           CN_StationID, stationId,
                                           CN_DataTime, DateTimeToDBStr(startTime), DateTimeToDBStr(endTime)
                                           );
                SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
                DataTable      dataTableTmp = new DataTable();
                adapter.Fill(dataTableTmp);
                for (int i = 0; i < dataTableTmp.Rows.Count; ++i)
                {
                    DateTime time = DateTime.Parse(dataTableTmp.Rows[i][CN_DataTime].ToString());
                    results.Add(time);
                }
            }
            catch (System.Exception ex)
            {
                Debug.WriteLine(ex.ToString());
                return(null);
            }
            return(results);
        }
Ejemplo n.º 10
0
        public List <CEntityWarningInfo> QueryWarningInfo(DateTime startTime, DateTime endTime)
        {
            string sql = string.Format("select * from {0} where {1} between {2} and {3};",
                                       CT_TableName,
                                       CN_DataTime,
                                       base.DateTimeToDBStr(startTime), base.DateTimeToDBStr(endTime));

            try
            {
                SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
                DataTable      dataTableTmp = new DataTable();
                adapter.Fill(dataTableTmp);
                List <CEntityWarningInfo> results = new List <CEntityWarningInfo>();
                for (int i = 0; i < dataTableTmp.Rows.Count; ++i)
                {
                    CEntityWarningInfo entity = new CEntityWarningInfo();
                    entity.DataTime      = DateTime.Parse(dataTableTmp.Rows[i][CN_DataTime].ToString());
                    entity.InfoDetail    = dataTableTmp.Rows[i][CN_InfoDetail].ToString();
                    entity.WarningInfoID = long.Parse(dataTableTmp.Rows[i][CN_InfoID].ToString());
                    results.Add(entity);
                }
                return(results);
            }
            catch (System.Exception ex)
            {
                Debug.WriteLine("QueryWarningInfo Exception\n{0}", ex.ToString());
                return(null);
            }
        }
Ejemplo n.º 11
0
        public int GetCountOfRecordByStationIdAndPeriod(string stationId, DateTime startTime,
                                                        DateTime endTime)
        {
            int iRecordCount = 0;

            try
            {
                string sql = string.Format("select count(*) from {1} where {2} = '{3}' and {4} between {5} and {6}",
                                           CN_DataTime,
                                           CT_TableName,
                                           CN_StationID, stationId,
                                           CN_DataTime, DateTimeToDBStr(startTime), DateTimeToDBStr(endTime)
                                           );
                SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
                DataTable      dataTableTmp = new DataTable();
                adapter.Fill(dataTableTmp);
                if (dataTableTmp.Rows.Count == 1)
                {
                    iRecordCount = int.Parse(dataTableTmp.Rows[0][0].ToString());
                }
                else
                {
                    // 不可能,应该有错误
                    Debug.WriteLine("GetCountOfRecordByStationIdAndPeriod Error");
                }
            }
            catch (System.Exception ex)
            {
                Debug.WriteLine(ex.ToString());
                return(-1);
            }
            return(iRecordCount);
        }
Ejemplo n.º 12
0
        //1009gm
        public List <CEntityWater> QueryA(string station, DateTime time)
        {
            List <CEntityWater> results   = new List <CEntityWater>();
            DateTime            startTime = time;
            DateTime            endTime   = startTime.AddHours(23).AddMinutes(59).AddSeconds(59);

            CTF_TableName = "water" + time.Year.ToString() + time.Month.ToString() + "A";
            CTT_TableName = "water" + time.Year.ToString() + time.Month.ToString() + "B";
            String         sql           = "select * from " + CF_TableName + " where StationID=" + station + " and Datatime between '" + startTime + "'and '" + endTime + "';";
            SqlDataAdapter adapter       = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTemp = new DataTable();

            adapter.Fill(dataTableTemp);
            int flag = dataTableTemp.Rows.Count;

            if (flag == 0)
            {
            }
            else
            {
                for (int rowid = 0; rowid < dataTableTemp.Rows.Count; ++rowid)
                {
                    CEntityWater water = new CEntityWater();
                    water.StationID   = dataTableTemp.Rows[rowid][CN_StationId].ToString();
                    water.TimeCollect = DateTime.Parse(dataTableTemp.Rows[rowid][CN_DataTime].ToString());
                    water.WaterStage  = decimal.Parse(dataTableTemp.Rows[rowid][CN_WaterStage].ToString());
                    results.Add(water);
                }
            }
            return(results);
        }
Ejemplo n.º 13
0
        /// <summary>
        /// 2018-11-24 gm
        /// 根据开始时间和结束时间查询水位信息
        /// </summary>
        /// <param name="station"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List <CEntityWater> QueryByTime(string station, DateTime start, DateTime end)
        {
            List <CEntityWater> results  = new List <CEntityWater>();
            String         sql           = "select * from " + CT_TableName + " where StationID=" + station + " and Datatime between '" + start + "'and '" + end + "';";
            SqlDataAdapter adapter       = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTemp = new DataTable();

            adapter.Fill(dataTableTemp);
            int flag = dataTableTemp.Rows.Count;

            if (flag == 0)
            {
            }
            else
            {
                for (int rowid = 0; rowid < dataTableTemp.Rows.Count; ++rowid)
                {
                    CEntityWater water = new CEntityWater();
                    water.StationID   = dataTableTemp.Rows[rowid][CN_StationId].ToString();
                    water.WaterStage  = decimal.Parse(dataTableTemp.Rows[rowid][CN_WaterStage].ToString());
                    water.TimeCollect = DateTime.Parse(dataTableTemp.Rows[rowid][CN_DataTime].ToString());
                    results.Add(water);
                }
            }
            return(results);
        }
Ejemplo n.º 14
0
        public bool GetMinDataTime(ref DateTime time)
        {
            // 获取数据表中最早的记录时间
            string sql = string.Format("select top 1 {0} from {1} order by {2};",
                                       CN_DataTime,
                                       CT_TableName, CN_DataTime);

            m_mutexWriteToDB.WaitOne();
            try
            {
                SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
                DataTable      dataTableTmp = new DataTable();
                adapter.Fill(dataTableTmp);
                if (dataTableTmp.Rows.Count > 0)
                {
                    time = DateTime.Parse(dataTableTmp.Rows[0][CN_DataTime].ToString());
                    return(true);
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString());
            }
            finally
            {
                m_mutexWriteToDB.ReleaseMutex();
            }
            return(false);
        }
Ejemplo n.º 15
0
        /// <summary>
        /// 根据站点ID和时间查询
        /// </summary>
        /// <param name="stationid"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List <CEntityEva> getEvabyTime(string stationid, DateTime start, DateTime end)
        {
            List <CEntityEva> evaList       = new List <CEntityEva>();
            String            sql           = "select * from " + CT_TableName + " where STCD=" + stationid + " and DT between '" + start + "'and '" + end + "';";
            SqlDataAdapter    adapter       = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable         dataTableTemp = new DataTable();

            adapter.Fill(dataTableTemp);
            int flag = dataTableTemp.Rows.Count;

            if (flag == 0)
            {
                return(null);
            }
            else
            {
                for (int rowid = 0; rowid < dataTableTemp.Rows.Count; ++rowid)
                {
                    CEntityEva eva = new CEntityEva();
                    eva.StationID   = dataTableTemp.Rows[rowid][CN_StationId].ToString();
                    eva.TimeCollect = DateTime.Parse(dataTableTemp.Rows[rowid][CN_DataTime].ToString());
                    if (dataTableTemp.Rows[rowid][CN_Eva] != null && dataTableTemp.Rows[rowid][CN_Eva].ToString() != "")
                    {
                        eva.E = decimal.Parse(dataTableTemp.Rows[rowid][CN_Eva].ToString());
                    }
                    else
                    {
                        eva.E = null;
                    }
                    if (dataTableTemp.Rows[rowid][CN_Rain] != null && dataTableTemp.Rows[rowid][CN_Rain].ToString() != "")
                    {
                        eva.P = decimal.Parse(dataTableTemp.Rows[rowid][CN_Rain].ToString());
                    }
                    else
                    {
                        eva.P = null;
                    }
                    if (dataTableTemp.Rows[rowid][CN_Rain8] != null && dataTableTemp.Rows[rowid][CN_Rain8].ToString() != "")
                    {
                        eva.P8 = decimal.Parse(dataTableTemp.Rows[rowid][CN_Rain8].ToString());
                    }
                    else
                    {
                        eva.P8 = null;
                    }
                    if (dataTableTemp.Rows[rowid][CN_Rain20] != null && dataTableTemp.Rows[rowid][CN_Rain20].ToString() != "")
                    {
                        eva.P20 = decimal.Parse(dataTableTemp.Rows[rowid][CN_Rain20].ToString());
                    }
                    else
                    {
                        eva.P20 = null;
                    }
                    evaList.Add(eva);
                }
            }
            return(evaList);
        }
Ejemplo n.º 16
0
        // 将当前所有数据写入数据库
        protected override bool AddDataToDB()
        {
            // 先获取对数据库的唯一访问权
            m_mutexWriteToDB.WaitOne();

            // 然后获取内存表的访问权
            m_mutexDataTable.WaitOne();

            if (m_tableDataAdded.Rows.Count <= 0)
            {
                m_mutexDataTable.ReleaseMutex();
                m_mutexWriteToDB.ReleaseMutex();
                return(true);
            }
            //清空内存表的所有内容,把内容复制到临时表tmp中
            DataTable tmp = m_tableDataAdded.Copy();

            m_tableDataAdded.Rows.Clear();

            // 释放内存表的互斥量
            m_mutexDataTable.ReleaseMutex();

            try
            {
                //将临时表中的内容写入数据库
                SqlConnection conn = CDBManager.GetInstacne().GetConnection();
                conn.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.BatchSize            = 1;
                    bulkCopy.BulkCopyTimeout      = 1800;
                    bulkCopy.DestinationTableName = CT_TableName;
                    //bulkCopy.ColumnMappings.Add(CN_RainID, CN_RainID);
                    bulkCopy.ColumnMappings.Add(CN_UserName, CN_UserName);
                    bulkCopy.ColumnMappings.Add(CN_Password, CN_Password);
                    bulkCopy.ColumnMappings.Add(CN_Administrator, CN_Administrator);
                    try
                    {
                        bulkCopy.WriteToServer(tmp);
                    }
                    catch (Exception e)
                    {
                        Debug.WriteLine(e.ToString());
                    }
                }
                conn.Close();   //关闭连接
            }
            catch (System.Exception ex)
            {
                Debug.WriteLine(ex.ToString());
                m_mutexWriteToDB.ReleaseMutex();
                return(false);
            }
            Debug.WriteLine("###{0} :add {1} lines to db", DateTime.Now, tmp.Rows.Count);
            m_mutexWriteToDB.ReleaseMutex();
            return(true);
        }
Ejemplo n.º 17
0
        /// <summary>
        /// 获取所有分中心信息
        /// </summary>
        public List <CEntitySerialPort> QueryAll()
        {
            var result  = new List <CEntitySerialPort>();
            var sqlConn = CDBManager.GetInstacne().GetConnection();

            try
            {
                m_mutexWriteToDB.WaitOne();         // 取对数据库的唯一访问权
                m_mutexDataTable.WaitOne();         // 获取内存表的访问权
                sqlConn.Open();                     // 建立数据库连接

                String sqlStr = GetQuerySQL();

                SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn);

                SqlDataReader reader = sqlCmd.ExecuteReader();

                Debug.Assert(reader.FieldCount == CN_FIELD_COUNT, CT_TableName + "表与类" + CT_EntityName + "中定义字段不符合");

                //  处理查询结果
                while (reader.Read())
                {
                    try
                    {
                        var port = new CEntitySerialPort();

                        port.PortNumber  = (Int32)reader[CN_PortNumber];
                        port.TransType   = CEnumHelper.DBStrToSerialTransType((String)reader[CN_TransType]);
                        port.Baudrate    = (Int32)reader[CN_Baudrate];
                        port.DataBit     = (Int32)reader[CN_Databit];
                        port.StopBit     = (Int32)reader[CN_Stopbit];
                        port.ParityType  = CEnumHelper.DBCharToPortParityType(((String)reader[CN_Parity])[0]);
                        port.Stream      = CEnumHelper.DBStrToSerialPortStreamType(reader[CN_Stream].ToString());
                        port.Break       = (Boolean)reader[CN_Break];
                        port.SwitchSatus = (Boolean)reader[CN_Open];
                        result.Add(port);
                    }
                    catch (Exception exp)
                    {
                        Debug.WriteLine(exp.Message);
                    }
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                sqlConn.Close();                    //  关闭数据库连接
                m_mutexDataTable.ReleaseMutex();    //  释放内存表的访问权
                m_mutexWriteToDB.ReleaseMutex();    //  释放数据库的访问权
            }
            return(result);
        }
Ejemplo n.º 18
0
        //1009gm CT_TableNameOld
        public List <CEntitySoilData> QueryForMonthTable(string stationId, DateTime date)
        {
            List <CEntitySoilData> results = new List <CEntitySoilData>();
            string sql = "select * from " + CT_TableName + " where Datatime in('";

            for (int j = 0; j < 23; j++)
            {
                sql  = sql + date + "','";
                date = date.AddHours(1);
            }
            sql = sql + date + "') and StationID=" + stationId + ";";
            SqlDataAdapter adapter       = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTemp = new DataTable();

            adapter.Fill(dataTableTemp);
            int flag = dataTableTemp.Rows.Count;

            if (flag == 0)
            {
            }
            else
            {
                for (int rowid = 0; rowid < dataTableTemp.Rows.Count; ++rowid)
                {
                    CEntitySoilData soilData = new CEntitySoilData();
                    object          aa       = dataTableTemp.Rows[rowid][CN_Moisture20].ToString();
                    if (dataTableTemp.Rows[rowid][CN_Moisture10].ToString() != "")
                    {
                        soilData.Moisture10 = float.Parse(dataTableTemp.Rows[rowid][CN_Moisture10].ToString());
                    }
                    else
                    {
                        soilData.Moisture10 = -1;
                    }
                    if (dataTableTemp.Rows[rowid][CN_Moisture20].ToString() != "")
                    {
                        soilData.Moisture20 = float.Parse(dataTableTemp.Rows[rowid][CN_Moisture20].ToString());
                    }
                    else
                    {
                        soilData.Moisture20 = -1;
                    }
                    if (dataTableTemp.Rows[rowid][CN_Moisture40].ToString() != "")
                    {
                        soilData.Moisture40 = float.Parse(dataTableTemp.Rows[rowid][CN_Moisture40].ToString());
                    }
                    else
                    {
                        soilData.Moisture40 = -1;
                    }
                    results.Add(soilData);
                }
            }
            return(results);
        }
Ejemplo n.º 19
0
        public string gethydlStation(string soil)
        {
            string         station;
            string         sql          = "select * from " + CS_TableName + " Where soilstation = " + soil + ";";
            SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTmp = new DataTable();

            adapter.Fill(dataTableTmp);
            station = dataTableTmp.Rows[0][1].ToString();
            return(station);
        }
Ejemplo n.º 20
0
        public List <CEntityTSRain> QueryForAll(string stationid, DateTime start, DateTime end)
        {
            List <CEntityTSRain> results = null;
            string         sql           = "select * from " + CT_TableName + " where stationid= " + stationid + " and between '" + start + "' and  '" + end + "' order by datatime desc;";
            SqlDataAdapter adapter       = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTemp = new DataTable();

            adapter.Fill(dataTableTemp);
            for (int rowid = 0; rowid < dataTableTemp.Rows.Count; ++rowid)
            {
                CEntityTSRain rain = new CEntityTSRain();
                rain.StationID   = dataTableTemp.Rows[rowid][CN_StationId].ToString();
                rain.TimeCollect = DateTime.Parse(dataTableTemp.Rows[rowid][CN_DataTime].ToString());
                if (dataTableTemp.Rows[rowid][CN_TotalRain].ToString() != "")
                {
                    rain.TotalRain = decimal.Parse(dataTableTemp.Rows[rowid][CN_TotalRain].ToString());
                }
                else
                {
                    rain.TotalRain = -9999;
                }
                try
                {
                    rain.TimeCollect = DateTime.Parse(dataTableTemp.Rows[rowid][CN_DataTime].ToString());
#pragma warning disable CS0168 // 声明了变量“e”,但从未使用过
                }catch (Exception e)
#pragma warning restore CS0168 // 声明了变量“e”,但从未使用过
                {
                }
                try
                {
                    rain.TimeRecieved = DateTime.Parse(dataTableTemp.Rows[rowid][CN_RecvDataTime].ToString());
                }
#pragma warning disable CS0168 // 声明了变量“e”,但从未使用过
                catch (Exception e)
#pragma warning restore CS0168 // 声明了变量“e”,但从未使用过
                {
                }


                try
                {
                    //rain.MessageType = dataTableTemp.Rows[rowid][CN_MsgType]));
                }
#pragma warning disable CS0168 // 声明了变量“e”,但从未使用过
                catch (Exception e)
#pragma warning restore CS0168 // 声明了变量“e”,但从未使用过
                {
                }

                results.Add(rain);
            }
            return(results);
        }
Ejemplo n.º 21
0
        // 将当前所有数据写入数据库
        protected override bool AddDataToDB()
        {
            // 先获取内存表的访问权
            m_mutexDataTable.WaitOne();

            if (m_tableDataAdded.Rows.Count <= 0)
            {
                m_mutexDataTable.ReleaseMutex();
                return(true);
            }
            //清空内存表的所有内容,把内容复制到临时表tmp中
            DataTable tmp = m_tableDataAdded.Copy();

            m_tableDataAdded.Rows.Clear();

            // 释放内存表的互斥量
            m_mutexDataTable.ReleaseMutex();

            // 再获取对数据库的唯一访问权
            m_mutexWriteToDB.WaitOne();

            try
            {
                //将临时表中的内容写入数据库
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(CDBManager.GetInstacne().GetConnectionString()))
                {
                    bulkCopy.DestinationTableName = CT_TableName;
                    bulkCopy.BatchSize            = 1;
                    bulkCopy.BulkCopyTimeout      = 1800;
                    bulkCopy.ColumnMappings.Add(CN_StationID, CN_StationID);
                    bulkCopy.ColumnMappings.Add(CN_DataTime, CN_DataTime);

                    try {
                        bulkCopy.WriteToServer(tmp);
                    } catch (Exception e) {
                        Debug.WriteLine(e.ToString());
                    }
                }
                System.Diagnostics.Debug.WriteLine("###{0} :add {1} lines to commnunicationrate", DateTime.Now, tmp.Rows.Count);
                return(true);
            }
            catch (System.Exception ex)
            {
                Debug.WriteLine(ex.ToString());
                return(false);
            }
            finally
            {
                m_mutexWriteToDB.ReleaseMutex();
            }
        }
Ejemplo n.º 22
0
        // 将当前所有数据插入数据库
        //protected override bool UpdateToDB()
        //{
        //    // 然后获取内存表的访问权
        //    m_mutexDataTable.WaitOne();

        //    if (m_tableDataUpdated.Rows.Count <= 0)
        //    {
        //        m_mutexDataTable.ReleaseMutex();
        //        return true;
        //    }
        //    //清空内存表的所有内容,把内容复制到临时表tmp中
        //    DataTable tmp = m_tableDataUpdated.Copy();
        //    m_tableDataUpdated.Rows.Clear();

        //    // 释放内存表的互斥量
        //    m_mutexDataTable.ReleaseMutex();

        //    // 先获取对数据库的唯一访问权
        //    m_mutexWriteToDB.WaitOne();

        //    try
        //    {
        //        //将临时表中的内容写入数据库
        //        //SqlConnection conn = CDBManager.GetInstacne().GetConnection();
        //        //conn.Open();
        //        string connstr = CDBManager.Instance.GetConnectionString();
        //        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connstr))
        //        {
        //            // 实时表有插入触发器
        //            bulkCopy.BatchSize = 1;
        //            bulkCopy.BulkCopyTimeout = 1800;
        //            bulkCopy.DestinationTableName = CT_TableName;
        //            bulkCopy.ColumnMappings.Add(CN_CName, CN_CName);
        //            bulkCopy.ColumnMappings.Add(CN_StationId, CN_StationId);
        //            bulkCopy.ColumnMappings.Add(CN_StationType, CN_StationType);
        //            bulkCopy.ColumnMappings.Add(CN_YesterdayRain, CN_YesterdayRain);
        //            bulkCopy.ColumnMappings.Add(CN_TodayRain, CN_TodayRain);
        //            bulkCopy.ColumnMappings.Add(CN_PeriodRain, CN_PeriodRain);
        //            bulkCopy.ColumnMappings.Add(CN_WaterStage, CN_WaterStage);
        //            bulkCopy.ColumnMappings.Add(CN_WaterFlow, CN_WaterFlow);
        //            bulkCopy.ColumnMappings.Add(CN_Voltage, CN_Voltage);
        //            bulkCopy.ColumnMappings.Add(CN_DataState, CN_DataState);
        //            bulkCopy.ColumnMappings.Add(CN_TransType, CN_TransType);
        //            bulkCopy.ColumnMappings.Add(CN_MsgType, CN_MsgType);
        //            bulkCopy.ColumnMappings.Add(CN_DataTime, CN_DataTime);
        //            try
        //            {
        //                bulkCopy.WriteToServer(tmp);
        //            }
        //            catch (Exception e)
        //            {
        //                Debug.WriteLine(e.ToString());
        //            }
        //        }
        //        //conn.Close();   //关闭连接
        //        Debug.WriteLine("###{0} :add {1} lines to currentdata db", DateTime.Now, tmp.Rows.Count);
        //        CDBLog.Instance.AddInfo(string.Format("添加{0}行到实时表", tmp.Rows.Count));
        //        return true;
        //    }
        //    catch (System.Exception ex)
        //    {
        //        Debug.WriteLine(ex.ToString());
        //        return false;
        //    }
        //    finally
        //    {
        //        //S_MutexWriteToDB.ReleaseMutex();
        //        m_mutexWriteToDB.ReleaseMutex();
        //    }

        //}

        //private static Boolean IsNew(CEntityRealTime realTime)
        //{
        //    String station = realTime.StrStationID;
        //    DateTime dateTime = realTime.TimeDeviceGained;
        //    String sql = "select * from " + CT_TableName + " where StationID='" + station + " And DataTime >= " + dateTime + "';";
        //    SqlDataAdapter adapter = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
        //    DataTable dataTableTemp = new DataTable();
        //    adapter.Fill(dataTableTemp);
        //    if (dataTableTemp.Rows.Count > 0) return true;
        //    return false;
        //}

        private static Boolean IsInDB(CEntityRealTime realTime)
        {
            String         station       = realTime.StrStationID;
            String         sql           = "select * from " + CT_TableName + " where StationID='" + station + "';";
            SqlDataAdapter adapter       = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTemp = new DataTable();

            adapter.Fill(dataTableTemp);
            if (dataTableTemp.Rows.Count > 0)
            {
                return(true);
            }
            return(false);
        }
Ejemplo n.º 23
0
        public List <CEntityStationMap> QueryAll()
        {
            var result  = new List <CEntityStationMap>();
            var sqlConn = CDBManager.GetInstacne().GetConnection();

            try
            {
                m_mutexWriteToDB.WaitOne();         // 取对数据库的唯一访问权
                m_mutexDataTable.WaitOne();         // 获取内存表的访问权
                sqlConn.Open();                     // 建立数据库连接

                String sqlStr = GetQuerySQL();

                SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn);

                SqlDataReader reader = sqlCmd.ExecuteReader();

                Debug.Assert(reader.FieldCount == CN_FIELD_COUNT, CT_TableName + "表与类" + CT_EntityName + "中定义字段不符合");

                //  处理查询结果
                while (reader.Read())
                {
                    try
                    {
                        var stationMap = new CEntityStationMap();
                        stationMap.StationID = reader[CN_StationID].ToString();
                        stationMap.StationID = reader[CN_StationID].ToString();
                        stationMap.StationID = reader[CN_StationID].ToString();
                        stationMap.StationID = reader[CN_StationID].ToString();
                        result.Add(stationMap);
                    }
                    catch (Exception exp)
                    {
                        Debug.WriteLine(exp.Message);
                    }
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                sqlConn.Close();                    //  关闭数据库连接
                m_mutexDataTable.ReleaseMutex();    //  释放内存表的访问权
                m_mutexWriteToDB.ReleaseMutex();    //  释放数据库的访问权
            }
            return(result);
        }
Ejemplo n.º 24
0
        public List <string> getAllGprs()
        {
            List <string>  results      = new List <string>();
            string         sql          = "select distinct  Gprs from " + CT_TableName + ";";
            SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTmp = new DataTable();

            adapter.Fill(dataTableTmp);
            for (int rowid = 0; rowid < dataTableTmp.Rows.Count; ++rowid)
            {
                string result = dataTableTmp.Rows[rowid][0].ToString();
                results.Add(result);
            }
            return(results);
        }
Ejemplo n.º 25
0
        public List <DateTime> getExistsTime(string stationid, DateTime startTime, DateTime endTime)
        {
            List <DateTime> results       = new List <DateTime>();
            string          sql           = "select datatime from " + CT_TableName + " where stationid= " + stationid + " and datatime between '" + startTime + "' and  '" + endTime + "' order by datatime;";
            SqlDataAdapter  adapter       = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable       dataTableTemp = new DataTable();

            adapter.Fill(dataTableTemp);
            for (int rowid = 0; rowid < dataTableTemp.Rows.Count; ++rowid)
            {
                DateTime dt = DateTime.Parse(dataTableTemp.Rows[rowid][0].ToString());
                results.Add(dt);
            }
            return(results);
        }
Ejemplo n.º 26
0
        //115 gm
        public List <CEntityStation> getAllGprs_1()
        {
            List <CEntityStation> results = new List <CEntityStation>();
            string         sql            = "select distinct  Gprs,stationid from " + CT_TableName + ";";
            SqlDataAdapter adapter        = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTmp   = new DataTable();

            adapter.Fill(dataTableTmp);
            for (int rowid = 0; rowid < dataTableTmp.Rows.Count; ++rowid)
            {
                CEntityStation result = new CEntityStation();
                result.GPRS      = dataTableTmp.Rows[rowid][0].ToString();
                result.StationID = dataTableTmp.Rows[rowid][1].ToString();
                results.Add(result);
            }
            return(results);
        }
Ejemplo n.º 27
0
        public bool checkWaterIsExists(string stationid, DateTime dt)
        {
            bool           flag          = false;
            string         sql           = "select count(waterstage) from " + CT_TableName + " where stationid= " + stationid + " and datatime=  '" + dt + "';";
            SqlDataAdapter adapter       = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTemp = new DataTable();

            adapter.Fill(dataTableTemp);
            for (int rowid = 0; rowid < dataTableTemp.Rows.Count; ++rowid)
            {
                int count = int.Parse(dataTableTemp.Rows[rowid][0].ToString());
                if (count > 0)
                {
                    flag = true;
                }
            }
            return(flag);
        }
Ejemplo n.º 28
0
        /// <summary>
        /// 获取所有分中心信息
        /// </summary>
        public List <CEntityUser> QueryAllUser()
        {
            var result  = new List <CEntityUser>();
            var sqlConn = CDBManager.GetInstacne().GetConnection();

            try
            {
                m_mutexWriteToDB.WaitOne();         // 取对数据库的唯一访问权
                m_mutexDataTable.WaitOne();         // 获取内存表的访问权
                sqlConn.Open();                     // 建立数据库连接

                String        sqlStr = "select * from " + CT_TableName;
                SqlCommand    sqlCmd = new SqlCommand(sqlStr, sqlConn);
                SqlDataReader reader = sqlCmd.ExecuteReader();

                //  处理查询结果
                while (reader.Read())
                {
                    try
                    {
                        var user = new CEntityUser();
                        user.UserID        = (Int32)reader[CN_UserID];
                        user.UserName      = (String)reader[CN_UserName];
                        user.Password      = (String)reader[CN_Password];
                        user.Administrator = (Boolean)reader[CN_Administrator];
                        result.Add(user);
                    }
                    catch
                    {
                    }
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                sqlConn.Close();                    //  关闭数据库连接
                m_mutexDataTable.ReleaseMutex();    //  释放内存表的访问权
                m_mutexWriteToDB.ReleaseMutex();    //  释放数据库的访问权
            }
            return(result);
        }
Ejemplo n.º 29
0
        //1009
        public CEntityStation QueryByGprs(string gprsID)
        {
            // List<CEntityStation> results = new List<CEntityStation>();
            CEntityStation result       = new CEntityStation();
            string         sql          = "select * from " + CT_TableName + " where " + CN_GPRS + " = '" + gprsID + "';";
            SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
            DataTable      dataTableTmp = new DataTable();

            adapter.Fill(dataTableTmp);
            if (dataTableTmp.Rows.Count == 1)
            {
                result.StationID   = dataTableTmp.Rows[0][CN_StationID].ToString();
                result.GPRS        = dataTableTmp.Rows[0][CN_GPRS].ToString();
                result.StationName = dataTableTmp.Rows[0][CN_StationName].ToString();
                result.SubCenterID = int.Parse(dataTableTmp.Rows[0][CN_SubCenterID].ToString());
                // results.Add(result);
            }
            return(result);
        }
Ejemplo n.º 30
0
        // 根据当前条件查询统计数据
        private void DoCountQuery()
        {
            string sql = "select count(*) count from " + CT_TableName + " " +
                         "where " + CN_StationId + " = " + m_strStaionId + " " +
                         "and " + TimeSelectString + CN_DataTime + "  between " + DateTimeToDBStr(m_startTime) +
                         "and " + DateTimeToDBStr(m_endTime);

            try
            {
                SqlDataAdapter adapter      = new SqlDataAdapter(sql, CDBManager.GetInstacne().GetConnection());
                DataTable      dataTableTmp = new DataTable();
                adapter.Fill(dataTableTmp);
                m_iRowCount  = Int32.Parse((dataTableTmp.Rows[0])[0].ToString());
                m_iPageCount = (int)Math.Ceiling((double)m_iRowCount / CDBParams.GetInstance().UIPageRowCount); //向上取整
            }
            catch (System.Exception ex)
            {
                Debug.WriteLine(ex.ToString());
            }
        }