Component File /DBUtil/TableCopyOperation.cs (C#)
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
}
}