/// <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;
}