IdentityDataOperation.cs :  » SQL-Clients » Database-Commander » YariSoft » DBUtil » C# / CSharp Open Source

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

namespace YariSoft.DBUtil{
  public class IdentityDataOperation :DBBaseOperation
  {
    #region Local variables
    private ArrayList    identityColumns    = null;
    private ArrayList    readOnlyColumns    = null;
    private OleDbCommand  identityCommand    = null;  
    #endregion

    public IdentityDataOperation ( OleDbConnection Connection, DataView Source, DataView Destination, System.Collections.ArrayList SelectedRows ):
      base ( Connection, Source, SelectedRows ) 
    {
      this.destination = Destination;
    }

    public bool Exec( OnDataChangedEventHandler OnDestinationDataChanged )
    {
      bool status = false;
      ConnectionState previousConnectionState = this.connection.State;
      
      try{
        if( previousConnectionState == ConnectionState.Closed ){
          this.connection.Open();
        }

        DataCopyOperation rowOperation = new DataCopyOperation( this.connection, (( DataView )this.source ), (( DataView )this.destination ), this.selectedRows );
        rowOperation.OnDestinationDataChanged += new YariSoft.DBUtil.DBBaseOperation.OnDataChangedEventHandler ( OnDestinationDataChanged );

        this.PrepareIdentityArrays ( this.connection, rowOperation, ( DataView )this.destination );

        if( rowOperation != null ){
          status = rowOperation.Exec();
          rowOperation.Dispose();
        }

      } finally {
        this.DisposeIdentityArrays ( this.connection, (( DataView ) this.destination ).Table.TableName );
      }

      if( previousConnectionState == ConnectionState.Closed ){
        this.connection.Close();
      }

      return status;
    }

    #region Private functions
    private void PrepareIdentityArrays( OleDbConnection DestConnection, DataCopyOperation RowOperation, DataView Destination )
    {
      if( this.identityColumns == null ){
        this.identityColumns = new ArrayList();
      }
      if( this.readOnlyColumns  == null ){
        this.readOnlyColumns = new ArrayList();
      }
      
      foreach( DataColumn column in Destination.Table.Columns ){
        if( column.AutoIncrement ){
          identityColumns.Add( column );
          column.AutoIncrement = false;
          if( column.ReadOnly ){
            column.ReadOnly = false;
            readOnlyColumns.Add( column );
          }
        }
      }
      if( this.ResetIdentityState( DestConnection, Destination.Table.TableName, true ) ){
        this.PrepareInsertCommand ( Destination.Table, DestConnection );
        if( identityColumns.Count > 0 ){
          RowOperation.OnNeedInsertIdentity += new DataCopyOperation.OnNeedInsertIdentityEventHandler( this.OnNeedInsertIdentity );
        }
      }
    }

    private void PrepareInsertCommand( DataTable Table, OleDbConnection DestConnection )
    {
      string insertSQL = "INSERT INTO [" + Table.TableName + "] (";
      string paramNames = "";
      
      foreach( DataColumn column in Table.Columns ){
        if( ! column.ReadOnly ){
          insertSQL += column.ColumnName + ",";
          paramNames += "?,";
        }
      }
      insertSQL  = insertSQL.Substring ( 0, insertSQL.Length - 1 );
      insertSQL += ") VALUES (";
      insertSQL += paramNames.Substring( 0, paramNames.Length - 1 ) + ")";

      this.identityCommand =new OleDbCommand( insertSQL, DestConnection );
    }

    private bool ResetIdentityState( OleDbConnection DestConnection, string TableName, bool Reset ) 
    {
      bool status = true;
      string SQL = "SET IDENTITY_INSERT [" + TableName + "] ";
      if( Reset ){
        SQL += "ON";
      } else {
        SQL += "OFF";
      }

      if( SQL != "" ){
        ConnectionState prevState = DestConnection.State;
        try{
          if( prevState == ConnectionState.Closed ){
            DestConnection.Open();
          }
          OleDbCommand command = new OleDbCommand ( SQL, DestConnection );
          command.ExecuteNonQuery();
          command.Dispose();
        }catch( Exception Exp ){
          YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
          status = false;
        }
        if( prevState == ConnectionState.Closed ){
          DestConnection.Close();
        }
      }

      return status;
    }

    private void DisposeIdentityArrays( OleDbConnection DestConnection, string TableName )
    {
      this.ResetIdentityState( DestConnection, TableName, false );

      foreach( DataColumn column in this.identityColumns ){
        column.AutoIncrement = true;
      }

      foreach( DataColumn column in this.readOnlyColumns ){
        column.ReadOnly = true;
      }
      this.identityColumns.Clear();
      this.readOnlyColumns.Clear();
      if( this.identityCommand != null ){
        this.identityCommand.Dispose();
      }
    }

    private bool OnNeedInsertIdentity( DataTable TableToUpdate, DataRow RowToUpdate, ref bool Updated )
    {
      Updated = false;
      try{
        this.identityCommand.Parameters.Clear();
        foreach( DataColumn column in TableToUpdate.Columns ){
          if( ! column.ReadOnly ){
            this.identityCommand.Parameters.Add( new OleDbParameter( column.ColumnName, RowToUpdate[ column ] ) );
          }
        }
        if( this.identityCommand.ExecuteNonQuery() == 1 ){
          Updated = true;
        }
      } catch ( Exception Exp ){
        YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
        return false;
      }
      
      return true;
    }
    #endregion

  }
}