Free cookie consent management tool by TermsFeed Policy Generator

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

Last change on this file since 12912 was 9615, checked in by mkommend, 11 years ago

#1734: Updated copyright information in all DataImporter classes.

File size: 11.0 KB
Line 
1#region License Information
2/* HeuristicLab
3 * Copyright (C) 2002-2013 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
4 *
5 * This file is part of HeuristicLab.
6 *
7 * HeuristicLab is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation, either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * HeuristicLab is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
19 */
20#endregion
21
22using System;
23using System.Collections.Generic;
24using System.Linq;
25using System.Text;
26using System.Data.Common;
27using System.Globalization;
28using System.Windows.Forms;
29using System.ComponentModel;
30using HeuristicLab.DataImporter.Data.Model;
31
32namespace HeuristicLab.DataImporter.DbExplorer.Interfaces {
33  public abstract class DbExplorerBase : IDbExplorer {
34
35    private DbConnectionBase connection;
36    protected DbConnectionBase Connection {
37      get { return this.connection; }
38      set { this.connection = value; }
39    }
40    public bool IsConnected {
41      get {
42        if (connection != null)
43          return connection.TestConnection();
44        return false;
45      }
46    }
47
48    protected virtual string EscapeSqlString(string s) {
49      return s;
50    }
51
52    public abstract string MenuItemDescription { get; }
53    public abstract bool IsDateDataType(string type);
54    public abstract bool IsNumericDataType(string type);
55    public abstract bool IsStringDataType(string type);
56    public abstract bool IsFilterableDataType(string type);
57    protected abstract string ConvertDate(DateTime date);
58    protected abstract string ConvertDouble(double value);
59    protected abstract IDbConnectionWizard DbConnectionWizard { get; }
60
61    private ProgressChangedEventHandler newRowLoaded;
62    public ProgressChangedEventHandler NewRowLoaded {
63      get { return newRowLoaded; }
64      set { newRowLoaded = value; }
65    }
66
67    private DbTable actTable;
68    private void FireNewRowLoaded(int actRow) {
69      if (newRowLoaded != null && actTable != null && actTable.AffectedRows != 0)
70        newRowLoaded(this, new ProgressChangedEventArgs(actRow * 100 / actTable.AffectedRows
71          , "Loading " + actRow + " / " + actTable.AffectedRows + " rows of table " + actTable.TableName));
72    }
73
74    public void ShowDbConnectionWizard() {
75      if (DbConnectionWizard.ShowDialog() == DialogResult.OK) {
76        if (!DbConnectionWizard.Connection.TestConnection()) {
77          this.connection = null;
78          HandleConnectionError();
79        } else
80          connection = DbConnectionWizard.Connection;
81      }
82    }
83
84    private void HandleConnectionError() {
85      DialogResult result = MessageBox.Show(DbConnectionWizard.Connection.ErrorMessage,
86        "Connect to database failed", MessageBoxButtons.RetryCancel);
87      if (result == DialogResult.Retry)
88        ShowDbConnectionWizard();
89    }
90    public abstract IEnumerable<DbTable> AllTables { get; }
91
92    public virtual void FillMinMaxValuesForTable(DbTable table) {
93      using (DbConnection conn = connection.CreateConnection()) {
94        conn.Open();
95        List<DbColumn> filterColumns = new List<DbColumn>();
96        using (DbCommand cmd = conn.CreateCommand()) {
97          string cmdText = @"select ";
98          foreach (DbColumn col in table.Columns)
99            if (this.IsFilterableDataType(col.SqlDataType) && !this.IsStringDataType(col.SqlDataType)) {
100              cmdText += @"min(" + EscapeSqlString(col.ColumnName) + "), max(" + EscapeSqlString(col.ColumnName) + "), ";
101              filterColumns.Add(col);
102            }
103          cmdText += "count(*) from " + EscapeSqlString(table.OwnerName) + "." + EscapeSqlString(table.TableName);
104          cmd.CommandText = cmdText;
105
106          using (DbDataReader reader = cmd.ExecuteReader()) {
107            reader.Read();
108            for (int i = 0; i < reader.FieldCount - 1; i++) {
109              if (i % 2 == 0)
110                filterColumns[i / 2].MinValue = reader.GetValue(i).ToString();
111              else
112                filterColumns[i / 2].MaxValue = reader.GetValue(i).ToString();
113            }
114            table.TotalRows = reader.GetInt32(reader.FieldCount - 1);
115            reader.Close();
116          }//end using reader
117          // cmd.Dispose();
118        }//end using command
119        conn.Close();
120      }//end using connection
121      table.UpdateInformationLoaded();
122    }
123
124    public void FillAffectedRowsForTable(DbTable table) {
125      string cmdText = @"select count(*) from " + EscapeSqlString(table.OwnerName) + "." + EscapeSqlString(table.TableName) + " where ";
126      bool executeSql = false;
127      foreach (DbColumn col in table.SelectedColumns) {
128        if (col.FilterColumn) {
129          executeSql = true;
130          if (IsNumericDataType(col.SqlDataType)) {
131            string min = this.ConvertDouble(double.Parse(col.MinValue));
132            string max = this.ConvertDouble(double.Parse(col.MaxValue));
133            cmdText += EscapeSqlString(col.ColumnName) + ">=" + min + " and " + EscapeSqlString(col.ColumnName) + "<=" + max + " and ";
134          } else if (IsDateDataType(col.SqlDataType)) {
135            string x = this.ConvertDate(DateTime.Parse(col.MinValue));
136            string y = this.ConvertDate(DateTime.Parse(col.MaxValue));
137            cmdText += EscapeSqlString(col.ColumnName) + ">=" + x + " and " + EscapeSqlString(col.ColumnName) + "<=" + y + " and ";
138          } else if (IsStringDataType(col.SqlDataType))
139            cmdText += EscapeSqlString(col.ColumnName) + " like '%" + EscapeSqlString(col.LikeValue) + "%' and ";
140        }
141      }
142      if (!executeSql) {
143        if (table.IsSelected)
144          table.AffectedRows = table.TotalRows;
145        else
146          table.AffectedRows = 0;
147      } else {
148        cmdText = cmdText.Remove(cmdText.Length - 5);
149        using (DbConnection conn = Connection.CreateConnection()) {
150          conn.Open();
151          using (DbCommand cmd = conn.CreateCommand()) {
152            cmd.CommandText = cmdText;
153            table.AffectedRows = Convert.ToInt32(cmd.ExecuteScalar());
154          }//end using cmd
155          conn.Close();
156        }//end using connection
157      }
158    }
159
160    public ColumnGroup CreateColumnGroup(DbTable table) {
161      //select clause
162      string sqlString = "select ";
163      foreach (DbColumn col in table.SelectedColumns) {
164        sqlString += EscapeSqlString(table.TableName) + "." + EscapeSqlString(col.ColumnName) + ", ";
165      }
166      sqlString = sqlString.Remove(sqlString.Length - 2);
167      sqlString += " from " + EscapeSqlString(table.OwnerName) + "." + EscapeSqlString(table.TableName);
168
169      //where clause
170      bool addedWhereclause = false;
171      foreach (DbColumn col in table.SelectedColumns) {
172        if (col.FilterColumn) {
173          if (!addedWhereclause) {
174            sqlString += " where ";
175            addedWhereclause = true;
176          }
177          if (IsNumericDataType(col.SqlDataType))
178            sqlString += EscapeSqlString(col.ColumnName) + ">=" + col.MinValue + " and " + EscapeSqlString(col.ColumnName) + "<=" + col.MaxValue + " and ";
179          else if (IsDateDataType(col.SqlDataType)) {
180            string x = this.ConvertDate(DateTime.Parse(col.MinValue));
181            string y = this.ConvertDate(DateTime.Parse(col.MaxValue));
182            sqlString += EscapeSqlString(col.ColumnName) + ">=" + x + " and " + EscapeSqlString(col.ColumnName) + "<=" + y + " and ";
183          } else if (IsStringDataType(col.SqlDataType))
184            sqlString += EscapeSqlString(col.ColumnName) + " like '%" + EscapeSqlString(col.LikeValue) + "%' and ";
185        }
186      }
187      if (addedWhereclause)
188        sqlString = sqlString.Remove(sqlString.Length - 5);
189
190      this.actTable = table;
191      ColumnGroup columnGroup = CreateColumnGroup(sqlString);
192      columnGroup.Name = table.TableName;
193      this.actTable = null;
194      return columnGroup;
195    }
196
197    public ColumnGroup CreateColumnGroup(string sqlString) {
198      ColumnGroup columnGroup = new ColumnGroup();
199      bool initColumns = true;
200      string dbDataType;
201      int actRow = 0;
202
203      using (DbConnection conn = connection.CreateConnection()) {
204        conn.Open();
205        using (DbCommand cmd = conn.CreateCommand()) {
206          cmd.CommandText = sqlString;
207          using (DbDataReader reader = cmd.ExecuteReader()) {
208            while (reader.Read()) {
209              //create & initialize columns
210              if (initColumns) {
211                for (int i = 0; i < reader.FieldCount; i++) {
212                  dbDataType = reader.GetDataTypeName(i);
213                  if (IsDateDataType(dbDataType))
214                    columnGroup.AddColumn(new DateTimeColumn(reader.GetName(i)));
215                  else if (IsNumericDataType(dbDataType))
216                    columnGroup.AddColumn(new DoubleColumn(reader.GetName(i)));
217                  else
218                    columnGroup.AddColumn(new StringColumn(reader.GetName(i)));
219                }
220                initColumns = false;
221              }
222              //fill columns with values
223              for (int i = 0; i < reader.FieldCount; i++) {
224                if (reader.IsDBNull(i)) {
225                  columnGroup.Columns.ElementAt(i).AddValue(null);
226                } else if (columnGroup.Columns.ElementAt(i).DataType == typeof(double?)) {
227                  try { columnGroup.Columns.ElementAt(i).AddValue(Convert.ToDouble(reader.GetValue(i))); }
228                  catch (DbException) { columnGroup.Columns.ElementAt(i).AddValue(null); }
229                } else if (columnGroup.Columns.ElementAt(i).DataType == typeof(DateTime?)) {
230                  try { columnGroup.Columns.ElementAt(i).AddValue(reader.GetDateTime(i)); }
231                  catch (DbException) { columnGroup.Columns.ElementAt(i).AddValue(null); }
232                } else {
233                  try { columnGroup.Columns.ElementAt(i).AddValue(reader.GetValue(i).ToString()); }
234                  catch (DbException) { columnGroup.Columns.ElementAt(i).AddValue(null); }
235                }
236              }
237              actRow++;
238              if (actRow % 1000 == 0)
239                FireNewRowLoaded(actRow);
240            }
241            if (initColumns) {
242              for (int i = 0; i < reader.FieldCount; i++) {
243                if (reader.GetName(i).ToUpper().Contains("ZEIT"))
244                  columnGroup.AddColumn(new DateTimeColumn(reader.GetName(i)));
245                else
246                  columnGroup.AddColumn(new DoubleColumn(reader.GetName(i)));
247              }
248            }
249            reader.Close();
250          }  //end using reader
251        } //end using command
252      } // using connection
253
254      return columnGroup;
255    }
256  }
257}
Note: See TracBrowser for help on using the repository browser.