public AS400ImportExport(SqlDatabaseConnection sqlDatabaseConnection, RootPathObject rootPath,
string tableName, string schemaName)
{
_conn = sqlDatabaseConnection;
_tableName = tableName;
_schemaName = schemaName;
}
public static void DeleteDirectoryFilesFromDb(SqlDatabaseConnection conn, string tableName, string schemaName,
IImportExportFactory factory, RootPathObject rootPath, List <string> paths)
{
try
{
var tableNames = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName)
.GetAllTableNames();
foreach (var table in tableNames)
{
var tableNameId = $"[{table.SchemaName}].[{table.TableName}]";
Logger.Info($"Purging table: {tableNameId}");
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText =
$@"DROP TABLE IF EXISTS {tableNameId}"
};
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Logger.Error(e, e.Message);
Logger.Error(e, "Skipping delete");
}
}
public static void ArchiveFileAtPath(string path, RootPathObject rootPath, Settings settings)
{
try
{
var archiveFileName = Path.Join(rootPath.ArchivePath, Path.GetFileName(path));
switch (rootPath.FileReadMode)
{
case Constants.FileModeLocal:
var archiveFilePath = GetUniqueFilePath(archiveFileName);
File.Copy(path, archiveFilePath, false);
DeleteFileAtPath(path, rootPath, settings, false);
break;
case Constants.FileModeFtp:
using (var client = GetFtpClient())
{
try
{
// var localFileStream = GetStream(path, rootPath.FileReadMode, true);
// client.Upload(localFileStream.Stream, archiveFileName);
// localFileStream.Close();
client.MoveFile(path, archiveFileName);
DeleteFileAtPath(path, rootPath, settings, true);
}
finally
{
client.Disconnect();
}
}
break;
case Constants.FileModeSftp:
using (var client = GetSftpClient())
{
try
{
// var localFileStream = GetStream(path, rootPath.FileReadMode, true);
// client.UploadFile(localFileStream.Stream, archiveFileName);
// localFileStream.Close();
var file = client.Get(path);
file.MoveTo(archiveFileName);
DeleteFileAtPath(path, rootPath, settings, true);
}
finally
{
client.Disconnect();
}
}
break;
}
}
catch (Exception e)
{
Logger.Error(e, $"Unable to archive file {path}");
Logger.Error(e, e.Message);
}
}
public FixedWidthColumnsImportExport(SqlDatabaseConnection sqlDatabaseConnection, RootPathObject rootPath,
string tableName, string schemaName)
{
_conn = sqlDatabaseConnection;
_rootPath = rootPath;
_tableName = tableName;
_schemaName = schemaName;
}
public ExcelImportExport(SqlDatabaseConnection sqlDatabaseConnection, RootPathObject rootPath,
string tableName, string schemaName)
{
_conn = sqlDatabaseConnection;
_rootPath = rootPath;
_tableName = tableName;
_schemaName = schemaName;
// required for parsing dos era excel files
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
}
private static long ImportRecordsForPath(IImportExportFactory factory, SqlDatabaseConnection conn,
RootPathObject rootPath,
string tableName, string schemaName, string path, bool downloadToLocal = false, long limit = long.MaxValue)
{
Logger.Info($"Preparing to load file: {path} from {rootPath.FileReadMode}");
var importExportFile = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName);
Logger.Info($"Begin loading file: {path} from {rootPath.FileReadMode}");
var rowsWritten = importExportFile.ImportTable(path, rootPath, downloadToLocal, limit);
Logger.Info($"Loaded file {path} with {rowsWritten} rows from {rootPath.FileReadMode}");
return(rowsWritten);
}
public static void LoadDirectoryFilesIntoDb(
IImportExportFactory factory, SqlDatabaseConnection conn, RootPathObject rootPath,
string tableName, string schemaName, List <string> paths, bool downloadToLocal, long recordLimit = long.MaxValue, int fileLimit = int.MaxValue
)
{
Logger.Info($"Loading files:\n {JsonConvert.SerializeObject(paths, Formatting.Indented)}");
DeleteDirectoryFilesFromDb(conn, tableName, schemaName, factory, rootPath, paths);
foreach (var path in paths.Take(fileLimit))
{
ImportRecordsForPath(factory, conn, rootPath, tableName, schemaName, path, downloadToLocal,
recordLimit);
}
if (paths.Count > 0)
{
Logger.Info($"Adding indexes for {JsonConvert.SerializeObject(paths, Formatting.Indented)}");
var importExportFile = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName);
AddIndexesToTables(importExportFile, conn);
Logger.Info($"Added indexes for {JsonConvert.SerializeObject(paths, Formatting.Indented)}");
}
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = -1)
{
var rowsRead = 0;
var rowsSkipped = 0;
List <string> headerColumns = new List <string>();
List <int> columnIndexes;
List <ExcelCell> orderedExcelCells = rootPath.ModeSettings.ExcelModeSettings.GetOrderedExcelCells();
Dictionary <string, object> excelCellsValues = new Dictionary <string, object>();
if (orderedExcelCells.Count > 0)
{
var cellsStreamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
using (var memoryStream = new MemoryStream())
{
cellsStreamWrapper.Stream.CopyTo(memoryStream);
memoryStream.Position = 0;
using (var reader = ExcelReaderFactory.CreateReader(cellsStreamWrapper.Stream))
{
var currentRow = 0;
foreach (var cell in orderedExcelCells)
{
while (reader.Read() && currentRow < cell.RowIndex)
{
currentRow++;
}
var rawValue = reader.GetValue(cell.ColumnIndex);
if (!excelCellsValues.TryAdd(cell.ColumnName, rawValue))
{
excelCellsValues.Add(cell.GetUniqueName(), rawValue);
}
currentRow++;
}
}
}
// close down stream
cellsStreamWrapper.Close();
}
var streamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
using (var memoryStream = new MemoryStream())
{
streamWrapper.Stream.CopyTo(memoryStream);
memoryStream.Position = 0;
using (var reader = ExcelReaderFactory.CreateReader(memoryStream))
{
// skip lines
if (rootPath.SkipLines > 0)
{
while (reader.Read() && rowsSkipped < rootPath.SkipLines)
{
rowsSkipped++;
}
}
else
{
reader.Read();
}
// get desired column indexes
columnIndexes = rootPath.ModeSettings.ExcelModeSettings.GetAllExcelColumnIndexes();
if (columnIndexes.Count == 0)
{
columnIndexes = Enumerable.Range(0, reader.FieldCount).ToList();
}
// get column names
foreach (var i in columnIndexes)
{
if (rootPath.ModeSettings.ExcelModeSettings.HasHeader)
{
var field = reader.GetValue(i)?.ToString();
if (string.IsNullOrWhiteSpace(field))
{
field = $"NO_HEADER_COLUMN_{i}";
}
if (headerColumns.Contains(field))
{
headerColumns.Add($"{field}_DUPLICATE_{i}");
}
else
{
headerColumns.Add(field);
}
}
else
{
headerColumns.Add($"COLUMN_{i}");
}
}
// setup db table
var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{_schemaName}].[{_tableName}] (");
foreach (var column in headerColumns)
{
querySb.Append(
$"[{column}] VARCHAR({int.MaxValue}),");
}
foreach (var cell in excelCellsValues)
{
querySb.Append(
$"[{cell.Key}] VARCHAR({int.MaxValue}),");
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Debug($"Create table query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = _conn,
CommandText = query
};
cmd.ExecuteNonQuery();
// prepare insert cmd with parameters
querySb = new StringBuilder($"INSERT INTO [{_schemaName}].[{_tableName}] (");
foreach (var column in headerColumns)
{
querySb.Append($"[{column}],");
}
foreach (var cell in excelCellsValues)
{
querySb.Append($"[{cell.Key}],");
}
querySb.Length--;
querySb.Append(") VALUES (");
foreach (var column in headerColumns)
{
var paramName = $"@param{headerColumns.IndexOf(column)}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
foreach (var cell in excelCellsValues)
{
var paramName = $"@param{cell.Key.Replace(" ", "")}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
querySb.Length--;
querySb.Append(");");
query = querySb.ToString();
Logger.Debug($"Insert record query: {query}");
cmd.CommandText = query;
// read records
var trans = _conn.BeginTransaction();
try
{
// read all lines from file
while (reader.Read() && rowsRead < limit)
{
foreach (var column in headerColumns)
{
var rawValue = reader.GetValue(columnIndexes[headerColumns.IndexOf(column)])
?.ToString();
cmd.Parameters[$"@param{headerColumns.IndexOf(column)}"].Value = rawValue;
}
foreach (var cell in excelCellsValues)
{
var rawValue = cell.Value?.ToString();
cmd.Parameters[$"@param{cell.Key.Replace(" ", "")}"].Value = rawValue;
}
cmd.ExecuteNonQuery();
rowsRead++;
// commit every 1000 rows
if (rowsRead % 1000 == 0)
{
trans.Commit();
trans = _conn.BeginTransaction();
}
}
// commit any pending inserts
trans.Commit();
// close down stream
streamWrapper.Close();
}
catch (Exception e)
{
// rollback on error
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
}
}
return(rowsRead);
}
public DelimitedImportExport(SqlDatabaseConnection sqlDatabaseConnection, string tableName, string schemaName, RootPathObject rootPath)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("TableName parameter is required.");
}
if (string.IsNullOrWhiteSpace(schemaName))
{
throw new Exception("SchemaName parameter is required.");
}
if (sqlDatabaseConnection.State == System.Data.ConnectionState.Closed)
{
sqlDatabaseConnection.Open();
}
SQLDatabaseConnection = sqlDatabaseConnection;
TableName = tableName;
SchemaName = schemaName;
Delimiter = rootPath.ModeSettings.DelimitedSettings.GetDelimiter();
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = long.MaxValue)
{
var rowCount = 0;
List <string> headerColumns = new List <string>();
using (DelimitedReader = new DelimitedFileReader(filePathAndName, rootPath, false))
{
DelimitedReader.Delimiter = Delimiter;
DelimitedReader.OnEmptyLine = BlankLine.SkipEntireLine;
DelimitedReader.MaximumLines = 1; //Just read one line to get the header info and/or number of columns.
while (DelimitedReader.ReadLine())
{
int columnCount = 0;
foreach (string field in DelimitedReader.Fields)
{
columnCount++;
if (rootPath.ModeSettings.DelimitedSettings.HasHeader)
{
if (headerColumns.Contains(field))
{
headerColumns.Add($"{field}_DUPLICATE_{columnCount}");
}
else
{
headerColumns.Add(field);
}
}
else
{
headerColumns.Add("Column" + columnCount);
}
}
break;
}
}
if (headerColumns.Count == 0)
{
throw new Exception("Columns are required, check the function parameters.");
}
Logger.Debug($"Headers: {JsonConvert.SerializeObject(headerColumns, Formatting.Indented)}");
if (SQLDatabaseConnection.State != ConnectionState.Open)
{
throw new Exception("A valid and open connection is required.");
}
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
{
if (SQLDatabaseTransaction != null)
{
cmd.Transaction = SQLDatabaseTransaction;
}
// cmd.CommandText = $"DROP TABLE IF EXISTS [{SchemaName}].[{TableName}]";
// cmd.ExecuteNonQuery();
cmd.CommandText = $"CREATE TABLE IF NOT EXISTS [{SchemaName}].[{TableName}] (";
foreach (var columnName in headerColumns)
{
cmd.CommandText +=
$"[{columnName}]" +
$" VARCHAR({int.MaxValue}),"; //The DataType none is used since we do not know if all rows have same datatype
}
cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 1); //Remove the last comma
cmd.CommandText += ");";
Logger.Debug($"Create table SQL: {cmd.CommandText}");
cmd.ExecuteNonQuery(); // Create table
var dt = SQLDatabaseConnection.GetSchema("Columns", new string[] { $"[{SchemaName}].[{TableName}]" });
// Sanity check if number of columns in CSV and table are equal
if (dt.Rows.Count != headerColumns.Count)
{
throw new Exception("Number of columns in CSV should be same as number of columns in the table");
}
// Start of code block to generate INSERT statement.
cmd.CommandText = $"INSERT INTO {SchemaName}.[{TableName}] VALUES (";
int paramCount = 0;
foreach (string columnName in headerColumns)
{
paramCount++;
cmd.CommandText +=
$"@param{paramCount},"; //The DataType none is used since we do not know if all rows have same datatype
}
cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 1); //Remove the last comma
cmd.CommandText += ");";
Logger.Debug($"Insert Row SQL: {cmd.CommandText}");
// Add parameters
paramCount = 0;
foreach (string columnName in headerColumns)
{
paramCount++;
cmd.Parameters.Add(
$"@param{paramCount}"); //The DataType none is used since we do not know if all rows have same datatype
}
// End of code block to generate INSERT statement.
Logger.Debug($"Reading delimited file {filePathAndName}");
//Read CSV once insert statement has been created.
using (DelimitedReader = new DelimitedFileReader(filePathAndName, rootPath, downloadToLocal))
{
DelimitedReader.Delimiter = Delimiter;
DelimitedReader.OnEmptyLine = BlankLine.SkipEntireLine;
DelimitedReader.SkipLines = rootPath.SkipLines;
//Skip the header line.
if (rootPath.ModeSettings.DelimitedSettings.HasHeader)
{
DelimitedReader.SkipLines += 1;
}
var trans = SQLDatabaseConnection.BeginTransaction();
try
{
while (DelimitedReader.ReadLine() && rowCount < limit)
{
int csvColumnCount = 0;
foreach (string fieldValue in DelimitedReader.Fields)
{
csvColumnCount++;
if (cmd.Parameters.IndexOf("@param" + csvColumnCount) != -1)
{
cmd.Parameters["@param" + csvColumnCount].Value =
fieldValue; //Assign File Column to parameter
}
}
cmd.ExecuteNonQuery();
rowCount++; // Count inserted rows.
// commit every 1000 rows
if (rowCount % 1000 == 0)
{
trans.Commit();
trans = SQLDatabaseConnection.BeginTransaction();
}
}
// commit any pending inserts
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
}
}
return(rowCount);
}
public IImportExportFile MakeImportExportFile(SqlDatabaseConnection sqlDatabaseConnection, RootPathObject rootPath, string tableName,
string schemaName)
{
return(new DelimitedImportExport(sqlDatabaseConnection, tableName, schemaName, rootPath));
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = long.MaxValue)
{
var formatCmdDictionary = new Dictionary <string, SqlDatabaseCommand>();
var recordGlobalHeaderMap = new Dictionary <string, string>();
var recordHeaderMap = new Dictionary <string, Dictionary <string, string> >();
var recordMap = new Dictionary <string, Dictionary <string, string> >();
var columnsMap = new Dictionary <string, List <Column> >();
// foreach (var format in AS400.Format25) // POC
foreach (var format in rootPath.ModeSettings.AS400Settings.Formats)
{
if (format.IsGlobalHeader)
{
continue;
}
var schemaName = _schemaName;
var tableName = $"{_tableName}_{format.KeyValue.Name}";
if (format.SingleRecordPerLine)
{
var columns = new List <Column>(format.Columns);
// foreach (var globalFormat in AS400.Format25.FindAll(f => f.IsGlobalHeader)) // POC
foreach (var globalFormat in rootPath.ModeSettings.AS400Settings.Formats.FindAll(f => f.IsGlobalHeader))
{
columns.AddRange(globalFormat.Columns);
}
CreateTable(schemaName, tableName, columns);
formatCmdDictionary[format.KeyValue.Name] = GetInsertCmd(schemaName, tableName, columns);
columnsMap[format.KeyValue.Name] = columns;
}
else
{
var columns = new List <Column>(format.MultiLineColumns);
// foreach (var globalFormat in AS400.Format25.FindAll(f => f.IsGlobalHeader)) // POC
foreach (var globalFormat in rootPath.ModeSettings.AS400Settings.Formats.FindAll(f => f.IsGlobalHeader))
{
columns.AddRange(globalFormat.Columns);
}
CreateTable(schemaName, tableName, columns);
formatCmdDictionary[format.KeyValue.Name] = GetInsertCmd(schemaName, tableName, columns);
columnsMap[format.KeyValue.Name] = columns;
}
recordHeaderMap[format.KeyValue.Name] = new Dictionary <string, string>();
recordMap[format.KeyValue.Name] = new Dictionary <string, string>();
}
// read file into db
var streamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
var file = streamWrapper.StreamReader;
string line;
var recordsInserted = 0;
var trans = _conn.BeginTransaction();
try
{
// read all lines from file
while ((line = file.ReadLine()) != null && recordsInserted < limit)
{
// var keyValue = line.Substring(0, 2; // POC
var keyValue = line.Substring(0, rootPath.ModeSettings.AS400Settings.KeyValueWidth);
// var format = AS400.Format25.FirstOrDefault(f => f.KeyValue.Value == keyValue); // POC
var format = rootPath.ModeSettings.AS400Settings.Formats.FirstOrDefault(f => f.KeyValue.Value == keyValue);
if (format == null)
{
continue;
}
if (format.SingleRecordPerLine)
{
foreach (var column in format.Columns)
{
var valueLength = column.ColumnEnd -
column.ColumnStart + 1;
var rawValue = new string(line.Substring(column.ColumnStart)
.Take(valueLength).ToArray());
if (format.IsGlobalHeader)
{
recordGlobalHeaderMap.TryAdd(column.ColumnName, rawValue);
continue;
}
// TODO handle not global header
}
}
else
{
var tagNameLength = format.MultiLineDefinition.TagNameEnd -
format.MultiLineDefinition.TagNameStart + 1;
var tagName = line.Substring(format.MultiLineDefinition.TagNameStart, tagNameLength).Trim();
var recordId = tagName.Split(format.MultiLineDefinition.TagNameDelimiter).First();
var columnName =
tagName.Substring(tagName.IndexOf(format.MultiLineDefinition.TagNameDelimiter) + 1);
var valueLength = int.Parse(line.Substring(format.MultiLineDefinition.ValueLengthStart,
format.MultiLineDefinition.ValueLengthEnd - format.MultiLineDefinition.ValueLengthStart +
1));
var rawValue = new string(line.Substring(format.MultiLineDefinition.ValueStart)
.Take(valueLength).ToArray());
bool insert = false;
bool headerTrigger = false;
bool recordTrigger = false;
if (format.HeaderRecordKeys.Contains(recordId))
{
insert = !recordHeaderMap[format.KeyValue.Name].TryAdd(tagName, rawValue);
headerTrigger = insert;
}
else
{
if (recordHeaderMap[format.KeyValue.Name].Count != 0)
{
insert = !recordMap[format.KeyValue.Name].TryAdd(columnName, rawValue);
recordTrigger = insert;
}
}
if (insert)
{
var cmd = formatCmdDictionary[format.KeyValue.Name];
if (cmd != null)
{
var columns = columnsMap[format.KeyValue.Name];
foreach (var column in columns)
{
string value;
if (column.IsHeader)
{
if (!recordHeaderMap[format.KeyValue.Name]
.TryGetValue(column.ColumnName, out value))
{
value = "";
}
cmd.Parameters[$"@{GetParamName(column)}"].Value =
column.TrimWhitespace ? value.Trim() : value;
}
else if (column.IsGlobalHeader)
{
if (!recordGlobalHeaderMap.TryGetValue(column.ColumnName, out value))
{
value = "";
}
cmd.Parameters[$"@{GetParamName(column)}"].Value =
column.TrimWhitespace ? value.Trim() : value;
}
else
{
if (!recordMap[format.KeyValue.Name]
.TryGetValue(column.ColumnName, out value))
{
value = "";
}
cmd.Parameters[$"@{GetParamName(column)}"].Value =
column.TrimWhitespace ? value.Trim() : value;
}
}
var hasKey = true;
foreach (var keyColumn in format.MultiLineColumns.Where(c => c.IsKey))
{
if (string.IsNullOrWhiteSpace(cmd.Parameters[$"@{GetParamName(keyColumn)}"].Value
.ToString()))
{
hasKey = false;
break;
}
}
// insert record
if (recordHeaderMap[format.KeyValue.Name].Count > 0 && hasKey)
{
cmd.ExecuteNonQuery();
recordsInserted++;
}
}
// clear storage
recordMap[format.KeyValue.Name].Clear();
if (headerTrigger)
{
recordHeaderMap[format.KeyValue.Name].Clear();
recordHeaderMap[format.KeyValue.Name].TryAdd(tagName, rawValue);
}
if (recordTrigger)
{
recordMap[format.KeyValue.Name].TryAdd(columnName, rawValue);
}
}
}
// commit every 1000 rows
if (recordsInserted % 1000 == 0)
{
trans.Commit();
trans = _conn.BeginTransaction();
}
}
// commit any pending inserts
trans.Commit();
// close down file
streamWrapper.Close();
}
catch (Exception e)
{
// rollback on error
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
return(recordsInserted);
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = -1)
{
var totalRowsRead = 0;
// get the global key
var globalKeyId = "GLOBAL_KEY";
var globalKeyIndexId = "GLOBAL_KEY_INDEX";
var globalKeySb = new StringBuilder();
var globalKeyValue = "";
// load xml doc
var stream = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
XmlDocument doc = new XmlDocument();
doc.Load(stream.Stream);
foreach (var xmlKey in rootPath.ModeSettings.XMLSettings.XmlKeys)
{
var elements = doc.GetElementsByTagName(xmlKey.ElementId);
XmlNode element;
try
{
element = elements[0];
}
catch (Exception e)
{
throw new Exception($"Element {xmlKey.ElementId} is defined as a key and the element does not exist in the file {Path.GetFileName(filePathAndName)}");
}
if (!string.IsNullOrWhiteSpace(xmlKey.AttributeId))
{
if (element.Attributes?[xmlKey.AttributeId] == null)
{
throw new Exception($"Attribute {xmlKey.AttributeId} is defined as a key on element {xmlKey.ElementId} and the attribute is null or not exist in the file {Path.GetFileName(filePathAndName)}");
}
var attribute = element.Attributes[xmlKey.AttributeId];
if (string.IsNullOrWhiteSpace(attribute.Value))
{
throw new Exception($"Attribute {xmlKey.AttributeId} is defined as a key on element {xmlKey.ElementId} and the attribute is null or not exist in the file {Path.GetFileName(filePathAndName)}");
}
globalKeySb.Append(attribute.Value);
}
else
{
var elementValue = element.Value;
if (string.IsNullOrWhiteSpace(elementValue))
{
throw new Exception($"Element {xmlKey.ElementId} is defined as a key and the element value is null in the file {Path.GetFileName(filePathAndName)}");
}
globalKeySb.Append(elementValue);
}
}
globalKeyValue = globalKeySb.ToString();
stream.Close();
// parse xml into multiple tables
DataSet dataSet = new DataSet();
stream = Utility.Utility.GetStream(rootPath.ModeSettings.XMLSettings.XsdFilePathAndName,
rootPath.FileReadMode, downloadToLocal);
dataSet.ReadXmlSchema(stream.Stream);
stream.Close();
dataSet.Locale = CultureInfo.InvariantCulture;
dataSet.EnforceConstraints = false;
stream = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
dataSet.ReadXml(stream.Stream, XmlReadMode.Auto);
stream.Close();
// create and load each table
foreach (DataTable table in dataSet.Tables)
{
var rowsRead = 0;
// create table
var fullTableName = $"{_tableName}_{table.TableName}";
var columns = new List <string>();
for (var i = 0; i < table.Columns.Count; i++)
{
columns.Add(table.Columns[i].ColumnName);
}
// append global key and global key index columns
columns.Add(globalKeyId);
columns.Add(globalKeyIndexId);
// setup db table
var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{_schemaName}].[{fullTableName}] (");
// add the columns
foreach (var column in columns)
{
querySb.Append(
$"[{column}] VARCHAR({int.MaxValue}),");
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Debug($"Create table query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = _conn,
CommandText = query
};
cmd.ExecuteNonQuery();
// prepare insert cmd with parameters
querySb = new StringBuilder($"INSERT INTO [{_schemaName}].[{fullTableName}] (");
foreach (var column in columns)
{
querySb.Append($"[{column}],");
}
querySb.Length--;
querySb.Append(") VALUES (");
foreach (var column in columns)
{
var paramName = $"@param{columns.IndexOf(column)}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
querySb.Length--;
querySb.Append(");");
query = querySb.ToString();
Logger.Debug($"Insert record query: {query}");
cmd.CommandText = query;
// load data
var trans = _conn.BeginTransaction();
try
{
foreach (var row in table.AsEnumerable())
{
var rowIndex = table.AsEnumerable().ToList().IndexOf(row);
foreach (var column in columns)
{
var columnIndex = columns.IndexOf(column);
if (column == globalKeyId)
{
cmd.Parameters[$"@param{columnIndex}"].Value = globalKeyValue;
}
else if (column == globalKeyIndexId)
{
cmd.Parameters[$"@param{columnIndex}"].Value = $"{globalKeyValue}_{rowIndex}";
}
else
{
var rawValue = row[columnIndex]?.ToString();
cmd.Parameters[$"@param{columnIndex}"].Value = rawValue;
}
}
cmd.ExecuteNonQuery();
rowsRead++;
// commit every 1000 rows
if (rowsRead % 1000 == 0)
{
trans.Commit();
trans = _conn.BeginTransaction();
}
if (rowsRead > limit)
{
break;
}
}
// commit any pending inserts
trans.Commit();
totalRowsRead += rowsRead;
}
catch (Exception e)
{
// rollback on error
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
}
return(totalRowsRead);
}
public IImportExportFile MakeImportExportFile(SqlDatabaseConnection sqlDatabaseConnection, RootPathObject rootPath,
string tableName, string schemaName)
{
return(new FixedWidthColumnsImportExport(sqlDatabaseConnection, rootPath, tableName, schemaName));
}
public IEnumerable <Schema> DiscoverSchemas(ServerCallContext context, IImportExportFactory factory, RootPathObject rootPath,
List <string> paths, int sampleSize = 5)
{
var schemaName = Constants.SchemaName;
var tableName = string.IsNullOrWhiteSpace(rootPath.Name)
? new DirectoryInfo(rootPath.RootPath).Name
: rootPath.Name;
var conn = Utility.Utility.GetSqlConnection(Constants.DiscoverDbPrefix);
Utility.Utility.LoadDirectoryFilesIntoDb(factory, conn, rootPath, tableName, schemaName,
paths, false, sampleSize, 1);
var schemas = new List <Schema>();
// foreach (var format in AS400.Format25) // POC
foreach (var format in rootPath.ModeSettings.AS400Settings.Formats)
{
if (format.IsGlobalHeader)
{
continue;
}
tableName = $"{tableName}_{format.KeyValue.Name}";
var schemaId = $"[{schemaName}].[{tableName}]";
var publisherMetaJson = new SchemaPublisherMetaJson
{
RootPath = rootPath
};
var schema = new Schema
{
Id = schemaId,
Name = tableName,
DataFlowDirection = Schema.Types.DataFlowDirection.ReadWrite,
Query = $"SELECT * FROM {schemaId}",
Properties = { },
};
schema = Discover.Discover.GetSchemaForQuery(context, schema, sampleSize, rootPath.Columns);
schema.PublisherMetaJson = JsonConvert.SerializeObject(publisherMetaJson);
schemas.Add(schema);
}
return(schemas);
}
public static void DeleteFileAtPath(string path, RootPathObject rootPath, Settings settings, bool deleteRemote)
{
DeleteFileAtPath(path, rootPath.FileReadMode, settings, deleteRemote);
}
public static IEnumerable <Schema> GetSchemasForDirectory(ServerCallContext context,
IImportExportFactory factory, RootPathObject rootPath, List <string> paths,
int sampleSize = 5)
{
if (paths.Count == 0)
{
return(new List <Schema>());
}
if (sampleSize == 0)
{
sampleSize = 5;
}
if (factory.CustomDiscover)
{
return(factory.MakeDiscoverer().DiscoverSchemas(context, factory, rootPath, paths, sampleSize));
}
var schemaName = Constants.SchemaName;
var tableName = string.IsNullOrWhiteSpace(rootPath.Name)
? new DirectoryInfo(rootPath.RootPath).Name
: rootPath.Name;
var conn = Utility.Utility.GetSqlConnection(Constants.DiscoverDbPrefix);
Utility.Utility.LoadDirectoryFilesIntoDb(factory, conn, rootPath, tableName, schemaName, paths, false, sampleSize,
1);
var tableNames = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName)
.GetAllTableNames();
var schemas = new List <Schema>();
foreach (var table in tableNames)
{
var tableNameId = $"[{table.SchemaName}].[{table.TableName}]";
var schema = new Schema
{
Id = tableNameId,
Name = table.TableName,
DataFlowDirection = Schema.Types.DataFlowDirection.Read,
Properties = { },
};
schema = GetSchemaForQuery(context, schema, sampleSize,
rootPath?.ModeSettings?.FixedWidthSettings?.Columns);
schemas.Add(schema);
}
return(schemas);
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = long.MaxValue)
{
// setup db table
var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{_schemaName}].[{_tableName}] (");
var primaryKeySb = new StringBuilder("PRIMARY KEY (");
var hasPrimaryKey = false;
foreach (var column in rootPath.ModeSettings.FixedWidthSettings.Columns)
{
querySb.Append(
$"[{column.ColumnName}] VARCHAR({int.MaxValue}){(column.IsKey ? " NOT NULL UNIQUE" : "")},");
if (column.IsKey)
{
primaryKeySb.Append($"[{column.ColumnName}],");
hasPrimaryKey = true;
}
}
if (hasPrimaryKey)
{
primaryKeySb.Length--;
primaryKeySb.Append(")");
querySb.Append($"{primaryKeySb});");
}
else
{
querySb.Length--;
querySb.Append(");");
}
var query = querySb.ToString();
Logger.Debug($"Create table query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = _conn,
CommandText = query
};
cmd.ExecuteNonQuery();
// read file into db
var streamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
var file = streamWrapper.StreamReader;
string line;
var rowsRead = 0;
var rowsSkipped = 0;
// prepare insert cmd with parameters
querySb = new StringBuilder($"INSERT INTO [{_schemaName}].[{_tableName}] (");
foreach (var column in rootPath.ModeSettings.FixedWidthSettings.Columns)
{
querySb.Append($"[{column.ColumnName}],");
}
querySb.Length--;
querySb.Append(") VALUES (");
foreach (var column in rootPath.ModeSettings.FixedWidthSettings.Columns)
{
var paramName = $"@param{rootPath.ModeSettings.FixedWidthSettings.Columns.IndexOf(column)}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
querySb.Length--;
querySb.Append(");");
query = querySb.ToString();
Logger.Debug($"Insert record query: {query}");
cmd.CommandText = query;
var trans = _conn.BeginTransaction();
try
{
// skip lines
if (rootPath.SkipLines > 0)
{
while (file.ReadLine() != null && rowsSkipped < rootPath.SkipLines)
{
rowsSkipped++;
}
}
// read all lines from file
while ((line = file.ReadLine()) != null && rowsRead < limit)
{
foreach (var column in rootPath.ModeSettings.FixedWidthSettings.Columns)
{
var rawValue = line.Substring(column.ColumnStart, column.ColumnEnd - column.ColumnStart + 1);
cmd.Parameters[$"@param{rootPath.ModeSettings.FixedWidthSettings.Columns.IndexOf(column)}"].Value = column.TrimWhitespace ? rawValue.Trim() : rawValue;
}
cmd.ExecuteNonQuery();
rowsRead++;
// commit every 1000 rows
if (rowsRead % 1000 == 0)
{
trans.Commit();
trans = _conn.BeginTransaction();
}
}
// commit any pending inserts
trans.Commit();
// close down file
streamWrapper.Close();
}
catch (Exception e)
{
// rollback on error
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
return(rowsRead);
}
/// <summary>
/// Initializes a new instance of the DelimitedFileReader class for the
/// specified file path.
/// </summary>
/// <param name="path">The name of the CSV file to read from</param>
/// <param name="rootPath">The root path object/param>
public DelimitedFileReader(string path, RootPathObject rootPath, bool downloadToLocal)
{
InitCsvReader();
_streamWrapper = Utility.Utility.GetStream(path, rootPath.FileReadMode, downloadToLocal);
_reader = _streamWrapper.StreamReader;
}
