source: branches/HeuristicLab.DataImporter/HeuristicLab.DataImporter.DbExplorer.Interfaces/DbExplorerBase.cs @ 12009

#region License Information
/* HeuristicLab
 * Copyright (C) 2002-2013 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
 *
 * This file is part of HeuristicLab.
 *
 * HeuristicLab is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * HeuristicLab is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
 */
#endregion

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Globalization;
using System.Windows.Forms;
using System.ComponentModel;
using HeuristicLab.DataImporter.Data.Model;

namespace HeuristicLab.DataImporter.DbExplorer.Interfaces {
  public abstract class DbExplorerBase : IDbExplorer {

    private DbConnectionBase connection;
    protected DbConnectionBase Connection {
      get { return this.connection; }
      set { this.connection = value; }
    }
    public bool IsConnected {
      get {
        if (connection != null)
          return connection.TestConnection();
        return false;
      }
    }

    protected virtual string EscapeSqlString(string s) {
      return s;
    }

    public abstract string MenuItemDescription { get; }
    public abstract bool IsDateDataType(string type);
    public abstract bool IsNumericDataType(string type);
    public abstract bool IsStringDataType(string type);
    public abstract bool IsFilterableDataType(string type);
    protected abstract string ConvertDate(DateTime date);
    protected abstract string ConvertDouble(double value);
    protected abstract IDbConnectionWizard DbConnectionWizard { get; }

    private ProgressChangedEventHandler newRowLoaded;
    public ProgressChangedEventHandler NewRowLoaded {
      get { return newRowLoaded; }
      set { newRowLoaded = value; }
    }

    private DbTable actTable;
    private void FireNewRowLoaded(int actRow) {
      if (newRowLoaded != null && actTable != null && actTable.AffectedRows != 0)
        newRowLoaded(this, new ProgressChangedEventArgs(actRow * 100 / actTable.AffectedRows
          , "Loading " + actRow + " / " + actTable.AffectedRows + " rows of table " + actTable.TableName));
    }

    public void ShowDbConnectionWizard() {
      if (DbConnectionWizard.ShowDialog() == DialogResult.OK) {
        if (!DbConnectionWizard.Connection.TestConnection()) {
          this.connection = null;
          HandleConnectionError();
        } else
          connection = DbConnectionWizard.Connection;
      }
    }

    private void HandleConnectionError() {
      DialogResult result = MessageBox.Show(DbConnectionWizard.Connection.ErrorMessage,
        "Connect to database failed", MessageBoxButtons.RetryCancel);
      if (result == DialogResult.Retry)
        ShowDbConnectionWizard();
    }
    public abstract IEnumerable<DbTable> AllTables { get; }

    public virtual void FillMinMaxValuesForTable(DbTable table) {
      using (DbConnection conn = connection.CreateConnection()) {
        conn.Open();
        List<DbColumn> filterColumns = new List<DbColumn>();
        using (DbCommand cmd = conn.CreateCommand()) {
          string cmdText = @"select ";
          foreach (DbColumn col in table.Columns)
            if (this.IsFilterableDataType(col.SqlDataType) && !this.IsStringDataType(col.SqlDataType)) {
              cmdText += @"min(" + EscapeSqlString(col.ColumnName) + "), max(" + EscapeSqlString(col.ColumnName) + "), ";
              filterColumns.Add(col);
            }
          cmdText += "count(*) from " + EscapeSqlString(table.OwnerName) + "." + EscapeSqlString(table.TableName);
          cmd.CommandText = cmdText;

          using (DbDataReader reader = cmd.ExecuteReader()) {
            reader.Read();
            for (int i = 0; i < reader.FieldCount - 1; i++) {
              if (i % 2 == 0)
                filterColumns[i / 2].MinValue = reader.GetValue(i).ToString();
              else
                filterColumns[i / 2].MaxValue = reader.GetValue(i).ToString();
            }
            table.TotalRows = reader.GetInt32(reader.FieldCount - 1);
            reader.Close();
          }//end using reader
          // cmd.Dispose();
        }//end using command
        conn.Close();
      }//end using connection
      table.UpdateInformationLoaded();
    }

    public void FillAffectedRowsForTable(DbTable table) {
      string cmdText = @"select count(*) from " + EscapeSqlString(table.OwnerName) + "." + EscapeSqlString(table.TableName) + " where ";
      bool executeSql = false;
      foreach (DbColumn col in table.SelectedColumns) {
        if (col.FilterColumn) {
          executeSql = true;
          if (IsNumericDataType(col.SqlDataType)) {
            string min = this.ConvertDouble(double.Parse(col.MinValue));
            string max = this.ConvertDouble(double.Parse(col.MaxValue));
            cmdText += EscapeSqlString(col.ColumnName) + ">=" + min + " and " + EscapeSqlString(col.ColumnName) + "<=" + max + " and ";
          } else if (IsDateDataType(col.SqlDataType)) {
            string x = this.ConvertDate(DateTime.Parse(col.MinValue));
            string y = this.ConvertDate(DateTime.Parse(col.MaxValue));
            cmdText += EscapeSqlString(col.ColumnName) + ">=" + x + " and " + EscapeSqlString(col.ColumnName) + "<=" + y + " and ";
          } else if (IsStringDataType(col.SqlDataType))
            cmdText += EscapeSqlString(col.ColumnName) + " like '%" + EscapeSqlString(col.LikeValue) + "%' and ";
        }
      }
      if (!executeSql) {
        if (table.IsSelected)
          table.AffectedRows = table.TotalRows;
        else
          table.AffectedRows = 0;
      } else {
        cmdText = cmdText.Remove(cmdText.Length - 5);
        using (DbConnection conn = Connection.CreateConnection()) {
          conn.Open();
          using (DbCommand cmd = conn.CreateCommand()) {
            cmd.CommandText = cmdText;
            table.AffectedRows = Convert.ToInt32(cmd.ExecuteScalar());
          }//end using cmd
          conn.Close();
        }//end using connection
      }
    }

