private static string BuildTableSelectSql(TableInfo table)
 {
     var sql = string.Format("SELECT {0} FROM {1};",
         string.Join(",", table.Columns.OrderBy(c => c.OrdinalPosition).Select(c => FormatColumn(table, c))),
         table.InputTableName);
     return sql;
 }
Ejemplo n.º 2
0
        public void Output(TableInfo table, DataTable data)
        {
            var outputFilename = string.Format("{0}.csv", data.TableName);

            _logger.Info("Writing data to {0}...", outputFilename);

            using (var stringWriter = new StringWriter())
            using (var csv = new CsvWriter(stringWriter))
            {
                foreach (DataColumn column in data.Columns)
                {
                    csv.WriteField(column.ColumnName);
                }
                csv.NextRecord();
                foreach (var row in data.AsEnumerable())
                {
                    foreach (var item in row.ItemArray)
                    {
                        csv.WriteField(item.ToString());
                    }
                    csv.NextRecord();
                }

                File.WriteAllText(outputFilename, stringWriter.ToString());
            }

            _logger.Info("Done writing data to {0}.", outputFilename);
        }
Ejemplo n.º 3
0
        public string BuildIndexCreateSql(TableInfo table, IndexInfo index)
        {
            var indexName = string.Format("{0}_{1}", table.OutputTableName, index.Name);

            var unique = index.IsUnique ? "UNIQUE " : "";

            return string.Format("CREATE {0}INDEX {1} ON {2} ({3});",
                                unique, _escaper(indexName), _escaper(table.OutputTableName),
                                string.Join(",", index.ColumnNames.Select(c => _escaper(c))));
        }
Ejemplo n.º 4
0
 public void ConfigureTableInsert(IDbCommand command, TableInfo table)
 {
     var columns = table.Columns.OrderBy(c => c.OrdinalPosition).ToList();
     command.CommandText = BuildTableInsertSql(table, columns);
     foreach (var column in columns)
     {
         var parameter = command.CreateParameter();
         ConfigureParameter(parameter, column);
         command.Parameters.Add(parameter);
     }
 }
Ejemplo n.º 5
0
        public void Output(TableInfo table, DataTable data)
        {
            var outputFilename = string.Format("{0}.json", data.TableName);

            _logger.Info("Writing data to {0}...", outputFilename);

            var json = _jsonFunc(data);
            if (!string.IsNullOrEmpty(json))
                File.WriteAllText(outputFilename, json);

            _logger.Info("Finished writing data to {0}.", outputFilename);
        }
Ejemplo n.º 6
0
        public void Output(TableInfo table, DataTable data)
        {
            var outputFilename = string.Format("{0}.xml", table.OutputTableName);

            _logger.Info("Writing data to {0}...", outputFilename);

            using (var writer = new StringWriter())
            {
                data.WriteXml(writer, XmlWriteMode.IgnoreSchema, false);

                File.WriteAllText(outputFilename, writer.ToString());
            }
            _logger.Info("Finished writing data to {0}.", outputFilename);
        }
 private static string FormatColumn(TableInfo table, ColumnInfo column)
 {
     OleDbType dbType;
     if (table.IsDbTypeOverridden(column.Name, out dbType))
     {
         switch (dbType)
         {
             case OleDbType.Binary:
                 return string.Format("CAST({0} as Blob) as {0}", column.Name);
             default:
                 throw new InvalidOperationException(string.Format("Unsupported dbType {0} for column {1}", dbType, column.Name));
         }
     }
     return column.Name;
 }
        public DataTable BuildDataTable(TableInfo table)
        {
            _logger.Info("Getting the {0} data...", table.OutputTableName);

            var dataTable = new DataTable(table.OutputTableName);
            using (var selectCommand = _conn.CreateCommand())
            {
                selectCommand.CommandText = BuildTableSelectSql(table);
                using (var dataAdapter = new OleDbDataAdapter(selectCommand))
                {
                    dataAdapter.Fill(dataTable);
                }
            }

            if (_trimSpaces)
                TrimSpaces(dataTable);

            _logger.Info("Finished getting the {0} data.", table.OutputTableName);

            return dataTable;
        }
        public DataTable BuildDataTable(TableInfo table)
        {
            var dataTable = new DataTable(table.OutputTableName);
            dataTable.Columns.Add(new DataColumn("category", typeof(string)) { MaxLength = 255 });
            dataTable.Columns.Add(new DataColumn("setting", typeof(string)) { MaxLength = 255 });
            dataTable.Columns.Add(new DataColumn("value", typeof(string)) { MaxLength = 255 });

            _logger.Info("Getting the {0} data...", table.OutputTableName);

            var iniData = new FileIniDataParser().ReadFile(_iniFile);
            foreach (var section in iniData.Sections)
            {
                foreach (var key in section.Keys)
                {
                    dataTable.Rows.Add(new object[] { section.SectionName, key.KeyName, key.Value });
                }
            }

            _logger.Info("Finished getting the {0} data.", table.OutputTableName);

            return dataTable;
        }
