public void DataTablesAddColumns() { List <TableFieldInfo> tableFieldList = TableFieldInfoDic.GetTableFieldInfoDic().GetAllTableFieldInfos(); int maxTables = 0; for (int i = 0; i < tableFieldList.Count; i++) { if (maxTables < tableFieldList[i].ValueNameCount) { maxTables = tableFieldList[i].ValueNameCount; } } DataGridViewTextBoxColumn colIp = new DataGridViewTextBoxColumn { DataPropertyName = "DataBaseId", HeaderText = @"数据库ID", Name = "colDataBaseId", //Resizable = DataGridViewTriState.True }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "TableName", HeaderText = @"表名", Name = "colTableName", //Resizable = DataGridViewTriState.True }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "OtherFlag", HeaderText = @"标记位", Name = "colOtherFlag", //Resizable = DataGridViewTriState.True }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "SensorType", HeaderText = @"传感器类型", Name = "colSensorType", //Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ValueNameCount", HeaderText = @"映射字段个数", Name = "colValueNameCount", //Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); if (maxTables >= 1) { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName1", HeaderText = @"字段1 Name", Name = "colExtractFieldName1", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId1", HeaderText = @"字段1 ID", Name = "colExtractValueNameId1", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); } else { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName1", HeaderText = @"字段1 Name", Name = "colExtractFieldName1", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId1", HeaderText = @"字段1 ID", Name = "colExtractValueNameId1", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); } if (maxTables >= 2) { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName2", HeaderText = @"字段2 Name", Name = "colExtractFieldName2", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId2", HeaderText = @"字段2 ID", Name = "colExtractValueNameId2", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); } else { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName2", HeaderText = @"ExtractFieldName2", Name = "colExtractFieldName2", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameID2", HeaderText = @"ExtractValueNameID2", Name = "colExtractValueNameID2", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); } if (maxTables >= 3) { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName3", HeaderText = @"字段3 Name", Name = "colExtractFieldName3", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId3", HeaderText = @"字段3 ID", Name = "colExtractValueNameId3", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); } else { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName3", HeaderText = @"ExtractFieldName3", Name = "colExtractFieldName3", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId3", HeaderText = @"ExtractValueNameId3", Name = "colExtractValueNameId3", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); } if (maxTables >= 4) { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName4", HeaderText = @"字段4 Name", Name = "colExtractFieldName4", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId4", HeaderText = @"字段4 ID", Name = "colExtractValueNameId4", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); } else { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName4", HeaderText = @"ExtractFieldName4", Name = "colExtractFieldName4", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId4", HeaderText = @"ExtractValueNameId4", Name = "colExtractValueNameId4", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); } if (maxTables >= 5) { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName5", HeaderText = @"字段5 Name", Name = "colExtractFieldName5", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId5", HeaderText = @"字段5 ID", Name = "colExtractValueNameId5", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); } else { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName5", HeaderText = @"ExtractFieldName5", Name = "colExtractFieldName5", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId5", HeaderText = @"ExtractValueNameId5", Name = "colExtractValueNameId5", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); } if (maxTables >= 6) { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName6", HeaderText = @"字段6 Name", Name = "colExtractFieldName6", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId6", HeaderText = @"字段6 ID", Name = "colExtractValueNameId6", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); } else { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName6", HeaderText = @"ExtractFieldName6", Name = "colExtractFieldName6", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId6", HeaderText = @"ExtractValueNameId6", Name = "colExtractValueNameId6", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); } if (maxTables >= 7) { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName7", HeaderText = @"字段7 Name", Name = "colExtractFieldName7", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId7", HeaderText = @"字段7 ID", Name = "colExtractValueNameId7", Resizable = DataGridViewTriState.True, }; dgvDataTableInfo.Columns.Add(colIp); } else { colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractFieldName7", HeaderText = @"ExtractFieldName7", Name = "colExtractFieldName7", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ExtractValueNameId7", HeaderText = @"ExtractValueNameId7", Name = "colExtractValueNameId7", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); } colIp = new DataGridViewTextBoxColumn { DataPropertyName = "Id", HeaderText = @"ID", Name = "colId", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ModuleNo", HeaderText = @"ModuleNo", Name = "colModuleNo", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ChannelId", HeaderText = @"ChannelId", Name = "colChannelId", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "AcqTime", HeaderText = @"AcqTime", Name = "colAcqTime", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "SensorID", HeaderText = @"SensorID", Name = "colSensorID", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "Reserved2", HeaderText = @"Reserved2", Name = "colReserved2", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); colIp = new DataGridViewTextBoxColumn { DataPropertyName = "ValueType", HeaderText = @"ValueType", Name = "colValueType", Resizable = DataGridViewTriState.True, Visible = false, }; dgvDataTableInfo.Columns.Add(colIp); this.dgvDataTableInfo.AutoGenerateColumns = false; }
private void btnDelete_Click(object sender, EventArgs e) { int m = 0; bool flag = false; for (m = 0; m < dgvDataBase.Rows.Count; m++) { if (dgvDataBase.Rows[m].Selected) { string Id = dgvDataBase.Rows[m].Cells[0].Value.ToString(); DialogResult result = MessageBox.Show(@"是否删除?", @"提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question); { if (result == DialogResult.Yes) { if (DataBaseNameDic.GetDataBaseNameDic().Delete(Convert.ToInt32(Id))) { bool deleResult = false; //同时删除与该数据库有关的配置 //删除配置表的信息 List <ConfigTable> listConfigTable = ConfigTableDic.GetConfigTableDic().SelectList(); for (int i = 0; i < listConfigTable.Count; i++) { if (listConfigTable[i].DataBaseId == Convert.ToInt32(Id)) { deleResult = ConfigTableDic.GetConfigTableDic().Delete(Convert.ToInt32(listConfigTable[i].ID)); } } //删除数据表的信息 List <TableFieldInfo> listTable = TableFieldInfoDic.GetTableFieldInfoDic().GetAllTableFieldInfos(); for (int i = 0; i < listTable.Count; i++) { if (listTable[i].DataBaseId == Convert.ToInt32(Id)) { deleResult = (deleResult && TableFieldInfoDic.GetTableFieldInfoDic().Delete(Convert.ToInt32(listTable[i].Id))); } } MessageBox.Show(@"删除成功"); flag = true; } else { MessageBox.Show(@"删除失败"); } } else { return; } } } } if (m == dgvDataBase.Rows.Count && !flag) { MessageBox.Show(@"请选择要删除的数据库信息"); m = 0; } dgvDataBase.Columns.Clear(); dgvDataBase.DataSource = null; DataBaseAddColumns(); dataGriewDataBaseSource = DataBaseNameDic.GetDataBaseNameDic().GetAllBaseNames(); dgvDataBase.DataSource = dataGriewDataBaseSource; dgvDataBase.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; if (dgvDataBase.Rows.Count > 0) { dgvDataBase.Rows[0].Selected = false; } BingDataSource(); }
private void BingDataSource() { int width = 0;//定义一个局部变量,用于存储自动调整列宽以后整个DtaGridView的宽度 //数据库信息绑定 dgvDataBase.Columns.Clear(); dgvDataBase.DataSource = null; DataBaseAddColumns(); dataGriewDataBaseSource = DataBaseNameDic.GetDataBaseNameDic().GetAllBaseNames(); dgvDataBase.DataSource = dataGriewDataBaseSource; dgvDataBase.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; //配置表信息绑定 gdvConfigTable.Columns.Clear(); gdvConfigTable.DataSource = null; ConfigTableAddColumns(); dataGriewConfigTableSource = ConfigTableDic.GetConfigTableDic().SelectList(); gdvConfigTable.DataSource = dataGriewConfigTableSource; gdvConfigTable.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; for (int i = 0; i < this.gdvConfigTable.Columns.Count; i++) //对于DataGridView的每一个列都调整 { this.gdvConfigTable.AutoResizeColumn(i, DataGridViewAutoSizeColumnMode.AllCells); //将每一列都调整为自动适应模式 width += this.gdvConfigTable.Columns[i].Width; //记录整个DataGridView的宽度 } if (width > this.gdvConfigTable.Size.Width) //判断调整后的宽度与原来设定的宽度的关系,如果是调整后的宽度大于原来设定的宽度,则将DataGridView的列自动调整模式设置为显示的列即可,如果是小于原来设定的宽度,将模式改为填充。 { this.gdvConfigTable.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells; } else { this.gdvConfigTable.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; } //数据表信息配置 dgvDataTableInfo.Columns.Clear(); dgvDataTableInfo.DataSource = null; DataTablesAddColumns(); dataGriewDataTableSource = TableFieldInfoDic.GetTableFieldInfoDic().GetAllTableFieldInfos(); dgvDataTableInfo.DataSource = dataGriewDataTableSource; dgvDataTableInfo.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; width = 0; for (int i = 0; i < this.dgvDataTableInfo.Columns.Count; i++) //对于DataGridView的每一个列都调整 { this.dgvDataTableInfo.AutoResizeColumn(i, DataGridViewAutoSizeColumnMode.AllCells); //将每一列都调整为自动适应模式 width += this.dgvDataTableInfo.Columns[i].Width; //记录整个DataGridView的宽度 } if (width > this.dgvDataTableInfo.Size.Width) //判断调整后的宽度与原来设定的宽度的关系,如果是调整后的宽度大于原来设定的宽度,则将DataGridView的列自动调整模式设置为显示的列即可,如果是小于原来设定的宽度,将模式改为填充。 { this.dgvDataTableInfo.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells; } else { this.dgvDataTableInfo.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; } if (dgvDataBase.Rows.Count > 0) { dgvDataBase.Rows[0].Selected = false; } }
/// <summary> /// 提取其他数据库 /// </summary> /// <param name="dbName"></param> private void ExtractOtherOneDb(DataBaseName dbName) { var bll = new SelectOtherTablesBll(); DataSet ds = null; log.Debug("提取其他数据库"); try { ds = bll.Select(dbName); } catch (Exception ex) { log.Error(ex.Message); } if (ds != null) { log.Debug("提取成功"); foreach (DataTable dt in ds.Tables) { try { List <Data> list = new List <Data>(); if (this.messagesShowEventHandler != null) { var msg = new StringBuilder(); msg.Append("提取").Append(dt.TableName).Append(dt.Rows.Count).Append("条数据"); log.Info(msg.ToString()); this.messagesShowEventHandler( this, new MessagesShowEventArgs { MessageType = MsgType.Info, MessagesShow = msg.ToString() }); } if (dt != null && dt.Rows.Count > 0) { int index = 0; bool flag = false; bool ultraSetFlag = false;//@Modify20150107by yww int flagCount = 0; ConcurrentDictionary <int, ConfigTableInfo> dic = new ConcurrentDictionary <int, ConfigTableInfo>(); ConfigTableInfo config = new ConfigTableInfo(); if ( !string.IsNullOrEmpty( TableFieldInfoDic.GetTableFieldInfoDic() .GeTableFieldInfo((int)dbName.ID, dt.TableName) .OtherFlag)) { index = 1; } if (ConfigTableDic.GetConfigTableDic().SelectList().Count > 0) { ConfigTable configtable = ConfigTableDic.GetConfigTableDic().SelecConfigTable((int)dbName.ID); //ConfigTable configtable = ConfigTableDic.GetConfigTableDic().SelecConfigTable((int)dbName.ID, dt.TableName); if (configtable != null) { flag = true; flagCount = 1; } // 特殊处理(在有Sensor配置的时候直接拿SensorID做为模块号) if (dt.TableName == "MainStreeData") { ultraSetFlag = true; flag = false; flagCount = 1; } } else { flag = false; flagCount = 2; } foreach (DataRow row in dt.Rows) { try { var data = new Data { ProjectCode = Convert.ToInt16(row[0]), DataBaseId = Convert.ToInt32(row[1]), SafeTypeId = Convert.ToInt32(row[2]), ChannelId = 1 }; data.DataSet = new List <double>(); if (flag) { int sensorid = 0; bool identifyBySerialNo = false; string serialNo = ""; if (row[4] != DBNull.Value) { if (int.TryParse(row[4].ToString(), out sensorid)) { data.SensorId = sensorid; identifyBySerialNo = false; } else { serialNo = row[4].ToString(); identifyBySerialNo = true; } //data.SensorId = Convert.ToInt32(row[4]); if (ConfigInfoTable.ConfigtableInfoDictionary.ContainsKey(data.DataBaseId)) { dic = ConfigInfoTable.ConfigtableInfoDictionary[data.DataBaseId]; } else { log.Error("字典中数据库ID关键字不存在"); continue; } if (!identifyBySerialNo) { if (dic.ContainsKey(data.SensorId)) { config = dic[data.SensorId]; } else { log.Error("字典中传感器ID关键字不存在"); continue; } } else { config = (from s in dic.Values where StringComparer.OrdinalIgnoreCase.Compare(s.Otherflag, serialNo) == 0 select s).FirstOrDefault(); if (config == null) { log.Error("字典中传感器唯一标示符不存在"); continue; } } data.ChannelId = config.ChannelId; data.MoudleNo = config.MoudleNo; } else // 没有传感器ID { data.MoudleNo = "1"; } } else { data.ChannelId = 1; if (!ultraSetFlag) { if (row[5] != DBNull.Value) { data.ChannelId = Convert.ToInt32(row[5]); } } if (row[4] != DBNull.Value) { data.MoudleNo = row[4].ToString(); } } if (index == 1) { data.OFlag = Convert.ToInt32(row[6]); } // 采集时间转换问题,提供几种常见格式的时间转换 DateTime acqtime = Convert.ToDateTime(row[3].ToString().Trim()); //string 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","yyyy-M-d h:mm:ss" // }; //timestr //bool isSuccess = DateTime.TryParseExact( // timestr, // timeformats, // CultureInfo.CurrentCulture, // DateTimeStyles.None, // out acqtime); //AssumeLocal //if (!isSuccess) //{ // try // { // acqtime = Convert.ToDateTime(timestr); // } // catch // { // log.Error("时间格式转换失败;" + timestr); // } //} data.CollectTime = acqtime; for (int i = 4 + flagCount + index; i < row.ItemArray.Length; i++) { double value; double.TryParse(row[i].ToString().Trim(), out value); data.DataSet.Add(value); } list.Add(data); } catch (Exception ex) { log.Error(ex.Message); } } string strtime = dt.Compute("Max(ACQUISITION_DATETIME)", Boolean.TrueString).ToString(); ExtractionConfigDic.GetExtractionConfigDic() .UpdateExtractionConfig( new ExtractionConfig { DataBaseId = (int)dbName.ID, TableName = dt.TableName, Acqtime = strtime }); } //添加到队列 foreach (Data data in list) { datasTobesent.Enqueue(data); } } catch (Exception ex) { log.Error(ex.Message); } } } else { log.Debug("DataSet==null"); } }
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()); }