public int AddDataBaseName(DataBaseName dataBaseName)
{
using (DbConnection conn = new DbConnection())
{
return(dataBaseName.Save());
}
}
/// <summary>
/// 分离数据库
/// </summary>
public void Detach(TextBox textBox)
{
var TableName = GetTableNames();
SqlConnection sqlConnection = new SqlConnection(SetConfig());
SqlCommand cmd = new SqlCommand(@"exec sp_detach_db " + DataBaseName, sqlConnection);
foreach (DataRow row in TableName)
{
if (row["name"].ToString().ToUpper() == DataBaseName.ToUpper()) //还差条件，不然容易误操作。
{
try
{
sqlConnection.Open();
if (sqlConnection.State.ToString() == "Open")
{
//textBox.Text += DateTime.Now.ToString("F") + $" 分离数据库-{FileName}-开始\r\n";
SetText(textBox, $"分离数据库-{FileName}-开始");
}
cmd.ExecuteNonQuery();
sqlConnection.Close();
if (sqlConnection.State.ToString() == "Closed")
{
//textBox.Text += DateTime.Now.ToString("F") + $" 分离数据库-{FileName}-完成\r\n";
SetText(textBox, $"分离数据库-{FileName}-完成");
}
}
catch (SqlException)
{
throw;
}
}
}
}
public bool AddDataBaseName()
{
DAL.Common.Initialise();
using (DbConnection conn = new DbConnection())
{
bool flag = false;
string[] names = pathTextBox.Text.Split("\\".ToCharArray());
DataBaseName dataBaseName = new DataBaseName()
{
DataBaseCode = names[names.Count() - 1],
Location = pathTextBox.Text,
DataBaseType = (int)DataBaseType.SQLite
};
List <DataBaseName> dataBaseNameList = DataBaseNameDic.GetDataBaseNameDic().GetAllBaseNames();
for (int i = 0; i < dataBaseNameList.Count; i++)
{
if (dataBaseNameList[i].Location == pathTextBox.Text)
{
flag = true;
}
}
if (!flag)
{
DataBaseNameDic.GetDataBaseNameDic().Add(dataBaseName);
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 往数据库中批量插入数据
/// </summary>
/// <param name="sourceDt">数据源表</param>
/// <param name="targetTable">服务器上目标表</param>
public static void BulkToDB(DataTable sourceDt, string targetTable, DataBaseName dataBaseName)
{
SqlConnection conn = new SqlConnection(GetEnumDes(dataBaseName.ToString()));
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的数据有效批量加载sql server表中
bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
bulkCopy.BatchSize = sourceDt.Rows.Count; //每一批次中的行数
try
{
conn.Open();
if (sourceDt != null && sourceDt.Rows.Count != 0)
{
bulkCopy.WriteToServer(sourceDt); //将提供的数据源中的所有行复制到目标表中
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}
private void lookForDatabases() // Add to method so it can be called when a database is created
{
comboBoxLoadDatabase.Items.Clear(); // Remove any existing items
string[] Databases = Directory.GetFiles(@"./databases/", "*.sqlite*"); // Create an array of all files ending in .sqlite within the databases folder
foreach (string DataBaseName in Databases) // For every result
{
String TempName = DataBaseName.Replace("./databases/", string.Empty); // Remove the folder from the name
TempName = TempName.Replace(".sqlite", string.Empty); // Remove the file extension from the name
TempName = TempName.Replace(".aes", " (Encrypted)"); // Remove the file extension from the name
comboBoxLoadDatabase.Items.Add(TempName); // Add the result to the comboBox
comboBoxLoadDatabase.SelectedIndex = 0; // Select the first index of the comboBox (Cant be done out of the for loop due to exception if no results are returned)
}
if (comboBoxLoadDatabase.SelectedIndex == -1) // If the SelectedIndex is -1 (no results added) Disable loading databases as there are none created
{
labelDBNotFound.Visible = true;
comboBoxLoadDatabase.Enabled = false;
textBoxLoadPassword.Enabled = false;
buttonLoadDatabase.Enabled = false;
}
else
{
labelDBNotFound.Visible = false;
comboBoxLoadDatabase.Enabled = true;
textBoxLoadPassword.Enabled = true;
buttonLoadDatabase.Enabled = true;
}
}
/// <summary>
/// 执行SQL语句，返回自增ID
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSQLReturnID(string SQLString, SqlParameter[] paras, DataBaseName dataBaseName)
{
using (SqlConnection connection = new SqlConnection(GetEnumDes(dataBaseName.ToString())))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
connection.Open();
cmd.CommandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;";
int id = Convert.ToInt32(cmd.ExecuteScalar());
return(id);
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
internal bool RestoreTable(DataBaseName dbName)
{
bool result = false;
switch (dbName)
{
case DataBaseName.Users:
result = LoadSQLTextFile(SQLStatement.GetInsertUserQuery());
break;
case DataBaseName.Category:
result = LoadSQLTextFile(SQLStatement.GetInsertCategoryQuery());
break;
case DataBaseName.Period_Type:
result = LoadSQLTextFile(SQLStatement.GetInsertPeriodTypeQuery());
break;
case DataBaseName.Period:
break;
case DataBaseName.Staff:
break;
case DataBaseName.All:
result = (LoadSQLTextFile(SQLStatement.GetInsertUserQuery()) &&
LoadSQLTextFile(SQLStatement.GetInsertCategoryQuery()) &&
LoadSQLTextFile(SQLStatement.GetInsertPeriodTypeQuery()));
break;
}
return(result);
}
public bool AddDataBaseName(string dbName, string path, int daType)
{
using (DbConnection conn = new DbConnection())
{
bool flag = false;
DataBaseName dataBaseName = new DataBaseName()
{
DataBaseCode = dbName,
Location = path,
DataBaseType = daType,
};
DataBaseNameDic.GetDataBaseNameDic();
List <DataBaseName> dataBaseNameList = DataBaseNameDic.GetDataBaseNameDic().GetAllBaseNames();
for (int i = 0; i < dataBaseNameList.Count; i++)
{
if (dataBaseNameList[i].Location == path)
{
flag = true;
}
}
if (!flag)
{
DataBaseNameDic.GetDataBaseNameDic().Add(dataBaseName);
return(true);
}
else
{
return(false);
}
}
}
public DataSet Select(DataBaseName database)
{
var file = new ExeConfigurationFileMap {
ExeConfigFilename = @".\config\Params.config"
};
Configuration config = ConfigurationManager.OpenMappedExeConfiguration(file, ConfigurationUserLevel.None);
string selectByDay = config.AppSettings.Settings["otherDBSelectByDay"].Value;
var strdelaytime = config.AppSettings.Settings["otherDBDelayMinute"].Value;
int.TryParse(strdelaytime, out delaytime);
bool isSelectedByDay = false;
if (!Boolean.TryParse(selectByDay, out isSelectedByDay))
{
isSelectedByDay = false;
}
if (isSelectedByDay)
{
return(SelectByDay(database));
}
else
{
return(SelectAll(database));
}
}
public bool Delete(int id)
{
var bll = new DataBaseNameBll();
var db = new DataBaseName();
dataBaseNames.TryRemove(id, out db);
return(bll.Delete(id));
}
internal static string GetConnectionName(DataBaseName dbName)
{
switch (dbName)
{
case DataBaseName.FreshFruit:
return("FreshFruit");
default:
throw new ArgumentException("未定义的数据库连接名称");
}
}
public MySqlOperator(DataBaseName databaseName, List <Assembly> mappingAssemblies = null) : base(databaseName)
{
_mappingAssemblies = mappingAssemblies ?? new List <Assembly>()
{
Assembly.GetCallingAssembly()
};
var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper <>), _mappingAssemblies, new MySqlDialect());
SqlGenerator = new SqlGeneratorImpl(config);
}
public MySqlOperator(DataBaseName databaseName, List<Assembly> mappingAssemblies = null)
: base(databaseName)
{
_mappingAssemblies = mappingAssemblies ?? new List<Assembly>()
{
Assembly.GetCallingAssembly()
};
var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), _mappingAssemblies, new MySqlDialect());
SqlGenerator = new SqlGeneratorImpl(config);
}
private IList <Data> SelectAndMove(DataBaseName path)
{
if (path.DataBaseType == (int)DataBaseType.Shake)
{
ArtAccDatum artdata = null;
string errorstr = string.Empty;
var list = new List <Data>();
string[] files = BinaryFileHelper.GetFileNames(path.Location, "*.usb", TxtDateType.Vibration);
//string[] files = Directory.GetFiles(path.Location,"*.usb");
foreach (string file in files)
{
DateTime lasTime;
DateTime acqtime = BinaryFileHelper.GetFileRecordTime(file, TxtDateType.Vibration);
//if (acqtime > lasTime)
{
if (BinaryFileHelper.Read(file, out artdata, out errorstr))
{
var pts = Path.GetDirectoryName(file).Split(Path.DirectorySeparatorChar);
int mod = -1;
int.TryParse(pts.Last(), out mod);
//DateTime acqtime = BinaryFileHelper.GetFileRecordTime(file, TxtDateType.Vibration);
for (int i = 0; i < artdata.MilliVolt.Length; i++)
{
Data data = new Data();
data.ChannelId = i + 1;
//data.MoudleNo = artdata.FileHeader.DeviceNum.ToString().Trim();
data.MoudleNo = mod.ToString();
data.OFlag = artdata.FileHeader.ADPara.Frequency;
data.DataBaseId = (int)path.ID;
data.SafeTypeId = (int)SensorCategory.Vibration;
data.ProjectCode = (short)ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode;
data.DataSet = new List <double>();
foreach (float value in artdata.MilliVolt[i])
{
data.DataSet.Add(value);
}
data.CollectTime = acqtime;
list.Add(data);
}
MoveFile(file);
}
}
}
return(list);
}
return(new List <Data>());
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbn">数据库名称</param>
public DataHandle(DataBaseName dbn)
{
//根据不同数据库名称初始化不同数据操作（改变连接字符串）
switch (dbn)
{
//case DataBaseName.SCIC60: DB = WebCommon.GetDB_WJSJZX(); break;
//case DataBaseName.WebPlat50: DB = WebCommon.GetDB_WEBPLA50(); break;
//case DataBaseName.DBSZHCACredit: DB = WebCommon.GetDB_DBSZHCACREDIT(); break;
case DataBaseName.WJSJZX: DB = WebCommon.GetDB_WJSJZX(); break;
}
//初始化参数
spc = DB.CreateSqlParameterCollection();
}
public bool Add(DataBaseName dataBaseName)
{
var bll = new DataBaseNameBll();
int id = bll.AddDataBaseName(dataBaseName);
if (id > 0)
{
dataBaseName.ID = id;
this.dataBaseNames.TryAdd(id, dataBaseName);
return(true);
}
return(false);
}
public static IDbConnection GetConnection(DataBaseName dataBaseEnum, ReadOrWriteDB optionEnum)
{
IDbConnection connection = new SqlConnection(ConfigSettings.ConnectionString);
DateTime now = DateTime.Now;
DateTime expireDate = new DateTime(2019, 12, 31);
if (DateTimeHelper.CheckDate && (now > expireDate))
{
throw new Exception("soft expired ");
}
connection.Open();
return(connection);
}
public static CommConnection GetConnection(DataBaseName DBName, ConnectionDBType type)
{
String connectionstring = String.Empty;
String GetDBName = String.Empty;
#region Get need used database name
if (DBName.ToString().StartsWith("Default"))
{
GetDBName = DBName.ToString().Replace("Default_", "");
}
else
{
GetDBName = DBName.ToString().Replace("DB_", "");
}
#endregion
if (type == ConnectionDBType.SqlServer)
{
//SQL Server
connectionstring = String.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};MultipleActiveResultSets=True", Server, GetDBName, Account, Password);
}
if (type == ConnectionDBType.OleDB)
{
//Access
String FilePath = Path + "\\_Code\\Database\\MainDB.mdb";
connectionstring = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", FilePath);
}
if (type == ConnectionDBType.MySql)
{
//SQL Server
connectionstring = String.Format("Network Address={0};Initial Catalog='{1}';Persist Security Info=no;User Name='{2}';Password='******'", Server, GetDBName, Account, Password);
}
if (conn == null)
{
conn = new CommConnection(connectionstring, type);
conn.Open();
}
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Dispose();
conn = null;
conn = new CommConnection(connectionstring, type);
conn.Open();
}
return(conn);
}
public static CommConnection GetConnection(DataBaseName DBName, ConnectionDBType type)
{
String connectionstring = String.Empty;
String GetDBName = String.Empty;
#region Get need used database name
if (DBName.ToString().StartsWith("Default"))
{
GetDBName = DBName.ToString().Replace("Default_", "");
}
else
{
GetDBName = DBName.ToString().Replace("DB_", "");
}
#endregion
if (type == ConnectionDBType.SqlServer)
{
//SQL Server
connectionstring = String.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};MultipleActiveResultSets=True", Server, GetDBName, Account, Password);
}
if (type == ConnectionDBType.OleDB)
{
//Access
String FilePath = Path + "\\_Code\\Database\\MainDB.mdb";
connectionstring = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", FilePath);
}
if (type == ConnectionDBType.MySql)
{
//SQL Server
connectionstring = String.Format("Network Address={0};Initial Catalog='{1}';Persist Security Info=no;User Name='{2}';Password='******'", Server, GetDBName, Account, Password);
}
if (conn == null)
{
conn = new CommConnection(connectionstring, type);
conn.Open();
}
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Dispose();
conn = null;
conn = new CommConnection(connectionstring, type);
conn.Open();
}
return conn;
}
/// <summary>
/// 创建对象
/// </summary>
/// <param name="databaseName"></param>
/// <returns></returns>
public IDataBase GetDataBase(DataBaseName databaseName)
{
switch (databaseName)
{
case DataBaseName.SQLITE:
return(new SqliteDataBase());
case DataBaseName.SQLSERVER:
return(new SqlServerDataBase());
default:
return(new SqlServerDataBase());
}
}
private static DbConnectionString GetResultConnectionString(DataBaseName dataBaseName)
{
var connectionString = String.Empty;
if (UseLocalDb() && Constants.IsWindows)
{
connectionString = Constants.CreateLocalConnectionString(dataBaseName);
}
else
{
connectionString = Constants.CreateServerConnectionString(dataBaseName);
}
return(new DbConnectionString(connectionString));
}
public bool Delete(int id)
{
using (DbConnection conn = new DbConnection())
{
try
{
DataBaseName.Delete(Where.Equal("ID", id));
return(true);
}
catch (Exception)
{
return(false);
}
}
}
private static string GetSQLServerConnectionString(DataBaseName database)
{
string ip = null;
if (database.Location != "")
{
string[] str = database.Location.Split(@":".ToCharArray());
ip = str[0];
}
return(string.Format(
"Data Source={0},1433;Initial Catalog={1};User ID={2};Password={3}",
ip,
database.DataBaseCode,
database.UserId,
database.Password));
}
public static string GetConnectionString(DataBaseName database)
{
switch (database.DataBaseType)
{
case (int)DataBaseType.SQLite:
return(GetSQLiteConnectionString(database));
case (int)DataBaseType.SQLServer:
return(GetSQLServerConnectionString(database));
case (int)DataBaseType.ACCESSOld:
case (int)DataBaseType.ACCESSNew:
return(GetAccessConnectionString(database));
default:
return(string.Empty);
}
}
public static SqlConnection GetConnection(DataBaseName DBName)
{
String connectionstring = String.Empty;
String GetDBName = String.Empty;
if (DBName == DataBaseName.AspNetWeb)
{
GetDBName = "AspNetWeb";
}
else if (DBName == DataBaseName.DB2)
{
GetDBName = "DB2";
}
else if (DBName == DataBaseName.DB3)
{
GetDBName = "DB3";
}
else
{
//DefDB
GetDBName = "AspNetWeb";
}
connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};MultipleActiveResultSets=True", Server, GetDBName, Account, Password);
if (conn == null)
{
conn = new SqlConnection(connectionstring);
conn.Open();
}
if (conn.State != System.Data.ConnectionState.Open)
{
//conn.ConnectionString = connectionstring;
//conn.Open();
conn.Dispose();
conn = null;
conn = new SqlConnection(connectionstring);
conn.Open();
}
return(conn);
}
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));
}
private static string GetAccessConnectionString(DataBaseName database)
{
string accessstr;
if (database.DataBaseCode.Contains(".mdb"))
{
accessstr = OldAccess;
}
else if (database.DataBaseCode.Contains(".accdb"))
{
accessstr = NewAccess;
}
else
{
throw new Exception("数据库类型不匹配");
}
return(string.Format(
"Provider={0};Persist Security Info=False;Data Source={1}",
accessstr,
database.Location));
}
/// <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 static SqlConnection GetConnection(DataBaseName DBName)
{
String connectionstring = String.Empty;
String GetDBName = String.Empty;
if (DBName == DataBaseName.AspNetWeb)
{
GetDBName = "AspNetWeb";
}
else if (DBName == DataBaseName.DB2)
{
GetDBName = "DB2";
}
else if (DBName == DataBaseName.DB3)
{
GetDBName = "DB3";
}
else {
//DefDB
GetDBName = "AspNetWeb";
}
connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};MultipleActiveResultSets=True", Server, GetDBName, Account, Password);
if (conn == null) {
conn = new SqlConnection(connectionstring);
conn.Open();
}
if (conn.State != System.Data.ConnectionState.Open){
//conn.ConnectionString = connectionstring;
//conn.Open();
conn.Dispose();
conn = null;
conn = new SqlConnection(connectionstring);
conn.Open();
}
return conn;
}
public IList <Data> TextOrBinarySelect(DataBaseName path)
{
var file = new ExeConfigurationFileMap {
ExeConfigFilename = @".\config\Params.config"
};
Configuration config = ConfigurationManager.OpenMappedExeConfiguration(file, ConfigurationUserLevel.None);
var movefileIfExtactSuccessful = config.AppSettings.Settings["MoveFileIfExtractSuccessful"].Value;
var movefileafterextract = true;
if (!Boolean.TryParse(movefileIfExtactSuccessful, out movefileafterextract))
{
movefileafterextract = true;
}
if (movefileafterextract)
{
return(SelectAndMove(path));
}
else
{
return(SelectByTime(path));
}
}
internal bool RestoreTable(DataBaseName dbName)
{
bool result = false;
switch (dbName)
{
case DataBaseName.Users:
result = LoadSQLTextFile(SQLStatement.GetInsertUserQuery());
break;
case DataBaseName.Category:
result = LoadSQLTextFile(SQLStatement.GetInsertCategoryQuery());
break;
case DataBaseName.Period_Type:
result = LoadSQLTextFile(SQLStatement.GetInsertPeriodTypeQuery());
break;
case DataBaseName.Period:
break;
case DataBaseName.Staff:
break;
case DataBaseName.All:
result = (LoadSQLTextFile(SQLStatement.GetInsertUserQuery()) &&
LoadSQLTextFile(SQLStatement.GetInsertCategoryQuery()) &&
LoadSQLTextFile(SQLStatement.GetInsertPeriodTypeQuery()));
break;
}
return result;
}
public DataBase(string dataBaseName)
{
DataBaseName = new DataBaseName(dataBaseName);
}
public static CommConnection GetConnection(DataBaseName DBName)
{
return GetConnection(DBName, ConnectionDBType.SqlServer);
}
public static CommConnection GetConnection(DataBaseName DBName)
{
return(GetConnection(DBName, ConnectionDBType.SqlServer));
}
public IList <Data> TextOrBinarySelect(DataBaseName path)
{
IList <Data> list = new List <Data>();
if (path.DataBaseType == (int)DataBaseType.Fiber)
{
DateTime readtime = new DateTime();
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"
};
var dir = new DirectoryInfo(path.Location);
DirectoryInfo[] childDirectories;
try
{
childDirectories = dir.GetDirectories("FBG_*");
}
catch (Exception ex)
{
return(list);
}
var fbgList = new List <string>();
if (childDirectories != null && childDirectories.Length > 0)
{
foreach (DirectoryInfo childDirectory in childDirectories)
{
fbgList.Add(childDirectory.FullName);
}
}
if (fbgList != null && fbgList.Count > 0)
{
foreach (string fbg in fbgList) //遍历所有光栅光纤文件夹
{
bool isSuccess = false;
try
{
if (ExtractionConfigDic.GetExtractionConfigDic()
.GetExtractionConfig((int)path.ID).Count > 0)
{
isSuccess =
DateTime.TryParseExact(
ExtractionConfigDic.GetExtractionConfigDic()
.GetExtractionConfig((int)path.ID)[0]
.Acqtime,
timeformats,
CultureInfo.CurrentCulture,
DateTimeStyles.None,
out readtime);
}
}
catch (Exception e)
{
throw e;
}
if (!isSuccess)
{
readtime = new DateTime(2013, 1, 1);
}
TimeSpan ts = DateTime.Now.Date.Subtract(readtime.Date);
var strdays = new string[ts.Days + 1];
for (int i = 0; i < strdays.Length; i++)
{
strdays[i] = readtime.AddDays(i).ToString("yyyyMMdd");
}
string moudleId = Path.GetFileNameWithoutExtension(fbg).Remove(0, 4);
foreach (string strday in strdays) // 遍历一个模块下某一天的所有通道的数据
{
string filter = "CH_*_" + strday + "_*.txt";
string[] files = Directory.GetFiles(fbg, filter, SearchOption.TopDirectoryOnly);
foreach (string file in files)// 遍历某通道的所有数据
{
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(file);
int channel =
int.Parse(
fileNameWithoutExtension.Split(
new[] { '_' },
StringSplitOptions.RemoveEmptyEntries)[1].Trim());
DateTime lasTime;
isSuccess =
DateTime.TryParseExact(
ExtractionConfigDic.GetExtractionConfigDic()
.GetExtractionConfig((int)path.ID, channel.ToString())
.Acqtime,
timeformats,
CultureInfo.CurrentCulture,
DateTimeStyles.None,
out lasTime);
try
{
using (var tfc = new ZetaTemporaryFileCloner(file))
{
using (var sr = new StreamReader(tfc.FilePath))
{
string linestr = string.Empty;
DateTime time = DateTime.Now;
while ((linestr = sr.ReadLine()) != null)
{
string[] pars = linestr.Split(
new[] { '\t' },
StringSplitOptions.RemoveEmptyEntries);
string[] tempTime1 = pars[0].Remove(0, 1).Split(new char[] { '-', ' ', '.', ':' });
string tempTime = null;
for (int i = 0; i < tempTime1.Count() - 1; i++)
{
tempTime += tempTime1[i];
}
if (!DateTime.TryParseExact(tempTime, "yyyyMMddHHmmss",
CultureInfo.CurrentCulture, DateTimeStyles.None, out time))
{
//MessageBox.Show("时间转换失败");
}
if (time > lasTime)
{
Data data = new Data();
data.MoudleNo = moudleId;
data.ChannelId = channel;
data.CollectTime = time;
data.DataBaseId = (int)path.ID;
data.SafeTypeId = 11;
data.DataSet = new List <double>();
try
{
for (int i = 1; i < pars.Length; i++)
{
data.DataSet.Add(double.Parse(pars[i].Trim()));
}
list.Add(data);
}
catch
{
continue;
}
}
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)path.ID,
TableName = channel.ToString(),
Acqtime =
time.ToString("yyyyMMddHHmmss.fff")
});
}
}
}
}
catch (Exception e)
{
throw e;
}
}
}
Console.WriteLine("AAA");
}
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)path.ID,
TableName = string.Empty,
Acqtime =
DateTime.Now.ToString("yyyyMMddHHmmss.fff")
});
}
}
return(list);
}
