tablecopyoperation.cs

一个通用的数据库访问层

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Collections;

namespace YariSoft.DBUtil
{
	public class TableCopyOperation : DBBaseOperation
	{
		#region Local variables
		private DataSet schema = null;
		private RowOperation prevRowOperation = RowOperation.RO_None;
		#endregion

		#region Constructor/Destructor
		public TableCopyOperation( DataSet Schema, OleDbConnection Connection, ArrayList SelectedRows )		
			:base( Connection, SelectedRows )
		{
			this.schema = Schema;
		}
		#endregion

		#region Protected functions
		protected override bool ExecOperation( int Position )
		{
			bool status = true;
			if( Util.IsTableExists( this.connection, ( string )this.selectedRows[Position], null )){

				DataTable destination = Util.GetTableSchemaFromDB ( this.connection, ( string )this.selectedRows[Position] );
				if( destination != null ){
					if( ! this.CompareTables( this.schema.Tables[ ( string )this.selectedRows[Position]], destination ) ){
						RowOperation result = RowOperation.RO_None;
						if( prevRowOperation != RowOperation.RO_Overwrite_All && prevRowOperation != RowOperation.RO_Skip_All ){
							ChangeTableForm form = new ChangeTableForm();
							result = form.ShowDialog( this.schema.Tables[ ( string )this.selectedRows[Position]], ref destination );
							form.Dispose();
						} else {
							result = prevRowOperation;
						}

						if( prevRowOperation != RowOperation.RO_Overwrite_All && prevRowOperation != RowOperation.RO_Skip_All ){
							prevRowOperation = result;
						}

						switch( result ){
							case RowOperation.RO_None:
								return false;
							case RowOperation.RO_Skip:
							case RowOperation.RO_Skip_All:	
								return true;
							case RowOperation.RO_Overwrite:	
							case RowOperation.RO_Overwrite_All:	
								status = this.TryToChangeTable( this.schema.Tables[ ( string )this.selectedRows[Position]], destination );
								break;
						}
					}
				}

				destination.Dispose();

			} else {
				DBCreateTableOperation operation =new DBCreateTableOperation( this.connection, this.schema.Tables[ ( string )this.selectedRows[Position]] );
				status = operation.Exec();
			}

			return status;		
		}

		protected override string GetProgressCaption()
		{
			return "Copy tables";
		}

		protected override string GetProgressMessage()
		{
			return "Copy tables to database '" + this.connection.Database + "'" ;
		}

		protected override bool BeforeProcess()
		{
			this.progress.Maximum = this.selectedRows.Count + this.schema.Tables.Count;
			return true;
		}

		protected override bool AfterProcess()
		{
			bool status = true;
			DataTable constraints = this.connection.GetOleDbSchemaTable( OleDbSchemaGuid.Foreign_Keys, new object [] {});
			for( int i = 0; !this.progress.Cancel && status && i <  this.schema.Tables.Count; i++ ){
				string tableName = this.schema.Tables[i].TableName.ToLower();
				foreach( string searchTable in this.selectedRows ){
					if( searchTable.ToLower() == tableName ){
						
						foreach( Constraint tmpConstraint in this.schema.Tables[i].Constraints ){
							if( tmpConstraint is ForeignKeyConstraint ){
								bool found = false;
								foreach( DataRow row in constraints.Rows ){
									if( row["FK_NAME"].ToString().ToLower() == tmpConstraint.ConstraintName.ToLower() ){
										found = true;
										break;
									}
								}
								if( ! found ){
									DBCreateFKConstraintOperation operation = new DBCreateFKConstraintOperation( this.connection, ( ForeignKeyConstraint )tmpConstraint );
									status = operation.Exec();
								}
							}
						}
						break;
					}
				}
				this.progress.Value ++;
			}
			constraints.Dispose();
			return status;
		}
		#endregion

