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

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

namespace YariSoft.DBUtil{
  public class DBCreateFKConstraintOperation : DBSchemaOperation
  {
    #region Local variables
    private ForeignKeyConstraint constraint;
    #endregion

    #region Constructor/Destructor
    public DBCreateFKConstraintOperation( OleDbConnection Connection, ForeignKeyConstraint Constraint )
    {
      this.connection = Connection;
      this.constraint = Constraint;
    }
    #endregion

    #region Public functions
    public override bool PrepareSQL()
    {
      bool status = false;
      if( this.connection.Provider.ToUpper().Substring( 0, 8 ) == "SQLOLEDB" ){
        status = this.PrepareMSSQLString();
      } else {
        YariSoft.Utils.YMessages.Show( "Unsupported OLE DB data provider!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
      }
      return status;
    }
    #endregion

    #region Private functions
    private bool PrepareMSSQLString()
    {
      bool status = true;
      bool tablesFound = false;
      ConnectionState previousConnectionState = this.connection.State;
      try{
        if( previousConnectionState == ConnectionState.Closed ){
          this.connection.Open();
        }
        if(    Util.IsTableExists( this.connection, this.constraint.Table.TableName, this._transaction ) 
          &&  Util.IsTableExists( this.connection, this.constraint.RelatedTable.TableName, this._transaction ) ){
          tablesFound   = true;
        }
        
      } catch( Exception Exp ){
        YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
        status = false;
      }

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

      if( ! status ){
        return status;
      }

      if( tablesFound ){
        string tempSQL ="ALTER TABLE [" + this.constraint.Table.TableName  + "] ";
        tempSQL += "ADD CONSTRAINT " + this.constraint.ConstraintName + " ";
        tempSQL += "FOREIGN KEY (";
        foreach ( DataColumn column in this.constraint.Columns ){
          tempSQL  += column.ColumnName + ", ";
        }
        tempSQL = tempSQL.Substring ( 0, tempSQL.Length - 2 ) + ") ";
        tempSQL  += "REFERENCES " + constraint.RelatedTable.TableName + "(";
        foreach ( DataColumn column in this.constraint.RelatedColumns ){
          tempSQL  += column.ColumnName + ", ";
        }
        tempSQL = tempSQL.Substring ( 0, tempSQL.Length - 2 ) + ") ";
        /*
        if( this.constraint.UpdateRule == Rule.Cascade ){
          tempSQL += " ON UPDATE CASCADE ";
        }
        if( this.constraint.DeleteRule == Rule.Cascade ){
          tempSQL += " ON DELETE CASCADE ";
        }
        */
        this.strSQL += "\r\n" + tempSQL;
      }

      return status;
    }
    #endregion
  }
}