コード例 #1
0
ファイル: mainData.cs プロジェクト: ChiangHanLung/PIC_VDS
		/// <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 ;
		
		}
コード例 #2
0
ファイル: mainData.cs プロジェクト: ChiangHanLung/PIC_VDS
        /// <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
                        
        }
コード例 #3
0
ファイル: mainData.cs プロジェクト: ChiangHanLung/PIC_VDS
		/// <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
                           
        }
コード例 #4
0
ファイル: mainData.cs プロジェクト: ChiangHanLung/PIC_VDS
        /// <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;

        }