		#region Private functions
		private bool TryToChangeTable( DataTable Source, DataTable Destination )
		{
			bool status = false;
			OleDbTransaction transaction = null;
			ConnectionState previousConnectionState = this.connection.State;
			try{
				if( previousConnectionState == ConnectionState.Closed ){
					this.connection.Open();
				}

				transaction = this.connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
				string tmpTableName = "__tmp_" + Destination.TableName;
				status = this.CreateTable( tmpTableName, Destination, transaction );
				if( status ){
					status = this.CopyDataToTable ( tmpTableName, Destination, transaction );
				}
				if( status ){
					status = this.DropTable( Destination.TableName, transaction );
				}
				if( status ){
					status = this.CreateTable( Source.TableName, Source, transaction );
				}
				if( status ){
					DataTable tmpTable = Source.Copy();
					for( int i = tmpTable.Columns.Count - 1; i >= 0; i-- ){
						string columnName = tmpTable.Columns[i].ColumnName.ToLower();
						bool found = false;
						foreach( DataColumn column in Destination.Columns ){
							if( column.ColumnName.ToLower() == columnName ){
								found = true;
								break;
							}
						}
						if( ! found ){
							tmpTable.Columns.RemoveAt(i);
						}
					}

					tmpTable.TableName = tmpTableName;
					status = this.CopyDataToTable ( Source.TableName, tmpTable, transaction );
					tmpTable.Dispose();
				}
				if( status ){
					status = this.DropTable( tmpTableName, transaction );
				}
				
			} catch( Exception Exp ){
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				status = false;
			}

			if( transaction != null ){
				if( status ){
					transaction.Commit();
				} else {
					transaction.Rollback();
				}
			}
			if( previousConnectionState == ConnectionState.Closed ){
				this.connection.Close();
			}

			return status;
		}

		private bool DropTable( string TableName, OleDbTransaction Transaction )
		{
			bool status = true;
			try{
				DBDropTableOperation operation =new DBDropTableOperation( this.connection, TableName );
				operation.StartTransaction = false;
				operation.Transaction = Transaction;
				status = operation.Exec();
			} catch( Exception Exp ){
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				status = false;
			}
			return status;
		}

		private bool CreateTable( string TableName, DataTable Table, OleDbTransaction Transaction )
		{
			bool status = true;
			try{
				DataTable tmpTable = Table.Copy();
				tmpTable.TableName = TableName;
				DBCreateTableOperation operation =new DBCreateTableOperation( this.connection, tmpTable );
				operation.StartTransaction = false;
				operation.Transaction = Transaction;
				status = operation.Exec();
				tmpTable.Dispose();
			} catch( Exception Exp ){
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				status = false;
			}
			return status;
		}

		private bool CopyDataToTable( string TableName, DataTable Table, OleDbTransaction Transaction )
		{
			bool status = true;
			OleDbCommand command = null;
			try{
				string  strSQL = "INSERT INTO [" + TableName + "] ";
				string columnNames = "";
				foreach ( DataColumn column in Table.Columns ){
					if( ! column.AutoIncrement ){
						columnNames += column.ColumnName + ", ";
					}
				}
				columnNames = columnNames.Substring ( 0, columnNames.Length - 2 );
				strSQL += "( " + columnNames + " ) SELECT " + columnNames;
				strSQL += " FROM [" + Table.TableName + "]";

				command = new OleDbCommand ( strSQL, this.connection, Transaction );
				command.ExecuteNonQuery();

			} catch( Exception Exp ){
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				status = false;
			}
			if( command != null ){
				command.Dispose();
			}
			return status;
		}

		private bool CompareTables( DataTable Source, DataTable Destination )
		{
			if( Source.Columns.Count != Destination.Columns.Count ){
				return false;
			}

			foreach( DataColumn srcColumn in Source.Columns ){
				string srcColumnName = srcColumn.ColumnName.ToLower();
				bool found = false;
				foreach( DataColumn destColumn in Destination.Columns ){
					if( destColumn.ColumnName.ToLower() == srcColumnName ){
						if( ! DBUtil.Util.CompareColumns( srcColumn, destColumn ) ){
							return false;
						}
						found = true;
						break;
					}
				}
				if( ! found ){
					return false;
				}
			}
			return true;
		}
		#endregion
		
	}
}