private int database_action_copy_noconnect_byuesql_for_move(cDatabase loDbS, bool xbForCopyUpdate ) { StringBuilder sb = new StringBuilder(); int PageCount=0; int ResultCode=0; #region ----- 先計算需要處理的筆數 ----- sb.AppendFormat("select count(*) from {0} where datediff( {1} , {2} , getdate() ) {3} {4}", moSource.msTable, moCondition.msUnit, moCondition.msField, moCondition.msCompare, moCondition.msValue ); loDbS.connect(moSource.msHost, moSource.msUid, moSource.msPwd, moSource.msDatabase ); try { loDbS.makeconnect(); loDbS.dsl( 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 // 計算頁數 if ( loDbS.miSumValue % int.Parse(moCondition.msPagesize) == 0) { PageCount = loDbS.miSumValue/int.Parse(moCondition.msPagesize); } else { PageCount = (loDbS.miSumValue/int.Parse(moCondition.msPagesize)) + 1; } for (int i=0;i<PageCount;i++) { sb.Remove(0,sb.Length); sb.AppendFormat("select top {0} * from {1} where datediff( {2} , {3} , getdate() ) {4} {5}", moCondition.msPagesize, moSource.msTable, moCondition.msUnit, moCondition.msField, moCondition.msCompare, moCondition.msValue ); ResultCode = database_action_copy_noconnect_bysql_baseprocess(loDbS, sb.ToString() ); if (ResultCode != (int) cMsg.MsgNo.SUCCESS) { return ResultCode; } else { #region ----- 刪除來源資料 ----- //TODO:加入刪除來源資料 #endregion } } return (int) cMsg.MsgNo.SUCCESS; }
/// <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 ; }
private int database_action_copy_noconnect_bysql_for_copyandcopyupdate(cDatabase loDbS, bool xbForCopyUpdate ) { StringBuilder sb = new StringBuilder(); #region ----- 選取資料 ----- if (xbForCopyUpdate) { sb.AppendFormat("select * from {0} where datediff( {1} , {2} , getdate() ) {3} {4} and {5} <> '{6}'", moSource.msTable , moCondition.msUnit , moCondition.msField , moCondition.msCompare , moCondition.msValue , moDestination.msField , moDestination.msValue ); } else { sb.AppendFormat("select * from {0} where datediff( {1} , {2} , getdate() ) {3} {4}", moSource.msTable, moCondition.msUnit, moCondition.msField, moCondition.msCompare, moCondition.msValue ); } #endregion loDbS.connect(moSource.msHost, moSource.msUid, moSource.msPwd, moSource.msDatabase ); return database_action_copy_noconnect_bysql_baseprocess(loDbS, sb.ToString() ); }
/// <summary> /// SQL Server 資料庫處理,來源與目的為同一台Server不同DataBase /// </summary> /// <param name="loDbS">需要處理的Source Database</param> /// <param name="xbForCopyUpdate">是否為CopyUpdate的動作(Copy到目的資料表後再更新來源特定的欄位值)</param> /// <returns>回傳成功及錯誤訊息</returns> private int database_action_copy_sameserver_bysql(cDatabase loDbS, bool xbForCopyUpdate ) { StringBuilder sb = new StringBuilder(); if (xbForCopyUpdate) { sb.AppendFormat("insert into {0}.{1} select * from {2}.{3} where datediff( {4} , {5} , getdate() ) {6} {7} and {8} <> '{9}'", moDestination.msDatabase, moDestination.msTable, moSource.msDatabase, moSource.msTable, moCondition.msUnit , moCondition.msField , moCondition.msCompare , moCondition.msValue , moDestination.msField , moDestination.msValue ); } else { sb.AppendFormat("insert into {0}.{1} select * from {2}.{3} where datediff( {4} , {5} , getdate() ) {6} {7}", moDestination.msDatabase, moDestination.msTable, moSource.msDatabase, moSource.msTable, moCondition.msUnit , moCondition.msField , moCondition.msCompare , moCondition.msValue ); } loDbS.connect(moSource.msHost, moSource.msUid, moSource.msPwd, moSource.msDatabase ); #region ----- 資料拷貝作業 ----- try { loDbS.makeconnect(); loDbS.dml(sb.ToString()); 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; } finally { loDbS.closeconnect(); } #endregion }