Ejemplo n.º 10
0
        private static void AddColumn(TableInfo tableInfo, DataRow row)
        {
            const string numericPrecisionColumn = "NUMERIC_PRECISION";
            const string numericScaleColumn = "NUMERIC_SCALE";
            const string characterMaximumLengthColumn = "CHARACTER_MAXIMUM_LENGTH";

            var columnName = row["COLUMN_NAME"].ToString();
            if (tableInfo.IsIgnoredColumn(columnName))
                return;

            var dbType = (OleDbType) row["DATA_TYPE"];
            OleDbType overiddenDbType;
            if (tableInfo.IsDbTypeOverridden(columnName, out overiddenDbType))
                dbType = overiddenDbType;

            var ordinalPosition = (Int64) row["ORDINAL_POSITION"];
            int? numericPrecision = null;
            if (row[numericPrecisionColumn] != DBNull.Value)
            {
                numericPrecision = int.Parse(row[numericPrecisionColumn].ToString());
            }
            int? numericScale = null;
            if (row[numericScaleColumn] != DBNull.Value)
            {
                numericScale = int.Parse(row[numericScaleColumn].ToString());
            }
            long? maxLength = null;
            if (row[characterMaximumLengthColumn] != DBNull.Value)
            {
                maxLength = long.Parse(row[characterMaximumLengthColumn].ToString());
            }

            long overriddenMaxLength;
            if (tableInfo.IsMaxLengthOverridden(columnName, out overriddenMaxLength))
            {
                maxLength = overriddenMaxLength;
            }

            switch (dbType)
            {
                case OleDbType.Char:
                case OleDbType.Integer:
                case OleDbType.Numeric:
                case OleDbType.Boolean:
                case OleDbType.DBDate:
                case OleDbType.Binary:
                    var columnInfo = new ColumnInfo(columnName, ordinalPosition, dbType, maxLength, numericPrecision,
                                                    numericScale,
                                                    tableInfo.PrimaryKey != null && columnName.Equals(tableInfo.PrimaryKey));
                    tableInfo.AddColumn(columnInfo);
                    break;
                default:
                    throw new InvalidOperationException(string.Format("Unknown database type: {0}", dbType));
            }
        }
Ejemplo n.º 11
0
        private static void AddIndexes(TableInfo tableInfo, DataView allIndexes)
        {
            const string viewColumnName = "COLUMN_NAME";
            const string viewExpression = "EXPRESSION";

            var table = tableInfo.InputTableName;

            allIndexes.RowFilter = string.Format("TABLE_NAME = '{0}'",  table);

            foreach (var indexName in GetIndexNames(allIndexes))
            {
                allIndexes.RowFilter = string.Format("TABLE_NAME = '{0}' AND INDEX_NAME = '{1}'", table, indexName);
                if (allIndexes.Count == 1)
                {
                    var columnName = allIndexes[0][viewColumnName].ToString();
                    var expression = allIndexes[0][viewExpression].ToString();
                    if (!tableInfo.IsIgnoredColumn(columnName))
                        tableInfo.AddIndex(new IndexInfo(indexName, expression, columnName,
                                                         tableInfo.IsUniqueColumns(new[] {columnName})));
                }
                else if (allIndexes.Count > 1)
                {
                    var expression = allIndexes[0][viewExpression].ToString();
                    var columnNames = (from DataRowView row in allIndexes select row[viewColumnName].ToString()).ToList();
                    if (!columnNames.Any(tableInfo.IsIgnoredColumn))
                        tableInfo.AddIndex(new IndexInfo(indexName, expression, columnNames,
                                                         tableInfo.IsUniqueColumns(columnNames)));
                }
            }
        }
Ejemplo n.º 12
0
        public void Output(TableInfo table, DataTable data)
        {
            if (_conn == null)
                return;

            var tableName = data.TableName;
            _logger.Info("Inserting the {0} data...", tableName);

            using (var insertCommand = _conn.CreateCommand())
            {
                _sqlBuilder.ConfigureTableInsert(insertCommand, table);
                using (var transaction = _conn.BeginTransaction())
                {
                    insertCommand.Transaction = transaction;
                    InsertData(data, insertCommand);
                    transaction.Commit();
                }
            }

            _logger.Info("Finished inserting the {0} data.", tableName);
        }
Ejemplo n.º 13
0
 public string BuildTableCreateSql(TableInfo table)
 {
     return string.Format("CREATE TABLE {0} ({1});", _escaper(table.OutputTableName),
         string.Join(",", table.Columns.OrderBy(c => c.OrdinalPosition).Select(BuildCreateTableColumnSql)));
 }
Ejemplo n.º 14
0
        private string BuildTableInsertSql(TableInfo table, IEnumerable<ColumnInfo> columns)
        {
            var columnNames = columns.Select(c => c.Name).ToList();

            var sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2});",
                                    _escaper(table.OutputTableName),
                                    string.Join(",", columnNames.Select(c => _escaper(c))),
                                    string.Join(",", columnNames.Select(c => _parameterNameBuilder(c))));
            return sql;
        }