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()
);
}
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 資料庫處理,來源與目的為同一台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
}