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
{
}
}
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(); // 释放数据库的访问权
}
}
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);
}
// 执行当前的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);
}
// 将当前所有数据写入数据库
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);
}
//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);
}
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));
}
}
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);
}
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);
}
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);
}
}
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);
}
//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);
}
/// <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);
}
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);
}
/// <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);
}
// 将当前所有数据写入数据库
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);
}
/// <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);
}
//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);
}
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);
}
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);
}
// 将当前所有数据写入数据库
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();
}
}
// 将当前所有数据插入数据库
//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);
}
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);
}
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);
}
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);
}
//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);
}
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);
}
/// <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);
}
//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);
}
// 根据当前条件查询统计数据
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());
}
}
