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

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

namespace YariSoft.DBUtil{
  public class SchemaGenerator
  {
    #region Local variables
    private DataSet _schema = null; 
    private DataTable _tables = null;
    private Hashtable _adapters = null;
    #endregion

    #region Properties
    public DataSet Schema 
    {
      get{ return this._schema; }
    }

    public DataTable Tables 
    {
      get{ return this._tables ; }
    }

    public Hashtable Adapters 
    {
      get{ return this._adapters ; }
    }


    #endregion

    #region Constructor/Destructor
    public SchemaGenerator()
    {
      this._schema = new DataSet();
      this._adapters = new Hashtable();
    }

    ~SchemaGenerator()
    {
      this.Dispose();
    }

    public void Dispose()
    {
      if( this._schema != null ){
        this._schema.Dispose();
      }
      if( this._tables != null ){
        this._tables.Dispose();
      }
      if( this._adapters != null ){
        foreach ( YOleDbAdapter adapter in this._adapters.Values ){
          adapter.Dispose();
        }
      }
    }
    #endregion

    #region Public functions
    public bool BuildSchema( OleDbConnection Connection )
    {
      bool status = true;

      ConnectionState previousConnectionState = Connection.State;
      try{
        if( previousConnectionState == ConnectionState.Closed ){
          Connection.Open();
        }
        this._schema.Relations.Clear();
        foreach( DataTable table in this._schema.Tables ){
          for( int i = table.Constraints.Count - 1; i >= 0; i-- ){
            if( table.Constraints[i] is ForeignKeyConstraint ){
              table.Constraints.Remove(table.Constraints[i]);
            }
          }
        }
        this._schema.Tables.Clear();

        this._tables = Connection.GetOleDbSchemaTable( OleDbSchemaGuid.Tables,
                                    new object[] {null, null, null, "TABLE"});
        this._tables.TableName = "__YS_Tables_Internal";
        foreach ( DataRow Row in this._tables.Rows ){
          if( ! this.AddTable( Connection, ( string )Row["TABLE_NAME"]) ){
            status = false;
            break;
          }
        }
        this._schema.EnforceConstraints = false;
        if( status ){
          status = this.AddConstraints( Connection );
        }

      } catch( Exception Exp ) {
        YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
        status = false;
      }

      if( previousConnectionState == ConnectionState.Closed ){
        Connection.Close();
      }
      return status;
    }
    #endregion

    #region Private functions
    private bool AddTable( OleDbConnection Connection, string TableName )
    {
      try {
        System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM ["+ TableName +"] Where 2 < 1", Connection );
        DataTable[] tables = dataAdapter.FillSchema( this._schema, System.Data.SchemaType.Source );
        foreach ( DataTable table in tables ){
          table.TableName = TableName;
        }
        dataAdapter.Dispose();
      } catch(Exception Exp ) {
        YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
        return false;
      }

      return true;
    }

    private bool AddConstraints( OleDbConnection Connection )
    {
      bool status = true;
      DataTable constraints = null;
      try {
        constraints = Connection.GetOleDbSchemaTable( OleDbSchemaGuid.Foreign_Keys, new object[] {});
        foreach ( DataRow row in constraints.Rows ){
          DataColumn pCol = this._schema.Tables[row["PK_TABLE_NAME"].ToString()].Columns[row["PK_COLUMN_NAME"].ToString()];
          DataColumn fCol = this._schema.Tables[row["FK_TABLE_NAME"].ToString()].Columns[row["FK_COLUMN_NAME"].ToString()];
          this._schema.Relations.Add( row["FK_NAME"].ToString(), pCol, fCol );
        }
      } catch(Exception Exp ) {
        YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
        status = false;
      }

      if( constraints != null ){
        constraints.Dispose();
      }
      return status;
    }
    #endregion
  }
}