    public ColumnGroup CreateColumnGroup(DbTable table) {
      //select clause
      string sqlString = "select ";
      foreach (DbColumn col in table.SelectedColumns) {
        sqlString += EscapeSqlString(table.TableName) + "." + EscapeSqlString(col.ColumnName) + ", ";
      }
      sqlString = sqlString.Remove(sqlString.Length - 2);
      sqlString += " from " + EscapeSqlString(table.OwnerName) + "." + EscapeSqlString(table.TableName);

      //where clause
      bool addedWhereclause = false;
      foreach (DbColumn col in table.SelectedColumns) {
        if (col.FilterColumn) {
          if (!addedWhereclause) {
            sqlString += " where ";
            addedWhereclause = true;
          }
          if (IsNumericDataType(col.SqlDataType))
            sqlString += EscapeSqlString(col.ColumnName) + ">=" + col.MinValue + " and " + EscapeSqlString(col.ColumnName) + "<=" + col.MaxValue + " and ";
          else if (IsDateDataType(col.SqlDataType)) {
            string x = this.ConvertDate(DateTime.Parse(col.MinValue));
            string y = this.ConvertDate(DateTime.Parse(col.MaxValue));
            sqlString += EscapeSqlString(col.ColumnName) + ">=" + x + " and " + EscapeSqlString(col.ColumnName) + "<=" + y + " and ";
          } else if (IsStringDataType(col.SqlDataType))
            sqlString += EscapeSqlString(col.ColumnName) + " like '%" + EscapeSqlString(col.LikeValue) + "%' and ";
        }
      }
      if (addedWhereclause)
        sqlString = sqlString.Remove(sqlString.Length - 5);

      this.actTable = table;
      ColumnGroup columnGroup = CreateColumnGroup(sqlString);
      columnGroup.Name = table.TableName;
      this.actTable = null;
      return columnGroup;
    }

    public ColumnGroup CreateColumnGroup(string sqlString) {
      ColumnGroup columnGroup = new ColumnGroup();
      bool initColumns = true;
      string dbDataType;
      int actRow = 0;

      using (DbConnection conn = connection.CreateConnection()) {
        conn.Open();
        using (DbCommand cmd = conn.CreateCommand()) {
          cmd.CommandText = sqlString;
          using (DbDataReader reader = cmd.ExecuteReader()) {
            while (reader.Read()) {
              //create & initialize columns
              if (initColumns) {
                for (int i = 0; i < reader.FieldCount; i++) {
                  dbDataType = reader.GetDataTypeName(i);
                  if (IsDateDataType(dbDataType))
                    columnGroup.AddColumn(new DateTimeColumn(reader.GetName(i)));
                  else if (IsNumericDataType(dbDataType))
                    columnGroup.AddColumn(new DoubleColumn(reader.GetName(i)));
                  else
                    columnGroup.AddColumn(new StringColumn(reader.GetName(i)));
                }
                initColumns = false;
              }
              //fill columns with values
              for (int i = 0; i < reader.FieldCount; i++) {
                if (reader.IsDBNull(i)) {
                  columnGroup.Columns.ElementAt(i).AddValue(null);
                } else if (columnGroup.Columns.ElementAt(i).DataType == typeof(double?)) {
                  try { columnGroup.Columns.ElementAt(i).AddValue(Convert.ToDouble(reader.GetValue(i))); }
                  catch (DbException) { columnGroup.Columns.ElementAt(i).AddValue(null); }
                } else if (columnGroup.Columns.ElementAt(i).DataType == typeof(DateTime?)) {
                  try { columnGroup.Columns.ElementAt(i).AddValue(reader.GetDateTime(i)); }
                  catch (DbException) { columnGroup.Columns.ElementAt(i).AddValue(null); }
                } else {
                  try { columnGroup.Columns.ElementAt(i).AddValue(reader.GetValue(i).ToString()); }
                  catch (DbException) { columnGroup.Columns.ElementAt(i).AddValue(null); }
                }
              }
              actRow++;
              if (actRow % 1000 == 0)
                FireNewRowLoaded(actRow);
            }
            if (initColumns) {
              for (int i = 0; i < reader.FieldCount; i++) {
                if (reader.GetName(i).ToUpper().Contains("ZEIT"))
                  columnGroup.AddColumn(new DateTimeColumn(reader.GetName(i)));
                else
                  columnGroup.AddColumn(new DoubleColumn(reader.GetName(i)));
              }
            }
            reader.Close();
          }  //end using reader
        } //end using command
      } // using connection

      return columnGroup;
    }
  }
}