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

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

namespace YariSoft.DBUtil{
  public class Util
  {
    #region Constructor/Destructor
    public Util()
    {
    }
    #endregion

    #region Public functions
    static public bool PrepareConnectionString ( IDbConnection Connection ) 
    {
      try {
        MSDASC.DataLinksClass dlc = new MSDASC.DataLinksClass();
        ADODB.Connection conn;
        if( Connection.ConnectionString != "" ){
          conn = new ADODB.Connection();
          conn.ConnectionString = Connection.ConnectionString;
          object obj = ( object )conn;
          if( dlc.PromptEdit ( ref obj ) ){
            Connection.ConnectionString = conn.ConnectionString;
          }
        } else {
          conn = ( ADODB.Connection )dlc.PromptNew();  
          if( conn != null ){
            Connection.ConnectionString = conn.ConnectionString;
          }
        }
      } catch(Exception Exp) {
        YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
        return false;
      }
      return true;
    }

    static public bool TryToSaveConcurrencyData ( DBConcurrencyException Exp, OleDbDataAdapter DataAdapter ) 
    {
      if( Exp == null || 
        Exp.Row == null || 
        DataAdapter == null || 
        DataAdapter.SelectCommand == null || 
        DataAdapter.SelectCommand.Connection == null ){
        return false;
      }
      
      if( Exp.Row.RowState != DataRowState.Modified ){
        return false;
      }

      bool result = true;
      ConnectionState prevState = DataAdapter.SelectCommand.Connection.State;
      OleDbCommand checkCommand = new OleDbCommand();
      OleDbCommand updateCommand = new OleDbCommand();
      try{
        checkCommand.Connection = DataAdapter.SelectCommand.Connection;
        checkCommand.CommandText = "SELECT COUNT(*) FROM [" + Exp.Row.Table.TableName + "]" ;
        
        updateCommand.Connection = DataAdapter.SelectCommand.Connection;
        updateCommand.CommandText = "UPDATE ["+ Exp.Row.Table.TableName +"] SET ";
        
        string conditionString = " WHERE ";

        foreach( DataColumn column in Exp.Row.Table.Columns ){
          if( ! column.AutoIncrement ){
            string curString = column.ColumnName + " = ?";
            updateCommand.CommandText  += curString +",";

            object val = Exp.Row[column, DataRowVersion.Current];
            if( Exp.Row.HasVersion( DataRowVersion.Proposed ) ){
              val = Exp.Row[column, DataRowVersion.Proposed];
            }

            updateCommand.Parameters.Add( new OleDbParameter( ( column.ColumnName ), val ) );

            conditionString += "(" + curString;

            if( column.AllowDBNull ){
              conditionString += " OR ( ? IS NULL AND " + column.ColumnName + " IS NULL )";
            }
            conditionString += ") AND";

            if( Exp.Row.HasVersion( DataRowVersion.Original ) ){
              val = Exp.Row[column, DataRowVersion.Original];
            }

            checkCommand.Parameters.Add( new OleDbParameter( ( "Original_" + column.ColumnName ), val ) );

            if( column.AllowDBNull ){
              checkCommand.Parameters.Add( new OleDbParameter( ( "Original_" + column.ColumnName + "1" ), val ) );
            }
          }
        }

        conditionString       = conditionString.Substring( 0, conditionString.Length - 4 );
        checkCommand.CommandText += conditionString;
        
        if ( prevState == ConnectionState.Closed ){
          DataAdapter.SelectCommand.Connection.Open();
        }

        if( (int)checkCommand.ExecuteScalar()== 1 ){
          updateCommand.CommandText  = updateCommand.CommandText.Substring ( 0, updateCommand.CommandText.Length - 1 );
          updateCommand.CommandText += conditionString;
        
          OleDbParameter[] paramsArray = new OleDbParameter[checkCommand.Parameters.Count];
          checkCommand.Parameters.CopyTo(paramsArray, 0);
          checkCommand.Parameters.Clear();

          foreach( OleDbParameter param in paramsArray ){
            updateCommand.Parameters.Add( param );
          }

          if( updateCommand.ExecuteNonQuery() != 1 ){
            result = false;
          }
        } else {
          result = false;
        }

        if ( prevState == ConnectionState.Closed ){
          DataAdapter.SelectCommand.Connection.Close();
        }
        //Exp.Row.ClearErrors();

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

      if ( prevState == ConnectionState.Closed ){
        DataAdapter.SelectCommand.Connection.Close();
      }

      checkCommand.Dispose();
      updateCommand.Dispose();

      return result;
    }

    static public bool IsTableExists ( OleDbConnection Connection, string TableName, OleDbTransaction Transaction ) 
    {
      bool status = true;
      OleDbCommand command = null;
      try{
        command = new OleDbCommand("Select count(*)From ["+ TableName +"] Where 2<1", Connection );
        if( Transaction != null ){
          command.Transaction = Transaction;
        }
        command.ExecuteNonQuery();
      }catch{
        status = false;
      }
      if( command != null ){
        command.Dispose();
      }
      return status;
    }

    static public DataTable GetTableFromDB ( OleDbConnection Connection, string SQL, string TableName  ) 
    {
      OleDbCommand command    = new OleDbCommand( SQL, Connection );
      OleDbDataAdapter adapter  = new OleDbDataAdapter();
      adapter.SelectCommand    = command;
      DataSet dataset        = new DataSet();
      adapter.Fill( dataset, TableName );
      DataTable result = dataset.Tables[ TableName ].Copy();
      dataset.Dispose();
      adapter.Dispose();
      command.Dispose();
      return result;
    }

    static public DataTable GetTableSchemaFromDB ( OleDbConnection Connection, string TableName ) 
    {
      string SQL = "Select * From ["+ TableName +"]";
      OleDbCommand command    = new OleDbCommand( SQL, Connection );
      OleDbDataAdapter adapter  = new OleDbDataAdapter();
      adapter.SelectCommand    = command;
      DataTable table = new DataTable( TableName );
      DataTable result = adapter.FillSchema ( table, System.Data.SchemaType.Source );
      table.Dispose();
      adapter.Dispose();
      command.Dispose();
      return result;
    }


    public static bool CompareColumns( DataColumn Source, DataColumn Destination )
    {
      if( Source.AllowDBNull != Destination.AllowDBNull ){
        return false;
      }
      if( Source.AutoIncrement != Destination.AutoIncrement ){
        return false;
      }
      if( Source.AutoIncrementStep != Destination.AutoIncrementStep ){
        return false;
      }
      if( Source.DataType != Destination.DataType ){
        return false;
      }
      if( Source.DefaultValue != Destination.DefaultValue ){
        return false;
      }
      if( Source.MaxLength != Destination.MaxLength ){
        return false;
      }
      if( Source.ReadOnly != Destination.ReadOnly ){
        return false;
      }
      if( Source.Unique != Destination.Unique ){
        return false;
      }

      return true;
    }

    public static string GetTableName( string TableName )
    {
      string result = TableName;
      result = "[" + result + "]";
      return result;
    }
    
    public static string GetTableNameByFK( string FKName, DataSet Schema )
    {
      int dotPosition = FKName.LastIndexOf( "." ) ;
      if( dotPosition > -1 ){
        FKName = FKName.Substring( ( dotPosition + 1 ), ( FKName.Length - dotPosition - 1 ) );
      }
      
      string childTableName = "";
      DataRelation relation = Schema.Relations[ FKName ];
      if( relation == null ){
        return "";
      } else {
        childTableName = relation.ChildTable.TableName;
      }
      return childTableName;
    }

    #endregion
  }
}