public DataSet Select(DataBaseName database) { string connstr = Connectionstring.GetConnectionString(database); var sqlstr = new StringBuilder(); sqlstr.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_SET_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 14 as SAFETY_FACTOR_TYPE_ID, AngleOriginalX, AngleOriginalY, AngleOffsetX, AngleOffsetY FROM D_OriginalInclinationData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME1 order by ID ASC") .Append(";") .Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_SET_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 12 as SAFETY_FACTOR_TYPE_ID, OriginalDisplayment, OffsetDisplayment FROM D_OriginalLVDTData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME2 order by ID ASC") .Append(";") .Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 6 as SAFETY_FACTOR_TYPE_ID, OrgVoltage, HUMILITY_VALUE, Mechan_Value FROM D_OriginalMagneticFluxData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME3 order by ID ASC") .Append(";") .Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 11 as SAFETY_FACTOR_TYPE_ID, ColPressureValue, CulcPressureValue FROM D_OriginalPressureData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME4 order by ID ASC") .Append(";") .Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 3 as SAFETY_FACTOR_TYPE_ID, RainFall FROM D_OriginalRainFallData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME5 order by ID ASC") .Append(";") .Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 9 as SAFETY_FACTOR_TYPE_ID, TEMPERATURE_VALUE, HUMILITY_VALUE FROM D_OriginalTempHumiData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME6 order by ID ASC") .Append(";") .Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_SET_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 2 as SAFETY_FACTOR_TYPE_ID, Frequency_VALUE, TEMPERATURE_VALUE, PhysicalValue FROM D_OriginalVibratingWireData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME7 order by ID ASC") .Append(";") .Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 1 as SAFETY_FACTOR_TYPE_ID, OrgVoltage, displayment FROM D_OriginalVoltageData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME8 order by ID ASC") .Append(";") .Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 5 as SAFETY_FACTOR_TYPE_ID, WIND_SPEED_VALUE, WIND_DIRECTION_VALUE, WIND_ELEVATION_VALUE, TEMPERATURE_VALUE FROM D_OriginalWindData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME9 order by ID ASC"); var para = new[] { new SQLiteParameter("@ProjectCode", ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode), new SQLiteParameter("@databaseId", database.ID), new SQLiteParameter("@ACQUISITION_DATETIME1", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalInclinationData").Acqtime), new SQLiteParameter("@ACQUISITION_DATETIME2", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalLVDTData").Acqtime), new SQLiteParameter("@ACQUISITION_DATETIME3", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalMagneticFluxData").Acqtime), new SQLiteParameter("@ACQUISITION_DATETIME4", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalPressureData").Acqtime), new SQLiteParameter("@ACQUISITION_DATETIME5", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalRainFallData").Acqtime), new SQLiteParameter("@ACQUISITION_DATETIME6", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalTempHumiData").Acqtime), new SQLiteParameter("@ACQUISITION_DATETIME7", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalVibratingWireData").Acqtime), new SQLiteParameter("@ACQUISITION_DATETIME8", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalVoltageData").Acqtime), new SQLiteParameter("@ACQUISITION_DATETIME9", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalWindData").Acqtime) }; var dbhelper = new DbHelperSqLiteP(connstr); return(dbhelper.Query(sqlstr.ToString(), para)); }
/// <summary> /// 获取数据表中数据最早时间 /// </summary> /// <param name="database">数据库信息</param> /// <param name="tableinfo">表信息</param> /// <returns></returns> private string GetDefaultMinTime(DataBaseName database, TableFieldInfo tableinfo) { string retTime = "2013-01-01 00:00:00"; var connstr = Connectionstring.GetConnectionString(database); switch (database.DataBaseType) { case (int)DataBaseType.SQLite: var db1 = new DbHelperSqLiteP(connstr); var tb1 = db1.Query(string.Format("select min({0}) as mintime from {1}", tableinfo.AcqTime, tableinfo.TableName)).Tables[0]; if (tb1 != null && tb1.Rows.Count > 0) { retTime = tb1.Rows[0]["mintime"].ToString(); } break; case (int)DataBaseType.SQLServer: var db2 = new DbHelperSQLP(connstr); var tb2 = db2.Query(string.Format("select min({0}) as mintime from {1}", tableinfo.AcqTime, tableinfo.TableName)).Tables[0]; if (tb2 != null && tb2.Rows.Count > 0) { retTime = tb2.Rows[0]["mintime"].ToString(); } break; case (int)DataBaseType.ACCESSOld: case (int)DataBaseType.ACCESSNew: var db3 = new DbHelperOleDbP(connstr); var tb3 = db3.Query(string.Format("select min({0}) as mintime from {1}", tableinfo.AcqTime, tableinfo.TableName)).Tables[0]; if (tb3 != null && tb3.Rows.Count > 0) { retTime = tb3.Rows[0]["mintime"].ToString(); } break; default: break; } return(retTime); }
//读取数据库中多有表的数据到内存中 public DataSet Select(DataBaseName database) { string connstr = Connectionstring.GetConnectionString(database); StringBuilder sqlstr = new StringBuilder(); List <ConfigTable> tables = ConfigTableDic.GetConfigTableDic().SelecConfigTables((int)database.ID); for (int i = 0; i < tables.Count; i++) { string str = string.Format( "select {0} as DataBaseID, '{1}' as TableName, ", (int)database.ID, tables[i].TableName); sqlstr.Append(str) .Append("[") .Append(tables[i].SensorId) .Append("],[") .Append(tables[i].ModuleNo); if (!string.IsNullOrEmpty(tables[i].ChannelId)) { sqlstr.Append("],[").Append(tables[i].ChannelId); } if (!string.IsNullOrEmpty(tables[i].OtherFlag)) { sqlstr.Append("],[").Append(tables[i].OtherFlag); } sqlstr.Append("] from ").Append(tables[i].TableName); if (i != tables.Count - 1) { sqlstr.Append(";"); } } switch (database.DataBaseType) { case (int)DataBaseType.SQLite: try { var dbHelper = new FreeSun.Common.DB.DbHelperSqLiteP(connstr); return(dbHelper.Query(sqlstr.ToString())); } catch (Exception ex) { _logger.ErrorFormat("{0} :{1}", ex.Message, sqlstr); } return(new DataSet()); case (int)DataBaseType.SQLServer: try { var sqlhelper = new DbHelperSQLP(connstr); return(sqlhelper.Query(sqlstr.ToString())); } catch (Exception ex) { _logger.ErrorFormat("{0} :{1}", ex.Message, sqlstr); } return(new DataSet()); case (int)DataBaseType.ACCESSOld: case (int)DataBaseType.ACCESSNew: var oledbhelper = new DbHelperOleDbP(connstr); string[] sqlstrings = sqlstr.ToString().Split(';'); DataSet[] ds = new DataSet[sqlstrings.Length]; for (int i = 0; i < sqlstrings.Length; i++) { try { ds[i] = oledbhelper.Query(sqlstrings[i]); } catch (Exception ex) { _logger.ErrorFormat("{0} :{1}", ex.Message, sqlstrings[i]); } } DataSet retSet = new DataSet(); for (int i = 0; i < sqlstrings.Length; i++) { retSet.Merge(ds[i]); } return(retSet); default: return(new DataSet()); } }
private DataSet SelectAll(DataBaseName database) { string connstr = Connectionstring.GetConnectionString(database); var sqlstr = new StringBuilder(); List <TableFieldInfo> tables = TableFieldInfoDic.GetTableFieldInfoDic().GetSameDataBaseTableFieldInfos((int)database.ID); for (int i = 0; i < tables.Count; i++) { // Select CONVERT(varchar(100), GETDATE(), 25) string str = string.Format( "select {0} as ProjectCode,{1} as DataBaseNameID,{2} as sensorType,{3} as ACQUISITION_DATETIME,", ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode, database.ID, tables[i].SensorType, tables[i].AcqTime); sqlstr.Append(str); if (ConfigInfoTable.ConfigtableInfoDictionary.ContainsKey((int)database.ID)) { sqlstr.Append(tables[i].SensorID).Append(","); } else { if (!string.IsNullOrEmpty(tables[i].ModuleNo)) { sqlstr.Append(tables[i].ModuleNo).Append(","); } if (!string.IsNullOrEmpty(tables[i].ChannelId)) { sqlstr.Append(tables[i].ChannelId).Append(","); } else { sqlstr.Append(string.Format("{0} as channelId", 1)).Append(","); } if (!string.IsNullOrEmpty(tables[i].OtherFlag)) { sqlstr.Append(tables[i].OtherFlag).Append(","); } } Type t = tables[i].GetType(); for (int j = 1; j <= tables[i].ValueNameCount; j++) { System.Reflection.PropertyInfo propertyInfo = t.GetProperty(FieldNamestr + j); if (!string.IsNullOrEmpty(propertyInfo.GetValue(tables[i], null).ToString())) { sqlstr.Append(propertyInfo.GetValue(tables[i], null)).Append(","); } } sqlstr.Replace(',', ' ', sqlstr.Length - 1, 1); sqlstr.Append("from ") .Append(tables[i].TableName) .Append(" where ") .Append(tables[i].AcqTime) .Append(" >@") .Append(acqtime) .Append(i); if (i != tables.Count - 1) { sqlstr.Append(";"); } } List <ExtractionConfig> list = ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID); switch (database.DataBaseType) { case (int)DataBaseType.SQLite: var sqlitepara = new SQLiteParameter[tables.Count]; if (sqlitepara.Length > 0) { for (int i = 0; i < sqlitepara.Length; i++) { var str = new StringBuilder(); str.Append("@").Append(acqtime).Append(i); sqlitepara[i] = new SQLiteParameter(str.ToString(), this.GetLastTime(list, tables[i].TableName)); } var sqlitehelper = new DbHelperSqLiteP(connstr); try { return(sqlitehelper.Query(sqlstr.ToString(), sqlitepara)); } catch (Exception ex) { throw ex; } } break; case (int)DataBaseType.SQLServer: var sqlpara = new SqlParameter[tables.Count]; if (sqlpara.Length > 0) { for (int i = 0; i < sqlpara.Length; i++) { var str = new StringBuilder(); str.Append("@").Append(acqtime).Append(i); string time = this.GetLastTime(list, tables[i].TableName); //string timestr = null; //string[] sliptime = time.Split(new char[]{'/',' ',':'}); //if (sliptime.Count() > 1) //{ // for (int n = 0; n < sliptime.Count(); n++) // { // if (sliptime[n].Length < 8 && sliptime[n]!="") // { // if (Convert.ToInt32(sliptime[n]) < 10 && sliptime[n].Length == 1) // { // sliptime[n] = "0" + sliptime[n]; // } // } // } //} //for (int m = 0; m < sliptime.Count(); m++) //{ // timestr += sliptime[m].Trim(); //} //string[] timeformats = // { // "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss.fff", // "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.fff", "yyyyMMddHHmmss", // "yyyyMMddHHmmss.fff", "yyyy-MM-dd h:mm:ss" // }; DateTime timeTemp = Convert.ToDateTime(time).AddMilliseconds(999); //bool isSuccess = DateTime.TryParseExact( //timestr, //timeformats, //CultureInfo.CurrentCulture, //DateTimeStyles.None, //out timeTemp); //AssumeLocal //if (!isSuccess) //{ // timeTemp = Convert.ToDateTime(timestr); //} sqlpara[i] = new SqlParameter(str.ToString(), timeTemp.ToString("yyyy-MM-dd HH:mm:ss.fff")); } var sqlhelper = new DbHelperSQLP(connstr); try { return(sqlhelper.Query(sqlstr.ToString(), 300, sqlpara)); } catch (Exception ex) { throw ex; } } break; case (int)DataBaseType.ACCESSOld: case (int)DataBaseType.ACCESSNew: var olepara = new OleDbParameter[tables.Count]; if (olepara.Length > 0) { for (int i = 0; i < olepara.Length; i++) { var str = new StringBuilder(); str.Append("@").Append(acqtime).Append(i); try { string timestr = this.GetLastTime(list, tables[i].TableName); DateTime timeTemp = Convert.ToDateTime(timestr); // string[] timeformats = // { // "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss.fff", // "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.fff", // "yyyyMMddHHmmss", "yyyyMMddHHmmss.fff", "yyyy-MM-dd h:mm:ss" // }; //bool isSuccess = DateTime.TryParseExact( //timestr, //timeformats, //CultureInfo.CurrentCulture, //DateTimeStyles.None, //out timeTemp); //AssumeLocal // if (!isSuccess) // { // timeTemp = Convert.ToDateTime(timestr); // } olepara[i] = new OleDbParameter(str.ToString(), timeTemp); } catch (Exception ex) { throw ex; } } var oledbhelper = new DbHelperOleDbP(connstr); string[] sqlstrings = sqlstr.ToString().Split(';'); DataSet[] ds = new DataSet[olepara.Length]; for (int i = 0; i < olepara.Length; i++) { ds[i] = new DataSet(); } for (int i = 0; i < olepara.Length; i++) { ds[i] = oledbhelper.Query(sqlstrings[i], olepara[i]); } DataSet retSet = new DataSet(); for (int i = 0; i < olepara.Length; i++) { retSet.Merge(ds[i]); } return(retSet); //return oledbhelper.Query(sqlstr.ToString(), olepara); } break; default: return(new DataSet()); } return(new DataSet()); }
/// <summary> /// 当待提取数据超过1天(提取历史数据)时 每次提取一天的数据 /// </summary> private DataSet SelectByDay(DataBaseName database) { var connstr = Connectionstring.GetConnectionString(database); var sqlstr = new StringBuilder(); var tables = TableFieldInfoDic.GetTableFieldInfoDic().GetSameDataBaseTableFieldInfos((int)database.ID); for (var i = 0; i < tables.Count; i++) { string str = string.Format( "select {0} as ProjectCode,{1} as DataBaseNameID,{2} as sensorType,{3} as ACQUISITION_DATETIME,", ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode, database.ID, tables[i].SensorType, tables[i].AcqTime); sqlstr.Append(str); if (ConfigInfoTable.ConfigtableInfoDictionary.ContainsKey((int)database.ID)) { sqlstr.Append(tables[i].SensorID).Append(","); } else { if (!string.IsNullOrEmpty(tables[i].ModuleNo)) { sqlstr.Append(tables[i].ModuleNo).Append(","); } if (!string.IsNullOrEmpty(tables[i].ChannelId)) { sqlstr.Append(tables[i].ChannelId).Append(","); } else { sqlstr.Append(string.Format("{0} as channelId", 1)).Append(","); } if (!string.IsNullOrEmpty(tables[i].OtherFlag)) { sqlstr.Append(tables[i].OtherFlag).Append(","); } } Type t = tables[i].GetType(); for (int j = 1; j <= tables[i].ValueNameCount; j++) { System.Reflection.PropertyInfo propertyInfo = t.GetProperty(FieldNamestr + j); if (!string.IsNullOrEmpty(propertyInfo.GetValue(tables[i], null).ToString())) { sqlstr.Append(propertyInfo.GetValue(tables[i], null)).Append(","); } } sqlstr.Replace(',', ' ', sqlstr.Length - 1, 1); sqlstr.Append("from ") .Append(tables[i].TableName) .Append(" where ") .Append(tables[i].AcqTime) .Append(" >@") .Append(acqtime) .Append(i) .Append(" and ") .Append(tables[i].AcqTime) .Append(" <=@") .Append(acqendtime) .Append(i); if (i != tables.Count - 1) { sqlstr.Append(";"); } } List <ExtractionConfig> list = ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID); switch (database.DataBaseType) { case (int)DataBaseType.SQLite: var sqlitepara = new SQLiteParameter[tables.Count * 2]; if (sqlitepara.Length > 0) { var endtime1 = new DateTime[tables.Count]; for (int i = 0; i < tables.Count; i++) { string time = this.GetLastTimeOrDefaultMin(list, tables[i], database); DateTime timeTemp = Convert.ToDateTime(time).AddSeconds(1); endtime1[i] = GetDayStepEndTime(timeTemp); sqlitepara[i * 2] = new SQLiteParameter(new StringBuilder().Append("@").Append(acqtime).Append(i).ToString(), timeTemp.ToString("yyyy-MM-dd HH:mm:ss")); sqlitepara[i * 2 + 1] = new SQLiteParameter(new StringBuilder().Append("@").Append(acqendtime).Append(i).ToString(), endtime1[i].ToString("yyyy-MM-dd HH:mm:ss")); } var sqlitehelper = new DbHelperSqLiteP(connstr); try { var s = sqlitehelper.Query(sqlstr.ToString(), sqlitepara); for (var i = 0; i < tables.Count; i++) { ExtractionConfigDic.GetExtractionConfigDic() .UpdateExtractionConfig( new ExtractionConfig { DataBaseId = (int)database.ID, TableName = tables[i].TableName, Acqtime = endtime1[i].ToString("yyyy-MM-dd HH:mm:ss") }); } return(s); } catch (Exception ex) { throw ex; } } break; case (int)DataBaseType.SQLServer: var sqlpara = new SqlParameter[tables.Count * 2]; if (sqlpara.Length > 0) { var endtime2 = new DateTime[tables.Count]; for (var i = 0; i < tables.Count; i++) { string time = this.GetLastTimeOrDefaultMin(list, tables[i], database); DateTime timeTemp = Convert.ToDateTime(time).AddSeconds(1); //这里加1秒是防止重复提取(因为时间记录中不包含毫秒数) endtime2[i] = GetDayStepEndTime(timeTemp); sqlpara[i * 2] = new SqlParameter(new StringBuilder().Append("@").Append(acqtime).Append(i).ToString(), timeTemp.ToString("yyyy-MM-dd HH:mm:ss")); sqlpara[i * 2 + 1] = new SqlParameter(new StringBuilder().Append("@").Append(acqendtime).Append(i).ToString(), endtime2[i].ToString("yyyy-MM-dd HH:mm:ss")); } var sqlhelper = new DbHelperSQLP(connstr); try { var s1 = sqlhelper.Query(sqlstr.ToString(), 300, sqlpara); for (var i = 0; i < tables.Count; i++) { ExtractionConfigDic.GetExtractionConfigDic() .UpdateExtractionConfig( new ExtractionConfig { DataBaseId = (int)database.ID, TableName = tables[i].TableName, Acqtime = endtime2[i].ToString("yyyy-MM-dd HH:mm:ss") }); } return(s1); } catch (Exception ex) { throw ex; } } break; case (int)DataBaseType.ACCESSOld: case (int)DataBaseType.ACCESSNew: var olepara = new OleDbParameter[tables.Count * 2]; if (olepara.Length > 0) { var endtime3 = new DateTime[tables.Count]; for (var i = 0; i < tables.Count; i++) { try { var timestr = this.GetLastTimeOrDefaultMin(list, tables[i], database); var timeTemp = Convert.ToDateTime(timestr).AddSeconds(1); endtime3[i] = GetDayStepEndTime(timeTemp); olepara[i * 2] = new OleDbParameter(new StringBuilder().Append("@").Append(acqtime).Append(i).ToString(), timeTemp); olepara[i * 2 + 1] = new OleDbParameter(new StringBuilder().Append("@").Append(acqendtime).Append(i).ToString(), endtime3[i]); } catch (Exception ex) { throw ex; } } var oledbhelper = new DbHelperOleDbP(connstr); string[] sqlstrings = sqlstr.ToString().Split(';'); DataSet[] ds = new DataSet[tables.Count]; for (int i = 0; i < tables.Count; i++) { ds[i] = new DataSet(); } for (int i = 0; i < tables.Count; i++) { //@MODIFY 2015-01-12 参数化SQL存在错误? var sql = sqlstrings[i].Replace("@" + acqtime + i.ToString(), "#" + Convert.ToDateTime(olepara[i * 2].Value).ToString("yyyy-MM-dd HH:mm:ss") + "#"); sql = sql.Replace("@" + acqendtime + i.ToString(), "#" + Convert.ToDateTime(olepara[i * 2 + 1].Value).ToString("yyyy-MM-dd HH:mm:ss") + "#"); ds[i] = oledbhelper.Query(sql); //ds[i] = oledbhelper.Query(sqlstrings[i], olepara[i * 2], olepara[i * 2 + 1]); ExtractionConfigDic.GetExtractionConfigDic() .UpdateExtractionConfig( new ExtractionConfig { DataBaseId = (int)database.ID, TableName = tables[i].TableName, Acqtime = endtime3[i].ToString("yyyy-MM-dd HH:mm:ss") }); } DataSet retSet = new DataSet(); for (int i = 0; i < tables.Count; i++) { retSet.Merge(ds[i]); } return(retSet); //return oledbhelper.Query(sqlstr.ToString(), olepara); } break; default: return(new DataSet()); }//switch return(new DataSet()); }