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());
}
