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; }
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); }
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)))); }
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); } }
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); }
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; }
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)); } }
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))); } } }
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); }
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))); }
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; }