/// <summary>
/// 連接Access資料庫
/// </summary>
/// <returns></returns>
private DaoErrMsg ConnectAccess()
{
DaoErrMsg Err = new DaoErrMsg();
if (m_SQL != null)
{
//表示已開啟過;//
return(Err);
}
m_SQL = CreateDbCom(DaoConfigFile.Instance.FileDatabase);
Err = m_SQL.Connect();
if (Err.isError)
{
System.Diagnostics.Debug.WriteLine(Err.ErrorMsg);
m_SQL = null;
return(Err);
}
DatabaseConnectedChange?.Invoke(true);
return(Err);
}
/// <summary>
/// 上傳本地資料庫資料至Server端資料庫，主要上傳[作業員]及[車型資料]這兩個表
/// </summary>
/// <param name="ServerDbPath"></param>
/// <returns></returns>
internal DaoErrMsg UploadLocalDatabase(string ServerDbPath)
{
DaoErrMsg Err = new DaoErrMsg();
DaoDbCommon ServerSQL = CreateDbCom(ServerDbPath);
Err = ServerSQL.Connect();
if (Err.isError)
{
System.Diagnostics.Debug.WriteLine(Err.ErrorMsg);
Logger.Info(Err.ErrorMsg);
ServerSQL = null;
return(Err);
}
//將本地資料庫的[作業員]及[車型資料]更新至Server端;//
string strSchema;
//讀取本地端車型資料;//
DataTable dtOp = GetDataTable(m_SQL, "SELECT * FROM 作業員");
//更新至Server資料庫作業員表格;//
ServerSQL.ExecuteNonQuery("DELETE * FROM 作業員");
foreach (DataRow row in dtOp.Rows)
{
strSchema = string.Format("INSERT INTO 作業員(代碼, 作業員姓名, 密碼, 權限) VALUES( '{0}', '{1}', '{2}', '{3}')",
row[0].ToString(),
row[1].ToString(),
row[2].ToString(),
row[3].ToString());
ServerSQL.ExecuteNonQuery(strSchema);
}
//讀取本地車型資料;//
DataTable dtCar = GetDataTable(m_SQL, "SELECT * FROM 車型資料");
//更新至Server資料庫車型資料表格;//
ServerSQL.ExecuteNonQuery("DELETE * FROM 車型資料");
foreach (DataRow row in dtCar.Rows)
{
strSchema = string.Format("INSERT INTO 車型資料(件號, 車型, 簡碼) VALUES( '{0}', '{1}', '{2}')",
row[0].ToString(),
row[1].ToString(),
row[2].ToString());
ServerSQL.ExecuteNonQuery(strSchema);
}
ServerSQL.Close();
ServerSQL = null;
return(new DaoErrMsg());
}
/// <summary>
/// 照SQL語法取得Table資料
/// </summary>
/// <param name="Schema"></param>
/// <returns></returns>
private DataTable GetDataTable(DaoDbCommon DbCom, string Schema, params object[] Values)
{
DataTable Dt;
DaoErrMsg Err = DbCom.GetDataTable(Schema, out Dt, Values);
if (Err.isError)
{
return(null);
}
return(Dt);
}
/// <summary>
/// 關閉資料庫連接
/// </summary>
internal void CloseDatabase()
{
if (m_SQL == null)
{
return;
}
m_SQL.Close();
m_SQL = null;
DatabaseConnectedChange?.Invoke(false);
}
/// <summary>
/// 檢查指定的資料表裡有沒有指定的欄位
/// </summary>
/// <param name="TableName"></param>
/// <param name="FieldName"></param>
/// <returns></returns>
private bool CheckFileld(DaoDbCommon DbCom, string TableName, string FieldName)
{
string strSchema = "select * from " + TableName;
DataTable Dt = GetDataTable(DbCom, strSchema);
if (Dt == null)
{
return(false);
}
return(Dt.Columns.Contains(FieldName));
}
/// <summary>
/// 檢查指定的Table是否存在
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
private bool CheckTable(DaoDbCommon DbCom, string TableName)
{
string strSchema = string.Format("select count(*) from MSysObjects where Name='{0}'", TableName);
string TableCount;
DaoErrMsg err = DbCom.ExecuteScalar(strSchema, out TableCount);
if (TableCount.ToInt() <= 0)
{
return(false);
}
return(true);
}
/// <summary>
/// 檢查Server端的Database是否可以連線
/// </summary>
/// <param name="Path"></param>
/// <returns></returns>
internal DaoErrMsg CheckServerDb(string Path)
{
DaoDbCommon ServerSQL = CreateDbCom(Path);
DaoErrMsg Err = ServerSQL.Connect();
if (Err.isError)
{
System.Diagnostics.Debug.WriteLine(Err.ErrorMsg);
ServerSQL = null;
return(Err);
}
//檢查欄位資訊並更新;//
CheckDatabase(ServerSQL);
ServerSQL.Close();
ServerSQL = null;
return(Err);
}
private DaoErrMsg CreateUpdateTimeTable(DaoDbCommon DbCom)
{
DaoErrMsg err = new DaoErrMsg();
if (CheckTable(DbCom, "更新時間") == false)
{
string strSchema = @"CREATE TABLE `更新時間` (
`ID` Long NOT NULL IDENTITY(1, 1) PRIMARY KEY,
`UpdateTime` DateTime NOT NULL,
`OpCode` VarChar(16) NOT NULL,
`User` VarChar(32) NOT NULL)" ;
err = DbCom.ExecuteNonQuery(strSchema.ToString());
if (err.isError == true)
{
return(err);
}
}
return(err);
}
/// <summary>
/// 檢查資料庫是否有必要的資料表，沒有的話就建立
/// </summary>
private DaoErrMsg CheckDatabase(DaoDbCommon DbCom)
{
StringBuilder sbSchema = new StringBuilder();
DaoErrMsg err = new DaoErrMsg();
if (CheckFileld(DbCom, "機台資訊", "預設機台") == false)
{
sbSchema.Append(@"ALTER TABLE 機台資訊 ADD COLUMN 預設機台 VARCHAR(1) DEFAULT 'N'; ");
err = DbCom.ExecuteNonQuery(sbSchema.ToString());
if (err.isError == true)
{
return(err);
}
sbSchema.Init();
sbSchema.Append(@"UPDATE 機台資訊 SET 預設機台='N';");
err = DbCom.ExecuteNonQuery(sbSchema.ToString());
if (err.isError == true)
{
return(err);
}
}
if (CheckFileld(DbCom, "備份路徑", "Type") == false)
{
sbSchema.Init();
sbSchema.Append("DROP TABLE 備份路徑;");
err = DbCom.ExecuteNonQuery(sbSchema.ToString());
if (err.isError == true)
{
return(err);
}
sbSchema.Init();
sbSchema.Append(@"CREATE TABLE `備份路徑` (
`Type` VarChar(15) DEFAULT '',
`ADDR` VarChar(50) WITH COMP );" );
err = DbCom.ExecuteNonQuery(sbSchema.ToString());
if (err.isError == true)
{
return(err);
}
sbSchema.Init();
string strTable = "備份路徑";
string strField = "[Type], [ADDR]";
err = InsertData(strTable, strField, "'Local', ''");
err = InsertData(strTable, strField, "'Server', ''");
err = InsertData(strTable, strField, "'ServerAccount', ''");
err = InsertData(strTable, strField, "'ServerPW', ''");
err = InsertData(strTable, strField, "'Report', ''");
err = InsertData(strTable, strField, "'Database', ''");
if (err.isError == true)
{
return(err);
}
}
//建立更新時間Table;//
//err = CreateUpdateTimeTable(DbCom);
//if (err.isError == true)
// return err;
return(err);
}