/// <summary> /// SQL Server 資料庫處理,來源與目的必須透過AP來連接 /// </summary> /// <param name="loDbS">需要處理的Source Database</param> /// <param name="xbForCopyUpdate">是否為CopyUpdate的動作(Copy到目的資料表後再更新來源特定的欄位值)</param> /// <returns>回傳成功及錯誤訊息</returns> private int database_action_copy_noconnect_bysql(cDatabase loDbS, bool xbForCopyUpdate ) { StringBuilder sb = new StringBuilder(); #region ----- 來源資料處理 ----- if( (this.moAction.miType == cAction.Type.COPY) || (this.moAction.miType == cAction.Type.COPY_UPDATE) )//是copy及copy update的動作 { return database_action_copy_noconnect_bysql_for_copyandcopyupdate(loDbS, xbForCopyUpdate ); } else { if (this.moAction.miType == cAction.Type.MOVE) // 是move的動作 { return database_action_copy_noconnect_byuesql_for_move(loDbS, xbForCopyUpdate ); } } #endregion #region ----- 來源資料開啟及選取 ----- try { loDbS.makeconnect(); loDbS.dql( sb.ToString() ) ; } catch(System.Data.SqlClient.SqlException se) { moLog.write("詳細錯誤內容:" + se.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } catch(Exception e) { moLog.write("詳細錯誤內容:" + e.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } finally { loDbS.closeconnect(); } #endregion // there is no data if( loDbS.miRowCount == 0 ) { return (int) cDatabase.DbErrorNo.SUCCESS ; } // destination cDatabase loDbD ; string lsSqlDValuePart = "" ; switch( moDestination.miServer ) { case cDestination.Server.MSSQL : loDbD = new cDbMsSql(); break ; case cDestination.Server.ACCESS : loDbD = new cDbAccess() ; break ; case cDestination.Server.NULL : return (int) cMsg.MsgNo.ERROR_DESTINATION_SERVER_IS_NULL ; break ; default : return (int) cMsg.MsgNo.ERROR_DESTINATION_SERVER_IS_UNKNOWN ; break ; } loDbD.connect(moDestination.msHost, moDestination.msUid, moDestination.msPwd, moDestination.msDatabase ); #region ----- 目的資料處理 ----- try { #region ----- 開啟資料連線 ----- // 設定流程步驟 moDestination.miFlowStep = cDestination.DestinationFlowStep.OpenConnection; loDbD.makeconnect(); #endregion #region ----- Truncate TempTable ------ // 設定流程步驟 moDestination.miFlowStep = cDestination.DestinationFlowStep.TruncateTempTable; if (this.moAction.miType == cAction.Type.MOVE) // 是move的動作 { sb.Remove(0,sb.Length); sb.AppendFormat("Truncate Table {0}", moDestination.msTemptable ); loDbD.dml( sb.ToString() ); } #endregion #region ----- 每次送一筆資料處理 ----- int ProcessCount = 0; // 設定流程步驟 moDestination.miFlowStep = cDestination.DestinationFlowStep.SendDataToDescByLoop; foreach( DataRow loRow in loDbS.moDataSet.Tables[ cDatabase.msTableNameInDataSet ].Rows ) { lsSqlDValuePart = "" ; int liColumnsIndex = 0 ; foreach( DataColumn loColumn in loDbS.moDataSet.Tables[ cDatabase.msTableNameInDataSet ].Columns) { if( liColumnsIndex > 0 ) lsSqlDValuePart += "," ; if (loRow[ loColumn ] == System.DBNull.Value) // 加入NULL判別 lsSqlDValuePart += "NULL"; else { if( loColumn.DataType.ToString() == "System.DateTime" ) { lsSqlDValuePart += "'" + DateTime.Parse( loRow[ loColumn ].ToString() ).ToString("yyyy/MM/dd HH:mm:ss") + "'" ; } else { lsSqlDValuePart += "'" + loRow[ loColumn ] + "'" ; } } liColumnsIndex++ ; } sb.Remove(0,sb.Length); sb.AppendFormat("insert into {0} values ( {1} )", moDestination.msTemptable, lsSqlDValuePart ); loDbD.dml(sb.ToString()); ProcessCount ++; } #endregion #region ----- 比較總量 ----- // 設定流程步驟 moDestination.miFlowStep = cDestination.DestinationFlowStep.CompartTotalRow; // 處理成功的總量與選取資料的總筆數相比 if ( ProcessCount != loDbS.miRowCount ) { sb.Remove(0,sb.Length); sb.AppendFormat("詳細錯誤內容:來源筆數({0}筆)與Temp資料表筆數({1}筆)不相符!", loDbS.miRowCount, ProcessCount ); throw new Exception(sb.ToString()); } #endregion #region ----- 輸入到正式資料表 ----- // 設定流程步驟 moDestination.miFlowStep = cDestination.DestinationFlowStep.InsertIntoRealTable; sb.Remove(0,sb.Length); sb.AppendFormat("Insert into {0} Select * From {1}", moDestination.msTable, moDestination.msTemptable ); loDbD.dml(sb.ToString()); #endregion } catch ( System.Data.SqlClient.SqlException se ) { moLog.write("詳細錯誤內容:" + se.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); if ( (moDestination.miFlowStep == cDestination.DestinationFlowStep.SendDataToDescByLoop) || (moDestination.miFlowStep == cDestination.DestinationFlowStep.CompartTotalRow) ) { #region ----- 發生錯誤,truncate temp table ----- try { sb.Remove(0,sb.Length); sb.AppendFormat("Truncate Table {0}", moDestination.msTemptable ); loDbD.dml(sb.ToString()); } catch( System.Data.SqlClient.SqlException se1 ) { moLog.write("詳細錯誤內容:" + se1.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } catch( Exception e ) { moLog.write("詳細錯誤內容:" + e.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } #endregion } if ( moDestination.miFlowStep == cDestination.DestinationFlowStep.InsertIntoRealTable ) { //TODO:找時間加入發送E-Mail的程式 } return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } catch ( Exception e ) { moLog.write("詳細錯誤內容:" + e.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); if ( (moDestination.miFlowStep == cDestination.DestinationFlowStep.SendDataToDescByLoop) || (moDestination.miFlowStep == cDestination.DestinationFlowStep.CompartTotalRow) ) { #region ----- 發生錯誤,truncate temp table ----- try { sb.Remove(0,sb.Length); sb.AppendFormat("Truncate Table {0}", moDestination.msTemptable ); loDbD.dml(sb.ToString()); } catch( System.Data.SqlClient.SqlException se ) { moLog.write("詳細錯誤內容:" + se.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } catch( Exception e1 ) { moLog.write("詳細錯誤內容:" + e1.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } #endregion } if ( moDestination.miFlowStep == cDestination.DestinationFlowStep.InsertIntoRealTable ) { //TODO:找時間加入發送E-Mail的程式 } return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } finally { loDbD.closeconnect(); } #endregion return (int) cMsg.MsgNo.SUCCESS ; }
/// <summary> /// 資料庫刪除作業 /// </summary> /// <param name="xbForRemove">是不是move的功能帶過來的</param> /// <returns>回傳處理訊息代碼</returns> private int database_action_delete(bool xbForRemove) { cDatabase loDb ; StringBuilder sb = new StringBuilder(); switch( moSource.miServer ) { case cSource.Server.MSSQL : loDb = new cDbMsSql() ; sb.AppendFormat("delete from {0} where datediff( {1} , {2} , getdate() ) {3} {4}", moSource.msTable, moCondition.msUnit, moCondition.msField, moCondition.msCompare, moCondition.msValue ); break ; case cSource.Server.ACCESS : loDb = new cDbAccess() ; break ; case cSource.Server.NULL : return (int) cMsg.MsgNo.ERROR_SOURCE_SERVER_IS_NULL ; break ; default : return (int) cMsg.MsgNo.ERROR_SOURCE_SERVER_IS_UNKNOWN ; break ; } loDb.connect(moSource.msHost, moSource.msUid, moSource.msPwd, moSource.msDatabase ); # region ----- 刪除資料處理 ----- try { loDb.makeconnect(); loDb.dml( sb.ToString() ) ; return (int) cMsg.MsgNo.SUCCESS ; } catch ( System.Data.SqlClient.SqlException se ) { moLog.write("詳細錯誤內容:" + se.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); if (xbForRemove) { //TODO:加入發E-Mail的程式 } return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } catch ( Exception e ) { moLog.write("詳細錯誤內容:" + e.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); if (xbForRemove) { //TODO:加入發E-Mail的程式 } return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } finally { loDb.closeconnect(); } #endregion }
/// <summary> /// 資料庫更新作業 /// </summary> /// <returns>回傳處理訊息代碼</returns> private int database_action_update() { cDatabase loDb ; StringBuilder sb = new StringBuilder(); switch( moSource.miServer ) { case cSource.Server.MSSQL : loDb = new cDbMsSql(); sb.AppendFormat("update {0} set {1} = '{2}' where datediff( {3} , {4} , getdate() ) {5} {6} and {7} <> '{8}'", moSource.msTable, moDestination.msField, moDestination.msValue, moCondition.msUnit, moCondition.msField, moCondition.msCompare, moCondition.msValue, moDestination.msField, moDestination.msValue ); break; case cSource.Server.ACCESS : loDb = new cDbAccess() ; break ; case cSource.Server.NULL : return (int) cMsg.MsgNo.ERROR_SOURCE_SERVER_IS_NULL ; break ; default : return (int) cMsg.MsgNo.ERROR_SOURCE_SERVER_IS_UNKNOWN ; break ; } loDb.connect(moSource.msHost, moSource.msUid, moSource.msPwd, moSource.msDatabase ); #region ----- 更新資料處理 ----- try { loDb.makeconnect(); loDb.dml(sb.ToString()) ; loDb.closeconnect(); return (int) cMsg.MsgNo.SUCCESS ; } catch ( System.Data.SqlClient.SqlException se ) { moLog.write("詳細錯誤內容:" + se.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } catch ( Exception e ) { moLog.write("詳細錯誤內容:" + e.Message, cLog.LogLevel.ERROR, new StackTrace(true) ); return (int)cMsg.MsgNo.ERROR_DATABASE_ERROR; } #endregion }
/// <summary> /// DataBase Copy作業 /// </summary> /// <param name="xbForCopyUpdate">是否為CopyUpdate的動作(Copy到目的資料表後再更新來源特定的欄位值)</param> /// <returns>回傳處理訊息</returns> private int database_action_copy(bool xbForCopyUpdate) { // source cDatabase loDbS ; int liDbProcessNo = 0; switch( moSource.miServer ) { case cSource.Server.MSSQL : loDbS = new cDbMsSql() ; switch ( moConnection.miType ) { case cConnection.Type.NOConnect: //無任何資料連結 liDbProcessNo = database_action_copy_noconnect_bysql(loDbS, xbForCopyUpdate ); break; case cConnection.Type.DBLink: //透過DB_Link liDbProcessNo = database_action_copy_dblink_bysql(loDbS, xbForCopyUpdate ); break; case cConnection.Type.SameServer: //同一台 SQL SERVER liDbProcessNo = database_action_copy_sameserver_bysql(loDbS, xbForCopyUpdate ); break; } break ; case cSource.Server.ACCESS : loDbS = new cDbAccess() ; break ; case cSource.Server.NULL : return (int) cMsg.MsgNo.ERROR_SOURCE_SERVER_IS_NULL ; break ; default : return (int) cMsg.MsgNo.ERROR_SOURCE_SERVER_IS_UNKNOWN ; break ; } return liDbProcessNo; }