/// <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()); }
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> /// <param name="Table"></param> /// <param name="Field"></param> /// <param name="Value"></param> /// <returns></returns> private DaoErrMsg InsertData(string Table, string Field, string Value) { DaoErrMsg err = new DaoErrMsg(); string strSchema = string.Format(@"INSERT INTO {0} ({1}) VALUES ({2});", Table, Field, Value); return(m_SQL.ExecuteNonQuery(strSchema.ToString()));; }
/// <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); }