Free cookie consent management tool by TermsFeed Policy Generator

source: branches/HeuristicLab.DataImporter/HeuristicLab.DataImporter.DbExplorer.MsSqlServer/SqlServerExplorer.cs @ 12296

Last change on this file since 12296 was 9627, checked in by mkommend, 11 years ago

#1734: Improved data importer usability:

  • new closing message
  • rename column reacts on lost focus
  • updated copyright info
File size: 4.5 KB
RevLine 
[7629]1#region License Information
2/* HeuristicLab
[9627]3 * Copyright (C) 2002-2013 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
[7629]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.Data.Common;
25using System.Globalization;
26using HeuristicLab.DataImporter.DbExplorer.Interfaces;
27
28namespace HeuristicLab.DataImporter.DbExplorer.MsSqlServer {
29  public class SqlServerExplorer : DbExplorerBase {
30
31    protected override string EscapeSqlString(string s) {
32      return "[" + s + "]";
33    }
34
35    private enum NumericDataType { Numeric, Decimal, Tinyint, Smallint, Int, bigint, Float, Real }
36    public override bool IsNumericDataType(string type) {
37      try {
38        Enum.Parse(typeof(NumericDataType), type, true);
39        return true;
40      }
41      catch (ArgumentException) { }
42      return false;
43    }
44
45    private enum DateDataType { DateTime, SmallDateTime }
46    public override bool IsDateDataType(string type) {
47      try {
48        Enum.Parse(typeof(DateDataType), type, true);
49        return true;
50      }
51      catch (ArgumentException) { }
52      return false;
53    }
54
55    private enum StringDataType { Char, NChar, Varchar, NVarchar }
56    public override bool IsStringDataType(string type) {
57      try {
58        Enum.Parse(typeof(StringDataType), type, true);
59        return true;
60      }
61      catch (ArgumentException) { }
62      return false;
63    }
64
65    public override bool IsFilterableDataType(string type) {
66      return IsNumericDataType(type) || IsDateDataType(type) || IsStringDataType(type);
67    }
68
69    protected override string ConvertDate(DateTime date) {
70      string ret = "'" + date.ToString(CultureInfo.InvariantCulture) + "'";
71      return ret;
72    }
73
74    protected override string ConvertDouble(double value) {
75      return value.ToString(CultureInfo.InvariantCulture);
76    }
77
78    public override string MenuItemDescription {
79      get { return "Connect to Microsoft SqlServer"; }
80    }
81
82    private SqlServerConnectionWizard wizard;
83    protected override IDbConnectionWizard DbConnectionWizard {
84      get {
85        if (wizard == null)
86          wizard = new SqlServerConnectionWizard();
87        return wizard;
88      }
89    }
90
91    public override IEnumerable<DbTable> AllTables {
92      get {
93        if (Connection == null)
94          throw new InvalidOperationException("Connection to database not correctly set!");
95        List<DbTable> allTables = new List<DbTable>();
96        using (DbConnection conn = Connection.CreateConnection()) {
97          conn.Open();
98          using (DbCommand cmd = conn.CreateCommand()) {
99            cmd.CommandText = @"select table_schema,table_name from INFORMATION_SCHEMA.tables";
100            using (DbDataReader reader = cmd.ExecuteReader()) {
101              while (reader.Read()) {
102                allTables.Add(new DbTable(reader.GetString(0), reader.GetString(1)));
103              }
104              reader.Close();
105            }//end using reader
106            cmd.Dispose();
107          }//end using command
108
109          //fill columns for table
110          foreach (DbTable table in allTables) {
111            using (DbCommand cmd = conn.CreateCommand()) {
112              cmd.CommandText = @"select column_name,data_type from INFORMATION_SCHEMA.columns where table_name = '" + table.TableName + "'";
113              using (DbDataReader reader = cmd.ExecuteReader()) {
114                while (reader.Read()) {
115                  table.AddColumn(new DbColumn(reader.GetString(0), reader.GetString(1)));
116                }
117                reader.Close();
118              }//end using reader
119            }//end using command
120
121          }//end foreach table
122          conn.Close();
123        }//end using connection
124        allTables.Sort(new DbTableComparator());
125        return allTables;
126      }
127    }
128  }
129}
Note: See TracBrowser for help on using the repository browser.